CODEWITHSUNDEEP
vb.netms-accessstring-concatenation
TwoDent
TwoDent

Reputation: 385

SQL Insert syntax error when I concatenate with "&"

I am using visual basic and access.

If I try concatenate with " +" , release the following error: Conversion from string "" to type 'Double' is not valid.

My code:

 Protected Friend Sub insertarProducto(ByVal codigo As String, ByVal modelo As String, ByVal serial As String, ByVal pallet As String, ByVal precio As Double, ByVal cantidad As Integer, ByVal descripcion As String)
    Try
        con.Open()
        adapter = New OleDbDataAdapter("Insert into Productos(Cod_Producto,Serial,Lote/pallet,Modelo,Descripcion,Precio,Cantidad)Values('" & codigo & "','" & serial & "','" & pallet & "','" & modelo & "','" & descripcion & "'," & precio & "," & cantidad & ")", con)
        adapter.Fill(tabla)
    Catch ex As Exception
        MsgBox("Problemas en la consulta: " + ex.Message(), MsgBoxStyle.Critical)
    End Try
    con.Close()
End Sub

The order of my columns is good. What's going on here?

Upvotes: 0

Views: 430

Answers (3)

TwoDent
TwoDent

Reputation: 385

(especially Ken ) I obeyed your advice. Finally I was able to successfully insert data to my database! First I modified the name of a column (Lote/pallet) and wrote this code:

Protected Friend Sub insertarProducto(ByVal codigo As String, ByVal modelo As String, ByVal serial As String, ByVal pallet As String, ByVal precio As Double, ByVal cantidad As Integer, ByVal descripcion As String, ByVal imagen As String)
    Dim cmd As String = "Insert into Productos(Cod_Producto,Serial,Lotpallet,Modelo,Descripcion,Precio,Cantidad,Imagen)Values(@Cod_Producto,@Serial,@Lotpallet,@Modelo,@Descripcion,@Precio,@Cantidad,@Imagen)"
    Try
        con.Open()
        comando = New OleDbCommand(cmd, con)
        comando.Parameters.AddWithValue("@Cod_Producto", codigo)
        comando.Parameters.AddWithValue("@Serial", serial)
        comando.Parameters.AddWithValue("@Lotpallet", pallet)
        comando.Parameters.AddWithValue("@Modelo", modelo)
        comando.Parameters.AddWithValue("@Descripcion", descripcion)
        comando.Parameters.AddWithValue("@Precio", precio)
        comando.Parameters.AddWithValue("@Cantidad", cantidad)
        comando.Parameters.AddWithValue("@Imagen", imagen)
        comando.ExecuteNonQuery()
        comando.Dispose()
    Catch ex As Exception
        MsgBox("Problemas en la consulta: " + ex.Message(), MsgBoxStyle.Critical)
    End Try
    con.Close()
End Sub

This web site help me a lot: http://www.codeguru.com/columns/vb/using-parameterized-queries-and-reports-in-vb.net-database-applications.htm

I dont know if was the best way to do it...Thank you so much for your advice!

Upvotes: 3

Werniel
Werniel

Reputation: 1

Your code is

OleDbDataAdapter("Insert into Productos(Cod_Producto,Serial,Lote/pallet,Modelo,Descripcion,Precio,Cantidad)Values('" & codigo & "','" & serial & "','" & pallet & "','" & modelo & "','" & descripcion & "'," & precio & "," & cantidad & ")", con)

Try checking the last 2 variables, it seeem that yoy are lacking a single and double quotes. It should look like this:

OleDbDataAdapter("Insert into Productos(Cod_Producto,Serial,Lote/pallet,Modelo,Descripcion,Precio,Cantidad)Values('" & codigo & "','" & serial & "','" & pallet & "','" & modelo & "','" & descripcion & "','" & precio & "','" & cantidad & "' "), con)

Upvotes: -1

Jande
Jande

Reputation: 1705

Use parameterized statements

            _command = New SqlCommand
            _command.Connection = connection
            _command.CommandTimeout = 30
             tabla = new dataset()
            _sqlda = New SqlDataAdapter(_command)

            _command.Parameters.AddWithValue("@Cod_Producto", codigo)
            _command.Parameters("@Cod_Producto").Direction = ParameterDirection.Input

            _command.Parameters.AddWithValue("@Serial", serial)
            _command.Parameters("@Serial").Direction = ParameterDirection.Input

         and so on..
          _sqlda.Fill(tabla)

or

      Public Function FillDataSet(query As String, ByVal ParamArray para() As Object) As DataSet

             dim _transaction As SqlTransaction
             Dim _command As SqlCommand

            _command = New SqlCommand(query, yourConnection)

            _ds = New DataSet
            _sqlda = New SqlDataAdapter(_command )
            _command.Transaction = _transaction

            For i = 0 To para.Count - 1
                _command.Parameters.AddWithValue("@" & i, para(i))
            Next

            _sqlda.Fill(_ds)

           return _ds

Upvotes: 1

Related Questions