Professional Web Applications Themes

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

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

  1. #1

    Default 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


    Robert Hanson Guest

  2. #2

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

    Bob Hanson Wrote:

    Yes with the same error.

    Thanks for asking.

    Regards,

    Bob Hanson

    "Tu-Thach" <tuthach> wrote in message
    news:095101c34f95$0f64ede0$a301280aphx.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
    > >
    > >
    > >.
    > >

    Robert V. Hanson Guest

  3. #3

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

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

    Tu-Thach
    Tu-Thach Guest

  4. #4

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

    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 [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Robert Hanson Guest

  5. #5

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

    No, I checked the two numeric values and they are set to 1.

    Thanks for trying,

    Bob Hanson
    CEO
    Custom Programming Unlimited LLC

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Robert Hanson Guest

  6. #6

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

    I found it this morning... The Parameter.Add statements need to be in the
    same order as the SQL Insert statement.

    Thanks for trying to help me and I hope that this can help others..

    Best Regards,

    Bob Hanson

    "Robert Hanson" <roberthcpuandsimplepdm.com> wrote in message
    news:#I19n#7TDHA.3132tk2msftngp13.phx.gbl...
    > No, I checked the two numeric values and they are set to 1.
    >
    > Thanks for trying,
    >
    > Bob Hanson
    > CEO
    > Custom Programming Unlimited LLC
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Robert V. Hanson Guest

  7. #7

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

    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 <roberthcpuandsimplepdm.com> wrote in message news:<#dEVOt5TDHA.1712TK2MSFTNGP11.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 [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!
    Tu-Thach Guest

Similar Threads

  1. Access data type mismatch for empty form field
    By createmedia in forum Coldfusion Database Access
    Replies: 5
    Last Post: January 27th, 05:24 PM
  2. Replies: 6
    Last Post: June 30th, 11:56 PM
  3. Incrementing a Long Integer in an Access table
    By madfiddler in forum ASP Database
    Replies: 2
    Last Post: December 23rd, 10:40 AM
  4. Error while using OLEDB (Jet) for Access within ASP
    By Terry Murray in forum ASP Database
    Replies: 19
    Last Post: December 12th, 06:50 PM
  5. Long Raw Type Mismatch error
    By Sean in forum ASP
    Replies: 0
    Last Post: July 23rd, 04:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139