Click here to Skip to main content
12,760,177 members (33,488 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB VB.NET
m getting this error "data type mismatch in criteria expression" in the line cd=oledbcom.executeNonQuery() in insert section

herz the code:
Imports System.Data.OleDb


Public Class Form1

    Dim oledbconn As OleDbConnection
    Dim oledbcom As OleDbCommand
    Dim oledbreader As OleDbDataReader
    Dim connectionString As String
    Dim command As String
    Dim cd As Integer






   

    Private Sub open_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles open.Click
        

        connectionString = "provider= Microsoft.Jet.OLEDB.4.0;Data source=Student.mdb"

        oledbconn = New OleDbConnection(connectionString)
        MsgBox("database connected")
        
       
    End Sub

    Private Sub fillreader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles fillreader.Click
        
        connectionString = "provider= Microsoft.Jet.OLEDB.4.0;Data source=Student.mdb"


        command = "select * from personinfo"
        oledbcom = New OleDbCommand(command, oledbconn)
        Try
            oledbreader = oledbcom.ExecuteReader()
            If (oledbreader.Read()) Then
                Txtroll.Text = oledbreader(0)
                Txtname.Text = oledbreader(1)
                txtemail.Text = oledbreader(2)
                Txtphone.Text = oledbreader(3)
                Txtpercentage.Text = oledbreader(4)
            End If
        Catch ex As Exception

        End Try
        


    End Sub

    Private Sub close_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles close.Click
       

        oledbconn.Close()
        MsgBox("database disconnected")



    End Sub

    Private Sub insert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles insert.Click
        
        connectionString = "provider= Microsoft.Jet.OLEDB.4.0;Data source=Student.mdb"

        oledbconn.Open()

        command = "insert into personinfo values('&Txtroll.text&','&Txtname.text&','&Txtemail.text&','&Txtphone.text&','&cint(Txtpercentage.text)&')"
        oledbcom = New OleDbCommand(command, oledbconn)
        cd = oledbcom.ExecuteNonQuery()
        MsgBox("data is entered in database")



        


    End Sub

    Private Sub Txtroll_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Txtroll.TextChanged

    End Sub

    Private Sub Txtname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Txtname.TextChanged

    End Sub
End Class
Posted 17-Jan-13 22:40pm
Updated 17-Jan-13 22:57pm
phil.o88K
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

&','&cint(Txtpercentage.text)&')"

You are converting text to integer but not removing the inverted quotes.
Get rid of them in the query - you won't even need the Cint.

E.g.&',&Txtpercentage.text&)"
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

command = "insert into personinfo values('&Txtroll.text&','&Txtname.text&','&Txtemail.text&','&Txtphone.text&','&cint(Txtpercentage.text)&')"


This is incorrect in several ways :

1- You should clearly state the name of the columns you are about to update in your insert query.
Thus :

command = "insert into personinfo (columnName1, columnName2, ...) values('&Txtroll.text&','&Txtname.text&','&Txtemail.text&','&Txtphone.text&','&cint(Txtpercentage.text)&')"


2- Do not ever construct a SQL query by concatenating strings. Better use parameterized query.
Thus :

command = "insert into personinfo (columnName1, columnName2, ...) values(@roll,@name,@email,@phone,@percentage)"
command.Parameters.AddWithValue("@roll", Txtrool.Text)
command.Parameters.AddWithValue("@name", Txtname.Text)


and so on...

Hope this helps.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170217.1 | Last Updated 18 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100