Inserting data to MySQL database from VB.NET

Question

I used the following code to insert data from my VB.NET into the MySQL database.

Dim connString As String = "Database=user;Data Source=localhost;" _
                 & "User Id=root;Password="
 Dim conn As New MySqlConnection(connString)
 Dim cmd As New MySqlCommand()
 Try
    conn.Open()
    cmd.Connection = conn
    getFormData()
    cmd.CommandText = "insert into privileges values (" & userId & "," & uname & "," & usb & "," & internet & "," & pro1 & "," & pro2 & "," & pro3 & "," & pro4 & "," & pro5 & ");"
    cmd.Parameters.AddWithValue("userId", userId)
    cmd.Parameters.AddWithValue("uname", uname)
    cmd.Parameters.AddWithValue("usb", cbusb.CheckState) 'Status", Convert.ToInt32(usb))
    cmd.Parameters.AddWithValue("internet", cbnet.CheckState) ' Convert.ToInt32(internet))
    cmd.Parameters.AddWithValue("pro1", txtpro1.Text)
    cmd.Parameters.AddWithValue("pro2", txtpro2.Text)
    cmd.Parameters.AddWithValue("pro3", txtpro3.Text)
    cmd.Parameters.AddWithValue("pro4", txtpro4.Text)
    cmd.Parameters.AddWithValue("pro5", txtpro5.Text)
   cmd.ExecuteNonQuery()
   MessageBox.Show("User Profile Created!", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
    conn.Close()

However I get the following error message

Unknown column 'B5' in 'field list'

B5 is what I entered into the text box, but I do not know what field list refers to

I thin, its a problem with the Parameters statement

v

cmd.Parameters.AddWithValue("usb", cbusb.CheckState) 'Status", Convert.ToInt32(usb)) is the error. quotes are off. hi-lighting in stack even shows it :D — xQbert
– xQbert, Commented Dec 27, 2011 at 13:37

Tony Hopkinson · Accepted Answer · 2011-12-27 13:37:42Z

1

cmd.CommandText = "insert into privileges values (@userId,@uname....,@pro5)";

Parameters are marked with @s

answered Dec 27, 2011 at 13:37

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yoosuf Over a year ago

Its working now but in MySQL the values are all displayed as NULL

Tony Hopkinson Over a year ago

So it's not throwing an exception, but no value inserted?. Use debug, check cmd's parameter colection make sure values are being set.

SecurityForces · Accepted Answer · 2012-06-05 22:59:58Z

Thats a decent way of doing it; however, I have this way that works as well:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
Dim conn As MySqlConnection

'Connect to the database using these credentials
conn = New MySqlConnection
conn.ConnectionString = "server=your server site (generally long url); user id=login id for mysql user; password=self explanatory; database=name of the DB you're trying to reach"

'Try and connect (conn.open)
Try
    conn.Open()

Catch myerror As MySqlException 'If it fails do this... (i.e. no internet connection, etc.)
    MsgBox("Error connecting to database. Check your internet connection.", MsgBoxStyle.Critical)
End Try


'MySQL query (where to call for information)
Dim myAdapter As New MySqlDataAdapter

'Tell where to find the file with the emails/passes stored
Dim sqlquery = "SELECT * FROM the database you selected above WHERE Email = '" & txtEmail.Text & "' AND Password = '" & txtPassword.Text & "'"
Dim myCommand As New MySqlCommand
myCommand.Connection = conn
myCommand.CommandText = sqlquery

'Start query
myAdapter.SelectCommand = myCommand
Dim myData As MySqlDataReader
myData = myCommand.ExecuteReader

If myData.HasRows = 0 Then
    MsgBox("Invalid email address or password.", MsgBoxStyle.Critical)

Else
    MsgBox("Logged in as " & txtEmail.Text & ".", MsgBoxStyle.Information)

    Me.Close()

End If

End Sub

Try that and write back how it worked. Its a bit more functional because it lays out each step clearer.

Collectives™ on Stack Overflow

Inserting data to MySQL database from VB.NET

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related