Question updating the database and other questions.

[adigilani]

Hi

Need some help here. I am learning to code in VB 2008 and my first assignment at school is to build a vending machine. I have a database and i am able to read the values of it in a variable using


Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=vendingMachine.mdb" ' Holding the connection string
Dim conn As New OleDbConnection ' Initilize a OLE connection object variable
Dim oleDBAdapter As New OleDbDataAdapter ' Initilize a OLE DB Adapter object variable
Dim ds As New DataSet ' Initilize a dataset object variable

conn = New OleDbConnection(sConnString) ' make instance of the connection

Try ' trying to open the db connection
conn.Open() ' open the connection
Catch ex As Exception ' catching error
MsgBox("Database connection Error...!")
mainFrm.Close()
End Try

end sub


sqlString = "SELECT * FROM myProducts"
oleDBAdapter = New OleDbDataAdapter(sqlString, conn)

oleDBAdapter.Fill(ds, "myProducts Table") ' retriving the products

sqlString = "SELECT * FROM MachineAdmin"
oleDBAdapter.SelectCommand.CommandText = sqlString
oleDBAdapter.Fill(ds, "MachineAdmin Table") ' retriving the coins

then i am storing the coins in an array

tempVar = 0

If ds.Tables(1).Rows.Count > 0 Then
Do While ds.Tables(1).Rows.Count > tempVar
For i = 0 To ds.Tables(1).Columns.Count - 2
machineCoinBank(i) = ds.Tables(1).Rows(tempVar).Item(i + 1)
MsgBox(machineCoinBank(i))
Next
tempVar = tempVar + 1
Loop
Else
MsgBox("Error loading values stored in coins in the variable...!")
Application.Exit()
End If


and then after receiving a coin from the user i want to add the coin to the appropriate type of coin list and update the database

i've tried using the follow

For tempVal = 0 To 7
ds.Tables(1).Rows(0).BeginEdit()
ds.Tables(1).Rows(0).Item(tempVal + 1) = CInt(machineCoinBank(tempVal)) + CInt(recievedCoins(tempVal)) ' Reset the recieved coins counter array
oleDBAdapter.Update(ds.Tables(1))
Next

The above all doesn't give me any exception or any error but the database is not getting updated.

The database is in the same folder as the project. I have checked it and nothing has happened to the values and i have checked the bin as well and nothing has happened.

Can anyone please help. I have tried looking at the internet but cannot find any thing which can point me to a right direction.

also i want to know

1) if i have the database in a different folder what will happen to it when i use the "add new data source". Does it get copied into the project folder. Well, i saw it did but what i actually mean to ask is that which database is in use then. the one in the project folder or the one in the original folder

2) if the same dataset defined on this form is accessible on the next form or i have to create dataset again. If yes then after manipulating with the data there how i can refresh the dataset when i come back to the main form.

Please.

 

[adigilani]

help please

can anyone help...

 

[rcombs4]

What are you trying to do?

What event/sub/function is all that code in? Is it all in the Form Load event? What is machineCoinBank? And I had the same problem that it seems your having...I put in comments where the code was fairly self-explanatory but did not put in comments in the section of code that wasn't obvious what you are doing.

Why not use Nested For loops instead of a For loop nested in a Do loop. your Do loop is basically a For loop.

 

[adigilani]

Hi

Thanks for your answer. The code goes as follow

