sql

C# and SQL Server 2005: Return value and update in one batch

Hi, Just curious whether it is reliable way to do following: I need to get some value and then update it, optimally in one batch. SELECT X FROM Y //will it be returned if executed in one batch? UPDATE Y SET X... Executed as Reader? I am not sure whether it will return if there is the UPDATE statetement. And one extra question, what if...

C# SqlCommand - cannot use parameters for column names, how to resolve?

Hi, Is there any way how to do that? This does not work: SqlCommand command = new SqlCommand("SELECT @slot FROM Users WHERE name=@name; "); prikaz.Parameters.AddWithValue("name", name); prikaz.Parameters.AddWithValue("slot", slot); The only thing I can think of is to use SP and declare and set the variable for the column. Seems to me ...

Selecting a user's friend's events

I have these information : Table "Users" => **Id** **Name** 1 a 2 b 3 c 4 d 5 e Table "Friends" => **SenderId** **ReceiverId** **State** 1 2 x 2 3 ok 3 1 ok 3...

SQL Trying to select only the first record out of several returned.

I have a query that returns students and their roommates. The idea is to have everyone show up in the first column and then column 2 and 3 to have each roommate. Column 3 could be blank, but columns 1 and 2 will always have results. My query is returning the correct data with the exception of when I there are 3 people in the room, I w...

SQL - Is it better to do a subquery here or an extended WHERE clause?

The CUST table below will be joined with ~10 tables. For this subquery in particular, am I better off simply joining up directly with the Customer table and moving the subquery's 4-part WHERE clause to the main query's WHERE clause? I'm primarily wondering if it is possible to cut down on the amount of processing that SQL Server has to...

AIR Sqlite: SQLEvent.RESULT not firing, but statemet IS executing properly

Ok it looks likes like I have stumbled upon a strange timing issue... I made a quick SQL wrapper class for executing sql statements. However after .execute() is called, the SQLEvent.RESULT event is never fired, but the new entry in the DB is created as it should be. The really really odd part is if I put a setTimeout() just after calli...

UPDATE IF EXISTS Query

Hi, I am trying to UPDATE a record if there is a row in the table. After updating a record I would like to return TRUE from my method. I am using the following query. I am using SQL server 2005. How do I know if my SQL query updated the table? Please let me know. Private Boolean UpdateTable() { string sql = "IF EXISTS(Select A.CNum ...

Saving user text selections architecture question

I have a website that allows users to highlight parts of a HTML document. Assume the document never changes. What are some ways to store user highlights? I thought about storing the beginning and ending character position, but it might not work with multiple user with overlapping highlights. ...

Various ways to query strings?

I'm working on an app that involves a lot of carefully designed strings. I'm in the process of designing the string format and for that I need to know what's possible and what's not when I'm querying the same data. Which ones of these are possible with MySQL? .. and how do I accomplish them? Results which contain this exact string -- ...

How To Move an Updated Record into a History Table?

I have the following table: CREATE TABLE FE_USER ( userid int identity (321,4) CONSTRAINT userid_pk PRIMARY KEY, username varchar(40) ); Its corresponding history table is CREATE TABLE FE_USER_HIST ( userid int, username varchar(40), v_action varchar(50) ); Every time an insert or update is occurred on FE_USER t...

SQLite multiple table INNER JOIN with USING (...) Errors

I am trying to run a query on a SQLite database that INNER JOINs two additional tables: SELECT usages.date AS date, usages.data AS data, stores.number AS store, items.name AS item FROM usages INNER JOIN stores USING (store_id) INNER JOIN items USING (item_id) However, I get the error SQL error: cannot join using colum...

Temporary Table Scope

I am making use of temporary tables #tempTable in my stored procedure - that I make use to run my ASP.net Reports (Reporting services) I am doing something like eg. Code SELECT * INTO #tempTable FROM Contacts WHERE ContactID < 10 Then I use something like SELECT o.* FROM #tempTable t INNER JOIN Orders o ON t.ContactID =o.ContactID ...

How Can i Let Stored Procedure Returns Varchar Value?

Here is my sample: ALTER PROCEDURE EmpFirstName @myParam int, @empFName varchar(20) output AS BEGIN SET NOCOUNT ON; SELECT @empFName = empfname FROM FE_QEMP WHERE empno = @myParam END GO myParam is the input and empFName will carry the output, so the procedure should only take 1 parameter since empFName is the...

SQL Append table queries

I have two tables, say table1 with two rows of data say row11 and row12 and table2 with 3 rows of data sat row21, row22, row23 Can anyone provide me with the SQL to create a query that returns row11 row12 row21 row22 row23 Note I dont want to create a new table just return the data. Tahnks ...

How to delete from multiple tables in MySQL?

I am trying to delete from a few tables at once. I've done a bit of research, and came up with this DELETE FROM `pets` p, `pets_activities` pa WHERE p.`order` > :order AND p.`pet_id` = :pet_id AND pa.`id` = p.`pet_id` However, I am getting this error Uncaught Database_Exception [ 1064 ]: You have ...

mysql query to update field to max(field) + 1

What I want to do is: UPDATE table SET field = MAX(field) + 1 WHERE id IN (1, 3, 5, 6, 8); The semantics of this statement, in my mind, would be first the database would go off and determine for me what the largest value of field is in all of table. It would then add 1 to that value, and assign the resulting value to the field column ...

SQL retrieve yearly repeated event

How to write a sql statement to retrieve repeated yearly event, which mean retrieve all the event regardless year only match with month and date. Select * from Event where [date] = date ? ...

Data Partition in C#

I want to distribute a large amount of data to different C# applications. For example, my table contains millions of records. I would like to specify that first 3 millions records are processed by App1 and next 3 million in another C# application App2 and so on. Table rows are deleted and added as per requirement. Now I want to write a S...

How to 'add' a column to a query result while the query contains aggregate function?

I have a table named 'Attendance' which is used to record student attendance time in courses. This table has 4 columns, say 'id', 'course_id', 'attendance_time', and 'student_name'. An example of few records in this table is: 23    100    1/1/2010 10:00:00    Tom 24    100    1/1/2010 10:20:00    Bob 25    187    1/2/2010 08:01:01    ...

SQL Join Retrives more rows than expected

Suppose I have the following tables Students (StudentId int Pk, StudentName nvarchar) Lectures (LectureId Pk, StartDate, EndDate) Enrollment (StudentID, LectureID) When I execute the following query: select StudentID From Students I get 8 rows.. And when i execute: select S.StudentID From Students S join Enrollment En On S.Student...