access updates

[kapakra]

hiya

i am working on some software for PAT Testing, and its a simple app in the end it takes readings and save them to a database, access 2003.

ive over come all my problems apart from this one! and ive got a desk piled high with examples and book on this but im still stuck!

all i need to do is update a row with a year, eg the row C0001 was 2005, now it needs to be 2006.

here is my code:

Try
Dim DBConn As OleDbConnection
Dim dbCommand As New OleDbCommand()
DBConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/pat.mdb;")
dbCommand.CommandText = "Update data SET Where ident= " & TextBox2.Text & " year= " & TextBox1.Text
dbCommand.Connection = DBConn
dbCommand.Connection.Open()
dbCommand.ExecuteNonQuery()
DBConn.Close()
Catch err As System.Exception
MsgBox(err.Message)
End Try

now i know ive prob done something stupid, but im teaching this to myself so any help/pointers would be good.

thanks.

 

[jmcilhinney]

Your SQL code is wrong. You want to SET a field to a value WHERE the PK is a particular value, e.g.

             dbCommand.CommandText = "UPDATE data SET year= " & TextBox1.Text & " WHERE ident= " & TextBox2.Text

Any simple SQL tutorial would have told you that, including this one.

Also, I STRONGLY recommend that you break the habit of using string concatenation to build SQL statements. It is bad idea for several reasons. A proper mechanism exists for inserting values into SQL code:

 dbCommand.CommandText = "UPDATE data SET year = @year WHERE ident = @ident"
dbCommand.Parameters.AddWithValue("@year", TextBox1.Text)
dbCommand.Parameters.AddWithValue("@ident", TextBox2.Text)

Note also that this code still doesn't ensure that the user has entered valid data. What if they the user enters "foobar" in TextBox1?

 

[cjard]

I second Mr McIlhinney's comments and further, I would encourage you to use the new data access routines in VS 2005 as they make working with databases quite nice, and much less involved - all you do is write the SQL

This link will help:
http://msdn2.microsoft.com/en-us/library/fxsa23t6.aspx

 

[kapakra]

Thank you both for you help, but i think im still missing some thing!

ive been reading cjard's link, and ive tried using this:

 Dim doneRow() As Data.DataRow
        doneRow = PatDataSet.Tables("done").Select("ident = 'C0001'")
        doneRow(0)("year") = "2008"

As for as i can see this is the same syntax as the exaple i have, but i get the error:

Index was outside the bounds of the array.

what am i missing here!

any pointers would be great.

thanks

 

[cjard]

Change your code:

        Dim doneRow() As Data.DataRow
        doneRow = PatDataSet.Tables("done").Select("ident = 'C0001'")
If doneRow IsNot Nothing and doneRow.Rows.Count > 0 then
        doneRow(0)("year") = "2008"
else
  Messagebox.show("donerows is null or has no rows -> the no row has an ident of C0001")
Endif

 

[kapakra]

hiya

thanx for the advice guys, ive sorted the problem!

it was something weird with the dataset.

thanks