CODEWITHSUNDEEP
sqlvb.netsql-server-2008insert
mays
mays

Reputation: 13

insert into related tables in same time

I'm working on vb.net project. I'm using VS2010 and SQL Server 2008.

I have many tables in my database, amongst them members and bank.

Then I created member_bank table with 2 columns: members.id and bank_id for the m:n relationship between those two tables.

How can I insert details into members and join member id directly with bank id in the member_bank table ?

I wrote this code put it doesn't work:

Dim saveok As Boolean = wnclass14.SQLExicute("Insert Into members (member_name,member_id,mobile,tel) values ('" & TextstdntName.Text & "','" & Textid.Text & "','" & TextMobile.Text & "','" & Texttel.Text & "')")
If saveok = True Then
        Dim saveok1 As Boolean = wnclass14.SQLExicute("Insert Into member_bank (id_member,id_bank) values (" & ComboBoxBank.SelectedValue & ")   where member_bank.id_member=members.id")

        If saveok1 = True Then .......

Upvotes: 0

Views: 1275

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416049

Part of the trick here is that you kind of want all of these to take place as part of a single transaction. Thankfully, it's not that hard to do this by sending multiple statements in a single call to your "SQLExicute" function (fix your spelling please):

wnclass14.SQLExicute( _
    "BEGIN TRANSACTION;" & _
    "DECLARE @NewID Int;" & _
    "INSERT INTO members (member_name, mobile, tel) " & _
      " VALUES ('..', '555-1234', '555-5678');" & _
    "SELECT @NewID = Scope_Identity();" & _ 
    "INSERT INTO member_bank (id_member, id_bank) " & _
      " VALUES (@NewID, '1234');" & _
    "COMMIT;)

And while we're here, you really need to fix this function so that it's also asking for parameter data.

It is not okay to to use string concatenation to include user-entered data in your sql statements.
It is not okay to skip this because you're still learning. And
it is not okay to "just get it working" first and then go back and fix the security issues afterwards

Upvotes: 2

sgeddes
sgeddes

Reputation: 62851

I assume you want to return the memberid from the members table after you do an insert? If so, you need to look into SCOPE_IDENTITY(). This should return the last Identity that was inserted into the table for your scope.

Here is a good article:

http://msdn.microsoft.com/en-us/library/ms190315.aspx

I'd supply code, but I don't really understand yours.

Good luck.

Upvotes: 0

Related Questions