Insert into two tables in code behind (VB.net)

Question

The solution that works for me is:

         sqlstr = "declare "
            sqlstr &= "@returnId int "
            sqlstr &= " BEGIN TRANSACTION " & _
                    " INSERT INTO sales ([user_name],[password],[full_Name],[email]) " & _
                    " VALUES (@user_name,@password,@full_Name,@email) " & _
                    " set @returnId = (select SCOPE_IDENTITY()) " & _
                    " INSERT INTO Sales_trade ([id_s],[trade]) " & _
                    " VALUES (@returnId,@trade) " & _
                    "IF (@@error <> 0) " & _
                    " ROLLBACK TRANSACTION " & _
                    " ELSE COMMIT TRANSACTION "

            cmd = New SqlCommand(sqlstr, myConn)
            Dim par1 As New SqlParameter
            par1.ParameterName = "@user_name"
            par1.Value = unam
            cmd.Parameters.Add(par1)

            'Do the same for all other parameters

            cmd.ExecuteNonQuery()

    myConn.Close()

This is how it worked for me, the code I did for parameter (par1) is the same all for the rest..

kostas ch. · Accepted Answer · 2013-08-06T09:40:58.417

1

Initially i suggest you to parametrized your query to avoid sql injection. Create a transaction and use the sql statement something like the above:

sqlstr = " BEGIN TRANSACTION " & _
" INSERT INTO sales ([user_name],[password],[full_Name],[email]) " & _
" VALUES (@user_name,@password,@full_Name,@email) " & _
" set @returnId = (select SCOPE_IDENTITY()) " & _
" INSERT INTO Sales_trade ([id_s],[trade]) " & _
" VALUES (@returnId,@trade) " & _
"IF (@@error <> 0) " & _
" ROLLBACK TRANSACTION " & _
" ELSE COMMIT TRANSACTION "
dim par as new SqlParameter
par.Name = "@user_name"
par.Value = "test"
.... do this for all parameters
cmd.Parameters.Add(par)

edited Aug 06 '13 at 09:40

answered Aug 06 '13 at 08:57

kostas ch.

1,960
1
17
30

it gave me error on column SCOPE_IDENTITY, can not recognize it ! – tollamie Aug 06 '13 at 09:18
@tollamie I have update my post. SCOPE_IDENTITY() is tsql function. So put () after SCOPE_IDENTITY and you will be ok. :) – kostas ch. Aug 06 '13 at 09:23
Thank you.. It's working now! However how can I use this in the code behind as I mentioned in my post ? – tollamie Aug 06 '13 at 09:31
Im sorry but par.name="@user_name" par.mail=@email is not correct ! – tollamie Aug 06 '13 at 09:53
No no, these are properies for SqlParameter. par.Name is the name of the parameter and par.Value is the value you want to take. Please have a look at this http://forums.asp.net/t/886691.aspx – kostas ch. Aug 06 '13 at 09:56
I got this error: cmd.Parameters.Add(par) Object reference not set to an instance of an object. – tollamie Aug 06 '13 at 10:15
Please read carefully SqlParameter usage in order to understand how to use them. It is a good lesson for you. – kostas ch. Aug 06 '13 at 10:18
There is just one more thing that I wanted to ask you!! Please check my edited Post.. – tollamie Aug 06 '13 at 17:13
@tollamie Sorry i can not understand what you want to do on the updated post. – kostas ch. Aug 07 '13 at 06:27
for the column trade of the table **Sales_trade** the value of **@trade** is CheckBoxList so if I have so many trades checked how to insert this in my table **Sales_trade** for just one **sale(id_s)** ! – tollamie Aug 07 '13 at 06:38
@tollamie this you have to do by vb. I mean first you add sales record and have an out parameter on sql which you will fill id with the new id and then create a new command in your for loop and add Sales_trade record with the same saleid. Do not forget to add transaction object in vb and not in sql. – kostas ch. Aug 07 '13 at 06:47
But how I can take the out parameter (id_s) on SQL and use it for the second insert ? Ps: the id_s is generated automatically for every sale inserted ! – tollamie Aug 07 '13 at 06:54
see the above. http://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net Generaly do a litle search on the net. It is very interesting part the ADO.net – kostas ch. Aug 07 '13 at 06:59

Insert into two tables in code behind (VB.net)

1 Answers1