Insert in 2 tables (using Id from 1st table)

kentwb · June 11th, 2013, 07:46 PM

I have a blog and I'm trying to build a Start_a_new_thread.aspx page where a logged in user can start a thread (thread table) and post the first comment to that thread (blog table). I get the thread created fine, but the ThreadId that's created never gets captured and the blog table get's no record inserted using that "ThreadId". ThreadId is a foreign key in the blog table. Am I doing something wrong in the way I use Scope_Identity...it's my first time trying to insert to two tables at once.

Code:

Imports System.Web.Services
Imports System.Web.Security
Imports System.Security
Imports System.Data
Imports System.Data.SqlClient

Partial Class Blog_New_Thread
  Inherits System.Web.UI.Page

  Protected Sub PostNewThreadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles PostNewThreadButton.Click
    If Not Page.IsValid Then Exit Sub

    ' Determine the currently logged on user's UserId
    Dim currentUser As MembershipUser = Membership.GetUser()
    Dim currentUserId As Guid = CType(currentUser.ProviderUserKey, Guid)

    ' Insert a new record into BlogThread and BlogPost
    Dim connectionString As String =
         ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Dim insertSql As String = "INSERT INTO Thread (UserId, Topic, ThreadCreated) VALUES (@UserId, @Topic, @ThreadCreated)" & _
    "Select Scope_Identity()"
    Dim ID As Integer
    Dim newinsertSql As String = "INSERT INTO Blog (ThreadId, UserId, Subject, Body, PostCreated) VALUES (@ThreadId, @UserId, @Subject, @Body, @PostCreated)"

    Using myConnection As New SqlConnection(connectionString)

      myConnection.Open()
      Dim myCommand As New SqlCommand(insertSql, myConnection)
      myCommand.Parameters.AddWithValue("@Topic", Topic.Text.Trim())
      myCommand.Parameters.AddWithValue("@UserId", currentUserId)
      myCommand.Parameters.AddWithValue("@ThreadCreated", DateTime.Now)
      myCommand.ExecuteNonQuery()
      ID = myCommand.ExecuteScalar()
      Dim newCommand As New SqlCommand(newinsertSql, myConnection)
      newCommand.Parameters.AddWithValue("@ThreadId", ID)
      newCommand.Parameters.AddWithValue("@UserId", currentUserId)
      newCommand.Parameters.AddWithValue("@Subject", Subject.Text.Trim())
      newCommand.Parameters.AddWithValue("@Body", Body.Text.Trim())
      newCommand.Parameters.AddWithValue("@PostCreated", DateTime.Now)
      newCommand.ExecuteNonQuery()
      myConnection.Close()
    End Using

    ' "Reset" the Topic TextBox

    Topic.Text = String.Empty
    Subject.Text = String.Empty
    Body.Text = String.Empty

    Response.Redirect("BlogThread.aspx")

  End Sub
End Class

kentwb · June 11th, 2013, 08:53 PM

I ended up creating a new GUID and using that for the Primary Key in the 1st table and Foreign Key in the second table. If that has any major drawbacks I'm here to learn. Thanks!

kentwb · June 17th, 2013, 11:43 AM

<code>

Imports System.Web.Services
Imports System.Web.Security
Imports System.Security
Imports System.Data
Imports System.Guid
Imports System.Data.SqlClient

Partial Class Blog_New_Thread
Inherits System.Web.UI.Page

Protected Sub PostNewThreadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles PostNewThreadButton.Click
If Not Page.IsValid Then Exit Sub

'assign uniqueidentifier in a variable
Dim MyGuid As Guid = Guid.NewGuid()

' Determine the currently logged on user's UserId
Dim currentUser As MembershipUser = Membership.GetUser()
Dim currentUserId As Guid = CType(currentUser.ProviderUserKey, Guid)

' Insert a new record into BlogThread and BlogPost
Dim connectionString As String = ConfigurationManager.ConnectionStrings("Connection String").ConnectionString
Dim insertSql As String = "INSERT INTO Thread (ThreadId, UserId, Topic, ThreadCreated) VALUES (@ThreadId, @UserId, @Topic, @ThreadCreated)"
Dim newinsertSql As String = "INSERT INTO Blog (ThreadId, UserId, Subject, Body, PostCreated) VALUES (@ThreadId, @UserId, @Subject, @Body, @PostCreated)"

Using myConnection As New SqlConnection(connectionString)

myConnection.Open()
Dim myCommand As New SqlCommand(insertSql, myConnection)
myCommand.Parameters.AddWithValue("@ThreadId", MyGuid)
myCommand.Parameters.AddWithValue("@UserId", currentUserId)
myCommand.Parameters.AddWithValue("@Topic", Topic.Text.Trim())
myCommand.Parameters.AddWithValue("@ThreadCreated" , DateTime.Now)
myCommand.ExecuteNonQuery()
Dim newCommand As New SqlCommand(newinsertSql, myConnection)
newCommand.Parameters.AddWithValue("@ThreadId", MyGuid)
newCommand.Parameters.AddWithValue("@UserId", currentUserId)
newCommand.Parameters.AddWithValue("@Subject", Subject.Text.Trim())
newCommand.Parameters.AddWithValue("@Body", Body.Text.Trim())
newCommand.Parameters.AddWithValue("@PostCreated", DateTime.Now)
newCommand.ExecuteNonQuery()
myConnection.Close()
End Using

' "Reset" the Topic, Subject and Body TextBoxes
Topic.Text = String.Empty
Subject.Text = String.Empty
Body.Text = String.Empty

'Redirect to page listing Blog Threads
Response.Redirect("BlogThread.aspx")

End Sub

End Class
</code>