Database problem

[gahhon]

i've linked the database correctly,
but i can't view the value after i drag the table from data source, and my database already saved data.
why? :(

[poojavb]

Can u show u connection code....

[gahhon]

Hmm, where can view the code?
in fact, i just go to Data and press add data source, and its link already
then i just drag the table from source.

[hericles]

When you run the project in debug mode (or normally if you have set exceptions) what error messages do you get (if any)? If you get an error it will tell you if it is caused by the connection to the database or something else.

[gahhon]

by the way, how to view the debug mode, and when running it don't have any errors message shown.
And i try preview it, it got data, but after i running it don't have any data shown.

[gahhon]

if i copy the solution folder to another computer,
the database cannot run due to invalid path? how come? because i put the database file inside Bin\Debug...

[gahhon]

 Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        Try
            connectionDatabase = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DonutDB.mdb;Persist Security Info=False;")
            Try
                Call connectionDatabase.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot Access Database Due to Some Reasons? Is the File on the Right Location?", "Invalid Path", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

            If connectionDatabase.State = ConnectionState.Open Then
                Dim SQL As New OleDb.OleDbCommand("SELECT * FROM STAFF WHERE Staff ID = '" & txtStaffID.Text & "' AND Staff Password = '" & txtPassword.Text & _
                                                  "'", connectionDatabase)
                Dim staffReader As OleDb.OleDbDataReader = SQL.ExecuteReader

                While staffReader.Read()
                    selectedStaffName = staffReader("Staff Name")
                    MsgBox(selectedStaffName.ToString())
                End While
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

Did my code something wrong? because when i press it occurs an error..
and i just want compare the ID and Password with database, and how can i know whether the id or password correct or wrong?

[poojavb]

Can u check if the prvider string u are entering is correct....coz ur code worked for me but I just changed my provider...

below is my connection string....

 connectionDatabase = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0.;Data Source=pacs_skan.mdb;Persist Security Info=False;")

for debugging u need to add the debug.print statements .i.e.

Debug.Print(selectedStaffName.ToString())

the debug statements can be viewed in ur immediate window
Debug -> Windows -> Immediate

there are also spaces in ur fieldnames... try giving [ ] to ur field name like [Staff ID]

Its not a good practive to give spaces in ur field names...

[gahhon]

before you suggest i already solved this problem by using the reader and assign each of them to an array then only compare... Sorry for the late inform and thanks for your suggestion...
by the way, how to add data to the database by using code? because i don't have use the adapter to update.

[hericles]

Adding to a database is similar to what you have above except you have an INSERT or UPDATE statement as your SQL statement. SO, create a database connection object with a valid connection string, create a command object passing in the parameters of your SQL statement and the connection. Then open the connection and call command.executeNonQuery() to add the data.

[kRod]

For insert query Click Here
For Executing The Command Click Here
You might want to have a go at this >>Click Here
Lots of helpful info on that site

[gahhon]

Okay, i got it..
but i got to assign the value to different table... so how? @@

i breifing explain what's my assignment do, i am doing a Donut ordering system...
so i will assignment the Order ID, Order Date into Order Table, and every Product ID, Product Name, Product Price into Product table, and Quantity, Sub-Total into Order_Product table.

Once the user click the confirm button, we will assignment all this data into database with different tables. so i am confuese how to do it?

[hericles]

Different inserts of new data are handled as different SQL statements. Set the statement to be the first insert and run it, then repeat until all tables that need to be updated have been. Now to do this properly you will want to look into database transactions. You can open a transaction on a database before you start the inserts and then when you are finished (and everything went well) commit the transaction - the changes take place then. If a problem occurs at any point you make the transaction rollback and it is as if none of the database changes ever occurred.
Without using transactions you can get yourself into a trouble if one insert runs and then a problem occurs.

[gahhon]

well, i am not really understand it..
but i show you what i've tried

 Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
        Try
            connectionDatabase = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DonutDB.mdb;Persist Security Info=False;")
            Try
                Call connectionDatabase.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot Access Database Due to Some Reasons? Is the File on the Right Location?", "Invalid Path", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

            If connectionDatabase.State = ConnectionState.Open Then
                Dim sqlInsertOrder As String
                Dim sqlInsertProduct As String
                Dim sqlInsertOrderProduct As String

                sqlInsertOrder = "INSERT INTO ORDER (Order ID, Order Date) " & _
                    "VALUES ('" & strOrderID & "', '" & Format(DateTime.Now, "dd/MM/yyyy") & "')"
                For i As Integer = 0 To lstProduct.Items.Count - 1
                    sqlInsertProduct = "INSERT INTO PRODUCT (Product ID, Product Name, Product Price) " & _
                                        "VALUES ('" & custOrder(i).strProductID & "', '" & custOrder(i).strProduct & "', '" & custOrder(i).dblProductPrice & "')"
                    sqlInsertOrderProduct = "INSERT INTO ORDER_PRODUCT (Order Quantity, SubTotal) " & _
                                            "VALUES ('" & custOrder(i).intQuantity & "', '" & custOrder(i).dblSubtotal & "')"
                Next

                Dim orderComman As New OleDb.OleDbCommand(sqlInsertOrder, connectionDatabase)
                Dim productCommand As New OleDb.OleDbCommand(sqlInsertProduct, connectionDatabase)
                Dim orderProductCommand As New OleDb.OleDbCommand(sqlInsertOrderProduct, connectionDatabase)

            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

[poojavb]

For updating

'Open Connection
Dim upCommand As OleDbCommand
upCommand = New OleDbCommand("Update Value set Colname ='" + Textbox1.Text + "'", Connection)
upCommand.ExecuteNonQuery()
'Close connection

For inserting

'Open Connection
Dim myCommand As OleDbCommand
myCommand = New OleDbCommand("INSERT INTO Tablename Values('" + txtID.Text + "','" + txtName.Text + "')", Connection)
Dim DBReader As OleDbDataReader = myCommand.ExecuteReader
DBReader.Close()
'Close Connection

[gahhon]

but how about insert into different tables?

[hericles]

You can set up your 3 statements as you have done and then do this:

Dim transaction As OleDbTransaction = conn.BeginTransaction()
Try
   Dim orderComman As New OleDb.OleDbCommand(sqlInsertOrder, connectionDatabase)
   Dim productCommand As New OleDb.OleDbCommand(sqlInsertProduct, connectionDatabase)
   Dim orderProductCommand As New OleDb.OleDbCommand(sqlInsertOrderProduct,         connectionDatabase)
   conn.Open()
   orderComman.ExecuteNonQuery()
   productCommand.ExecuteNonQuery()
   orderProductCommand.ExecuteNonQuery()
   transaction.Commit()
  conn.Close()

Catch ex As Exception
   transaction.Rollback()
   conn.Close()

Now all 3 commands will execute but only if all 3 run correctly will your database be altered. If anything goes wrong the transaction is rolled back and the database isn't affected by any of the commands. But if you're unsure look online for more examples.

[gahhon]

okay, i following your code it don't have any errors occur when load,
but when i click this confirm button and go to my summary form, it don't have show any records. please check what's wrong with my code? between this two forms.

 Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
        Try
            connectionDatabase = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DonutDB.mdb;Persist Security Info=False;")
            Try
                Call connectionDatabase.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot Access Database Due to Some Reasons? Is the File on the Right Location?", "Invalid Path", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

            If connectionDatabase.State = ConnectionState.Open Then
                Dim sqlInsertOrder As String
                Dim sqlInsertProduct As String
                Dim sqlInsertOrderProduct As String


                Dim transaction As OleDb.OleDbTransaction = connectionDatabase.BeginTransaction()

                Try
                    For i As Integer = 0 To lstProduct.Items.Count - 1
                        sqlInsertOrder = "INSERT INTO ORDER (Order ID, Order Date) " & _
                                         "VALUES ('" & strOrderID & "', '" & Format(DateTime.Now, "dd/MM/yyyy") & "')"

                        sqlInsertProduct = "INSERT INTO PRODUCT (Product ID, Product Name, Product Price) " & _
                                            "VALUES ('" & custOrder(i).strProductID & "', '" & custOrder(i).strProduct & "', '" & custOrder(i).dblProductPrice & "')"

                        sqlInsertOrderProduct = "INSERT INTO ORDER_PRODUCT (Order Quantity, SubTotal) " & _
                                                "VALUES ('" & custOrder(i).intQuantity & "', '" & custOrder(i).dblSubtotal & "')"

                        Dim orderComman As New OleDb.OleDbCommand(sqlInsertOrder, connectionDatabase)
                        Dim productCommand As New OleDb.OleDbCommand(sqlInsertProduct, connectionDatabase)
                        Dim orderProductCommand As New OleDb.OleDbCommand(sqlInsertOrderProduct, connectionDatabase)

                        connectionDatabase.Open()
                        orderComman.ExecuteNonQuery()
                        orderProductCommand.ExecuteNonQuery()
                        productCommand.ExecuteNonQuery()
                        transaction.Commit()
                        connectionDatabase.Close()
                    Next
                Catch ex As Exception
                    transaction.Rollback()
                    connectionDatabase.Close()
                End Try
                connectionDatabase.Close()
            End If
        Catch ex As Exception


    Private Sub frmSummaryoad(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            connectionDatabase = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DonutDB.mdb;Persist Security Info=False;")
            Try
                Call connectionDatabase.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot Access Database Due to Some Reasons? Is the File on the Right Location?", "Invalid Path", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

            If connectionDatabase.State = ConnectionState.Open Then
                Dim SQL As New OleDb.OleDbCommand("SELECT * FROM ORDER_PRODUCT", connectionDatabase)
                Dim dataAdapter As New OleDb.OleDbDataAdapter(SQL)
                Dim dataTable As New DataTable("ORDER_PRODUCT")

                dataAdapter.Fill(dataTable)
                DataGridView1.DataSource = dataTable
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    MessageBox.Show(ex.ToString())
    End Try
End Sub

This is after i edit the btnConfirm, now let's see frmSummary load

    Private Sub frmSummaryoad(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            connectionDatabase = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DonutDB.mdb;Persist Security Info=False;")
            Try
                Call connectionDatabase.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot Access Database Due to Some Reasons? Is the File on the Right Location?", "Invalid Path", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

            If connectionDatabase.State = ConnectionState.Open Then
                Dim SQL As New OleDb.OleDbCommand("SELECT * FROM ORDER_PRODUCT", connectionDatabase)
                Dim dataAdapter As New OleDb.OleDbDataAdapter(SQL)
                Dim dataTable As New DataTable("ORDER_PRODUCT")

                dataAdapter.Fill(dataTable)
                DataGridView1.DataSource = dataTable
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

So, what's wrong with my code?

[gahhon]

It cannot insert into database when click confirm because just now i use messagebox at exception there.
and the exception error message appear.

[gahhon]

I've found the problem is comes from .ExecuteNonQuery statement...
i also had tried don't use this statement, and the result is added successful but when viewing the table from form Summary (is successful to view the data) is don't have result out. i am doubting what's wrong with the code since i following the instruction to do it.

[hericles]

Sorry, you've confused me. Are you saying that if you don't use ExecuteNonQuery the data is inserted but when you do use it you have an error? When you say that the result is added successfully the code simply ran without errors or that you can actually see the data inserted in the database?
In your previous post you mentioned getting an error message, what error was it?

[gahhon]

Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
        Try
            connectionDatabase = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DonutDB.mdb;Persist Security Info=False;")
            Try
                Call connectionDatabase.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot Access Database Due to Some Reasons? Is the File on the Right Location?", "Invalid Path", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
            connectionDatabase.Close()
            connectionDatabase.Open()

            If connectionDatabase.State = ConnectionState.Open Then
                Dim sqlInsertOrder As String
                Dim sqlInsertProduct As String
                Dim sqlInsertOrderProduct As String


                Dim transaction As OleDb.OleDbTransaction = connectionDatabase.BeginTransaction()

                Try
                    For i As Integer = 0 To lstProduct.Items.Count - 1
                        sqlInsertOrder = "INSERT INTO ORDER * " & _
                                         "VALUES ('" & strOrderID & "', '" & Format(DateTime.Now, "dd/MM/yyyy") & strStaffID & "')"

                        sqlInsertProduct = "INSERT INTO PRODUCT * " & _
                                            "VALUES ('" & custOrder(i).strProductID & "', '" & custOrder(i).strProduct & "', '" & custOrder(i).dblProductPrice & "')"

                        sqlInsertOrderProduct = "INSERT INTO ORDER_PRODUCT * " & _
                                                "VALUES ('" & strOrderID & custOrder(i).strProductID & custOrder(i).intQuantity & "', '" & custOrder(i).dblSubtotal & "')"

                        Dim orderComman As New OleDb.OleDbCommand(sqlInsertOrder, connectionDatabase)
                        Dim productCommand As New OleDb.OleDbCommand(sqlInsertProduct, connectionDatabase)
                        Dim orderProductCommand As New OleDb.OleDbCommand(sqlInsertOrderProduct, connectionDatabase)

                        connectionDatabase.Open()
                        orderComman.ExecuteNonQuery()
                        orderProductCommand.ExecuteReader()
                        productCommand.ExecuteNonQuery()
                        transaction.Commit()
                        connectionDatabase.Close()
                        MessageBox.Show("Order Has Been Recorded", "Order Recorded", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Me.Hide()
                        frmMenu.Show()
                    Next
                Catch ex As Exception
                    MessageBox.Show("Order Can't Record Due to Some Reasons", "Order Not Record", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    transaction.Rollback()
                    connectionDatabase.Close()
                End Try
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

When click this button, it can't to be add into the database and show the messagebox "Order Can't Record .... "

[hericles]

I.m guessing it is your INSERT code. INSERT INTO table * isn't valid. If you are inserting into all columns just use INSERT INTO table_name VALUES(...).
Also, remove your custom message and replace it with ex.Message to view the actual error text.

[gahhon]

Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
        Try
            connectionDatabase = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DonutDB.mdb;Persist Security Info=False;")
            Try
                Call connectionDatabase.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot Access Database Due to Some Reasons? Is the File on the Right Location?", "Invalid Path", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

            If connectionDatabase.State = ConnectionState.Open Then
                Dim sqlInsertOrder As String
                Dim sqlInsertProduct As String
                Dim sqlInsertOrderProduct As String


                Dim transaction As OleDb.OleDbTransaction = connectionDatabase.BeginTransaction()

                Try
                    For i As Integer = 0 To lstProduct.Items.Count - 1
                        sqlInsertOrder = "INSERT INTO ORDER " & _
                                         "VALUES ('" & strOrderID & "', '" & Format(DateTime.Now, "dd/MM/yyyy") & "', '" & strStaffID & "')"

                        sqlInsertProduct = "INSERT INTO PRODUCT " & _
                                            "VALUES ('" & custOrder(i).strProductID & "', '" & custOrder(i).strProduct & "', '" & custOrder(i).dblProductPrice & "')"

                        sqlInsertOrderProduct = "INSERT INTO ORDER_PRODUCT " & _
                                                "VALUES ('" & strOrderID & "', '" & custOrder(i).strProductID & "', '" & custOrder(i).intQuantity & "', '" & custOrder(i).dblSubtotal & "')"

                        Dim orderComman As New OleDb.OleDbCommand(sqlInsertOrder, connectionDatabase, transaction)
                        Dim productCommand As New OleDb.OleDbCommand(sqlInsertProduct, connectionDatabase, transaction)
                        Dim orderProductCommand As New OleDb.OleDbCommand(sqlInsertOrderProduct, connectionDatabase, transaction)

                        'connectionDatabase.Open()
                        orderComman.ExecuteNonQuery()
                        orderProductCommand.ExecuteReader()
                        productCommand.ExecuteNonQuery()
                        transaction.Commit()
                        'connectionDatabase.Close()
                        MessageBox.Show("Order Has Been Recorded", "Order Recorded", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Me.Hide()
                        frmMenu.Show()
                    Next
                Catch ex As Exception
                    MessageBox.Show(ex.ToString(), "Order Not Record", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    transaction.Rollback()
                    'connectionDatabase.Close()
                End Try
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

This code is after edit when i look through the error message provided by system,
now the error is like this.

[kRod]

What are the Primary Keys for your MS ACCESS Tables? Are they AutoNumbers?
Why are variables with names that sound like Numbers and Dates wrapped in single quotes?
If you are not entering all the values for all fields you need to specify the fields that the values correspond to, in the same order that appear in the MS ACCESS table.

You should set a Break Point at the start of your Sub btnConfirm_Click and step through it catch the values of sqlInsertOrder, sqlInsertProduct, and sqlInsertOrderProduct variables. When you copy them paste them into the Query Design Window of MS ACCESS and see if they execute.

[jezguitarist30]

I will assume that the code you use for opening the database will look like this.

Public Sub Opendatabase

 if connectionDatabase.state = closed then

 connectionDatabase.connectionstring = "Put your data source here"
 connectionDatabase.open

 end if

End Sub

Now this will be the code for adding record..

Try

    call OpenDatabase()

    For i As Integer = 0 To lstProduct.Items.Count - 1


        sqlInsertOrder = "INSERT INTO ORDER (Order ID, Order Date) VALUES ('" & strOrderID & "', '" & Format(DateTime.Now, "dd/MM/yyyy") & "')"
        sqlInsertProduct = "INSERT INTO PRODUCT (Product ID, Product Name, Product Price) VALUES ('" & custOrder(i).strProductID & "', '" & custOrder(i).strProduct & "', '" & custOrder(i).dblProductPrice & "')"
        sqlInsertOrderProduct = "INSERT INTO ORDER_PRODUCT (Order Quantity, SubTotal) VALUES ('" & custOrder(i).intQuantity & "', '" & custOrder(i).dblSubtotal & "')"

        Dim orderComman As New OleDb.OleDbCommand(sqlInsertOrder, connectionDatabase)
        Dim productCommand As New OleDb.OleDbCommand(sqlInsertProduct, connectionDatabase)
        Dim orderProductCommand As New OleDb.OleDbCommand(sqlInsertOrderProduct, connectionDatabase)

        orderComman.ExecuteNonQuery()
        orderProductCommand.ExecuteNonQuery()
        productCommand.ExecuteNonQuery()

        transaction.Commit() 'I dont know what code you applied in this transaction.commit but for saving a record the above
                             ' code will work just fine. 

    Next

                    Msgbox("All record has been saved.")

    Catch ex As Exception

    msgbox(ex.message)         ----> 'Adding a error message also helps in debugging.
    transaction.Rollback()     ----> 'I suggest that you also removed this code
    connectionDatabase.Close() ----> 'This code is just fine

    End Try

Hope This help...

[gahhon]

i solved the problem already, this is because of the sequence of command.ExecuteNonQuery() goes wrong...
my insertion has no wrong. :)
so the solution is first orderCommand excute first then go to product and the last is go to order product, this is because the database relationship. :)