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 up

Solution 1:

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

Dim cmdText AsString = "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()
EndUsingEndUsing

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