I have a problem with my SQL statement

Question

    Threads 
------- 
ThreadID
 UsersID 
Date 
ThreadTitle
 ThreadParagraph 
ThreadClosed 

  Topics 
-----
 TopicsID 
Theme
 Topics 
Date

Here is my statement:

  StringBuilder insertCommand = new StringBuilder();
    insertCommand.Append("DECLARE @TopicsID int");
    insertCommand.Append("INSERT INTO Topics(Theme,Topics,Date)");
    insertCommand.Append("VALUES(@topic,@subTopic,GETDATE())");
    insertCommand.Append("SET @TopicsID = SCOPE_IDENTITY()");

    insertCommand.Append("INSERT INTO Threads(UsersID,TopicsID,Date,ThreadTitle,ThreadParagraph,ThreadClosed)");
    insertCommand.Append("VALUES(@uniqueIdentifier,@TopicsID,GETDATE(),@questionTitle,@questionParagraph,0)");

I get this:

Incorrect syntax near the keyword 'INTO'. Must declare the scalar variable "@TopicsID". Must declare the scalar variable "@TopicsID".

If you take the sql out into SSMS can you execute it successfully? — Jon Egerton, Jun 07 '11 at 10:31
Have you trying printing the created query (just before or after) ruuning it? — ypercubeᵀᴹ, Jun 07 '11 at 10:32
After @RQDQ comment, i corrected, and i got the following error: Incorrect syntax near the keyword 'INTO'. Must declare the scalar variable "@TopicsID". Must declare the scalar variable "@TopicsID". — WithFlyingColors, Jun 07 '11 at 10:37

score 2 · Answer 1 · answered Jun 07 '11 at 10:30

2

The first thing I notice is:

insertCommand.Append("VALUES('@topic,@subTopic,GETDATE()')");

might need to be:

insertCommand.Append("VALUES(@topic,@subTopic,GETDATE())");

It looks like you have some extra single quotes in there.

answered Jun 07 '11 at 10:30

RQDQ

15,461
2
32
59

I thought it was a good thing to put them :(..even necessary..as it makes everything in the VALUES be in quotes.. or is it only when i put simple values (not functions and variables..e.g. VALUES('Gardening','Tools','08/02/2003'); – WithFlyingColors Jun 07 '11 at 10:35
Yes, you only put single quotes around them in this case. – DOK Jun 07 '11 at 10:39
Putting single quotes around characters is how to pass them as a string. It's the same idea as doing MessageBox("1 + 1") - it will display 1 + 1 in a messagebox instead of 2. – RQDQ Jun 07 '11 at 10:42

score 2 · Answer 2 · answered Jun 07 '11 at 10:30

You need a semi-colon after DECLARE @TopicsID int

That will take care of the "incorrect syntax" and declaring the scalar variable.

And I believe that you need to remove the single quotes around your three VALUES. That is causing it to think you have supplied only one VALUE instead of three.

score 1 · Accepted Answer · answered Jun 07 '11 at 10:30

1

Try insertCommand.AppendLine

SQL sees

DECLARE @TopicsID intINSERT INTO Topics(Theme,Topics,Date) ...

You need to separate the distinct statements

answered Jun 07 '11 at 10:30

Binary Worrier

50,774
20
136
184

That work. wheni put the Declare statment with the insert into statment it worked...!xD – WithFlyingColors Jun 07 '11 at 10:45

score 1 · Answer 4 · answered Jun 07 '11 at 10:32

Your single quotes on the last line are wrong - its turning it all onto one string:

Change

  insertCommand.Append("VALUES('@uniqueIdentifier,@TopicsID,GETDATE(),@questionTitle,@questionParagraph,0')");

to

insertCommand.Append("VALUES(@uniqueIdentifier,@TopicsID,GETDATE(),@questionTitle,@questionParagraph,0)");

I have a problem with my SQL statement

4 Answers4