CODEWITHSUNDEEP
mysqlvb.net
Guillermo Crucci
Guillermo Crucci

Reputation: 13

Error making an insert to mysql from VB.net

This was working ok but suddenly it stopped to work and started to give me this error, either inserting new data or updating. I must say that I know is bad to concatenate but our tachers obligates us do it this way.

I tried the same query in navicat and it works, but, when i put it in VB.net it start to give me this error.

Query code:

UPDATE personal SET nombre = '" & ModUser_txtNombre.Text & "', 2nombre = '" & ModUser_txt2Nombre.Text & "', apellido = '" & ModUser_txtApellido.Text & "', 2apellido = '" & ModUser_txt2apellido.Text & "', sexo = '" & ModUser_cbSexo.Text & "', f_nac = '" & ModUser_dateFNacimiento.Text & "', calle = '" & ModUser_txtDirCalle.Text & "', numero = " & ModUser_txtDirNumero.Text & ", estado_civil = '" & ModUser_cbEstCivil.Text & "', email = '" & ModUser_txtEmail.Text & "', hijos = '" & ModUser_txtHijos.Text & "', tel_celular = " & ModUser_txtCelular.Text & ", tel_fijo = " & ModUser_txtFijo.Text & ", f_ingreso = '" & ModUser_dateFIngreso.Text & "', cargo = " & cargo.ToString & ", grado = " & ModUser_cbGrado.Text & ", departamento = " & departamento.ToString & ", email_empr = '" & ModUser_txtMailEmpresarial.Text & "', tlf_empr = " & ModUser_txtTlfEmpresarial.Text & ", rol = " & rol.ToString & " WHERE doc = " & List_txtBuscar.Text & "(now insert)    

    insert into Personal (rol, doc, nombre, apellido, passwd, sexo, f_nac, 
                          estado_civil, f_ingreso, cargo, grado, dpto, email,
                           email_empr, tlf_empr, calle, numero, 2nombre,
                           2apellido, activo,) 
