Update table using a FOR EACH statement 1

[proteome]

Is it possible to update a table using some kind of for each statement??

I would like to create a column that contains the results from a parsing procedure that depends on another column in the same table. Something like the following pseudocode:

FOR EACH Row in tableA
index1 = InStr(1, file, "/")
index2 = Instr((index1 + 1), file, ".")
difference = index2-index1-1
Update tableA Set parse = Mid(file, index1+1, difference)
End FOR EACH

The object of the code would be to parse every file entry in the table and update the same table by inserting the parsed data into another tuple.


Thanks much in advance

 

[NBartomeli]

Are you working with DataSets?

If so, an example might be something like:

[COLOR=green]'Assumes you have DataSet myDS which is filled[/color]
Dim dT as new DataTable
Dim dR as DataRow

[COLOR=green]'Point dT to a table in the dataset[/color]
dT = myDS.Tables(0) 'or .Tables("table_name")
[COLOR=green]'loop through the rows of the table[/color]
For Each dR in dT.Rows
    [COLOR=green]'pull values from each column[/color]
    Dim strFirstColValue as String = dR(0).ToString
    Dim strSecondColValue as String = dR(1).ToString
    [COLOR=green]'Do your data parsing here[/color]
    dR(0) = New_Value1
    dR(1) = New_Value2
Next

Hope this is sort of what you are looking for.

-Nick

 

[proteome]

I am working with tables in MS Access

 

[proteome]

Is it possible to loop through the rows of an Access table as you have demonstrated above??

 

[NBartomeli]

I only have limited experience with MS Access databases through VB.NET, but I will try to give you assumptions based on how you would do this through SQL Server

[COLOR=green]'I think you use OleDBConnection for access, correct if I am wrong[/color]
Dim cN as new OleDbConnection("connection_string")
Dim myDataAdapter as new OleDBDataAdapter("query_string",cN)
Dim myAccessTableDataSet as new Dataset
Dim dt as new datatable
Dim dr as datarow

Try
   [COLOR=green]'try and get the data from access[/color]
   MyDataAdapter.Fill(MyAccessTableDataSet)
   [COLOR=green]'point to the table you want from the query [/color]
   dt = MyAccessTableDataSet.Tables(0)
   For Each dr in dt.Rows
     [COLOR=green]'do whatever you want with the data, as shown above[/color]
   Next
Catch ex as exception
   [COLOR=green]'Handle exception here, messagebox maybe?[/color]
End try

Hope maybe this helps a little more

 

[proteome]

thanks NBartomeli,

question, would the "query string" basically be an sql string such as

SELECT * FROM tableA

and the connection would be

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= databasepath"

finally
Tables(0) would the 0 be my tablename


and how do you put your code into a code window or box

many thanks again I greatly appreciate your help and understanding. Hopefully not too many questions

 

[NBartomeli]

Yes, the "Query String" would be a SQL Select Statement, View, or Stored Procedure call.. any command that would return data

The connection string would be as you stated

And MyDataSet.Tables(0) refers to the first table returned by the query. Since you could, if you wanted, have the query execute more than one select statement, you have to specify the table you are looking for. You could simply put the name of the table in there, like .Tables("table_name"), I personally prefer to reference by index

I dont understand what you mean by "code window or box", can you ellaborate more? If you mean where to put the code to execute you could put it in a form_load procedure, a button press procedure, or in a module that fires independently from any form.

Hope this helps some more.

 

[proteome]

Thanks very much with a little tweaking and using a commandbuilder I got it to work thanks for all of your help.

The box I was talking about deals with pasting code into this forum, your code looks like

CODE

then a nice box, really cool and easy to read, how do you do that??

 

[NBartomeli]

use the

tags. anything you put between them will show up in the "code" boxes