rodrigortz rodrigortz - 3 months ago 12
SQL Question

VB.net: INSERT INTO query generating all "-1" in Access Database

I have the following code and query:

Dim sqlCom As New OleDb.OleDbCommand("INSERT INTO clasificador (codigo, deudor, oportunidad, banca, ejecutivo, garantia, ciiu, ras, actividad, fecha, analista, estatus, concepto, ult_act, ingresos, tipo, cumplimiento, multa_pot, roa2, ie2, ant, act, covenants, faltas, oportunidades, costos, pmaa, potencial) VALUES (codigo=@codigo, deudor=@deudor, oportunidad=@oportunidad, banca=@banca, ejecutivo=@ejecutivo, garantia=@garantia, ciiu=@ciiu, ras=@ras, actividad=@actividad, fecha=@fecha, analista=@analista, estatus=@estatus, concepto=@concepto, ult_act=@ult_act, ingresos=@ingresos, tipo=@tipo, cumplimiento=@cumplimiento, multa_pot=@multa_pot, roa2=@roa2, ie2=@ie2, ant=@ant, act=@act, covenants=@covenants, faltas=@faltas, oportunidades=@oportunidades, costos=@costos, pmaa=@pmaa, potencial=@potencial)", conn)
sqlCom.Parameters.AddWithValue("@codigo", txtCodigo.Text)
sqlCom.Parameters.AddWithValue("@deudor", txtDeudor.Text)
sqlCom.Parameters.AddWithValue("@oportunidad", txtOportunidad.Text)
sqlCom.Parameters.AddWithValue("@banca", drpBanca.Text)
sqlCom.Parameters.AddWithValue("@ejecutivo", txtEjecutivo.Text)
sqlCom.Parameters.AddWithValue("@garantia", drpGarantia.Text)
sqlCom.Parameters.AddWithValue("@ciiu", txtCIIU.Text)
sqlCom.Parameters.AddWithValue("@ras", txtRAS.Text)
sqlCom.Parameters.AddWithValue("@actividad", txtActividad.Text)
sqlCom.Parameters.AddWithValue("@fecha", dteFecha.Text)
sqlCom.Parameters.AddWithValue("@analista", txtAnalista.Text)
sqlCom.Parameters.AddWithValue("@estatus", drpEstatus.Text)
sqlCom.Parameters.AddWithValue("@concepto", drpConcepto.Text)
sqlCom.Parameters.AddWithValue("@ult_act", dteUltAct.Text)
sqlCom.Parameters.AddWithValue("@ingresos", txtIngresos.Text)
sqlCom.Parameters.AddWithValue("@tipo", txtTipo.Text)
sqlCom.Parameters.AddWithValue("@cumplimiento", drpCumplimiento.Text)
sqlCom.Parameters.AddWithValue("@multa_pot", txtMultaPot.Text)
sqlCom.Parameters.AddWithValue("@roa2", txtROA2.Text)
sqlCom.Parameters.AddWithValue("@ie2", txtIE2.Text)
sqlCom.Parameters.AddWithValue("@ant", txtAnt.Text)
sqlCom.Parameters.AddWithValue("@act", txtAct.Text)
sqlCom.Parameters.AddWithValue("@covenants", txtCovenants.Text)
sqlCom.Parameters.AddWithValue("@faltas", drpFaltas.Text)
sqlCom.Parameters.AddWithValue("@oportunidades", txtOportunidades.Text)
sqlCom.Parameters.AddWithValue("@costos", txtCostos.Text)
sqlCom.Parameters.AddWithValue("@pmaa", txtPMAA.Text)
sqlCom.Parameters.AddWithValue("@potencial", txtPotencial.Text)
Try
'MsgBox("GO!")
Select Case MsgBox("┬┐Seguro que desea crear?", MsgBoxStyle.YesNo, "Confirmar")
Case MsgBoxResult.Yes
sqlCom.ExecuteNonQuery()
MsgBox("¡Listo!")
Me.Show()
Case MsgBoxResult.No
End Select
'MsgBox("DONE!")
Catch ex As Exception
MsgBox(ex.ToString)
End Try


I get no errors of any kind and everything works fine, but the information being inserted into the database is all "-1" (w/o quotes) in all fields (except date fields which have "12/29/1899"). I've gone through maybe 20 questions in Stackoverflow but they're all general syntax or spelling errors.

Any ideas?

Answer

Every single one of these is WRONG:

INSERT ...  VALUES (codigo=@codigo, deudor=@deudor
                       ^^^^^^^^

You're not inserting your values, you're insert the result of a bunch of equality tests. You're inserting a record, so there's NOTHING to test for equality against, and all the tests fail, hence your -1.

Insert syntax is

INSERT INTO sometable (field1, field2, ...) VALUES (value1, value2, ...)

so you should have JUST this:

INSERT ... VALUES (@codigo, @deudor, etc....)