CODEWITHSUNDEEP
vb.netsql-updatems-access-2010
Silver
Silver

Reputation: 29

Using an update statement for Access database (VB 2008)

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

Upvotes: 0

Views: 27250

Answers (1)

Steve
Steve

Reputation: 216363

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

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

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

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.

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

Upvotes: 2

Related Questions