I have a table in a SQL Server database with an NTEXT column. This column may contain data that is enclosed with double quotes. When I query for this column, I want to remove these leading and trailing quotes. For example: "this is a test message" should become this is a test message I know of the LTRIM and RTRIM functions but th...
Table1 has a list of items. Table2 has a list of groups the items can be associated with. Table3 is a cross-reference between 1 and 2. The groups in table 2 are set up in hierarchical fashion. Key ParentKey Name 1 NULL TopGroup1 2 NULL TopGroup2 3 1 MiddleGroup1 4 2 Middle...
So I've got some data. There are entities. Entities have an arbitrary number of items. Items can be one of a defined set of types. An entity can have more than one item of a given type. I can get a list of items that an entity has. What I want is to get a list of types that an entity doesn't have an item for. Here's my schema: entitie...
I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: SELECT emailID, fromEmail, subject, timestamp, MIN(read) as read FROM incomingEmails WHERE toUserID = '$userID' GROUP BY LOWER(fromEmail) ORDER BY timestamp DESC The query almost works as I want it--it s...
I'm with a very strange problem. I've a table with more than 800.000 records and 2GB mdf Database. When I try to get the last records: I'm only getting the records until one month ago, the last ones doesn't show up. Dim Items = From Item In DB.Items _ Where Item.CatID = CatID _ Order By Item.PubDate Descending ...
Hi, If I have a application that accepts a query that returns 4 columns, 1 of which is an extra info column that can be empty, and the column doesn't exist in the database I am using how could I 'create' it so that the data retrieved using the query always has 4 columns, 1 nbeing empty? ...
Hi folks, I've created a query that groups production data on ISO week by using this query SELECT to_char(wid_date,'IYYY'), to_char(wid_date,'IW'), tonnes FROM production WHERE to_char(wid_date,'IYYY') = '2009' GROUP BY to_char(wid_date,'IYYY'), to_char(wid_date,'IW') The problem is that our "production weeks" don't follow the ISO ...
I need to test some asp.net code that handles timeout exceptions from a call to mssql database. I'm about to reduce my CommandTimeout parameter on the connection however this might cause the errors before reaching the my code. Is their a simple way to cause the database to timeout 'on cue'? ...
Currently what i am doing for transaction management is: Connection connection = getConnection(); connection.setAutoCommit(false); updateTableX ( connection, ... ); updateTableY ( connection, ... ); connection.commit(); closeConnection(); I would like to know, if it is possible to avoid closing the connection in my 'updateTableX' meth...
I created a standard ASP.NET MVC project and then added a SQL Express database in the App_Data folder. The connection string is recorded into the web.config file. I then created a LINQ to SQL data model for the database. I want to test the data model from my Test project but I don't know how to go about this correctly because I am using ...
For example I have the following tables resulting from: CREATE TABLE A (Id int, BId int, Number int) CREATE TABLE B (Id int, Number decimal(10,2)) GO INSERT INTO A VALUES(1, 3, 10) INSERT INTO B VALUES(3, 50) INSERT INTO A VALUES(2, 5, 20) INSERT INTO B VALUES(5, 671.35) GO And I run the following query multiple times: SELECT * FROM ...
Hi I need a small help regarding writing a simple query. I want to display results based on max number of employees in a dept. Here is my table empid dept sal emp001 d001 10000 emp002 d001 10000 emp003 d002 20000 emp004 d001 10000 emp005 ...
Say I have a query like this: select ((amount1 - amount2)/ amount1) as chg from t1 where ((amount1 - amount2)/ amount1) > 1 OR ((amount1 - amount2)/ amount1) < 0.3 Is there a way I can perform the arithmetic calculation only once instead of doing it thrice as in the above query ? EDIT: I am not sure if the datab...
I am trying to update table A with data from table B. I thought I could do something like: UPDATE A SET A.name = B.name WHERE A.id = B.id but alas, this does not work. Anyone have an idea of how I can do this? ...
topic.php $id = isset($_GET['id']) ? intval($_GET['id']) : 0; $query = mysql_query("SELECT * FROM topics WHERE id = $id"); $row = mysql_fetch_assoc($query); $title = htmlspecialchars($row['title']); $text = bbcode($row['text']); view/topic.php <h1><?=$title?></h1> <p><?=$text?></p> <h1>Replies</h1> $q = mysql_query("SELECT * FRO...
Hey all. I'm not sure how I could express the following query in C# using Linq to SQL. Here is a short snippet of the pure SQL: select t1.WholesalerID, t1.RetailerID, sum(t1.Col1) as 'Column 1', sum(t2.Col1) as 'Other Column 1', (sum(t1.Col1) - sum(t2.Col1)) as 'Column 1 Difference', sum(t1.Col2) as 'Column 2', sum...
A friend asked me for help on building a query that would show how many pieces of each model were sold on each day of the month, showing zeros when no pieces were sold for a particular model on a particular day, even if no items of any model are sold on that day. I came up with the query below, but it isn't working as expected. I'm only ...
For eaxmple, LINQ to SQL is sending the following: exec sp_executesql N'SELECT [t0].[HomeID], [t0].[Bedrooms], [t0].[ImageURL], [t0].[Price], [t0].[Available], [t0].[Description] FROM [dbo].[Homes] AS [t0] WHERE ([t0].[Description] LIKE @p0) AND ([t0].[Available] = @p1) AND ([t0].[Price] >= @p2) AND ([t0]...
i have two fields with a string in it, and i want a combination of those in a third field like: UPDATE table SET field3=field1 . '_' . field2 what would be the right syntax for that? it is mysql okay that CONCAT Thing worked, is there a function to convert those fields to lowercase?? ...
I need to pass an array of "id's" to SP, delete all rows from the table EXCEPT the rows that match id's in the array. How can I do it in a most simple way? ...