0

I am building a forum and I have two tables:

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


Topics
-----
TopicsID
Theme
Topics
Date

The ThreadID is connected to the users table with a primary key: Topics.TopicsID(PK)==Threads.TopicID(FK)

First i insert into the Topics table and then to the Threads table. My goal is to obtain the ID of Topics.TopicID with Identity_Scope() and pass it to the second insert which is Threads.TopicID

Here is what i have done, but i am not sure if it is correct:

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 have got all the otehr parameters obtained from the controls the users presses or feeds information into, so dont worry about them. All i am worried about is passing the same TopicID from the Topic table to Thread table (Column name: TopicID).

WithFlyingColors
  • 2,650
  • 4
  • 20
  • 25
  • 1
    It's `SCOPE_IDENTITY`, not `IDENTITY_SCOPE`. See this related question: http://stackoverflow.com/q/42648/1583 – Oded Jun 07 '11 at 08:47
  • You gonna need to declare variable @TopicSID – Magnus Jun 07 '11 at 08:50
  • `VALUES('@topic,@subTopic,GETDATE()'` is definitely wrong - it's missing a terminating `)`, and missing the fact that you pass separate values, not a `'` delimited string in the `VALUES` clause. Get rid of the `'` marks inside both `VALUES` clauses. – Damien_The_Unbeliever Jun 07 '11 at 09:25
  • Why to get rid of the '' marks in "VALUES(' ')", they will make the statement in the Values a string – WithFlyingColors Jun 07 '11 at 09:49

1 Answers1

0

Both Magnus & Damien_The_Unbeliever are right - you have few syntax errors (or typos). Correct insert command should be something like

    insertCommand.Append(@"
          DECLARE @TopicSID int

          INSERT INTO Topics(Theme,Topics,Date)
          VALUES(@topic,@subTopic,GETDATE())

          SET @TopicSID = SCOPE_IDENTITY()

          INSERT INTO Threads(UsersID,TopicsID,Date,ThreadTitle,ThreadParagraph,ThreadClosed)
          VALUES(@uniqueIdentifier,@TopicSID ,GETDATE(),@questionTitle,@questionParagraph,0)
    ");
VinayC
  • 47,395
  • 5
  • 59
  • 72
  • I corrected it. But now i get : Incorrect syntax near the keyword 'INTO'. Must declare the scalar variable "@TopicsID". There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. – WithFlyingColors Jun 07 '11 at 10:18