473,326 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
4 3641
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
Could you post the code that inserts the data into the
table?

Tu-Thach
Nov 17 '05 #3
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought...
2
by: Andy Davis | last post by:
Dear Group I am trying to automate process of adding a new record id in my form using the following code when the user clicks the "Add New Record" button. For example if the last record id is...
4
by: Troy | last post by:
We recently installed the .Net framework on a windows 2000 server. Shortly after that we experienced intermitant problems running a web based program that accesses an Access 2002 database. The...
3
by: Brian Foree | last post by:
I am developing an ASP.NET application that uses Access 2000 as its backend, and have just started getting the following error on 2 ASP.NET pages that had been working until late last week (and I...
1
by: Reza Nabi | last post by:
Dear All: I have been developing ASP.NET application on MS Access database using ODBC. When I was trying to save more than 255 chars in a Memo field I got the following error. ERROR Invalid...
18
by: JohnR | last post by:
From reading the documentation, this should be a relatively easy thing. I have an arraylist of custom class instances which I want to search with an"indexof" where I'm passing an instance if the...
6
by: rn5a | last post by:
During registration, users are supposed to enter the following details: First Name, Last Name, EMail, UserName, Password, Confirm Password, Address, City, State, Country, Zip & Phone Number. I am...
2
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...
0
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.