holi4683 holi4683 - 3 months ago 12
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


Code:

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 ()
Try
dbConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\aholiday\Desktop\Test\Test_be.accdb"
dbConnect.Open()
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
Try
dbInsert.CommandText = "INSERT INTO Log VALUES (Stamp,Stamp, Status, lc, pc, WeightAConvert);"
dbInsert.CommandType = CommandType.Text
dbInsert.Connection = dbConnect
dbInsert.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Catch ex As Exception
MessageBox.Show(ex.Message)
Me.Close()
End Try
End Sub


Updated code:

Try

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

dbCon.Open()

Dim rows = cmd.ExecuteNonQuery()

cmd.Dispose()

End Using

dbCon.Close()

dbCon.Dispose()

End Using



Catch ex As Exception

MessageBox.Show(ex.Message)

Exit Sub

End Try

Answer

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
        dbCon.Open()
        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