Update MS Access Database Records

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

NoSpamMgbworld
Tue Jun 28 08:39:07 CDT 2005

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!
***************************


"thomasp@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
>

thomasp
Tue Jun 28 08:41:24 CDT 2005

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

thomasp
Tue Jun 28 09:34:30 CDT 2005


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

Mighty
Tue Jun 28 12:41:40 CDT 2005

> 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

Dennis
Tue Jun 28 18:11:02 CDT 2005

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


"thomasp@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
>

thomasp
Wed Jun 29 06:03:59 CDT 2005

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

thomasp
Wed Jun 29 06:08:32 CDT 2005

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

Dennis
Wed Jun 29 18:24:02 CDT 2005

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


"thomasp@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
>

Dennis
Wed Jun 29 18:28:02 CDT 2005

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


"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
>
>
> "thomasp@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
> >