values(" & adduser_rol & ", " & AddUser_txtDocumento.Text & ", '" 
  & AddUser_txtNombre.Text & "', '" & AddUser_txtApellido.Text & "', " 
  & AddUser_txtContraseña.Text & ", '" & AddUser_cbSexo.Text & "', '" 
  & AddUser_dateFNacimiento.Text & "', '" & AddUser_cbEstCivil.Text & "', '" 
  & AddUser_dateFIngreso.Text & "', '" & AddUser_txtCargo.Text & "', " 
  & AddUser_cbGrado.Text & ", '" & AddUser_txtDepartamento.Text & "', '" 
  & AddUser_txtEmail.Text & "', '" & AddUser_txtMailEmpresarial.Text & "', " 
  & AddUser_txtTlfEmpresarial.Text & ", '" & AddUser_txtDirCalle.Text & "', " 
  & AddUser_txtDirNumero.Text & ", '" & 
     AddUser_txt2Nombre.Text & "', '" & AddUser_txt2apellido.Text & "', 's')

Code in visual:

   cnn.Open()
        Dim Query As String
        Query = "UPDATE personal SET nombre = '" & ModUser_txtNombre.Text & "', 2nombre = '" & ModUser_txt2Nombre.Text & "', apellido = '" & ModUser_txtApellido.Text & "', 2apellido = '" & ModUser_txt2apellido.Text & "', sexo = '" & ModUser_cbSexo.Text & "', f_nac = '" & ModUser_dateFNacimiento.Text & "', calle = '" & ModUser_txtDirCalle.Text & "', numero = " & ModUser_txtDirNumero.Text & ", estado_civil = '" & ModUser_cbEstCivil.Text & "', email = '" & ModUser_txtEmail.Text & "', hijos = '" & ModUser_txtHijos.Text & "', tel_celular = " & ModUser_txtCelular.Text & ", tel_fijo = " & ModUser_txtFijo.Text & ", f_ingreso = '" & ModUser_dateFIngreso.Text & "', cargo = " & cargo.ToString & ", grado = " & ModUser_cbGrado.Text & ", departamento = " & departamento.ToString & ", email_empr = '" & ModUser_txtMailEmpresarial.Text & "', tlf_empr = " & ModUser_txtTlfEmpresarial.Text & ", rol = " & rol.ToString & " WHERE doc = " & List_txtBuscar.Text & ""
        Dim cmd As New MySqlCommand(Query, cnn)
        rdr = cmd.ExecuteReader
        cnn.Close()

(now insert)

Dim adduser_rol As Integer

        If AddUser_rbAdmin.Checked Then
            adduser_rol = 1
        ElseIf AddUser_rbUsuario.Checked Then
            adduser_rol = 2
        Else
            MsgBox("Debe seleccionar si el nuevo empleado es usuario o administrador.")
        End If
        Dim cnn As New MySqlConnection
        cnn.ConnectionString = ("data source=localhost;user id=root; password='1234';database=tax")
        Try
            Dim Query As String
            Query = "insert into Personal (rol, doc, nombre, apellido, passwd, sexo, f_nac, estado_civil, f_ingreso, cargo, grado, dpto, email, email_empr, tlf_empr, calle, numero, 2nombre, 2apellido, activo,) values(" & adduser_rol & ", " & AddUser_txtDocumento.Text & ", '" & AddUser_txtNombre.Text & "', '" & AddUser_txtApellido.Text & "', " & AddUser_txtContraseña.Text & ", '" & AddUser_cbSexo.Text & "', '" & AddUser_dateFNacimiento.Text & "', '" & AddUser_cbEstCivil.Text & "', '" & AddUser_dateFIngreso.Text & "', '" & AddUser_txtCargo.Text & "', " & AddUser_cbGrado.Text & ", '" & AddUser_txtDepartamento.Text & "', '" & AddUser_txtEmail.Text & "', '" & AddUser_txtMailEmpresarial.Text & "', " & AddUser_txtTlfEmpresarial.Text & ", '" & AddUser_txtDirCalle.Text & "', " & AddUser_txtDirNumero.Text & ", '" & AddUser_txt2Nombre.Text & "', '" & AddUser_txt2apellido.Text & "', 's', )"
            cnn.Open()
            Dim cmd As New MySqlCommand
            cmd = New MySqlCommand(Query, cnn)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Usuario guardado")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

And in both when I press the button it gives me the following errors:

Updating Unknown column 'i' in 'field list'

Inserting ..."for the right syntax to use near')values(X,XXXXXXXX, 'XXXX', 'XXXX', XXXXXXXX, 'XXXXXX', 'XXXX-XX-XX','c' at line 1 (being X any value possible)

Table "Personal" desing:

1

Upvotes: 0

Views: 83

Answers (1)

nbk
nbk

Reputation: 49373

You sql statement is wrong. At the end of the columns list you have an comma too much

 Query = "insert into Personal (rol, doc, nombre, apellido, passwd, sexo, f_nac
    , estado_civil, f_ingreso, cargo, grado, dpto, email, email_empr, tlf_empr
    , calle, numero, 2nombre, 2apellido, activo) values(" & adduser_rol & ", " 
     & AddUser_txtDocumento.Text & ", '" & AddUser_txtNombre.Text & "', '" 
     & AddUser_txtApellido.Text & "', " & AddUser_txtContraseña.Text & ", '" 
     & AddUser_cbSexo.Text & "', '" & AddUser_dateFNacimiento.Text & "', '" 
     & AddUser_cbEstCivil.Text & "', '" & AddUser_dateFIngreso.Text & "', '" 
      & AddUser_txtCargo.Text & "', " & AddUser_cbGrado.Text & ", '" 
      & AddUser_txtDepartamento.Text & "', '" & AddUser_txtEmail.Text & "', '" 
      & AddUser_txtMailEmpresarial.Text & "', " & AddUser_txtTlfEmpresarial.Text 
      & ", '" & AddUser_txtDirCalle.Text & "', " & AddUser_txtDirNumero.Text 
      & ", '" & AddUser_txt2Nombre.Text & "', '" & AddUser_txt2apellido.Text 
      & "', 's', )"

Use in Vb net also Prepared statement Like

cmd.Connection = conn

cmd.CommandText = "INSERT INTO  Personal (rol, doc, nombre, apellido, passwd, sexo
                  , f_nac, estado_civil, f_ingreso, cargo, grado, dpto, email, email_empr, tlf_empr
    , calle, numero, 2nombre, 2apellido, activo) VALUES(@adduser_rol, @AddUser_txtDocumento,.....)"

cmd.Prepare()

cmd.Parameters.AddWithValue("@adduser_rol", adduser_rol)
cmd.Parameters.AddWithValue("@AddUser_txtDocumento", AddUser_txtDocumento.Text)
...
cmd.ExecuteNonQuery()

Upvotes: 1

Related Questions