Hi,

I'm trying to insert one value (an order) into the table Order and (via a for-loop) all the products in that order in the table Product, hence, one order can have multiple products (and must have at least one). I have an automatically increased value for the OrderID as the primary key for Order, and I have a foreign key named OrderID in the Product table. So far, I _think_ everything's logically correct.

However, I don't understand how to retrieve the OrderID to be able to insert it in the Product table upon insertion. I guess this is done all the time, but the only solution I can think of is to make a new SQL Command, asking for the just created OrderID to use it in the SQL Command for the products' for-loop. I'm sure that's a bad idea. :-)

Can I use relationships or so to make this automatically updated (that is, to have the Product table "check for" the OrderID and insert the OrderID upon insertion of the Product row(s))?

I hope this is clear to you. Thanks in advance for all help! (VB, Asp.Net 2.0, VWD Express, SQL Server Express)

Pettrer


Coding is indeed a nine-to-five job; nine pm to five am.

__________________
Coding is indeed a nine-to-five job; nine pm to five am.

Set the ID column to be an identity column. Then when you insert into that table, you can return the ID just created from the inserted column. Then you can use that value for your inserts into the related table.

J,

Thanks for your reply. Doesn't this mean that I still need a Sql statement (SELECT TOP1 id FROM table_1 ORDER BY id DESC) in order to get this new id? Or is there another, automatic, way to, as you say, "return the ID just created from the inserted column"?

Muchos gracias!
P


Coding is indeed a nine-to-five job; nine pm to five am.

No pettrer you cant use in this way that if there is another record inserted before this query then you loss the last one so for this use the triggers. For this write the trigger on the insertion of new item in order table and in that trigger you can get the new inserted value by inserted key word





(*_*)

Numan
--------------------------------------------------
It is not important what you get But important is how you got it

You create the column as identity. Then create a stored procedure. You need to create an output parameter in the stored procedure. In the stored proc, return the identity of the row just inserted
outputparam = SCOPE_IDENTITY() --Identity of last row inserted

You can now grab that value on the front end. Then use it to insert in the related table.

Also, Numans' suggestion will also work, you can create a loop in the trigger to do multiple inserts based on the initial insert

Thanks to the both of you for your insightful help!
I didn't believe there would be so much to do to get this logically simple thing to work. I'll search around for how to create stored procedures and triggers, and how to make identities as well!

It's going to be an interesting night for me!

Thanks again,

P

Coding is indeed a nine-to-five job; nine pm to five am.

The identity is simply setting a property on the column in the table. Stored procs and triggers are not hard and many examples are out on the web and in the sql server express Books On Line.

Hi,

After some weeks I've returned to this problem (and also read about Sql injections in Beginning Asp.Net 2.0 by Wrox).

Now I've tried to put together the code but can't really get it to work. The object is to insert some data in one table, and then to retrieve that table's newwly created unique ID, to put several rows in another table with this ID (think one order with many products, which need to be related to the new orderID). I think I almost got it but I don'tknow how to initiate the new ID (see below), and of course there might be other problems as well. This is my code (and there shouldn't be any other errors in it - it runs correctly when I don't use the SCOPE_IDENTITY feature) ...:

Protected Sub RegistreraButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RegistreraButton.Click
        Dim fid As String = Session("fid")
        Dim dtmp As String = DatumTextBox.Text
        dtmp = "20" + Left(dtmp, 2) + "-" + Mid(dtmp, 3, 2) + "-" + Right(dtmp, 2)
        Dim datumet As Date = CDate(dtmp)
        Dim sign As String = Session("signatur")
        Dim i As Integer = 0

        Dim MyConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationMa nager.ConnectionStrings("ekodbConnectionString").C onnectionString)
        Dim MySQL As String = "Insert into vat (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", direction = "output")) THIS IS PROBABLY WHAT'S CAUSING ME TROUBLE
        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 dtext As String = "0.00"
            Dim ktext As String = "0.00"
            If row.Cells(3).Text.Length > 3 Then dtext = Replace(row.Cells(3).Text, ",", ".")
            If row.Cells(4).Text.Length > 3 Then ktext = 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, det, kret, ptext) " & _
                "Values (@verid, @kpnr, @kst, @projekt, @det, @kret, @ptext)"

            Dim Cmdpost As New SqlCommand(MySQLpost, MyConn)
            With Cmdpost.Parameters
                .Add(New SqlParameter("@verid", p))
                .Add(New SqlParameter("@kpnr", kpnr))
                .Add(New SqlParameter("@kst", kst))
                .Add(New SqlParameter("@projekt", projekt))
                .Add(New SqlParameter("@det", dtext))
                .Add(New SqlParameter("@kret", ktext))
                .Add(New SqlParameter("@ptext", ptext))
            End With
            Cmdpost.ExecuteNonQuery()
        Next
        MyConn.Close()
    End Sub

Oh - and thanks Jason for setting me straight regarding paramterized Sql. This is my first try and I think I actually got it to work!

Thanks in advance,

P


Coding is indeed a nine-to-five job; nine pm to five am.