holi4683 holi4683 - 5 days ago 7
Vb.net Question

Adding data to an Access table

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 DBConnection and DBCommand object. Don't do that as it can result in all sorts of problems:

  • Fundamentally, a DBCommand object is query-specific so it has no re-use value
  • The next time you go to use that command object it will already have 6 parameters defined in it. This can result in too many parameters for the next time.
  • Since the DBCommand object requires a reference to the connection (dbInsert.Connection = dbConnect in your code), by not disposing of it, you are also not closing or disposing of that connection.

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)   ' use YOUR connection string
    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 may be a reserved word in Access-SQL, so I escaped the name1. The columns and their order is specified in the SQL statement.
  • The ?,?,?,? list in the SQL are placeholders. You can also use @p1, @p2,@p3... or even names, but OleDB will use them by position only (read on).
  • The data for each is provided with each cmd.Parameters.Add("?",... statement. This is how your code maps/provides the value for each parameter in the SQL.
  • A DateTime column will have both the date and time. I am not sure why you store them separately.
  • With other Providers they map the value based on the Parameter name (cmd.Parameters.Add("@firstName", ...).Value = myFirstNameVar). OleDB doesnt use named parameters as such, so you must provide the parameter values in the exact same order as the column names appear in the SQL.
  • 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.

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 in this case, but it is on a list

See also: Using Statement on MSDN

Comments