I am tring to insert into two but something goes wrong

Question

I am tring to insert into two tables but something goes wrong. It returns this message:

Additional information: Incorrect syntax near 'GO'.

con.Open();
SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT dbo.Tomasos ON;GO DECLARE @IDENTITY INT INSERT INTO Bestallning (BestallningDatum, Totalbelopp, Levererad, KundID) VALUES ('28-02-2014', '250', '1', '1') SELECT @IDENTITY = SCOPE_IDENTITY() INSERT INTO BestallningMatratt (MatrattID, BestallningID, Antal) VALUES ('1', @IDENTITY, '3') SET IDENTITY_INSERT dbo.Tomasos OFF; GO", con);
cmd.ExecuteNonQuery();
con.Close();

*GO is not a Transact-SQL statement...* - https://msdn.microsoft.com/en-us/library/ms188037.aspx — Amit, Nov 16 '15 at 11:47

score 2 · Answer 1 · answered Nov 16 '15 at 11:49

2

SET IDENTITY_INSERT dbo.Tomasos ON;

DECLARE @IDENTITY INT INSERT INTO Bestallning (BestallningDatum, Totalbelopp, Levererad, KundID) VALUES ('28-02-2014', '250', '1', '1')
SELECT @IDENTITY = SCOPE_IDENTITY()
INSERT INTO BestallningMatratt (MatrattID, BestallningID, Antal) VALUES ('1', @IDENTITY, '3') SET IDENTITY_INSERT dbo.Tomasos OFF;

I dont think you need the GO at the end i also think you need

SET IDENTITY_INSERT dbo.Tomasos OFF;

answered Nov 16 '15 at 11:49

P6345uk

703
10
26

duplicate here http://stackoverflow.com/questions/18596876/go-statements-blowing-up-sql-execution-in-net – P6345uk Nov 16 '15 at 11:51

Cetin Basoz · Answer 2 · 2015-11-16T13:23:26.483

go is a batch separator and here you don't have commands that need to be executed separately, thus you can do it as a single batch:

con.Open();
SqlCommand cmd = new SqlCommand(@"SET IDENTITY_INSERT dbo.Tomasos ON;
DECLARE @IDENTITY INT;
INSERT INTO Bestallning 
  (BestallningDatum, Totalbelopp, Levererad, KundID) 
  VALUES 
  ('28-02-2014', '250', '1', '1');

SELECT @IDENTITY = SCOPE_IDENTITY();
INSERT INTO BestallningMatratt 
  (MatrattID, BestallningID, Antal) 
  VALUES 
  ('1', @IDENTITY, '3');
SET IDENTITY_INSERT dbo.Tomasos OFF;", con);
cmd.ExecuteNonQuery();
con.Close();

EDIT: BTW looking at the values and field names, it sounds like some of the values are not strings in fact but you are relying on implicit conversion which is very dangerous. Since you are using C#, it would be much easier and safer to pass real values with real datatypes as parameters instead (and in real life nobody would harcode the values anyway).

Ok, I have tried but now I get this message: Additional information: Incorrect syntax near ';'. — G.D, Nov 16 '15 at 12:08
My bad, there wouldn't be ; inside that second insert statement. Edited. — Cetin Basoz, Nov 16 '15 at 13:23

I am tring to insert into two but something goes wrong

2 Answers2