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

Discussion in 'ASP .Net' started by Robert Hanson, Jul 21, 2003.

  1. 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, Jul 21, 2003
    #1
    1. Advertising

  2. Bob Hanson Wrote:

    Yes with the same error.

    Thanks for asking.

    Regards,

    Bob Hanson

    "Tu-Thach" <> wrote in message
    news:095101c34f95$0f64ede0$...
    > 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, Jul 21, 2003
    #2
    1. Advertising

  3. Robert Hanson

    Tu-Thach Guest

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

    Tu-Thach
    Tu-Thach, Jul 21, 2003
    #3
  4. 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

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Robert Hanson, Jul 21, 2003
    #4
  5. Robert Hanson

    Tu-Thach Guest

    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

    Robert Hanson <> wrote in message news:<#>...
    > 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
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    Tu-Thach, Jul 22, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robert Hanson
    Replies:
    1
    Views:
    337
    Robert V. Hanson
    Jul 22, 2003
  2. nita
    Replies:
    1
    Views:
    854
    Saravana
    Nov 20, 2004
  3. Siegfried Heintze
    Replies:
    1
    Views:
    4,661
    =?Utf-8?B?UmFrZXNoIFJhamFu?=
    Sep 12, 2005
  4. music4
    Replies:
    3
    Views:
    896
    CBFalconer
    Jan 5, 2004
  5. Suresh V
    Replies:
    5
    Views:
    3,678
    SaticCaster
    Jul 5, 2010
Loading...

Share This Page