Error when trying to add an Access DB Record containing a Long Integer type field using OLEDB in ADO

[Robert Hanson]

Hi All,

I am trying to add a record to a datatable that is connected to an Access
database. I had no trouble with string and date fields, but for this
record, I have two Long Integer field types and I get the following error:

"Data type mismatch in criteria expression."

I am using OleDbType.Integer as the type which matched the Int32 size for
the Long Integer in Access, but I can't seem to get past the error.

Please Help!!

Bob Hanson

 

[Robert V. Hanson]

Bob Hanson Wrote:

Yes with the same error.

Thanks for asking.

Regards,

Bob Hanson

 

[Tu-Thach]

Could you post the code that inserts the data into the
table?

Tu-Thach

 

[Robert Hanson]

Here it is:

Public Function AddNewEroutingProcessStepsDBRecords(ByVal
strEroutingTemplateName As String, ByVal strErouting As String) As
Boolean
Dim strQuery As String = "Select
Routing_Name,Step_Number,Group_Name,Notify_Only_Flag,Step_Description,St
ep_Rejected_Previous_Step_Number From Routing_Template_Step_Data Where
Routing_Name = '" + strEroutingTemplateName + "'"
Dim OLEDataAdapter As New OleDb.OleDbDataAdapter(strQuery,
cnSimpleEroutingOleDb)
Dim TempDataTable As New DataTable("Routing_Template_Step_Data")
Dim iResult As Integer
Try
iResult = OLEDataAdapter.Fill(TempDataTable)
Dim TempDataRows(), TempDataRow As DataRow
Dim TempRowCounter As Integer
TempDataRows = TempDataTable.Select
If TempDataRows.Length > 0 Then
Dim lStepNumber, lStepRejectedPreviousStepNumber As Long
Dim strGroupName, strStepDescription As String
Dim bNotifyOnlyFlag As Boolean
Dim strInsertRecordQuery As String = "Insert Into
Routing_Process_Step_Data(Unique_Routing_Number,Step_Number,Group_Name,N
otify_Only_Flag,Step_Description,Step_Rejected_Previous_Step_Number)
Values(@Unique_Routing_Number,@Step_Number,@Group_Name,@Notify_Only_Flag
,@Step_Description,@Step_Rejected_Previous_Step_Number)"
Dim InsertOledbCommand As New
OleDb.OleDbCommand(strInsertRecordQuery, cnSimpleEroutingOleDb)
For TempRowCounter = 0 To TempDataRows.Length - 1
TempDataRow = TempDataRows(TempRowCounter)
lStepNumber = CLng(TempDataRow("Step_Number"))
lStepRejectedPreviousStepNumber =
CLng(TempDataRow("Step_Rejected_Previous_Step_Number"))
strGroupName = CStr(TempDataRow("Group_Name"))
strStepDescription =
CStr(TempDataRow("Step_Description"))
bNotifyOnlyFlag =
CBool(TempDataRow("Notify_Only_Flag"))

InsertOledbCommand.Parameters.Add("@Unique_Routing_Number",
OleDb.OleDbType.VarChar, 20).Value = strErouting
InsertOledbCommand.Parameters.Add("@Step_Number",
OleDb.OleDbType.Integer).Value = CInt(lStepNumber)

InsertOledbCommand.Parameters.Add("@Step_Rejected_Previous_Step_Number",
OleDb.OleDbType.Integer).Value = CInt(lStepRejectedPreviousStepNumber)
InsertOledbCommand.Parameters.Add("@Group_Name",
OleDb.OleDbType.VarChar, 30).Value = strGroupName

InsertOledbCommand.Parameters.Add("@Step_Description",
OleDb.OleDbType.VarChar, 255).Value = strStepDescription

InsertOledbCommand.Parameters.Add("@Notify_Only_Flag",
OleDb.OleDbType.Boolean).Value = bNotifyOnlyFlag
InsertOledbCommand.Connection.Open()
InsertOledbCommand.ExecuteNonQuery()
Next
End If
StopSimpleEroutingDBConnection()
Return True
Catch e As Exception
Return False
End Try
End Function

Thanks for working on this,

Bob Hanson
CEO
Custom Programming Unlimited LLC

 

[Tu-Thach]

Robert,
I look at your code below and saw that when you add the parameters to
your Insert statement, you added them out of order. You should add
the parameters in the same order as they are defined in your statement
i.e.

@Unique_Routing_Number,@Step_Number,@Group_Name,@Notify_Only_Flag
,@Step_Description,@Step_Rejected_Previous_Step_Number

That is because OleDb uses positional parameters and not named
parameters like SqlClient.

Hope that helps.
Tu-Thach