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

[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.Integ er 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" <tu*****@yahoo. com> wrote in message
news:09******** *************** *****@phx.gbl.. .

Have you tried to use OleDbType.BigIn t instead of
OleDbType.Integ er?

Tu-Thach

-----Original Message-----
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.Integ er 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
.

[Tu-Thach]

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

Tu-Thach

[Robert Hanson]

Here it is:

Public Function AddNewEroutingP rocessStepsDBRe cords(ByVal
strEroutingTemp lateName As String, ByVal strErouting As String) As
Boolean
Dim strQuery As String = "Select
Routing_Name,St ep_Number,Group _Name,Notify_On ly_Flag,Step_De scription,St
ep_Rejected_Pre vious_Step_Numb er From Routing_Templat e_Step_Data Where
Routing_Name = '" + strEroutingTemp lateName + "'"
Dim OLEDataAdapter As New OleDb.OleDbData Adapter(strQuer y,
cnSimpleEroutin gOleDb)
Dim TempDataTable As New DataTable("Rout ing_Template_St ep_Data")
Dim iResult As Integer
Try
iResult = OLEDataAdapter. Fill(TempDataTa ble)
Dim TempDataRows(), TempDataRow As DataRow
Dim TempRowCounter As Integer
TempDataRows = TempDataTable.S elect
If TempDataRows.Le ngth > 0 Then
Dim lStepNumber, lStepRejectedPr eviousStepNumbe r As Long
Dim strGroupName, strStepDescript ion As String
Dim bNotifyOnlyFlag As Boolean
Dim strInsertRecord Query As String = "Insert Into
Routing_Process _Step_Data(Uniq ue_Routing_Numb er,Step_Number, Group_Name,N
otify_Only_Flag ,Step_Descripti on,Step_Rejecte d_Previous_Step _Number)
Values(@Unique_ Routing_Number, @Step_Number,@G roup_Name,@Noti fy_Only_Flag
,@Step_Descript ion,@Step_Rejec ted_Previous_St ep_Number)"
Dim InsertOledbComm and As New
OleDb.OleDbComm and(strInsertRe cordQuery, cnSimpleEroutin gOleDb)
For TempRowCounter = 0 To TempDataRows.Le ngth - 1
TempDataRow = TempDataRows(Te mpRowCounter)
lStepNumber = CLng(TempDataRo w("Step_Number" ))
lStepRejectedPr eviousStepNumbe r =
CLng(TempDataRo w("Step_Rejecte d_Previous_Step _Number"))
strGroupName = CStr(TempDataRo w("Group_Name") )
strStepDescript ion =
CStr(TempDataRo w("Step_Descrip tion"))
bNotifyOnlyFlag =
CBool(TempDataR ow("Notify_Only _Flag"))

InsertOledbComm and.Parameters. Add("@Unique_Ro uting_Number",
OleDb.OleDbType .VarChar, 20).Value = strErouting
InsertOledbComm and.Parameters. Add("@Step_Numb er",
OleDb.OleDbType .Integer).Value = CInt(lStepNumbe r)

InsertOledbComm and.Parameters. Add("@Step_Reje cted_Previous_S tep_Number",
OleDb.OleDbType .Integer).Value = CInt(lStepRejec tedPreviousStep Number)
InsertOledbComm and.Parameters. Add("@Group_Nam e",
OleDb.OleDbType .VarChar, 30).Value = strGroupName

InsertOledbComm and.Parameters. Add("@Step_Desc ription",
OleDb.OleDbType .VarChar, 255).Value = strStepDescript ion

InsertOledbComm and.Parameters. Add("@Notify_On ly_Flag",
OleDb.OleDbType .Boolean).Value = bNotifyOnlyFlag
InsertOledbComm and.Connection. Open()
InsertOledbComm and.ExecuteNonQ uery()
Next
End If
StopSimpleErout ingDBConnection ()
Return True
Catch e As Exception
Return False
End Try
End Function

Thanks for working on this,

Bob Hanson
CEO
Custom Programming Unlimited LLC

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[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_N umber,@Group_Na me,@Notify_Only _Flag
,@Step_Descript ion,@Step_Rejec ted_Previous_St ep_Number

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

Hope that helps.
Tu-Thach

Robert Hanson <ro*****@cpuand simplepdm.com> wrote in message news:<#d******* *******@TK2MSFT NGP11.phx.gbl>. ..

Here it is:

Public Function AddNewEroutingP rocessStepsDBRe cords(ByVal
strEroutingTemp lateName As String, ByVal strErouting As String) As
Boolean
Dim strQuery As String = "Select
Routing_Name,St ep_Number,Group _Name,Notify_On ly_Flag,Step_De scription,St
ep_Rejected_Pre vious_Step_Numb er From Routing_Templat e_Step_Data Where
Routing_Name = '" + strEroutingTemp lateName + "'"
Dim OLEDataAdapter As New OleDb.OleDbData Adapter(strQuer y,
cnSimpleEroutin gOleDb)
Dim TempDataTable As New DataTable("Rout ing_Template_St ep_Data")
Dim iResult As Integer
Try
iResult = OLEDataAdapter. Fill(TempDataTa ble)
Dim TempDataRows(), TempDataRow As DataRow
Dim TempRowCounter As Integer
TempDataRows = TempDataTable.S elect
If TempDataRows.Le ngth > 0 Then
Dim lStepNumber, lStepRejectedPr eviousStepNumbe r As Long
Dim strGroupName, strStepDescript ion As String
Dim bNotifyOnlyFlag As Boolean
Dim strInsertRecord Query As String = "Insert Into
Routing_Process _Step_Data(Uniq ue_Routing_Numb er,Step_Number, Group_Name,N
otify_Only_Flag ,Step_Descripti on,Step_Rejecte d_Previous_Step _Number)
Values(@Unique_ Routing_Number, @Step_Number,@G roup_Name,@Noti fy_Only_Flag
,@Step_Descript ion,@Step_Rejec ted_Previous_St ep_Number)"
Dim InsertOledbComm and As New
OleDb.OleDbComm and(strInsertRe cordQuery, cnSimpleEroutin gOleDb)
For TempRowCounter = 0 To TempDataRows.Le ngth - 1
TempDataRow = TempDataRows(Te mpRowCounter)
lStepNumber = CLng(TempDataRo w("Step_Number" ))
lStepRejectedPr eviousStepNumbe r =
CLng(TempDataRo w("Step_Rejecte d_Previous_Step _Number"))
strGroupName = CStr(TempDataRo w("Group_Name") )
strStepDescript ion =
CStr(TempDataRo w("Step_Descrip tion"))
bNotifyOnlyFlag =
CBool(TempDataR ow("Notify_Only _Flag"))

InsertOledbComm and.Parameters. Add("@Unique_Ro uting_Number",
OleDb.OleDbType .VarChar, 20).Value = strErouting
InsertOledbComm and.Parameters. Add("@Step_Numb er",
OleDb.OleDbType .Integer).Value = CInt(lStepNumbe r)

InsertOledbComm and.Parameters. Add("@Step_Reje cted_Previous_S tep_Number",
OleDb.OleDbType .Integer).Value = CInt(lStepRejec tedPreviousStep Number)
InsertOledbComm and.Parameters. Add("@Group_Nam e",
OleDb.OleDbType .VarChar, 30).Value = strGroupName

InsertOledbComm and.Parameters. Add("@Step_Desc ription",
OleDb.OleDbType .VarChar, 255).Value = strStepDescript ion

InsertOledbComm and.Parameters. Add("@Notify_On ly_Flag",
OleDb.OleDbType .Boolean).Value = bNotifyOnlyFlag
InsertOledbComm and.Connection. Open()
InsertOledbComm and.ExecuteNonQ uery()
Next
End If
StopSimpleErout ingDBConnection ()
Return True
Catch e As Exception
Return False
End Try
End Function

Thanks for working on this,

Bob Hanson
CEO
Custom Programming Unlimited LLC

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!