SCOPE IDENTITY Syntax

Hi,

I've been trying to insert data in a Sql server (.mdf) db and use SCOPE IDENTITY to be able to insert an id in both the parent and the child tables. However, I don't know how to write one of the lines correctly:

Dim MyConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)

Dim MySQL As String = "INSERT INTO Document (fid, serie, vnr, datum, vtext, sign) " & _

"Values (@fid, @serie, @vnr, @datum, @vtext, @sign) Select @verid=SCOPE_IDENTITY()"

Dim Cmd As New SqlCommand(MySQL, MyConn)

With Cmd.Parameters

.Add(New SqlParameter("@fid", fid))

.Add(New SqlParameter("@serie", SerieDropDownList.SelectedValue))

.Add(New SqlParameter("@vnr", vnr))

.Add(New SqlParameter("@datum", datumet))

.Add(New SqlParameter("@vtext", VtextTextBox.Text))

.Add(New SqlParameter("@sign", sign))

.Add(New SqlParameter("@verid", SqlDbType.Int)) 'HERE IS THE LINE THAT SHOULD BE CHANGED (OR SO I THINK)

End With

MyConn.Open()

Cmd.ExecuteNonQuery()

Dim p As SqlParameter = Cmd.Parameters.Add("@verid", SqlDbType.Int)

p.Direction = ParameterDirection.Output

Dim verid = Cmd.Parameters("@verid").Value.ToString

For Each row As GridViewRow In StampelGridView.Rows

Dim kpnr As String = row.Cells(0).Text

Dim kst As String = row.Cells(1).Text

If kst = " " Then kst = ""

Dim projekt As String = row.Cells(2).Text

If projekt = " " Then projekt = ""

Dim debettext As String = "0.00"

Dim kredittext As String = "0.00"

If row.Cells(3).Text.Length > 3 Then debettext = Replace(row.Cells(3).Text, ",", ".")

If row.Cells(4).Text.Length > 3 Then kredittext = Replace(row.Cells(4).Text, ",", ".")

Dim ptext As String = row.Cells(5).Text

If ptext = " " Then ptext = ""

Dim MySQLpost As String = "INSERT INTO Vpost (verid, kpnr, kst, projekt, debet, kredit, ptext) " & _

"Values (@verid, @kpnr, @kst, @projekt, @debet, @kredit, @ptext)"

Dim Cmdpost As New SqlCommand(MySQLpost, MyConn)

With Cmdpost.Parameters

.Add(New SqlParameter("@verid", p)) 'THIS SHOULD CHECK OUT AS WELL...

.Add(New SqlParameter("@kpnr", kpnr))

.Add(New SqlParameter("@kst", kst))

.Add(New SqlParameter("@projekt", projekt))

.Add(New SqlParameter("@debet", debettext))

.Add(New SqlParameter("@kredit", kredittext))

.Add(New SqlParameter("@ptext", ptext))

End With

Cmdpost.ExecuteNonQuery()

Next

MyConn.Close()

In other words, the verid is the id of the new post in the first table. This id should be inserted in the new table as well.

Thank you very much in advance fro helping me out! I've worked a LONG time with this.

Pettrer