A very basic read and update

TheWizardofInt

TheWizardofInt

Well-known member
Joined
Dec 31, 1969
Messages
333
Location
Orlando, FL
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.

Code: 
    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 doesnt 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?
 
Havent used the datareader before, but from what I can see you dont actually seem to be executing any update command. Also Im 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 Im sure if Im wrong someone will put me right :-)
 
Hog,
The Wiz isnt using a datareader, he declared a dataadapter named datareader.

Wiz,
The above isnt a good idea. See the confusion it causes.

I dont 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 its problems) that defines your insert, delete, and add parameters.

Then you can call the datadapters update method.

Heres a previous thread that will serve as a tutorial

http://www.computerhelp.forum/showthread.php?s=&threadid=71027

Jon
 
Last edited by a moderator:
hog is right!
Its 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 wouldnt use the expression datareader for a DataAdapter either. Thats really confusing. --> hog :D
 
Tried a different, more basic update:

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

Code: 
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?
 
TheWizardofInt

i think u should do this

Code: 
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
 
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?
 
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

Code: 
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()
 
That mixes a SQL command with an OLEDB connection and, when I changed it to OleDB, it didnt 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 didnt 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
 
Ok, got it.

This is what worked for me:

Code: 
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
 
Back
Top