How To Loop On Rows From A Database Table

How To Loop On Rows From A Database Table

• CodeNet

  Dear ECList

  this code you can use to loop

  Your_RS.Open("SELECT * FROM YourTableName", Your_Con, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockBatchOptimistic)

  With Your_RS

  Do Until .EOF

  V_field1 = UCase(Your_RS.Fields("Yourfieldname").Value())

  V_field1 = Trim(V_field1)

  If V_field1 = V_field2 Then

  V_Found = True

  MsgBox("Repeated With The Following Item : " + Chr(13) + "Code : " + V_field1 + Chr(13) + "Name : " + UCase(Your_RS.Fields("Unit_Name").Value()), MsgBoxStyle.Exclamation, "Your Program Name")

  TextBox1.Text = ""

  TextBox1.Focus()

  Exit Do

  Else

  V_Found = False

  End If

  .MoveNext()

  Loop

  End With

  
  
  

• Nori_at_Japan

  Well, I've tried that avenue, but because the remote server is SQL Server and the local server is MS Access, I couldn't get that to work.

  But what I have now is almost working. I am having a weird issue that seems to be a timing issue though. Sometimes poping the records into the database doesn't work. They just don't go in...or sometimes only 2 out 5 go in. I have simplified the code down so that I only have a simple insert statement now, and it still doesn't always work.

  The steps I take are:

  1) Open a connection to the SQL Server DB (remote). Query the database to get the records I need.

  2) Open a connection the local access database. Delete the old set of records in that table.

  3) Loop through the records from the SQL Server database and insert them 1 at a time into the database. Put up a dialog box saying which item is being added.

  4) Close the db connections.

  When this is finished, sometimes the local access database table stays empty...and sometimes it works fine using exactly the same code. I am not getting any error messages or complaints and I don't understand why the exact same code works against the exact same records sometimes, but not other times.

  
  

• BaNgInG

  Thank you. I'll give this a try.
  

• Jos Wilson

  
  OK, well here is an example of what I have used just in case you might find it useful. Like I said, the connection I make is to the Access database and I'm importing from SQL Server (using an ODBC connection in the SQL string):

  INSERT INTO Orders (CustomerID, EmployeeID, ShippedDate)
  SELECT CustomerID,
  EmployeeID,
  IIf(ShippedDate < Now(),Now(),ShippedDate)
  FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];

  As you can see I used the IIf statement to make a change to the ShippedDate before it was imported.

  
  
  

• reZer

  Dim cn As New SqlClient.SqlConnection("User ID=myname;Password=myPassword;Initial Catalog=mydatabase;Data Source=123.22.44.35,1433")

  Dim cmd As New System.Data.SqlClient.SqlCommand("SELECT * FROM manufacturer_list", cn)

  Dim dr As System.Data.Common.DbDataReader

  cn.Open()

  dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

  Do While dr.Read()

  MsgBox (dr("manufacturer_name"))

  Loop

  dr.Close()

  
  

• GMatono

  Good question. To make a long story short, I am trying to kill two birds with one stone. I am using this as part of the process for copying records from a remote SQL Server to a local MS Access database, so that information can be used offline. Usually a few records are queried to be added to the local database, but before they go in, I need to check on a few things and make some adjustments, so I more or less need to touch each one, hence the looping on the records (during the loop I am looking at the records, making some adjustments and then poping them into the local database)

  Hope this helps you.

  
  

• Yaniv1

  
  Just out of curiosity, what is it that you need to with each row before inserting into an Access table It *may* be possible to accomplish this via Jet SQL.
  
  

• jeanlucve

  Dear friend,

  I'd like to ask you why are you need this

  to assign a validation rule or check record ...etc

  thanks

  
  
  

• llihp

  Paul, Thanks.

  I am really doing some relatively basic checking of a couple of the fields. For instance, I check the datestamp and reset it if its more than a month old.

  Right now I am using SqlConnection to hit the remote database and OleDbConnection to hit the local MS Acess one.

  Eron

  
  

• Steeler

  Well, it probably would, but its based on ADO.net, and I haven't yet ever gotten anything working when trying to use examples using that protocol. For instance if I paste the code from the example above into visual studio (see below) all the ADODB's are underlined and the error message reads "ADODB.Connection is not defined". I've never been able to figure out what I have to do to actually use ADO.net, so I've been using other methods of doing this (ie- "SqlConnection" or "OLEdbConnection")

  Dim cn As ADODB.Connection, rsSource As ADODB.Recordset, rsDest As ADODB.Recordset

  cn = New ADODB.Connection

  cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb")

  rsSource = cn.Execute("SELECT * FROM Customers")

  rsDest = MakeRS(rsSource)

  OpenAndFillRS(rsSource, rsDest)

  rsSource.Close()

  cn.Close()

  DataGrid1.DataSource = rsDest

  
  

• Gary Fuhr

  ECList wrote:

  I want to loop on the rows from a database table, but I can't work out how to do it. So far, the code I have is: Dim sConnectionString As String _ = "User ID=myname;Password=myPassword;Initial Catalog=mydatabase;Data Source=123.22.44.35,1433" Dim objConn As New SqlConnection(sConnectionString) objConn.Open() Dim sSQL As String = "SELECT * FROM manufacturer_list" Dim objCmd As New SqlCommand(sSQL, objConn) So from there, how could I loop through each record Thanks.

  i don't know about sqlserver but in oledb i used to continue it like this

  dim reader as oledbdatareader = objCmd.executereader() while reader.read() dim whateverVar = reader("column1") dim whateverVar = reader("column2") 'do what ever you want by those vars end while reader.close() objconn.close()

  
  
  

• ServiceDVD.it

  
  It doesn't sound like you tried using Jet OLEDB (w/ODBC to SQL), which may provide the functionality you need to handle this via SQL.

  In any event, I can't say for sure whether this would work because I still don't know what sort of operations you need to perform on the data. ;-)

  
  
  

• SSM

  This is a good example.

  http://support.microsoft.com/default.aspx scid=kb;en-us;247868

  
  
  
• How To Loop On Rows From A Database Table