Hi

I need to insert some data from Text boxes in an aspx page into an Access DB but I keep getting a Data type mismatch in criteria expression error.

The problem is the Double which I think I have converted correctly. All the other fields are OK as strings but the value from the Grade TextBox needs to go into a Number field in the Access DB that has been set to Double. Here's my code

Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click

        Dim g As Double
        Dim st As String

        st = Grade.Text.Trim
        g = CDbl(st)


        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                                        & Server.MapPath("Test.mdb") & ";"
        Dim MySQL As String = "INSERT INTO tblPerson " & _
           "(Name, EMail, Webpage, Comments, Age, Birth, Grade) VALUES " & _
           "('" & Name.Text & "','" & Email.Text & "','" _
           & Webpage.Text & "','" & Comments.Text & "','" _
           & Age.Text & "','" & Birth.Text & "'," & g & ")"

        Dim MyConn As New Data.OleDb.OleDbConnection(strConn)
        Dim cmd As New Data.OleDb.OleDbCommand(MySQL, MyConn)
        MyConn.Open()
        cmd.ExecuteNonQuery()
        MyConn.Close()
        Response.Redirect("GuestBook.aspx")
    End Sub

The code works perfectly without the inclusion of Grade but generates the error when I include Grade.

Thanks
ViRiPuff

Hi

I need to insert some data from Text boxes in an aspx page into an Access DB but I keep getting a Data type mismatch in criteria expression error.

The problem is the Double which I think I have converted correctly. All the other fields are OK as strings but the value from the Grade TextBox needs to go into a Number field in the Access DB that has been set to Double. Here's my code

Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click

        Dim g As Double
        Dim st As String

        st = Grade.Text.Trim
        g = CDbl(st)


        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                                        & Server.MapPath("Test.mdb") & ";"
        Dim MySQL As String = "INSERT INTO tblPerson " & _
           "(Name, EMail, Webpage, Comments, Age, Birth, Grade) VALUES " & _
           "('" & Name.Text & "','" & Email.Text & "','" _
           & Webpage.Text & "','" & Comments.Text & "','" _
           & Age.Text & "','" & Birth.Text & "'," & g & ")"

        Dim MyConn As New Data.OleDb.OleDbConnection(strConn)
        Dim cmd As New Data.OleDb.OleDbCommand(MySQL, MyConn)
        MyConn.Open()
        cmd.ExecuteNonQuery()
        MyConn.Close()
        Response.Redirect("GuestBook.aspx")
    End Sub

The code works perfectly without the inclusion of Grade but generates the error when I include Grade.

Thanks
ViRiPuff

Try using the QBE in Access to build a SQL string to insert the values and then use that string to form the SQL your passing in. It will show you how Access passes in a double or if that still doesn't work get msgbox to show your generated SQL statement and compare it to the Access one.

Thanks for your reply I copied the code onto a new Sub and it works correctly.

I think the error was that I had left some fields blank when testing

ViRiPuFF

This article has been dead for over six months. Start a new discussion instead.