对Access数据库使用更新语句(VB 2008) [英] Using an update statement for Access database (VB 2008)

问题描述

我正在尝试为我的程序创建一条更新语句，该语句将根据用户输入的数据使用SQL更新数据库，但不幸的是，我一次只能更新一列，有时却一无所获工作.我知道此功能是非常基本的，并且不能很安全地抵御攻击，但这只是我正在做的一个小项目.不幸的是，我只有基本的编程技能，所以我很难使这部分工作.如果能提供任何帮助，将不胜感激.

I'm trying to create an update statement for my program that will update a database using SQL based on the data the user inputs, unfortunately I have the problem that I can only update one column at a time and sometimes none of them work. I am aware that this function is very basic and not very secure against attacks, but it is a small project that I am making. Unfortunately I only have basic programming skills so I am having trouble getting this part to work. If any help could be given it will be much appreciated.

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

    Dim con As New OleDb.OleDbConnection

    Dim d1 As New OleDb.OleDbDataAdapter
    Dim d2 As New OleDb.OleDbDataAdapter
    Dim d3 As New OleDb.OleDbDataAdapter
    Dim d4 As New OleDb.OleDbDataAdapter
    Dim d5 As New OleDb.OleDbDataAdapter
    Dim d6 As New OleDb.OleDbDataAdapter
    Dim d7 As New OleDb.OleDbDataAdapter
    Dim d8 As New OleDb.OleDbDataAdapter
    Dim d9 As New OleDb.OleDbDataAdapter
    Dim d10 As New OleDb.OleDbDataAdapter

    Dim dt As New DataTable("Animals")

    'uses the 2010 compatible connection string
    con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = h:\Animals.accdb"
    con.Open()

    MsgBox("UPDATE Animals SET LatinName = '" & latintxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'")
    d1 = New OleDb.OleDbDataAdapter("UPDATE Animals SET LatinName = '" & latintxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d2 = New OleDb.OleDbDataAdapter("UPDATE Animals SET LocationFound = '" & locationtxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d3 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AverageHeight = '" & heighttxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d4 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AverageWeight = '" & weighttxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d5 = New OleDb.OleDbDataAdapter("UPDATE Animals SET DietaryNeeds = '" & diettxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d6 = New OleDb.OleDbDataAdapter("UPDATE Animals SET ConservationStatus = '" & statustxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d7 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AverageLifeSpan = '" & lifetxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d8 = New OleDb.OleDbDataAdapter("UPDATE Animals SET BreedingSeason = '" & breedtxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d9 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AverageLength = '" & lengthtxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d10 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AnimalName = '" & nametxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)

    d1.Fill(dt)
    d2.Fill(dt)
    d3.Fill(dt)
    d4.Fill(dt)
    d5.Fill(dt)
    d6.Fill(dt)
    d7.Fill(dt)
    d8.Fill(dt)
    d9.Fill(dt)
    d10.Fill(dt)

    con.Close()

End Sub

推荐答案

要执行更新命令，您可以编写一条语句并使用 OleDbCommand . oledb.oledbcommand.executenonquery.aspx" rel ="nofollow noreferrer"> ExecuteNonQuery 方法.

To execute an update command you could write a single statement and use a OleDbCommand with ExecuteNonQuery method.

Dim cmdText As String = "UPDATE Animals SET LatinName=?,LocationFound=?,AverageHeight=?," + 
                 "AverageWeight = ?, DietaryNeeds = ?, ConservationStatus = ?, " + 
                 "AverageLifeSpan = ?, BreedingSeason = ?, AverageLength = ? " +
                 "WHERE AnimalName = ?"

Using con = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = h:\Animals.accdb")
Using cmd = new OleDbCommand(cmdText, con)
    con.Open()
    cmd.Parameters.AddWithValue("@p1",latintxt.Text)
    cmd.Parameters.AddWithValue("@p2",locationtxt.Text)
    cmd.Parameters.AddWithValue("@p3",heighttxt.Text)
    cmd.Parameters.AddWithValue("@p4",weighttxt.Text)
    cmd.Parameters.AddWithValue("@p5",diettxt.Text)
    cmd.Parameters.AddWithValue("@p6",statustxt.Text)
    cmd.Parameters.AddWithValue("@p7",lifetxt.Text)
    cmd.Parameters.AddWithValue("@p8",breedtxt.Text)
    cmd.Parameters.AddWithValue("@p9",lengthtxt.Text)
    cmd.Parameters.AddWithValue("@p10",nametxt.Text)
    cmd.ExecuteNonQuery()
End Using
End Using

有几个问题需要注意，并且可能导致更新失败.
首先，所有参数值都是字符串类型，这可能是您的主要问题.如果数据库字段不是文本类型，则需要将这些值转换为适当的类型.
例如，如果字段AverageHeight是数字(双精度)，则参数应写为:

There are a couple of problems to be aware and that could lead to an update failure.
First, all the parameters values are of type string and this could be your main problem. If the database fields are not of text type then you need to convert these values to the appropriate type.
For example, if the field AverageHeight is numeric (double) then the parameter should be written as:

cmd.Parameters.AddWithValue("@p3",Convert.ToDouble(heighttxt.Text))

并且，当然，heighttxt中显示的文本应该可以转换为双精度.

and, of course, the text present in the heighttxt should be convertible to a double.

第二个问题是用于查找要更新的记录的参数的内容.
在查询中，该字段名为AnimalName，并使用Form1.txtname.Text搜索记录，但是在同一查询文本中，您尝试使用nametxt.Text更新WHERE子句中使用的相同字段.从逻辑上讲，这两个字段包含相同的值，因此您只需要一个参数.

The second problem is the content of the parameter used to find the record to update.
In your query this field is named AnimalName and you search the record using Form1.txtname.Text, but in the same query text you try to update the same field used in the WHERE clause using nametxt.Text. Logically these two fields contains the same value so you need only one parameter.

最后要记住的是，在OleDb中，参数不是通过名称来识别的，而是通过它们在命令文本中的位置来识别的.因此，请注意将参数添加到参数集合的正确顺序

Last point to remember, in OleDb the parameters are not recognized by their name but by their position inside the command text. So beware of the correct order in which the parameters are added to the parameter collection

这篇关于对Access数据库使用更新语句(VB 2008)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！