holi4683 holi4683 - 2 months ago 8
Vb.net Question

Adding data to a table in Access from vb.net

My application reads a scale through a comport and does some logic. I then want it to output 6 readings to an Access database:

  • date/time time twice

  • status

  • Layer count

  • Part count

  • scale weight.

I pulled this code from online and the statement
dbInsert.ExcuteNonQuery ()
gets the error

data type mismatch in criteria expression


Dim dbInsert As New OleDb.OleDbCommand
Dim dbConnect As New OleDb.OleDbConnection
Dim Line As String = Environment.NewLine
Dim Status As String
Dim Stamp As Date
Dim pc As Double
Dim lc As Double

Sub AddToDb ()
dbConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\aholiday\Desktop\Test\Test_be.accdb"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Date_Stamp"
dbInsert.Parameters.Item("Date_Stamp").Value = Stamp
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Time_Stamp"
dbInsert.Parameters.Item("Time_Stamp").Value = Stamp
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Status"
dbInsert.Parameters.Item("Status").Value = Status
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Layer_Count"
dbInsert.Parameters.Item("Layer_Count").Value = lc
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Part_Count"
dbInsert.Parameters.Item("Part_Count").Value = pc
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Weight"
dbInsert.Parameters.Item("Weight").Value = WeightAConvert
dbInsert.CommandText = "INSERT INTO Log VALUES (Stamp,Stamp, Status, lc, pc, WeightAConvert);"
dbInsert.CommandType = CommandType.Text
dbInsert.Connection = dbConnect
Catch ex As Exception
End Try
Catch ex As Exception
End Try
End Sub

Updated code:


Dim SQL = "INSERT INTO Scale_Log (Date_Stamp,Time_Stamp, Status, Layer_Count, Part_Count, Weight) VALUES (Stamp,Stamp,Status,lc,pc,WeightAConvert)"

Using dbCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\aholiday\Desktop\Test\Line_6&7_Weight_Log_be.accdb")

Using cmd As New OleDbCommand(SQL, dbCon)

cmd.Parameters.Add("Date_Stamp", OleDbType.Date).Value = Stamp

cmd.Parameters.Add("Time_Stamp", OleDbType.Date).Value = Stamp

cmd.Parameters.Add("Status", OleDbType.VarChar).Value = Status

cmd.Parameters.Add("Lay_Count", OleDbType.Double).Value = lc

cmd.Parameters.Add("Part_Count", OleDbType.Double).Value = pc

cmd.Parameters.Add("Weight", OleDbType.Double).Value = WeightAConvert


Dim rows = cmd.ExecuteNonQuery()


End Using



End Using

Catch ex As Exception


Exit Sub

End Try


You are not specifying the columns in the SQL so you have no control over which variable is mapped to what database column. It will work when you just happen to add the Parameters in the right order. Dont leave it to chance: when it is wrong you can get a data mismatch error.

Also, it appears that you are using a global connection and DBCommand object. Dont do that. The next time you go to use that Command object it will already have 6 parameters defined in it. This can also result in a data type mismatch.

And of course, if the type of data passed does not match the db column type, it can result in data type mismatch. You want something like this:

Dim SQL = "INSERT INTO [Log] (colA, colB, colC...) VALUES (?,?,?,?,?,?)"

' dont use global provider objects
Using dbCon As New OleDbConnection(ACEConnStr)
    Using cmd As New OleDbCommand(SQL, dbCon)

        ' do these in the exact same order as the cols are listed in the SQL
        cmd.Parameters.Add("?", OleDbType.Date).Value = Stamp       ' colA
        cmd.Parameters.Add("?", OleDbType.Date).Value = Stamp       ' colB
        cmd.Parameters.Add("?", OleDbType.VarChar).Value = Status
        cmd.Parameters.Add("?", OleDbType.Double).Value = ItemWeight 'colF
        Dim rows = cmd.ExecuteNonQuery()
    End Using
End Using

First, Log can be a reserved word in Access-SQL, so I escaped the name1. The columns and their order is specified in the SQL statement. With Access/OleDB, you must add the data/parameters in that exact order.

The Using blocks create the target objects for you to use, then close and dispose of them at the end. Your connectionstring can be a global variable though.

Finally, rather than global vars for things like layercount consider passing them:

Sub AddToDb(foo As String, Stamp As DateTime, lc As Double....)

1. I am not sure that is actually true, but it is on a list