如何在VB.Net和MS Access 2007中更新发票表 [英] How to update invoice table in VB.Net and MS Access 2007

问题描述

大家好我的名字是Sarfaraz，我在我的项目中遇到了以下问题，我在VB.Net和MS Access 2007中开发。

我实际上有一个名为 INVOICE ，字段名称为 ac_no ，信用卡，借记和日期。我在此表中添加了一条新记录，如果发票表中尚未提供帐号ac_no但是如果ac_no已经可用，我只想通过输入两个值来更新贷记和借方金额以及贷记金额文本框txtcredit和txtdebit但是我得到一个错误，比如creteria表达式中的数据类型不匹配。

我想要如果信用字段有1000而且借记卡有500用于具有ac_no 112的记录然后我想要值如果我在txtcredit和txtdebit文本框中输入1000和500，那么就像信用字段2000和借记字段1000一样。



我的代码如下：

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:

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

推荐答案

我认为你的问题是当你的sql尝试将数字添加到字符串时它会失败，如果你给出55的信用值那么

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

set [credit]= [credit] + '55' 

，因为''您的输入将被视为字符串值。尝试删除数字列的''。

because of '' your input will consider as string value. try by removing '' for number columns.

最后用以下代码解决了问题：

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 />

这个解释如下：

http://msdn.microsoft.com/en-us/library /dd627355%28v=office.12%29.aspx [ ^ ]

这篇关于如何在VB.Net和MS Access 2007中更新发票表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！