What is wrong with this SQL query?

Question

I run this query

"insert into students (StudentName) values ('reza');insert into  Records (RecordValue,StudentID)" +
            " values (20,@@IDENTITY)";

in C# and get following exception :

Characters found after end of SQL statement

Answer 1

You need to create a Stored Procedure that contains those SQL statements, and then execute the Stored Procedure from your C# code.

Answer 2

I just ran this code and it worked out:

SqlConnection cn = new SqlConnection("...");
SqlCommand cm = cn.CreateCommand();
cm.CommandText = 
    "INSERT INTO MyTable (FieldA) VALUES ('Sample'); " +
    "INSERT INTO MyTable (FieldB) VALUES (@@Identity); ";
cn.Open();
cm.ExecuteNonQuery();

Maybe you need to add a space after that first semicolon character.

Answer 3

I guess you want to retrieve the identity of the newly inserted student and then insert that into the "Records" table, right?

I would strongly suggest you use SCOPE_IDENTITY() instead of @@IDENTITY which has some problems if you have e.g. triggers on your table. Pinal Dave has a great blog post about those problems.

Also, if you call SQL Server from C#, I'd strongly recommend you use the native .NET SQL Provider (SqlConnection, SqlCommand, etc.) - not oledbcommand.

Try this

using (SqlConnection _con = new SqlConnection("server=(local);database=TEST;integrated security=SSPI;"))
{
    string queryStmt = "INSERT INTO dbo.Students (StudentName) VALUES('reza'); " +
                       "INSERT INTO dbo.Records(RecordID, StudentID) VALUES (20, SCOPE_IDENTITY());";

    using (SqlCommand _cmd = new SqlCommand(queryStmt, _con))
    {
       try
       {
            _con.Open();
            _cmd.ExecuteNonQuery();
            _con.Close();
       }
       catch (Exception exc)
       {
           string msg = exc.Message;
       }
    }
 }

This certainly works, I just tested it successfully in my setting.

Answer 4

You should wrap this up into a stored procedure because logically it's one unit of work - and you may want to call it from more than one location too.

I would also seriously consider whether you should wrap the two statements up into a transaction - you wouldn't want the insert into students succeeding and that into records failing. OK that's an edge condition but it's easy to guard against in a SP and makes more professional code.

Another advantage of using an SP in this case is that as you're actioning a couple of inserts already it's quite possible that you'll want to extend this later - insert into a finance table for example. If you wrap up into an SP you can just alter the SP rather than have to grep code for all instances of a new student insert, amend and recompile.

Answer 5

You can do this command much as shown - you do not need to have a temporary variable nor do you need a "GO" or a space after the semicolon. I do agree with Marc_s that you should use SCOPE_IDENTITY() to avoid problems with other transactions sneaking a value in.

The question is: why do you have quotes around your statement and a semicolon at the end? Clearly you are pulling this from code and that is where I'd look. So...first, run this command in SQL Server Management Studio or the like to verify that it works. Once you do verify it (it should work assuming your table structure is what I think it is) then figure out what your code is doing wrong.

Update: I am laughing here as my answer is "correcting" lots of other answers that are disappearing as it becomes obvious that they are not right.