Update MS Access Database Records

28 Jun 2005 12:36 PM

thomasp

First of all, thanks for the help on my previous VB.NET/MS Access questions.
This time I need do the following

1. Connect to a table
2. step through each of its records
3. read the value of two of the records fields
4. pass those values to a function
5. write the value returned by the function the same record in a third field

Hope this makes sense. Basically I want to read records and based on values
of two of their fields I want to update another of their fields.

Thanks Thomas

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

28 Jun 2005 1:39 PM

Cowboy (Gregory A. Beamer) - MVP

Following what you are saying:
Use a DataReader to pull the records, as it is a forward only stream. Pull
the two bits you need, run the function and update. If you have problems with
the update (possible with Access), save the value of the primary key field
and the calculation to some form of persistent store (this can be a file or
even a hashtable (temporary persistence)). When you are done with all rows,
you can run through the persistent store and update values.

A better way:
Write a query in access that runs through each record and does the
calculation. Something like:

UPDATE MyTable
SET FieldNew = Col1 * Col2

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quoteHide quote

"thom***@msala.net" wrote:

> First of all, thanks for the help on my previous VB.NET/MS Access questions.
> This time I need do the following
>
> 1. Connect to a table
> 2. step through each of its records
> 3. read the value of two of the records fields
> 4. pass those values to a function
> 5. write the value returned by the function the same record in a third field
>
> Hope this makes sense. Basically I want to read records and based on values
> of two of their fields I want to update another of their fields.
>
> Thanks Thomas
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> ------->>>>>>http://www.NewsDemon.com<<<<<<------
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access
>

28 Jun 2005 2:34 PM

thomasp

Gregory,

Just to give you an idea of what I am doing, to two pieces of information
that are pulled from the records are the Db and Velocity of rounds tracked
by a radar. These two parameters are sent to the WeaponType function. This
function runs yet another query with the two parameters to see if they match
a weapon type. If so it returns a value and it is written to the record
where the parameters came from. The code gets run on about 3000 or more
targets each time, so I am trying to find the most profient method to use.

As I am using this, I don't think the one query route you mentioned will
work. If I am wrong, please let me know as I am sure that would work
faster.

Thanks,

Thomas

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

28 Jun 2005 5:41 PM

Mighty Chaffinch

> Just to give you an idea of what I am doing, to two pieces of information
> that are pulled from the records are the Db and Velocity of rounds tracked
> by a radar. These two parameters are sent to the WeaponType function.
> This
> function runs yet another query with the two parameters to see if they
> match
> a weapon type. If so it returns a value and it is written to the record
> where the parameters came from. The code gets run on about 3000 or more
> targets each time, so I am trying to find the most profient method to use.

It does sound like you're performing a database join in your code there. Is
there some reason why you can't do the whole thing in SQL?

MC

29 Jun 2005 11:03 AM

thomasp

I don't see how this could be done in a join. Maybe you could give an
example.

thanks

Thomas

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

28 Jun 2005 1:41 PM

thomasp

This is what I come up with, it works, but open for any further suggestions.

Private Sub cmdApply_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdApply.Click

        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim sql As String
        Dim strWeapon As String
        Dim myConnection As New OleDbConnection(strConn)
        Dim myCommand As New OleDbCommand(strSQL, myConnection)
        Dim myReader As OleDbDataReader

        strSQL = "SELECT * FROM Q36"
        myConnection.Open()
        myReader = myCommand.ExecuteReader()

        Try
            While myReader.Read()

                If Not IsDBNull(myReader!Db) And Not
       IsDBNull(myReader!Velocity) Then
                    strWeapon = funWeaponType(Val(myReader!Db),
       Val(myReader!Velocity))
                Else
                    strWeapon = "False"
                End If

                If strWeapon <> myReader!Weapon_Type Then
                    sql = "UPDATE Q36 SET Weapon_Type = '" & strWeapon & "'
       WHERE ID = " & myReader!ID
                    cmd = New OleDbCommand(sql, myConnection)
                    cmd.ExecuteNonQuery()
                End If

            End While
        Finally
            myReader.Close()
            myConnection.Close()
        End Try

    End Sub

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

28 Jun 2005 11:11 PM

Dennis

