Help with creating an INSERT statement using MySQL

[kismetgerald]

Guys,

I'm having problems with my MySQL query. VB keeps throwing the following exception:

The CommandText property has not been properly initialized

Here's the code I'm using:

[code2=vbnet]Dim dbConn As New MySqlConnection("Server=" & FormLogin.ComboBoxServerIP.SelectedItem & ";Port=3306;Uid=parts;Password=parts;Database=accounting")
Dim dbQuery As String = ""
Dim dbData As MySqlDataReader
Dim dbAdapter As New MySqlDataAdapter
Dim dbCmd As New MySqlCommand

Try
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
Else
dbConn.Open()
dbCmd.CommandText = dbQuery
dbCmd.Connection = dbConn
dbAdapter.SelectCommand = dbCmd
dbData = dbCmd.ExecuteReader
dbQuery = "INSERT INTO customer (accountNumber, nameLAST, nameFIRST, nameCOMPANY, addressSTREET, addressSTREET1, addressCITY, addressSTATE, addressZIPCODE, phone, fax, email) VALUES('" & TextBoxAccount.Text & "','" & TextBoxLastName.Text & "','" & TextBoxFirstName.Text & "','" & TextBoxCompanyName.Text & "','" & TextBoxAddress1.Text & "','" & TextBoxAddress2.Text & "','" & TextBoxCity.Text & "','" & ComboBoxState.SelectedItem & "','" & MaskedTextBoxZip.Text & "','" & MaskedTextBoxPhone.Text & "','" & MaskedTextBoxFax.Text & "','" & TextBoxEmail.Text & "')"
End If
Catch ex As MySqlException
MessageBox.Show("Error connecting to database server. Please call the IT/Systems Helpdesk for support." & vbCrLf & vbCrLf & ex.ToString)
End Try
dbConn.Close()[/code2]

Your help would be greatly appreciated, thanks.

[brandonio21]

This is due to the placement of certain elements of your code. All you need to do is change this line of your code
[code2=vbnet]Dim dbQuery As String = ""[/code2]

To:

[code2=vbnet]Dim dbQuery As String = "INSERT INTO customer (accountNumber, nameLAST, nameFIRST, nameCOMPANY, addressSTREET, addressSTREET1, addressCITY, addressSTATE, addressZIPCODE, phone, fax, email) VALUES('" & TextBoxAccount.Text & "','" & TextBoxLastName.Text & "','" & TextBoxFirstName.Text & "','" & TextBoxCompanyName.Text & "','" & TextBoxAddress1.Text & "','" & TextBoxAddress2.Text & "','" & TextBoxCity.Text & "','" & ComboBoxState.SelectedItem & "','" & MaskedTextBoxZip.Text & "','" & MaskedTextBoxPhone.Text & "','" & MaskedTextBoxFax.Text & "','" & TextBoxEmail.Text & "')"[/code2]

Then, delete the line that previously contained that data (line 16 in the code box thing) And you should be good!

[kismetgerald]

Thanks Brandon - you're AMAZING!

I managed to follow one of your videos in which you explained in detail how the MySQL works in VB.net. I followed it line by line and got it working for me............so A BIG THANK YOU!!!

[kismetgerald]

Hey bud,

I'm running into an exception with the following code. The exception is below and this happens after the first part of the IF statement is evaluated and an existing record is found in the database. When I click the OK button to close the messagebox - that's when the exception is thrown.

VB then points to the following line:
[code2=vbnet]While dbData.Read()[/code2]

HERE'S THE EXCEPTION:

Invalid attempt to Read when reader is closed

HERE'S MY SOLUTION:
I decided to remove the dbData.close() line just before the Else statement and the exception is no longer being thrown. Is this fine or does this create another issue that I'm unaware of?

HERE'S MY CODE:
[code2=vbnet]dbConn = New MySqlConnection("Server=" & FormLogin.ComboBoxServerIP.SelectedItem & ";Port=3306;Uid=parts;Password=parts;Database=accounting")
Dim account As Boolean = True
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
dbConn.Open()
Dim dbQuery As String = "SELECT * FROM customer WHERE accountNumber = '" & TextBoxAccount.Text & "';"
Dim dbData As MySqlDataReader
Dim dbAdapter As New MySqlDataAdapter
Dim dbCmd As New MySqlCommand
dbCmd.CommandText = dbQuery
dbCmd.Connection = dbConn
dbAdapter.SelectCommand = dbCmd
dbData = dbCmd.ExecuteReader
While dbData.Read()
If dbData.HasRows() = True Then
MessageBox.Show("Customer record already exists!")
account = False
dbData.Close()
Else
dbData.Close()
account = True
End If
End While
dbData.Close()
If account = True Then
Dim dbQuery2 As String = "INSERT INTO customer (accountNumber, nameLAST, nameFIRST, nameCOMPANY, addressSTREET, addressSTREET1," & _
"addressCITY, addressSTATE, addressZIPCODE, phone, fax, email)" & _
"VALUES('" & TextBoxAccount.Text & "','" & TextBoxLastName.Text & "','" & TextBoxFirstName.Text & "','" & TextBoxCompanyName.Text & _
"','" & TextBoxAddress1.Text & "','" & TextBoxAddress2.Text & "','" & TextBoxCity.Text & "','" & ComboBoxState.SelectedItem & _
"','" & MaskedTextBoxZip.Text & "','" & MaskedTextBoxPhone.Text & "','" & MaskedTextBoxFax.Text & "','" & TextBoxEmail.Text & "')"
Dim dbData2 As MySqlDataReader
Dim dbAdapter2 As New MySqlDataAdapter
Dim dbCmd2 As New MySqlCommand
dbCmd2.CommandText = dbQuery2
dbCmd2.Connection = dbConn
dbAdapter2.SelectCommand = dbCmd2
dbData2 = dbCmd2.ExecuteReader
Call lockForm()
MessageBox.Show("Customer account record saved successfully!")
End If[/code2]

Your help would be greatly appreciated, thanks.

[brandonio21]

Yeah, you should be fine. However, you will still need to close the database connection in order to prevent any memory leakages within your application. Try playing around with your code and place dbData.Close() in various locations. Specifically, it should be somewhere after all of the code is finished executing!

Hopefully this helps.

Also, since you seem to be pretty active on the forum, please read this post: <!-- l --><a class="postlink-local" href="http://bpforums.info/viewtopic.php?f=8&t=569">viewtopic.php?f=8&t=569</a><!-- l -->