Retrieving and saving data to Mysql Database using Visual Basic 2010

[iriah]

Can Somebody help me? i have an error whenever i want to save the data i have retrieved from different table in mysql. my system is doorlock system. im going to choose an item in the combobox and when i click the button, the data i'd chose from the combo box will be retrieved and will be saved in different table. but if it's not in the database there will be a messagebox that is going to appear saying " Invalid Input." i tried this codes:

Imports MySql.Data.MySqlClient

Public Class Form2
  Dim conn As MySqlConnection
    Dim comm As MySqlCommand

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;userid=root;password=;database=admin"
        Dim Reader As MySqlDataReader

        Try
            conn.Open()
            Dim Query As String
            Query = "select * from admin.schedule where section= '" & ComboBox1.SelectedItem & "', name= '" & ComboBox2.SelectedItem & "', subject= '" & ComboBox3.SelectedItem & "', room= '" & Label6.Text & "', timein= '" & TextBox1.Text & "', timeout='" & TextBox2.Text & "'"
            comm = New MySqlCommand(Query, conn)
            Reader = comm.ExecuteReader


            If Reader.HasRows Then
                conn.Open()
                Query = "insert into door1 (room_no,name,subject,section,time_login) values ('" & Label6.Text & "', '" & ComboBox1.SelectedItem & "','" & ComboBox2.SelectedItem & "','" & ComboBox3.SelectedItem & "', '" & Label1.Text & "')"
                comm = New MySqlCommand(Query, conn)
                Reader = comm.ExecuteReader
                MessageBox.Show("Log in successful")



            Else
                MessageBox.Show("Access Denied")
                ComboBox1.Text = ""
                ComboBox2.Text = ""
                ComboBox3.Text = ""
                TextBox1.Text = Nothing
                TextBox2.Text = Nothing

            End If

            conn.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

    End Sub

but it displays an error " You have an error in your SQL Syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ' name= 'Daniel', subject= 'mech201', room= 'MAC laboratory', timein= '',timeout= '' at line1"

by the way im using visual basic 2010 .can somebody help me for the right command im going to use. Thank you

[Reverend Jim]

After you create the query but before you execute it add the following line

Debug.WriteLine(query)

then post the output here. You also might want to have a look at using paramterized queries.

[Santanu.Das]

This is my opinion that you have faced an exception for the use of same Command and reader ** objects for two different works simultaneously.
I am tring to describe it.
1) Connection object **conn is already opened at the line no. 14. So why are you tried to open an opened connection object at the line no. 22? This is an another cause for exception. Remove the line no.22.

2) comm and Reader are command object and SQlReader object and you have already used them to execute the SQL statement and to read data from dataTable at the line 17 & 18 respectively. How do you use them at the line no.24 & 25 for different works when they are already opened and busy to perform their works?
So declare another command and reader object to perform the insert method and use them at the line no.24 & 25.

3) thirdly, Reader = comm.ExecuteReader
ExecuteReader method of an command object should be used to read data from a table or a view. You use it rightly at the line no.18 to read the table name admin.schedule to read data from it. But ExecuteReader never performs to insert data into a table, which you tried to use at the line no.25.
To insert a data row into a table you must have use ExecuteNonQuery

Your codes should be

Public Class Form2

  'Declaring objects
  Dim conn As MySqlConnection
  Dim comm As MySqlCommand
  Dim Reader As MySqlDataReader

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    conn = New MySqlConnection
    conn.ConnectionString = "server=localhost;userid=root;password=;database=admin;"

    Try
        conn.Open()

        Dim Query As String = "select * from admin.schedule where section= '" & ComboBox1.SelectedItem & "', name= '" & ComboBox2.SelectedItem & "', subject= '" & ComboBox3.SelectedItem & "', room= '" & Label6.Text & "', timein= '" & TextBox1.Text & "', timeout='" & TextBox2.Text & "'"

        comm = New MySqlCommand(Query, conn)
        Reader = comm.ExecuteReader

        If Reader.HasRows Then

            Query = "insert into door1 (room_no,name,subject,section,time_login) values ('" & Label6.Text & "', '" & ComboBox1.SelectedItem & "','" & ComboBox2.SelectedItem & "','" & ComboBox3.SelectedItem & "', '" & Label1.Text & "')"

            'declaring a new command object
            Dim comm1 As MySqlCommand

            comm1 = New MySqlCommand(Query, conn)
            Dim Result As Integer = comm1.ExecuteNonQuery()

            comm1.Dispose
            if Result>0 Then
                MessageBox.Show("Log in successful")
            End If
        Else
            MessageBox.Show("Access Denied")
            ComboBox1.Text = ""
            ComboBox2.Text = ""
            ComboBox3.Text = ""
            TextBox1.Text = Nothing
            TextBox2.Text = Nothing
        End If
        conn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Hope it can help you.

[Mbai]

    > this works successfully for inserting values to mysql database

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 con = New MySqlConnection("server=localhost;user id=root;database=property")
        Dim rd As MySqlDataReader
        Try
            con.Open()
            Dim qr As String
            qr = "INSERT INTO Property.login(FullNames,IdNo,PhoneNo,Gender) VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox6.Text & "')"
            cmd = New MySqlCommand(qr, con)
            rd = cmd.ExecuteScalar
MsgBox("saved")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        con.Dispose()
 End Sub

[Mr.M]

Why didn't you use Parameters.AddWithValue("@room_no", Label6.Text) & ""? it works like charm and also if there is an error it will point straight to where you are having an error.

The error you are having indicate that there is a miss match of the code, I mean the MySQL script code doesn't match the scritp (Query) you wrote so that why it says check the version.

This normally happens when you are missing something like for instance "Select * myTable where name ='myname' this will also produce the same error that because I missed out the from which is part of the query or MySQL Script engine, to solve it I will have to recheck my code till I spot what I'm missing then write it which on this case is from. Other things which cuase this is missing the ', missing & "" at the end after the ) also on your error did you also notice that your tools TextBox1 and 2 are null?

timein= '',timeout= '' at line1"

I strongly suggest you write your code in this way:

Dim query As String = "INSERT INTO [PHONE DIRECTORY] VALUES(@id, @description)"
Dim command As New SqlCommand(query, conn)
command.Parameters.Add("@id", SqlDbType.Int).Value = Convert.toInt32(Textbox1.Text)
command.Parameters.Add("@description", SqlDbType.VarChar).Value = Textbox2.Text

Take note of @ to identify your parameters