-
Nov 15th, 2007, 03:47 AM
#1
Thread Starter
Frenzied Member
[RESOLVED] [2005] Updating datagrid to database
Hi All,
I would like to have a feature on my program but i am unsure on how to do so.
I would like to query a database and pull the Data from the database into a datatable, no worries there.
Code:
Sub LoadDataTable()
'Using a Data adapter to load data into the DataTable..
DA = New OleDb.OleDbDataAdapter("Select * from Employees WHERE Datey>= 26/07/07", CN)
DT = New DataTable
DA.Fill(DT)
'initializing command builder, this will be used later..
CMB = New OleDb.OleDbCommandBuilder(DA)
End Sub
But i would like the user to be able to edit the data in the datagrid and click a button to save and it will update the database.
But if the row is a new row then it will insert the data into the database.
Any ideas on how to do that?
Many thanks
ken
However, if there is no
Last edited by dinosaur_uk; Nov 15th, 2007 at 03:52 AM.
If you find my thread helpful, please remember to rate me
-
Nov 15th, 2007, 04:02 AM
#2
Thread Starter
Frenzied Member
Re: [2005] Updating datagrid to database
I am not using any relational database, so can i use a datatable instead of a dataset?
If you find my thread helpful, please remember to rate me
-
Nov 15th, 2007, 04:46 AM
#3
Thread Starter
Frenzied Member
Re: [2005] Updating datagrid to database
Ok got the additions and updating working...
how do i delete the rows?
Code:
Dim dtt As New DataTable
dtt = DT.GetChanges()
'we are going to use the CommandBuilder to get the insert command, which the DataAdapter will use to update the Data Base
DA.InsertCommand = CMB.GetInsertCommand 'the command builder is already created and initialized in Form1_Load()..
'inserts the new row to the database
DA.Update(dtt)
If you find my thread helpful, please remember to rate me
-
Nov 15th, 2007, 04:52 AM
#4
Thread Starter
Frenzied Member
Re: [2005] Updating datagrid to database
Argh, this is confusing,
Ok this is my database in access.
Tablename: "Employees"
EmpID Name Salary DeptID
1 Raja Edited 30000 5
2 Rani 20000 20
5 Vijay 24000 20
6 Siva 10000 10
I want to just "SELECT [Name], [Salary] FROM [Employees]" and put it into a datagrid (via a datatable)
Then The user can edit, insert or delete the entries and then click a button to write the changes to the database.
Is this possible?
the code above works when i "SELECT * From [Employees]", but when i run the same code the following error is thrown up
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
Please helppppp
If you find my thread helpful, please remember to rate me
-
Nov 15th, 2007, 09:12 AM
#5
Thread Starter
Frenzied Member
Re: [2005] Updating datagrid to database
If you find my thread helpful, please remember to rate me
-
Nov 16th, 2007, 01:03 AM
#6
Re: [2005] Updating datagrid to database
You can't use a CommandBuilder to create your UpdateCommand and DeleteCommand if your query doesn't return PK information. How is each row to be updated or deleted to be identified if there's no PK? Follow the Data Access Examples link in my signature.
-
Nov 19th, 2007, 04:26 PM
#7
Thread Starter
Frenzied Member
Re: [2005] Updating datagrid to database
Hi jmcilhinney,
Have read your post about the Data Access Examples, and it is brilliant
I was wondering for your example:
Code:
Dim connection As New SqlConnection("connection string here")Dim adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", connection)Dim builder As New SqlCommandBuilder(adapter) adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey Dim table As New DataTable 'Retrieve the data.adapter.Fill(table) 'The table can be used here to display and edit the data.'That will most likely involve data-binding but that is not a data access issue. 'Save the changes.adapter.Update(table)
How can i make it that it displays a customised header in the datagridview? For example, instead of the field name in the database, i want to show a more descriptive column name.
is it using table.column(0).columnname = "Detailed title" ? coz it does not seem to work
i am using .net 2.0
If you find my thread helpful, please remember to rate me
-
Nov 19th, 2007, 04:27 PM
#8
Thread Starter
Frenzied Member
Re: [2005] Updating datagrid to database
Code:
Dim adapter As OleDbDataAdapter
Dim dt As DataTable
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=;Data Source=" & Application.StartupPath & "\SNS.mdb")
adapter = New OleDbDataAdapter("SELECT * FROM Wells", connection)
Dim builder As New OleDbCommandBuilder(adapter)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
dt = New DataTable
dt.Clear()
'Retrieve the data.
adapter.Fill(dt)
Me.DataGridView1.DataSource = dt
'The table can be used here to display and edit the data.
'That will most likely involve data-binding but that is not a data access issue.
'Save the changes.
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
adapter.Update(dt)
End Sub
If you find my thread helpful, please remember to rate me
-
Nov 19th, 2007, 05:24 PM
#9
Re: [2005] Updating datagrid to database
The DataGridViewColumn class has a HeaderText property that you can set if you don't want the name of the bound DataColumn used. You can either access the columns after setting the DataSource property, which is where columns are automatically generated, or you can create the columns at design time and specify the HeaderText then.
-
Nov 19th, 2007, 06:46 PM
#10
Thread Starter
Frenzied Member
Re: [2005] Updating datagrid to database
legend!, have sorted it out...and it updates back to the database too
amazing!
Code:
Dim dc As DataGridViewColumn
dc = Me.DataGridView1.Columns(1)
dc.HeaderText = "Woo"
If you find my thread helpful, please remember to rate me
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|