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? :(
gahhon 13 Junior Poster
poojavb 29 Junior Poster
Can u show u connection code....
gahhon 13 Junior Poster
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 289 Master Poster Featured Poster
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 13 Junior Poster
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 13 Junior Poster
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 13 Junior Poster
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 29 Junior Poster
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 13 Junior Poster
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 289 Master Poster Featured Poster
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 49 Junior Poster
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 13 Junior Poster
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 289 Master Poster Featured Poster
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 13 Junior Poster
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 29 Junior Poster
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 13 Junior Poster
but how about insert into different tables?
hericles 289 Master Poster Featured Poster
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 13 Junior Poster
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 13 Junior Poster
It cannot insert into database when click confirm because just now i use messagebox at exception there.
and the exception error message appear.
gahhon 13 Junior Poster
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 289 Master Poster Featured Poster
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 13 Junior Poster
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 289 Master Poster Featured Poster
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 13 Junior Poster
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 49 Junior Poster
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 0 Light Poster
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 13 Junior Poster
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. :)
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.