Click here to Skip to main content
15,878,003 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone My name is Sarfaraz and I am stuck with the following problem in my project which I am developing in VB.Net and MS Access 2007.
I am actually having a table called INVOICE with field name as ac_no,credit,debit and dated. I am adding a new record in this table if the account number ac_no is not already available in the invoice table but if the ac_no is already available I just wanted to update the credit and debit fields with the amount credited and debited by entering values in two text boxes txtcredit and txtdebit but i am getting an error like data type mismatch in creteria expression.
I want like if credit field has 1000 and debit has 500 for a record having ac_no 112 then i want the values to be like 2000 for credit field and 1000 for debit field if i enter 1000 and 500 in txtcredit and txtdebit textboxes.

My code is as below:
VB
Private Sub btnok_Click(sender As Object, e As EventArgs) Handles btnok.Click
        If Con.State = ConnectionState.Open Then
            Con.Close()
        End If
        Dim query As String
        Dim credit, debit As Double
        Dim dt As DateTime
        dt = dtpicker1.Value.Date
        credit = CDbl(txtcredit.Text)
        debit = CDbl(txtdebit.Text)
        query = "update invoice set [credit]='" & CDbl(txtcredit.Text) & " ' ,[debit]= ' " & CDbl(txtdebit.Text) & " ',[dated]=' " & dtpicker1.Value.Date & " ' where [ac_no]= ' " & Integer.Parse(txtacno.Text) & " '"
        Dim cmd = New OleDbCommand
        Con.Open()
        cmd.CommandText = query
        cmd.Connection = Con
        cmd.ExecuteNonQuery()
        Con.Close()
        MsgBox("Successfully  edited ")
    End Sub

Thank you
Posted
Updated 29-Dec-14 22:00pm
v5

i think your issue is when your sql try to add number to string it will fail, if you give credit value as 55 then
SQL
set [credit]= [credit] + '55' 

because of '' your input will consider as string value. try by removing '' for number columns.
 
Share this answer
 
Comments
sarfarazbhat 30-Dec-14 4:00am    
No working and i tried the following code but still nothing happens displays data type mismatch in creteria:

Dim query As String
Dim credit, debit As Double
Dim dt As DateTime
dt = dtpicker1.Value.Date
credit = CDbl(txtcredit.Text)
debit = CDbl(txtdebit.Text)
query = "update invoice set [credit]='" & CDbl(txtcredit.Text) & " ' ,[debit]= ' " & CDbl(txtdebit.Text) & " ',[dated]=' " & dtpicker1.Value.Date & " ' where [ac_no]= ' " & Integer.Parse(txtacno.Text) & " '"
Dim cmd = New OleDbCommand
Con.Open()
cmd.CommandText = query
cmd.Connection = Con
cmd.ExecuteNonQuery()
Con.Close()
MsgBox("Successfully edited ")
Finally solved the problem with the following code:
<br />
query = "update invoice set [credit]='" & CDbl(txtcredit.Text) & " ' ,[debit]= ' " & CDbl(txtdebit.Text) & " ',[dated]=' " & dtpicker1.Value.Date & " ' where [ac_no]=" & txtacno.Text<br />



This is explained on:
http://msdn.microsoft.com/en-us/library/dd627355%28v=office.12%29.aspx[^]
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900