I need Step By Step Data Walkthrough

[dre-logics]

I use visual basic 8.0 en MySQL.

I have a connection witho my MySQL database "test" with Myconn.
I have two table's :

Rental
Articlenr Articledis Articlerent
100 beerclass 15
200 wineclass 10

Stock
Articlenr Articledis stock
200 Wineclass 25

So far i have this :

Dim con As New SqlConnection
Dim cmd As New SqlCommand
MYconn.Open()
cmd.Connection = Myconn
cmd.CommandText = "SELECT * FROM rental ORDER BY articlenr"
Dim lrd As SqlDataReader = cmd.ExecuteReader()

i want to go the first record in table rental
While lrd.Read()
*I this loop i want to do the following!!! *
i want to look of the article of rental appears in stock table
Yes, i want update record stock table : stock.stock= stock.stock-rental.Articlerent
No, i want to insert record stock table with the record from the rental table
After this i want to the next record in rental table, until all eof() rental table
End While

Thanks in advance,

Andre

[jt_murphree]

The way I do this is with a dataTable and dataRow

dim dbTable as dataTable
dim dbRow as dataRow
dim rowCount as int32

Dim dataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM table ORDER BY ID", con)
            dataAdapter.Fill(dbTable)
            rowCount = dbTable.Rows.Count

            For i As Int32 = 0 To rowCount - 1
                dbRow = matTable.Rows(i)
                cmbMat1.Items.Add(dbRow.Item("ID"))
            Next

In this case I am using the data to fill a combobox but you can modify it for any purpose. us dbRow.Item("<field name>") to get the values from the row. I you are doing update you can use this to obtain a key value for updating the table.

[dre-logics]

First thanks for the quick response jt murphree and the fine code!!
I still thought about following your code.
I have created this code. My question is can this work?

Dim con As New SqlConnection
Dim cmd1,cmd2 As New SqlCommand
MYconn.Open()
cmd1.Connection = Myconn
cmd2.Connection = Myconn
cmd1.CommandText = "SELECT * FROM rental ORDER BY articlenr"
cmd2.CommandText = "SELECT * FROM stock ORDER BY articlenr"
Dim rs1 As SqlDataReader = cmd1.ExecuteReader()
Dim rs2 As SqlDataReader = cmd2.ExecuteReader()


Do While Not rs1.EOF
   artnr=rental.articlenr

   Do While Not rs2.EOF
     if artnr=stock.articlenr then
       found=true
       end loop 
     else
       found=false
       rs2.Movenext
     end if
   loop
if found then
    'update stament
else
   ' insert stament
 endif  

rs1.Movenext
loop

rs1.Close
rs2.close
myconn.Close

[dre-logics]

The way I do this is with a dataTable and dataRow

dim dbTable as dataTable
dim dbRow as dataRow
dim rowCount as int32

Dim dataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM table ORDER BY ID", con)
            dataAdapter.Fill(dbTable)
            rowCount = dbTable.Rows.Count

            For i As Int32 = 0 To rowCount - 1
                dbRow = matTable.Rows(i)   
                cmbMat1.Items.Add(dbRow.Item("ID"))
            Next

In this case I am using the data to fill a combobox but you can modify it for any purpose. us dbRow.Item("<field name>") to get the values from the row. I you are doing update you can use this to obtain a key value for updating the table.

in the line of the code is changed an article in the file?
it is possible to adapt your code for my purpose
I expected a solution two loops:

Do while eof ()  Rental Tabel
    Do while eof ()  Stock table
        if rental.article=stock.article
             found=true
            end loop  [I]an article can only occur once in stock[/I]     
        else
            found=false
        end if
         next record
     Enddo
      If found=true       
      'update record in stock
      else
        ' insert record in stock
      endif
next record
Enddo

[jt_murphree]

I believe the code can work but you will have to set rental and stock up as dataTables and fill them with data from an adapter, at least that is the only way I know of doing it. Another thing, I don't think the end loop will work in the if statement. To exit the loop from there you would either use exit or exit loop, can't remember which off the top of my head.

[dre-logics]

Thanks for the solution. It works perfectly.
Thanks again.

A wish you healthy and happy 2010 !