Public Class frmMachine
    Dim recievedCoins(8) As Integer ' received coin register
    Dim recievedMoney As Decimal = 0.0 ' received money register
    Dim machineCoinBank(8) As Integer ' machine coin register
    Dim tempVar As Integer = 0 ' a temp variable to assist in loops and vice versa
    ' Holding the connection string
    Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=vendingMachine.mdb”
    Dim conn As New OleDbConnection ' Initilize a OLE connection object variable
    Dim oleDBAdapter As New OleDbDataAdapter ' Initilize a OLE DB Adapter object variable
    Dim ds As New DataSet ' Initilize a dataset object variable


    Private Sub frmMachine_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        myInitializations() ' calling myInitializations()
    End Sub


    Private Sub myInitializations()
        openDBConn() ' opening database connection
        readDB() ' reading the database and initializing the variables: products() &  machineCoinBank()
    End Sub

    Private Sub openDBConn()
        conn = New OleDbConnection(sConnString) ' make instance of the connection
        Try ' trying to open the db connection
            conn.Open() ' open the connection
        Catch ex As Exception ' catching error
            MsgBox("Error...! Database don’t open")
            mainFrm.Close()
        End Try
    End Sub

    Private Sub readDB()
        Dim sqlString As String
        sqlString = "SELECT * FROM myProducts"
        oleDBAdapter = New OleDbDataAdapter(sqlString, conn)
        oleDBAdapter.Fill(ds, "myProducts Table") ' retriving the products
        sqlString = "SELECT * FROM MachineAdmin"
        oleDBAdapter.SelectCommand.CommandText = sqlString
        oleDBAdapter.Fill(ds, "MachineAdmin Table") ' retriving the coins

        Dim i As Integer
        i = 0

        tempVar = 0

        ' retrieving the products
        If ds.Tables(0).Rows.Count > 0 Then
            Do While ds.Tables(0).Rows.Count > tempVar
                For Me.tempVar = 0 To ds.Tables(0).Rows.Count - 1
                    products(tempVar, 0) = ds.Tables(0).Rows(tempVar).Item(1) ‘ product name
                    products(tempVar, 1) = ds.Tables(0).Rows(tempVar).Item(2) ‘ product price
                    products(tempVar, 2) = ds.Tables(0).Rows(tempVar).Item(3) ‘ product quantity
                Next
            Loop
        Else
            MsgBox("Error...! database opened but no values retrieved")
            mainFrm.Close()
        End If


        tempVar = 0

        ' retrieving the coins
        If ds.Tables(1).Rows.Count > 0 Then
            Do While ds.Tables(1).Rows.Count > tempVar
                For i = 0 To ds.Tables(1).Columns.Count - 2
                    machineCoinBank(i) = ds.Tables(1).Rows(tempVar).Item(i + 1)
                    MsgBox(machineCoinBank(i))
                Next
                tempVar = tempVar + 1
            Loop
        Else
            MsgBox("Error...! Database opened but values are not loaded")
            Application.Exit()
        End If
    End Sub
End Class
[code]
problem lies here. What I am doing is receiving the coins from the user and after dispensing the product I am calling this event to deposit the coins back in to the machine bank. I am calling depositCoinsInMachineBank() this from another sub called giveChangeBack() which is fired when the user click on the product dispensing button. But the problem is the ds.table(1).rows(0).item(tempval+1) value is not changing after ds.Tables(1).Rows(0).Item(tempVal + 1) = CInt(machineCoinBank(tempVal)) + CInt(recievedCoins(tempVal)) and database is not updating. It is not giving me any exception or error.

[code]
    Private Sub depositCoinsInMachineBank()
        For tempVal = 0 To 7
            ds.Tables(1).Rows(0).BeginEdit()
            MsgBox(ds.Tables(1).Rows(0).Item(tempVal + 1))
            ds.Tables(1).Rows(0).Item(tempVal + 1) = CInt(machineCoinBank(tempVal)) + CInt(recievedCoins(tempVal))
            recievedCoins(tempVal) = 0 ' Reset the recieved coins counter array
            MsgBox(ds.Tables(1).Rows(0).Item(tempVal + 1))
            ds.AcceptChanges()

            'Me.rs.Fields(tempVal + 1).Value = CInt(machineCoinBank(tempVal)) + CInt(recievedCoins(tempVal)) ' Reset the recieved coins counter array
            'MsgBox(Me.rs.Fields(tempVal + 1).Name & ": machine had " & CInt(machineCoinBank(tempVal)) & ". Received " & CInt(recievedCoins(tempVal)) & " now has " & rs.Fields(tempVal + 1).Value)

            'Me.rs.UpdateBatch()
        Next
        Me.oleDBAdapter.Update(ds.Tables(1))
    End Sub
End Class
[code]

 

[cjard]

please change the final code tag of your post to [/code]
(with a slash)