Hi,
I want to insert data from the form into two mysql tables and insert the first table generated auto number into another table. I tried the below but it gives me the error in the line cmd.CommandText = str_sql
An unhandled exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll
pls help regards.
What I have tried:
Function addrec()
connect()
Dim ireturn As Boolean
Dim str_sql As String = "INSERT INTO `people`( `first_name`, `last_name`, `phone_number`,`mobile`, `email`, `address`, `city`, `state`, `zip`, `country`, `comments`) VALUES (@firstname,@lastname,@phone,@mobile,@email,@address,@city,@state,@zip,@country,@comments)"
cmd = New MySqlCommand
cmd.CommandText = str_sql
With cmd
.CommandType = CommandType.Text
.Parameters.AddWithValue("@firstname", txtfirstname.Text)
.Parameters.AddWithValue("@lastname", txtlastname.Text)
.Parameters.AddWithValue("@phone", txtphone.Text)
.Parameters.AddWithValue("@mobile", txtmobile.Text)
.Parameters.AddWithValue("@email", txtemail.Text)
.Parameters.AddWithValue("@address", txtaddress.Text)
.Parameters.AddWithValue("@city", txtcity.Text)
.Parameters.AddWithValue("@state", txtstate.Text)
.Parameters.AddWithValue("@zip", txtzip.Text)
.Parameters.AddWithValue("@country", txtcountary.Text)
.Parameters.AddWithValue("@comments", txtcomments.Text)
End With
Dim newAutoNumberValue As Integer
Try
cmd.ExecuteNonQuery()
cmd.CommandText = "SELECT @@IDENTITY"
newAutoNumberValue = cmd.ExecuteScalar()
ireturn = True
Catch ex As MySqlException
MsgBox(ex.Message.ToString)
ireturn = False
Finally
End Try
Dim str_sql2 As String = "INSERT INTO `customer`( `person_id`, `company`, `bank_name`,`account_number`)VALUES (@autonum,@company,@bank,@acc)"
cmd = New MySqlCommand
cmd.CommandText = str_sql2
With cmd
.Parameters.AddWithValue("@autonum", newAutoNumberValue)
.Parameters.AddWithValue("@company", txtcompany.Text)
.Parameters.AddWithValue("@bank", txtbank.Text)
.Parameters.AddWithValue("@acc", txtaccnum.Text)
End With
Try
cmd.ExecuteNonQuery()
ireturn = True
MsgBox("Record added successfully!",, "Succeeded")
Catch ex As MySqlException
MsgBox(ex.Message.ToString)
ireturn = False
Finally
dbcon.Close()
End Try
Return ireturn
End Function