VB.net adding parent and child records to MySQL db

Question

I was asked to create a program that inserts records into one parent table and multiple child tables. My question is, how do I know what the PK is for the parent table, so that I may add it as a FK in the child? The PK for the parent is an auto number. As I stated in my title, I'm using VB.net, mySQL, through an ODBC connection. I have to do this through the code and cannot use stored procedures. Any suggestions?

thanks

my transaction looks like this:

     Dim cmdText As String = "INSERT INTO candidate(first_name, last_name, phone1, phone2, email1, city, " _
                    & " state, country, zip,primary_contact_id ) VALUES (?,?, ?, ?,?,?, ?,?,?,?)"

    If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If
    Dim SqlStatus As Integer
    Dim trans As Odbc.OdbcTransaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
    Dim cmd As OdbcCommand = New OdbcCommand(cmdText, conn, trans)


    Try
        cmd.Parameters.Clear()
        cmd.CommandType = CommandType.Text 'The default is CommandType.Text

        With cmd.Parameters
            .Add("@first_name", OdbcType.VarChar).Value = fName
            .Add("@last_name", OdbcType.VarChar).Value = lName
            .Add("@phone1", OdbcType.VarChar).Value = phone
            .Add("@phone2", OdbcType.VarChar).Value = mobilePhone
            .Add("@email1", OdbcType.VarChar).Value = email
            .Add("@city", OdbcType.VarChar).Value = city
            .Add("@state", OdbcType.VarChar).Value = state
            .Add("@country", OdbcType.VarChar).Value = country
            .Add("@zip", OdbcType.VarChar).Value = zip
            .Add("@primary_contact_id", OdbcType.Int).Value = getContactFK
        End With

        SqlStatus = cmd.ExecuteNonQuery

         If Not SqlStatus = 0 Then
            trans.Commit()
            Me.Close()
        Else
            MsgBox("Not Updated")
        End If



    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        cmd.Dispose()
        trans.Dispose()
    End Try

I'm still working on the code, so not sure if it works just yet jason

score 3 · Accepted Answer · answered Oct 28 '11 at 16:07

3

Take a look at How to Get the Unique ID for the Last Inserted Row

Since you're going through ODBC and cannot use a stored proc you will have to execute two SQL statements together (as a batch). First your insert and then SELECT LAST_INSERT_ID()

It should look something like:

INSERT INTO ... ;
SELECT LAST_INSERT_ID();

Since you're expecting a result you need to execute from your client code as a SELECT statement. And since this is a batch operation with an insert you should also consider using a transaction.

answered Oct 28 '11 at 16:07

Paul Sasik

79,492
20
149
189

Thanks, I will give it a try! one more question, if the child insert fails, i want all inserts to fail. can i do this in a transaction? – jason Oct 28 '11 at 16:10
Yes you can. That's where the goodness of transaction really comes into play. Just know that your batch will get bigger and more complex since you'll want to set up those inserts within that statement as well, but that is the right way to do it. – Paul Sasik Oct 28 '11 at 16:14
Thanks! you wouldnt have any example code for doing a transaction with select last_insert_idI()? I haven't worked with transactions in vb.net before. thanks – jason Oct 28 '11 at 16:19
If it helps, i can post the transaction vb.net code I have, as well – jason Oct 28 '11 at 16:24
not sure what upvote mean but I just added the transaction code im working on, if that helps – jason Oct 28 '11 at 16:57
Upvoting means... well up arrow next to the answer when it's useful, in addition to accepting an answer. It's all in the FAQ. Your latest code looks good at a glance. If you have any problems with it you should add a new question though. – Paul Sasik Oct 28 '11 at 17:17

score 3 · Answer 2 · answered Oct 28 '11 at 16:07

3

You can use

"; select last_insert_id()"

At the end of your insert for the parent table. And then use

Dim id as Integer = cint(command.ExecuteScalar())

To get the resulting key to use in the child inserts

answered Oct 28 '11 at 16:07

Jay

5,897
1
25
28

VB.net adding parent and child records to MySQL db

2 Answers2