VB.net/ Please help me for this. Cannot find out anywhere
Hello guys,
My problem is that I want something so easy but I don't know exactly what to do.
- I have an application and I have a Datagrid.
- This datagrid has 3 attributes.
- 2 attributes are from Orders Table in Access .mdb
- 1 attribute is from Users Table in the same database
- These two tables have a relationship which is user_id
What I do is to display these attribute through an Adapter and using Dataset.
When I create the adapter it asks for select SQL statement. I write it, but it can be only for Orders table if I want adapter to create automatically the UpdateCommand SQL statement. If I will try to combine these two tables, no UpdateCommand SQL statement is created.
What I want is to tell me how can I show these attributes in Datagrid and how can I update any change in datagrid in each table in .mdb.
The problem is that it updates me only the master table which is Orders(the table for which I wrote the Select SQL statement) and no updates in Users.
More explained:
I have 3 attributes, order_id, order_name and user_name.
user_name is the attribute from the second table(Users)
I can only update with the current updatecommand(automatically created by adapter) the Orders table but nothing happened in Users table if I change the name of a user in datagrid.
I want datagrid to send in each table the appropriate changes.
Thanks from now for each help.
aposavvas@gmail.com
[1676 byte] By [
ApSav] at [2008-1-8]
Hi Apsav,
Based on your post, you want to fetch data from related tables ,show these data in the Datagridview control and save data changes in the datagridview control to each table in the database. I recommend you take the following actions:
1. When you need to fetch data from related tables, a popular solution is to you use a plain old INNER JOIN SQL command. It merges the column of the two input tables that you need to work with into a single dataset . I recommend you fetch the primarykey column in the different table respectively in order to save the data change to the database.The following code creates a dataset where you find two columns from Customers and three columns from Orders.
Code Snippet
SELECT c.CustID, c.City, o.Date, o.TotalPrice, oShipAddress FROM Customers AS c INNER JOIN Orders AS o ON c.CustID = o.CustID
2. Write the corresponding commands to save the different datatable respectively. You can create the new two datatables which is inserted the corresponding column and call dataadapter.Update method respectively.
An alternative method is that you ought to use the datarelation class. I think it is very easy to display the relation between the different tables and save the data change to the different datatable respectively. Hope this helps.
Thanks for your questions.
Thank you for your answer I found it too helpful. Do you think this can be used for Datagrids? Because the automated updatecommand that is created by VB adapter has some "?" inside and I do not know how to put them in.
If you want, I can give you my two tables and show me the updatecommand for these tables.
Thank you.
This is another way I solved my problem.
Try
If MessageBox.Show("Are you sure you want to update database?", "Update confirmation..", MsgBoxStyle.YesNo) = DialogResult.Yes Then
Dim trans As OleDb.OleDbTransaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
'update username in Users table
If dataGrid1.CurrentCell.ColumnNumber = 1 Then
Dim cmd As OleDb.OleDbCommand = conn.CreateCommand()
Dim a As String = "UPDATE Users SET Username='" & dataGrid1.Item(dataGrid1.CurrentCell.RowNumber, dataGrid1.CurrentCell.ColumnNumber) & "' WHERE Uid=" & dataGrid1.Item(dataGrid1.CurrentRowIndex, 0)
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = a
cmd.ExecuteNonQuery()
End If
'update stringtexten in Stringtranslation table
If dataGrid1.CurrentCell.ColumnNumber = 2 Then
Dim cmd1 As OleDb.OleDbCommand = conn.CreateCommand()
Dim a1 As String = "UPDATE StringTranslation SET StringTextEn='" & dataGrid1.Item(dataGrid1.CurrentCell.RowNumber, dataGrid1.CurrentCell.ColumnNumber) & "' WHERE StringTextEn='" & val3 & "'"
cmd1.Transaction = trans
cmd1.CommandText = a1
cmd1.ExecuteNonQuery()
End If
'update priority in Users table
If dataGrid1.CurrentCell.ColumnNumber = 4 Then
Dim cmd2 As OleDb.OleDbCommand = conn.CreateCommand()
Dim a2 As String = "UPDATE Users SET Priority='" & dataGrid1.Item(dataGrid1.CurrentCell.RowNumber, dataGrid1.CurrentCell.ColumnNumber) & "' WHERE Uid=" & dataGrid1.Item(dataGrid1.CurrentRowIndex, 0)
cmd2.Transaction = trans
cmd2.CommandText = a2
cmd2.ExecuteNonQuery()
End If
trans.Commit()
'UsersTable.Clear() ' Remove the old data.
'user_diplay_information_en.Fill(UsersTable) ' Reload the data.
button3_Click(Nothing, New EventArgs)
dataGrid1.ResetBindings() ' Redisplay the data.
MsgBox("Changes have be done!")
flag = True
End If
Catch ex As Exception
End Try
 Riquel Dong – MSFT wrote: |
| Hi Apsav, Based on your post, you want to fetch data from related tables ,show these data in the Datagridview control and save data changes in the datagridview control to each table in the database. I recommend you take the following actions: 1. When you need to fetch data from related tables, a popular solution is to you use a plain old INNER JOIN SQL command. It merges the column of the two input tables that you need to work with into a single dataset . I recommend you fetch the primarykey column in the different table respectively in order to save the data change to the database.The following code creates a dataset where you find two columns from Customers and three columns from Orders. Code Snippet SELECT c.CustID, c.City, o.Date, o.TotalPrice, oShipAddress FROM Customers AS c INNER JOIN Orders AS o ON c.CustID = o.CustID 2. Write the corresponding commands to save the different datatable respectively. You can create the new two datatables which is inserted the corresponding column and call dataadapter.Update method respectively. An alternative method is that you ought to use the datarelation class. I think it is very easy to display the relation between the different tables and save the data change to the different datatable respectively. Hope this helps. Thanks for your questions.
| |
