none
Error when calling MS Access queries from VB.NET RRS feed

  • Question

  • I am using the following code to execute an MS Access query from VB.Net (VS2010)

    Dim cSql As String = ""
    Dim cnnAccess As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; Data Source=C:\Fld1\tran1.mdb")
    cmdAccess = New System.Data.OleDb.OleDbCommand(cSql, cnnAccess)
    cmdAccess.CommandType = CommandType.StoredProcedure
    cmdAccess.CommandText = "[G - UpdateQT]"
    cmdAccess.ExecuteScalar()

    The above code gives me the error "Data type mismatch in criteria expression."

    In the access query I have the following sQL

    UPDATE TrustT SET Field8 = format(Field8,'0000000000000.00');

    When I open MS Access and double click on this same query ([G - UpdateQT]), it works without any issues. I dont know why it's not working with VB.NET.

    Any other MS queries from VB.NET work fine.

    Any help is greatly appreciated.


    • Edited by dper77 Wednesday, September 27, 2017 5:41 PM
    Wednesday, September 27, 2017 5:40 PM

All replies

  • What is the data type of Field8? If it's numeric then you cannot assign the results of Format, which would be a text value, to a numeric value.

    Keep in mind that Access and OLEDB use different rules when executing SQL.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, September 27, 2017 7:59 PM
  • Hi dper77,

    Based on your code, I find some place you might need to modify.

    Public Sub LoadMyData(ByVal connectionString As String)
            Dim connection As New OleDbConnection(connectionString)
            Dim command As New OleDbCommand()
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "name of the stored procedure"
            connection.Open()
            command.ExecuteScalar()
            connection.Close()
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 28, 2017 2:37 AM
    Moderator