这个编码有什么问题 [英] what wrong with this coding

问题描述

我正在尝试通过vb.net更新MS Access数据库，但显示数据类型不匹配条件表达式.....我尝试将主键设置为自动编号...以及数字和文本，所以所有错误我都更改了&使用+也会出错，为什么..."where billno =" primarykey as number .....

i am trying to update ms access database through vb.net but show datatype mismatch criteria expression........i tried primary key as autonumber... and number and text then all so error i changed & using + then also error why... "where billno=" primarykey as number .....

Try
    Dim cmd As New OleDbCommand
    cmd.CommandText = "update Bill_Mst set Patient_no=" + ComboBox1.Text + ",dt=" + dtp1.Text + ",ward_charges=" + TextBox3.Text + ",No_of_day=" + TextBox4.Text + ",ward_amount=" + TextBox5.Text + ",Doctor_charges=" + TextBox6.Text + ",Xray_charges=" + TextBox7.Text + ",Net_amount=" + TextBox8.Text + " WHERE billNo=" + TextBox1.Text + ""
    cmd.Connection = c1
    c1.Open()
    cmd.ExecuteNonQuery()
    MsgBox("Record Update")
    Dim da As New OleDbDataAdapter("select * from Bill_Mst", c1)
    Dim dt As New DataTable
    da.Fill(dt)
    DataGridView1.DataSource = dt
    DataGridView1.Refresh()

Catch ex As Exception
    MsgBox(ex.Message)
End Try

推荐答案

实际上，您犯的错误与我今天早些时候看到的错误完全相同.这是我指的问题(和答案):如何在c#中搜索记录 [^ ]
这是我的答案的(部分)副本:

[...]您的代码在许多层次上都有缺陷.
以您的查询字符串为例，您只需构建它并在其中粘贴一些用户输入即可.好吧，假设此用户键入的内容类似于; --drop table student_info ...或更糟！即使是简单而纯真的输入(例如"D''Artagnan")也会打断您的查询.这称为 SQL注入 [ ^ ]并且非常危险(不幸的是非常常见).
因此，您需要参数化您的查询.幸运的是，这并不难.考虑以下代码:

Actually you are making the exact same mistakes I''ve seen someone make earlier today. Here is the question (and answers) I am referring to: how search record in c#[^]
Here is a (partial) copy of my answer:

[...] your code is flawed on many levels.
Take your query string, you simply build it and paste some user input in it. Well, suppose this user types something like ; --drop table student_info... Or worse! Even a simple and innocent input, like "D''Artagnan" will break your query. This is called SQL injection[^] and is very dangerous (and unfortunately very common).
So you need to parameterize your query. Luckily this is not hard. Consider the following code:

Dim cmd As New SqlCommand("SELECT * FROM student_info WHERE no = @Number", DBConn)
cmd.Parameters.AddWithValue("@Number", CInt(TextBox12.Text))
da = New SqlDataAdapter(cmd)
'...

注意您的代码如何变得更好地可读?您的查询现在受到SQL注入的保护，"D''Artagnan"不会破坏它！此外，当您使用参数化查询时，SQL Server性能将提高.这是双赢的局面！
但是，这并不能解决所有问题.您的TextBox12.Text是用户输入，因此必须经过验证.也许用户没有输入有效的Integer值.
因此，请考虑使用 Integer.TryParse [

Notice how your code becomes better readable? Your query is now protected from SQL injection and "D''Artagnan" will not break it! What''s more, SQL servers performance will increase when you use parameterized queries. It''s a win win win situation!
However, this doesn''t fix everything. Your TextBox12.Text is user input and thus must be validated. Perhaps the user didn''t enter a valid Integer value.
So consider the following code, using Integer.TryParse[^]:

Dim userInput As Integer
If Integer.TryParse(TextBox12.Text, userInput) Then
   cmd.Parameters.AddWithValue("@Number", userInput)
Else
   ' User did not type a valid numeric value.
   ' Possibly show them a MessageBox, whatever.
End If

因此，我们可以进行下一步.让您的用户知道是否有任何问题.现在，您抓到Exception，就像什么都没发生一样简单地返回.用户会想知道为什么他们看不到他们的记录...如果您不处理Exception，请让它冒泡到UI并至少向用户显示那里出了问题.实际上，我已经写了一篇有关正确使用Try Catch块的文章:使用Try ... Catch. ..，最后！ [ ^ ].
本文讨论的另一个主题是使用代码块 [对象实现IDisposable [处置 [

So that brings us to the next point. Letting your users know if anything went wrong. You now catch an Exception and simply return like nothing happened. The user will wonder why they don''t see their records... If you do NOT handle an Exception let it bubble up to the UI and at least show the user something has gone wrong there. Actually I have written an article on proper use of Try Catch blocks: Using Try... Catch..., Finally![^].
Another topic that article discusses is the Using block[^]. It''s about cleaning up resources, which I don''t see you do.
After you''re done with your SqlCommand, your SqlConnection or your SqlDataAdapter you should properly dispose of them. The rule here is that when an Object Implements IDisposable[^] you should call Dispose[^] once you''re done.

So now look at the completely revised code:

' I am assuming DBConn is a class field and might be used elsewhere.
' I assume ds is a class field.
Try
   ds.Clear()
   ' Put your SqlCommand in a Using block.
   Using cmd As New SqlCommand("SELECT * FROM student_info WHERE no = @Number")
      ' Validate the users input.
      Dim userInput As Integer
      If Integer.TryParse(TextBox12.Text, userInput) Then
         ' The users input was valid.
         cmd.Parameters.AddWithValue("@Number", userInput)
         ' put your SqlDataAdapter in a Using block.
         ' Not sure if a SqlDataAdapter automatically opens a connection, so...
         DBConn.Open()
         Using da As New SqlDataAdapter(cmd)
            da.Fill(ds, "student_info")
            Bind()
         End Using
      Else
         ' The users input was invalid.
         MessageBox.Show("Please enter a valid numeric value")
      End If
   End Using
Catch ex As Exception
   ' Perhaps log the Exception.
   MessageBox.Show(String.Format("An exception has occurred:{0}{1}", Environment.Newline, ex.Message))
Finally
   ' Closing a closed connection is not a problem.
   DBConn.Close
   ' Don't dispose the DBConn yet if it is used elsewhere.
End Try

该代码可能会更加优雅.但这是一个好的开始:)

也许如果您采用这些做法，您的错误将会消失，或者至少您将能够更好地找到它.
我认为您可以将此示例应用于自己的应用程序.您只需在我的示例中将Sql替换为OleDb，它仍然可以工作.变量名称不同，技术相同.
祝你好运！ :)

This code could be a lot more elegant. But this is a good start :)

Perhaps if you apply these practices your error will disappear or at least you will be better able to locate it.
I think you can apply this example to your own application. You can simply replace Sql in my example with OleDb and it will still work. Variable names differ, techniques are the same.
Good luck! :)

这篇关于这个编码有什么问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！