mysql conected to vb.net

[lssam]

l cant get add data to my data base or delete any .
l am using mysqlworkbench 5.3 CE , this is what l have done in vb.net

Imports MySql.Data.MySqlClient ' Establece una conexión la librería MySql
Module Module1



    Public con As New MySqlConnection 'Define la conexiòn
    Private _host As String = "localhost" ' Conecta la base de datos 
    Private _user As String = "root" 'Corresponde al usuario que usted definió cuando crea la BD
    Private _pass As String = "12345" 'Se refiere al password que ustde definíó en la BD
    Public cmd As New MySqlCommand
    Public reader As MySqlDataReader

    Sub CONECTAR()
        Try 'Es una cadena de conexión de elementos hacia la BD
            con = New MySqlConnection("Server=" + _host + ";User Id=" + _user + ";Password=" + _pass + ";Database=escuela;")
            con.Open()
            ' If con.State = ConnectionState.Open Then ACTIVE ESTO SI QUIERE COMPROBAR  QUE LA CONEXION ESTA BIEN
            ' MsgBox("Conexión correcta") ' Comprueba que la conexión se ha realizado bien ACTIVE ESTO SI QUIERE COMPROBAR  QUE LA CONEXION ESTA BIEN
            ' End If ACTIVE ESTO SI QUIERE COMPROBAR  QUE LA CONEXION ESTA BIEN

        Catch ex As Exception 'POR SI FALLA LA CONEXION
            Console.WriteLine(ex.Message) ' MSG ENVIAR UN ERROR
        End Try

    End Sub


End Module

Imports MySql.Data.MySqlClient

Public Class Form1

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

        Try

            CONECTAR()
            Dim Sql As String = "INSERT INTO asignatura(ID_ASIGNATURA,ABREVIATURA, NUMERO,NOMBRE )  VALUES (" + id.Text + " , '" + abrev.Text + "', '" + nom.Text + "', '" + num.Text + "')"
            cmd = New MySqlCommand(Sql, con)
            cmd.ExecuteNonQuery() ' READER AYUDA A RECUPERAR DATOS
            con.Clone()


        Catch ex As Exception 'POR SI FALLA LA CONEXION
            Console.WriteLine(ex.Message) ' MSG ENVIAR UN ERROR
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            CONECTAR()
            Dim Sql As String = "select * FROM asignatura WHERE ID_ASIGNATURA=' id.Text '"
            cmd = New MySqlCommand(Sql, con) ' CON ES EL NOMBRE DE LA CONEXION
            reader = cmd.ExecuteReader() ' READER AYUDA A RECUPERAR DATOS

            While reader.Read()
                id.Text = reader.GetString(0) ' OJO EL TEXT DEBE COINCIDIR CON LA DATA DE LA TABLA
                abrev.Text = reader.GetString(1) ' OJO EL TEXT DEBE COINCIDIR CON LA DATA DE LA TABLA
                num.Text = reader.GetString(2) ' OJO EL TEXT DEBE COINCIDIR CON LA DATA DE LA TABLA
                nom.Text = reader.GetString(3) ' OJO EL TEXT DEBE COINCIDIR CON LA DATA DE LA TABLA

            End While
        Catch ex As Exception 'POR SI FALLA LA CONEXION
            Console.WriteLine(ex.Message) ' MSG ENVIAR UN ERROR
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ' CLAUSULA SQL ELIMINAR REGISTRO
        Try
            CONECTAR()
            Dim Sql As String = "DELETE FROM asignatura Where ID_ASIGNATURA= 'id.Text.Trim'"
            cmd = New MySqlCommand(Sql, con)
            Dim myCommand As New MySqlCommand(Sql)
            myCommand.ExecuteNonQuery()

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub

End Class

[Tengkorak]

i think this wrong in your delete code :

Dim Sql As String = "DELETE FROM asignatura Where ID_ASIGNATURA= 'id.Text.Trim'"

should be:

Dim Sql As String = "DELETE FROM asignatura Where ID_ASIGNATURA= '" & id.Text.Trim  & "'"

id.Text.Trim is textbox control, doesn't it ?

[techgnome]

id is the text box.... id.text would be the text contents of the text box... id.text.trim would be a call to the Trim function of a string object that should trim all white space from the front and back of the string.

-tg

[lssam]

l should say first Thanks you .
yes , l missed the ( & &) , but in the DB ID_ASIGNATURA is Integer type that is why l l dont put "" ""

now l can insert , add but l cant delete

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ' CLAUSULA SQL ELIMINAR REGISTRO
        Try
            CONECTAR()
            Dim Sql As String = "DELETE FROM asignatura Where ID_ASIGNATURA= " & id.Text & ""
            cmd = New MySqlCommand(Sql, con)
            Dim myCommand As New MySqlCommand(Sql)
            myCommand.ExecuteNonQuery()

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub

    Public Function Sql() As Object
        Throw New NotImplementedException
    End Function

End Class

something wrong in this , l just dont know what is it ?

[formlesstree4]

I'm not sure why you have that public function there...that seems like a bad idea. Regardless, what exactly happens?

[lssam]

l cant delete from the data base that is all , l want to be able to delete data based on ID . as the code says .

should l delete that publci funciton ?

[lssam]

finally got the reason why :


here is final code : l had to excute the funciton ExecuteNonQuery() on the calling of the class MySqlCommand after passing to it sql and con arguments .

       CONECTAR()
            Dim Sql As String = "DELETE FROM asignatura Where ID_ASIGNATURA = " & id.Text & ""
            cmd = New MySqlCommand
            cmd(Sql, con).ExecuteNonQuery()
            MsgBox("El Registro a sido Eliinado")
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try