Using a DataSet might be a quicker solution (read the columns you want to
check as well as those to set into a dataset using .fill , change the dataset
values you need to change, then update the database using .update.

--
Dennis in Houston

Show quoteHide quote

"thom***@msala.net" wrote:

> This is what I come up with, it works, but open for any further suggestions.
>
> Private Sub cmdApply_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles cmdApply.Click
>
>         Dim cmd As System.Data.OleDb.OleDbCommand
>         Dim sql As String
>         Dim strWeapon As String
>         Dim myConnection As New OleDbConnection(strConn)
>         Dim myCommand As New OleDbCommand(strSQL, myConnection)
>         Dim myReader As OleDbDataReader
>
>         strSQL = "SELECT * FROM Q36"
>         myConnection.Open()
>         myReader = myCommand.ExecuteReader()
>
>         Try
>             While myReader.Read()
>
>                 If Not IsDBNull(myReader!Db) And Not
>        IsDBNull(myReader!Velocity) Then
>                     strWeapon = funWeaponType(Val(myReader!Db),
>        Val(myReader!Velocity))
>                 Else
>                     strWeapon = "False"
>                 End If
>
>                 If strWeapon <> myReader!Weapon_Type Then
>                     sql = "UPDATE Q36 SET Weapon_Type = '" & strWeapon & "'
>        WHERE ID = " & myReader!ID
>                     cmd = New OleDbCommand(sql, myConnection)
>                     cmd.ExecuteNonQuery()
>                 End If
>
>             End While
>         Finally
>             myReader.Close()
>             myConnection.Close()
>         End Try
>
>     End Sub
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
>       ------->>>>>>http://www.NewsDemon.com<<<<<<------
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access
>

29 Jun 2005 11:08 AM

thomasp

Dennis,

If you have time, could you post an example of using the DataSet with only
the needed columns?

Thanks,

Thomas

P.S. I am doing all this in Iraq. I have a very simple web site that I am
trying to keep up while I am here. It just shows some of the daily stuff
that happens here.

http://www.msala.net/archives.php

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

29 Jun 2005 11:24 PM

Dennis

Try something like the below...not tested of course as I have no idea what ID
is nor weapon_type nor Db Column, etc. are supposed be..I've assumed they are
columns in your DataBase and somewhere you are inputing an ID to match to the
DataBase ID column. I've also assumed that FunWeapon is a function that you
define somewhere. Good Luck!

        Dim sql As String
        Dim strWeapon As String
        'Define a DataSet and a Data Adapter
        Dim Dset as New DataSet
        Dim DBAdapt As New OleDbDataAdapter
       'Define SQL search string (not sure about the <>DbNull as it may be
different syntax required)
        strSQL = "SELECT ID, Db, Velocity, Weapon_Type FROM Q36 WHERE ID
='ID' AND Db<>DbNull AND Velocity<>DbNull"
         'Open the DataSet and return rows found matching SQL search into
Dataset
         DBAdapt.SelectCommand = New OleDb.OleDbCommand(strSQL, strConn)
         DBAdapt.Fill(Dset, "Q36")
         'Iterate thru each row returned and reset the Weapon_Type Field
         Dim drow as DataRow
         For Each drow in Dset.Tables(0).Rows
            strWeapon =
funWeaponType(Val(dr.Item("Db"),Val(dr.Item("Velocity)))
            If strWeapon <> dr.Item("Weapon_Type) Then
               dr.Item("Weapon_Type") = strWeapon
         next
         'Update the DataBase
         DBAdapt.Update(Dset, "Q36")
         'Clean up the objects
         DBAdapt.Dispose
         DSet.Dispose

--
Dennis in Houston

Show quoteHide quote

"thom***@msala.net" wrote:

> Dennis,
>
> If you have time, could you post an example of using the DataSet with only
> the needed columns?
>
> Thanks,
>
> Thomas
>
> P.S. I am doing all this in Iraq. I have a very simple web site that I am
> trying to keep up while I am here. It just shows some of the daily stuff
> that happens here.
>
> http://www.msala.net/archives.php
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> ------->>>>>>http://www.NewsDemon.com<<<<<<------
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access
>

29 Jun 2005 11:28 PM

Dennis

Sorry, I forgot to define a CommandBuilder...insert the following line just
after the Dim DBAdapt...

Dim DBCmdBuilder As New OleDb.OleDbCommandBuilder(DBAdapt)

--
Dennis in Houston

Show quoteHide quote

"Dennis" wrote:

> Try something like the below...not tested of course as I have no idea what ID
> is nor weapon_type nor Db Column, etc. are supposed be..I've assumed they are
> columns in your DataBase and somewhere you are inputing an ID to match to the
> DataBase ID column. I've also assumed that FunWeapon is a function that you
> define somewhere. Good Luck!
>
>         Dim sql As String
>         Dim strWeapon As String
>         'Define a DataSet and a Data Adapter
>         Dim Dset as New DataSet
>         Dim DBAdapt As New OleDbDataAdapter
>        'Define SQL search string (not sure about the <>DbNull as it may be
> different syntax required)
>         strSQL = "SELECT ID, Db, Velocity, Weapon_Type FROM Q36 WHERE ID
> ='ID' AND Db<>DbNull AND Velocity<>DbNull"
>          'Open the DataSet and return rows found matching SQL search into
> Dataset
>          DBAdapt.SelectCommand = New OleDb.OleDbCommand(strSQL, strConn)
>          DBAdapt.Fill(Dset, "Q36")
>          'Iterate thru each row returned and reset the Weapon_Type Field
>          Dim drow as DataRow
>          For Each drow in Dset.Tables(0).Rows
>             strWeapon =
> funWeaponType(Val(dr.Item("Db"),Val(dr.Item("Velocity)))
>             If strWeapon <> dr.Item("Weapon_Type) Then
>                dr.Item("Weapon_Type") = strWeapon
>          next
>          'Update the DataBase
>          DBAdapt.Update(Dset, "Q36")
>          'Clean up the objects
>          DBAdapt.Dispose
>          DSet.Dispose
> --
> Dennis in Houston
>
>
> "thom***@msala.net" wrote:
>
> > Dennis,
> >
> > If you have time, could you post an example of using the DataSet with only
> > the needed columns?
> >
> > Thanks,
> >
> > Thomas
> >
> > P.S. I am doing all this in Iraq. I have a very simple web site that I am
> > trying to keep up while I am here. It just shows some of the daily stuff
> > that happens here.
> >
> > http://www.msala.net/archives.php
> >
> > --
> > Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> >       ------->>>>>>http://www.NewsDemon.com<<<<<<------
> > Unlimited Access, Anonymous Accounts, Uncensored Broadband Access
> >