0

Hoping someone can help me out with this.

I've made an app linked to a mysql db. I'm writing details of remote hosts to a database at the minute. I'm saving remote credentials too, but in a different table. I have a colomn in my 'credentials' table called 'hosts_linked_id' which i want to contain the id of the parent record in the 'hosts' table.

Here is my code so far...

    SQLConnection.ConnectionString = connectionstring
    Try
        If SQLConnection.State = ConnectionState.Closed Then
            SQLConnection.Open()
            Dim SQLStatement As String = "INSERT INTO hosts(name, description, host, type, port) VALUES('" & txtname.Text & "','" & txtdescription.Text & "','" & txthost.Text & "','" & cmbtype.Text & "','" & txtport.Text & "')"
            SaveData(SQLStatement)

            SQLConnection.Open()
            SQLStatement = "INSERT INTO credentials(hosts_linked_id, username, password, type) VALUES('" & txtname.Text & "','" & txtusername.Text & "','" & encryptedpassword & "','" & cmbtype.Text & "')"
            SaveData(SQLStatement)

        Else
            SQLConnection.Close()

        End If

Also, here's the 'SaveData' function...

Public Sub SaveData(ByRef SQLStatement As String)
    Dim cmd As MySqlCommand = New MySqlCommand

    With cmd
        .CommandText = SQLStatement
        .CommandType = CommandType.Text
        .Connection = SQLConnection
        .ExecuteNonQuery()
    End With
    SQLConnection.Close()
    MsgBox("Host has been added")
    txtname.Text = ""
    txtdescription.Text = ""
    txthost.Text = ""
    cmbtype.Text = ""
    txtport.Text = ""
End Sub

What i need to do is get the id of the record created when my first 'INSERT' statement is executed into a variable so i can insert it into the 'credentials' table when my second 'INSERT' statement is executed.

I've googled the hell out of this and tried a few different methods, all without success.

Can anyone help point me in the right direction?

Thanks in advance!!

TL;DR: Need to get the ID of mysql record added when insert statement is executed and drop it into a variable

John
  • 755
  • 1
  • 18
  • 46
  • 1
    http://stackoverflow.com/questions/9791156/return-last-id-identity-on-insert-row-vb-net-mysql might answer your question. – Tin Tran Nov 24 '13 at 23:40
  • 1
    It sure did! Thanks Tin Tran. All i had to do was make my first insert statement a double query to include... SELECT LAST_INSERT_ID() Then add this to the savedata function... Dim cmd_result As Integer = CInt(cmd.ExecuteScalar()) – John Nov 25 '13 at 00:28
  • Ahhh now i'm getting two of each record added to each table!! *sigh* – John Nov 25 '13 at 01:03

0 Answers0