By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,453 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.BigInt instead of
OleDbType.Integer?

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.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
.

Nov 17 '05 #2

P: n/a
Could you post the code that inserts the data into the
table?

Tu-Thach
Nov 17 '05 #3

P: n/a
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_Fl ag,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,St ep_Number,Group_Name,N
otify_Only_Flag,Step_Description,Step_Rejected_Pre vious_Step_Number)
Values(@Unique_Routing_Number,@Step_Number,@Group_ Name,@Notify_Only_Flag
,@Step_Description,@Step_Rejected_Previous_Step_Nu mber)"
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_Numb er"))
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_Descripti on",
OleDb.OleDbType.VarChar, 255).Value = strStepDescription

InsertOledbCommand.Parameters.Add("@Notify_Only_Fl ag",
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

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

P: n/a
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,@N otify_Only_Flag
,@Step_Description,@Step_Rejected_Previous_Step_Nu mber

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

Hope that helps.
Tu-Thach

Robert Hanson <ro*****@cpuandsimplepdm.com> wrote in message news:<#d**************@TK2MSFTNGP11.phx.gbl>...
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_Fl ag,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,St ep_Number,Group_Name,N
otify_Only_Flag,Step_Description,Step_Rejected_Pre vious_Step_Number)
Values(@Unique_Routing_Number,@Step_Number,@Group_ Name,@Notify_Only_Flag
,@Step_Description,@Step_Rejected_Previous_Step_Nu mber)"
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_Numb er"))
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_Descripti on",
OleDb.OleDbType.VarChar, 255).Value = strStepDescription

InsertOledbCommand.Parameters.Add("@Notify_Only_Fl ag",
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

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

Nov 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.