A very basic read and update

[TheWizardofInt]

This should be the most basic thing you can do with ADO.Net, so of course I am baffled by it

 

I want to read a field in an Access table, update it, and write it back to the table.

 

   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       Dim oConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database/GMVAR.mdb") & ";")
       Dim lNumber As Long
       ' Build our SQL query
       Dim sNumber As String
       Dim cmd As New OleDb.OleDbCommand()
       Dim strSQL As String = "Select Login From VARS WHere pwd='xyz123'"
       Dim ds As DataTable = New Data.DataTable()
       Dim dc As Data.DataColumn

       cmd.Connection = oConnection

       cmd.CommandText = strSQL
       Dim datareader As OleDb.OleDbDataAdapter
       datareader = New OleDb.OleDbDataAdapter(cmd)

       datareader.Fill(ds)
       With ds.Rows(0)
           sNumber = .Item("Login").ToString
           lNumber = Val(sNumber) + 1
           sNumber = CStr(lNumber)
           .Item("Login") = sNumber
       End With
       strSQL = "Update VARS set login ='" & sNumber & "' where pwd='xyz123'"

       datareader.UpdateCommand = New OleDb.OleDbCommand(strSQL, oConnection)
       oConnection.Close()
   End Sub

 

This gives no error, but also doesn't update the field. The read ability is ok - I get the value from the field and it does increment

 

Where an I making what is likely a very obvious mistake?

[hog]

Haven't used the datareader before, but from what I can see you don't actually seem to be executing any update command. Also I'm sure I will be corrected here but I thought the datareader was a read only object. If updates are required the data adapter should be used. Like I say I'm sure if I'm wrong someone will put me right :-)

[jfackler]

Hog,

The Wiz isn't using a datareader, he declared a dataadapter named datareader.

 

Wiz,

The above isn't a good idea. See the confusion it causes.

 

I don't see a commandbuilder object (not ideal) or an sql statement defining your update command.

The update command you define above seems to only address connection to the database....the dataadapter already knows how to do that.

 

You need to utilize an sql statement (a commandbuilder object can build this for you but it has it's problems) that defines your insert, delete, and add parameters.

 

Then you can call the datadapters update method.

 

Here's a previous thread that will serve as a tutorial

 

http://www.xtremedotnettalk.com/showthread.php?s=&threadid=71027

 

Jon

Edited May 8, 2003 by jfackler

[APaule]

hog is right!

It's nice to assign an UpdateCommand to the dataadapter, but even nicer to call the Update for that adapter to save changes to the database. ;)

 

And I wouldn't use the expression datareader for a DataAdapter either. That's really confusing. --> hog :D

[TheWizardofInt]

Tried a different, more basic update:

 

This is right out of the book and the help files"

 

Dim mySelectText As String = _

"Select [Login], [PWD] From [Counter] Where [pwd] = 'xyz123'"

Dim mySelectConn As New SysADO.OleDbConnection _

("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database/VARS.mdb") & ";")

Dim myDataAdapter As New SysADO.OleDbDataAdapter(mySelectText, mySelectConn)

Dim sNumber As String = "1001"

myDataAdapter.UpdateCommand.CommandText = _

"Update COUNTER set login='" & sNumber & "' where pwd='xyz123'"

myDataAdapter.UpdateCommand.Connection = _

myDataAdapter.SelectCommand.Connection

[\code]

 

And it gives the error: Object reference not set to an instance of an object.

 

Anyone see the missing object?

[soniix]

TheWizardofInt

 

i think u should do this

 

dim cmd as sqlcommand
With ds.Rows(0)
   sNumber = .Item("Login").ToString
   lNumber = Val(sNumber) + 1
   sNumber = CStr(lNumber)
   .Item("Login") = sNumber
   strSQL = "Update VARS set login ='" & sNumber & "' where pwd='xyz123'"
   try
       cmd = new sqlcommand(strsql,oconnection)
       cmd.executenonquery()
   catch ex as exception
       msgbox(ex.tostring)
   end try
end with

[TheWizardofInt]

It comes back and says the connection is closed. Tried closing and reopening the connection, but it still says the connection is closed

 

Ever seen that before?

[soniix]

at which line this error occurs?

[soniix]

sorry i didnt read ur code right :)

 

first thing is that in ur first code u only try to update the row(0) thats only a single row not the whole table

 

second thing is, tell me what u really want to do, because as it looks to me u want to

 

read the row with pwd xyz123

then set its login number 1 higher

then write it back to the table right?

 

but how many rows r affected by this SELECT statement only one or more?

 

if there r more rows with that pwd then u can try this, its the simpliest way to update the table

 

'set the connection 
Dim oConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database/GMVAR.mdb") & ";")
'Your sNumber goes here
Dim sNumber As Integer = 12345 
'create the update command
Dim cmd As New SqlCommand("UPDATE vars SET login='" & sNumber & "' WHERE pwd='xyz123'", oConnection)
'open the connection
oConnection.Open()
'run the update command against the database
cmd.ExecuteNonQuery()
'cloe connection
oConnection.Close()

[TheWizardofInt]

That mixes a SQL command with an OLEDB connection and, when I changed it to OleDB, it didn't work.

 

Yes, I am trying to update one row out of a table with many rows. What I have done instead is to move the data to a database with one row, one field, and then the update didn't work either.

 

I must be missing something pretty basic not to be able to do something as simple as update a field in a database.

 

Do you have an example of an OleDb where you pull a field value from a table, change it and update the table

 

And I wanted to say, I appreciate that you made such an effort to figure out what I am trying to do and help me

[TheWizardofInt]

Ok, got it.

 

This is what worked for me:

 

Dim sNumber As String
       Dim MyConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database/Counter.mdb") & ";")
       MyConnection.Open()
       Dim MyCommand As New OleDb.OleDbCommand("SELECT [Count] FROM [Counter] WHERE ID = 1", MyConnection)
       Dim MyReader As OleDb.OleDbDataReader = MyCommand.ExecuteReader()
       If MyReader.Read Then
           sNumber = MyReader("Count")
           Dim lNumber As Long = Val(sNumber + 1)
           sNumber = CStr(lNumber)
       Else
           MyConnection.Close()
           Exit Sub
       End If
       MyReader.Close()
       MyCommand.Dispose()
       MyCommand = New OleDb.OleDbCommand("UPDATE [Counter] SET [Count] = '" & sNumber & "'", MyConnection)
       MyCommand.ExecuteNonQuery()
       MyConnection.Close()
       MyCommand.Dispose()

 

Why the brackets are necessary, I have no idea, but they were the key to it

[PlausiblyDamp]

not sure about counter but Count is a keyword in sql hence the need for the []

[soniix]

oh sorry hehe i dont use OleDb much,so i mistook OleDb with SQLDb sorry