| Groups | Blog | Home |
dotnet ado.net : Multiple table in dataset query
simple updates, like if you have a list of fields with a primary key. You can *not* do updates using the CommandBuilder if you have a join in your SQL statement. You will have to write your own SQL for the update, insert, and delete commands, so they update only the one table. Example: Update tblStaff Set staff_id = ?, staff_initials = ?, staff_name = ?, staff_title = ?, staff_active = ? WHERE staff_id = ? and then add parameters for those (in that order) from the data row in your dataset. Does that make sense? Robin S. ------------------------------------- [quoted text, click to view] "Microsoft" <lee.clements.nospam@btopenworld.com> wrote in message news:%2380X%23$VRHHA.4448@TK2MSFTNGP04.phx.gbl... > Hi everyone, > > Before I begin I should point out that I am quite new to programming and > VB.net and am using VS2003 with SQLExpress 2005. > > I have two data tables a staff table and an office table, the linking > field is office_id. I have created a form and added a datagrid, when the > form loads I create a new DataAdapter, Connection and DataSet and select > the records from the staff table linking with the office table to display > the office name rather than the ID. > > Private Sub frmStaff_Load(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles MyBase.Load > > Dim dcConn as New OleDB.OleDbConnection("Integrated Security=SSPI;Packet > Size=4096;Data Source='SERVER\INSTANCE';Initial Catalog=Planner;Persist > Security Info=False;Provider="SQLOLEDB.1") > > Dim daStaff = New OleDb.OleDbDataAdapter("SELECT a.staff_id, > a.staff_initials, a.staff_name, a.staff_title, a.staff_active, > b.office_name FROM tblStaff a, tblOffice b WHERE a.staff_office_id = > b.office_id", dcConn) > > Dim dsStaff as New System.Data.DataSet > daStaff.Fill(dsStaff, "tblStaff") > > ' set the bding context > currManager = CType(Me.BindingContext(dsStaff.Tables("Staff")), > CurrencyManager) > > ' Set the databindings > DataGrid1.DataSource = dsStaff.Tables("Staff") > > .... > Here I create all the DataGridTextBoxColumn and set the mapping to the > relevant field, etc. > .... > > End Sub > > ' The procedure for saving the changes is:- > Private Sub SaveChanges() > Dim cmdBuilder As New OleDb.OleDbCommandBuilder(daStaff) > Dim lngRetVal As Long > > lngRetVal = daStaff.Update(dsStaff, "Staff") > If lngRetVal > 0 Then > dsStaff.AcceptChanges() > End If > End Sub > > The form loads ok and the grid is populated, however when I call the > update command on the DataAdapter I get an error saying that I cannot use > the commandbuilder to update multiple tables. So my question is how the > hell do I do this? I prefer to manually code it rather than using the > wizards to really understand it. Plus the wizards don't actually work > SQLExpress 2005. I have read several tutorials but none seem to cover > what I want, my MCSD book I bought doesn't really explain how to do this > in real terms. > > Any help, pointers, guidance or links to useful articles would be very > much appreciated. I'm completely lost and don'tknow where to go from > here. > > Thank you in advance for taking the time to read my query! > Cheers > Lee >
Hi everyone,
Before I begin I should point out that I am quite new to programming and VB.net and am using VS2003 with SQLExpress 2005. I have two data tables a staff table and an office table, the linking field is office_id. I have created a form and added a datagrid, when the form loads I create a new DataAdapter, Connection and DataSet and select the records from the staff table linking with the office table to display the office name rather than the ID. Private Sub frmStaff_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim dcConn as New OleDB.OleDbConnection("Integrated Security=SSPI;Packet Size=4096;Data Source='SERVER\INSTANCE';Initial Catalog=Planner;Persist Security Info=False;Provider="SQLOLEDB.1") Dim daStaff = New OleDb.OleDbDataAdapter("SELECT a.staff_id, a.staff_initials, a.staff_name, a.staff_title, a.staff_active, b.office_name FROM tblStaff a, tblOffice b WHERE a.staff_office_id = b.office_id", dcConn) Dim dsStaff as New System.Data.DataSet daStaff.Fill(dsStaff, "tblStaff") ' set the bding context currManager = CType(Me.BindingContext(dsStaff.Tables("Staff")), CurrencyManager) ' Set the databindings DataGrid1.DataSource = dsStaff.Tables("Staff") .... Here I create all the DataGridTextBoxColumn and set the mapping to the relevant field, etc. .... End Sub ' The procedure for saving the changes is:- Private Sub SaveChanges() Dim cmdBuilder As New OleDb.OleDbCommandBuilder(daStaff) Dim lngRetVal As Long lngRetVal = daStaff.Update(dsStaff, "Staff") If lngRetVal > 0 Then dsStaff.AcceptChanges() End If End Sub The form loads ok and the grid is populated, however when I call the update command on the DataAdapter I get an error saying that I cannot use the commandbuilder to update multiple tables. So my question is how the hell do I do this? I prefer to manually code it rather than using the wizards to really understand it. Plus the wizards don't actually work SQLExpress 2005. I have read several tutorials but none seem to cover what I want, my MCSD book I bought doesn't really explain how to do this in real terms. Any help, pointers, guidance or links to useful articles would be very much appreciated. I'm completely lost and don'tknow where to go from here. Thank you in advance for taking the time to read my query! Cheers Lee
Lee,
Here is some information on placing a combobox into a datagrid in .Net 1.1: http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q480q Also, keep in mind that the code that Robin showed you does not handle concurrency issues. If your program will be used by multiple users all hitting a central database then you must deal with concurrency in your SQL update and delete statements. Kerry Moorman [quoted text, click to view] "Microsoft" wrote:
> Yes I think I undestand where your coming from, effectively I need to create > a new parameterized query for the update and insert command which is then > assigned to the dataadapter. > > I have found some articles on doing this so I will have a play around with > it. The other problem I've recognised in doing this is that ideally I need a > combobox in the dataqgrid for the user to select from the fields in the > other table - which isn't supported with the datagrid in 1.1, not sure about > 2.0. I will get to this later, for now I will concentrate on getting the > updates working. > > Thanks for your help. > Lee > > > "RobinS" <RobinS@NoSpam.yah.none> wrote in message > news:DIednU8aQKZ3h1zYnZ2dnUVZ_r-onZ2d@comcast.com... > > First of all, be aware that the CommandBuilder is only effective for > > really simple updates, like if you have a list of fields with a primary > > key. > > > > You can *not* do updates using the CommandBuilder if you have a join in > > your SQL statement. > > > > You will have to write your own SQL for the update, insert, and delete > > commands, so they update only the one table. > > > > Example: > > > > Update tblStaff Set staff_id = ?, staff_initials = ?, staff_name = ?, > > staff_title = ?, staff_active = ? WHERE staff_id = ? > > > > and then add parameters for those (in that order) from the data row in > > your dataset. > > > > Does that make sense? > > > > Robin S. > > ------------------------------------- > > "Microsoft" <lee.clements.nospam@btopenworld.com> wrote in message > > news:%2380X%23$VRHHA.4448@TK2MSFTNGP04.phx.gbl... > >> Hi everyone, > >> > >> Before I begin I should point out that I am quite new to programming and > >> VB.net and am using VS2003 with SQLExpress 2005. > >> > >> I have two data tables a staff table and an office table, the linking > >> field is office_id. I have created a form and added a datagrid, when the > >> form loads I create a new DataAdapter, Connection and DataSet and select > >> the records from the staff table linking with the office table to display > >> the office name rather than the ID. > >> > >> Private Sub frmStaff_Load(ByVal sender As System.Object, ByVal e As > >> System.EventArgs) Handles MyBase.Load > >> > >> Dim dcConn as New OleDB.OleDbConnection("Integrated Security=SSPI;Packet > >> Size=4096;Data Source='SERVER\INSTANCE';Initial Catalog=Planner;Persist > >> Security Info=False;Provider="SQLOLEDB.1") > >> > >> Dim daStaff = New OleDb.OleDbDataAdapter("SELECT a.staff_id, > >> a.staff_initials, a.staff_name, a.staff_title, a.staff_active, > >> b.office_name FROM tblStaff a, tblOffice b WHERE a.staff_office_id = > >> b.office_id", dcConn) > >> > >> Dim dsStaff as New System.Data.DataSet > >> daStaff.Fill(dsStaff, "tblStaff") > >> > >> ' set the bding context > >> currManager = CType(Me.BindingContext(dsStaff.Tables("Staff")), > >> CurrencyManager) > >> > >> ' Set the databindings > >> DataGrid1.DataSource = dsStaff.Tables("Staff") > >> > >> .... > >> Here I create all the DataGridTextBoxColumn and set the mapping to the > >> relevant field, etc. > >> .... > >> > >> End Sub > >> > >> ' The procedure for saving the changes is:- > >> Private Sub SaveChanges() > >> Dim cmdBuilder As New OleDb.OleDbCommandBuilder(daStaff) > >> Dim lngRetVal As Long > >> > >> lngRetVal = daStaff.Update(dsStaff, "Staff") > >> If lngRetVal > 0 Then > >> dsStaff.AcceptChanges() > >> End If > >> End Sub > >> > >> The form loads ok and the grid is populated, however when I call the > >> update command on the DataAdapter I get an error saying that I cannot use > >> the commandbuilder to update multiple tables. So my question is how the > >> hell do I do this? I prefer to manually code it rather than using the > >> wizards to really understand it. Plus the wizards don't actually work > >> SQLExpress 2005. I have read several tutorials but none seem to cover > >> what I want, my MCSD book I bought doesn't really explain how to do this > >> in real terms. > >> > >> Any help, pointers, guidance or links to useful articles would be very > >> much appreciated. I'm completely lost and don'tknow where to go from > >> here. > >> > >> Thank you in advance for taking the time to read my query! > >> Cheers > >> Lee > >> > > > > > >
Yes I think I undestand where your coming from, effectively I need to create
a new parameterized query for the update and insert command which is then assigned to the dataadapter. I have found some articles on doing this so I will have a play around with it. The other problem I've recognised in doing this is that ideally I need a combobox in the dataqgrid for the user to select from the fields in the other table - which isn't supported with the datagrid in 1.1, not sure about 2.0. I will get to this later, for now I will concentrate on getting the updates working. Thanks for your help. Lee [quoted text, click to view] "RobinS" <RobinS@NoSpam.yah.none> wrote in message news:DIednU8aQKZ3h1zYnZ2dnUVZ_r-onZ2d@comcast.com... > First of all, be aware that the CommandBuilder is only effective for > really simple updates, like if you have a list of fields with a primary > key. > > You can *not* do updates using the CommandBuilder if you have a join in > your SQL statement. > > You will have to write your own SQL for the update, insert, and delete > commands, so they update only the one table. > > Example: > > Update tblStaff Set staff_id = ?, staff_initials = ?, staff_name = ?, > staff_title = ?, staff_active = ? WHERE staff_id = ? > > and then add parameters for those (in that order) from the data row in > your dataset. > > Does that make sense? > > Robin S. > ------------------------------------- > "Microsoft" <lee.clements.nospam@btopenworld.com> wrote in message > news:%2380X%23$VRHHA.4448@TK2MSFTNGP04.phx.gbl... >> Hi everyone, >> >> Before I begin I should point out that I am quite new to programming and >> VB.net and am using VS2003 with SQLExpress 2005. >> >> I have two data tables a staff table and an office table, the linking >> field is office_id. I have created a form and added a datagrid, when the >> form loads I create a new DataAdapter, Connection and DataSet and select >> the records from the staff table linking with the office table to display >> the office name rather than the ID. >> >> Private Sub frmStaff_Load(ByVal sender As System.Object, ByVal e As >> System.EventArgs) Handles MyBase.Load >> >> Dim dcConn as New OleDB.OleDbConnection("Integrated Security=SSPI;Packet >> Size=4096;Data Source='SERVER\INSTANCE';Initial Catalog=Planner;Persist >> Security Info=False;Provider="SQLOLEDB.1") >> >> Dim daStaff = New OleDb.OleDbDataAdapter("SELECT a.staff_id, >> a.staff_initials, a.staff_name, a.staff_title, a.staff_active, >> b.office_name FROM tblStaff a, tblOffice b WHERE a.staff_office_id = >> b.office_id", dcConn) >> >> Dim dsStaff as New System.Data.DataSet >> daStaff.Fill(dsStaff, "tblStaff") >> >> ' set the bding context >> currManager = CType(Me.BindingContext(dsStaff.Tables("Staff")), >> CurrencyManager) >> >> ' Set the databindings >> DataGrid1.DataSource = dsStaff.Tables("Staff") >> >> .... >> Here I create all the DataGridTextBoxColumn and set the mapping to the >> relevant field, etc. >> .... >> >> End Sub >> >> ' The procedure for saving the changes is:- >> Private Sub SaveChanges() >> Dim cmdBuilder As New OleDb.OleDbCommandBuilder(daStaff) >> Dim lngRetVal As Long >> >> lngRetVal = daStaff.Update(dsStaff, "Staff") >> If lngRetVal > 0 Then >> dsStaff.AcceptChanges() >> End If >> End Sub >> >> The form loads ok and the grid is populated, however when I call the >> update command on the DataAdapter I get an error saying that I cannot use >> the commandbuilder to update multiple tables. So my question is how the >> hell do I do this? I prefer to manually code it rather than using the >> wizards to really understand it. Plus the wizards don't actually work >> SQLExpress 2005. I have read several tutorials but none seem to cover >> what I want, my MCSD book I bought doesn't really explain how to do this >> in real terms. >> >> Any help, pointers, guidance or links to useful articles would be very >> much appreciated. I'm completely lost and don'tknow where to go from >> here. >> >> Thank you in advance for taking the time to read my query! >> Cheers >> Lee >> > >
When you call Update on the data adapter, it will iterate through the rows.
If a row is marked as Modified, it will run your query defined in the Update Command object. If it is marked as deleted, it will run the query defined in the Delete Command object. If it is marked as Added, it will run the query defined in the Insert Command object. Hope this helps. Robin S. --------------------------- [quoted text, click to view] "Microsoft" <lee.clements.nospam@btopenworld.com> wrote in message
news:eG0LfsiRHHA.1860@TK2MSFTNGP06.phx.gbl... > Thanks Kerry for the pointers on the combobox, I will look into this in > the future once I have the basic data stuff sorted. I have come a little > bit unstuck actually, I have modified my SaveChanges proc to reflect the > new Update command and its parameters, but I am a bit lost in actually > performing the update. I have multiple rows that have been modified so do > I have to execute the DataAdapter.Update(x,x) multiple times for each > row? > > The code I have so far is :- > > Private Sub SaveChanges() > Dim UpdateStaffCmd As New OleDb.OleDbCommand > Dim lngRetVal As Long > Dim dsChanges As DataSet > Dim dtTable As DataTable > Dim myDataRow As DataRow > > ' create a parameterized query to perform the update > UpdateStaffCmd.CommandType = CommandType.Text > > ' the '?' represent the parameters > UpdateStaffCmd.CommandText = "Update tblStaff Set staff_id = ?, > staff_initials = ?, staff_name = ?, staff_title = ?, staff_active = ?, > staff_office_id = ? WHERE staff_id = ?" > > ' add the parameters in the same order as indicated the commandtext > UpdateStaffCmd.Parameters.Add("staff_id", OleDb.OleDbType.Integer, 4) > UpdateStaffCmd.Parameters.Add("staff_initials", OleDb.OleDbType.VarChar, > 4) > UpdateStaffCmd.Parameters.Add("staff_nane", OleDb.OleDbType.VarChar, 50) > UpdateStaffCmd.Parameters.Add("staff_title", OleDb.OleDbType.VarChar, 50) > UpdateStaffCmd.Parameters.Add("staff_active", OleDb.OleDbType.Binary) > > ' The datagrid would display LONDON but the ID value is actually 1, so do > I need another datatable to hold this information and use a datarelation > object? this is much harder than the old ADO!!! > UpdateStaffCmd.Parameters.Add("staff_office_id", OleDb.OleDbType.Integer) > > ' assign the update command to the data adapter > daStaff.UpdateCommand = UpdateStaffCmd > > ' get the changed datarows and store in new dataset > dsChanges = dsStaff.GetChanges(DataRowState.Modified) > > ' get the datatable so that I can access the datarow > dtTable = dsChanges.Tables("Staff") > > For Each myDataRow In dtTable.Rows > Console.WriteLine(myDataRow.Item) > Next > > lngRetVal = daStaff.Update(dsChanges, "Staff") > > If lngRetVal > 0 Then > dsStaff.AcceptChanges() > End If > > End Sub > > > Sorry - this probably seems very trivial, but I cannot seem to find a > comprehensive example in any documentation, just bits and pieces which I > am trying to tie together. At least I am learning how to do it properly. > I am reading an article on Data concurrency at the minute, very > interesting - not something I had even considered yet. Oh boy - its going > to be a long night. > > Thank you all for any assistance. > Lee > > > > "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message > news:9DC8D049-F072-44C4-8271-A92CFADBA983@microsoft.com... >> Lee, >> >> Here is some information on placing a combobox into a datagrid in .Net >> 1.1: >> >> http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q480q >> >> Also, keep in mind that the code that Robin showed you does not handle >> concurrency issues. If your program will be used by multiple users all >> hitting a central database then you must deal with concurrency in your >> SQL >> update and delete statements. >> >> Kerry Moorman >> >> >> "Microsoft" wrote: >> >>> Yes I think I undestand where your coming from, effectively I need to >>> create >>> a new parameterized query for the update and insert command which is >>> then >>> assigned to the dataadapter. >>> >>> I have found some articles on doing this so I will have a play around >>> with >>> it. The other problem I've recognised in doing this is that ideally I >>> need a >>> combobox in the dataqgrid for the user to select from the fields in the >>> other table - which isn't supported with the datagrid in 1.1, not sure >>> about >>> 2.0. I will get to this later, for now I will concentrate on getting >>> the >>> updates working. >>> >>> Thanks for your help. >>> Lee >>> >>> >>> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >>> news:DIednU8aQKZ3h1zYnZ2dnUVZ_r-onZ2d@comcast.com... >>> > First of all, be aware that the CommandBuilder is only effective for >>> > really simple updates, like if you have a list of fields with a >>> > primary >>> > key. >>> > >>> > You can *not* do updates using the CommandBuilder if you have a join >>> > in >>> > your SQL statement. >>> > >>> > You will have to write your own SQL for the update, insert, and >>> > delete >>> > commands, so they update only the one table. >>> > >>> > Example: >>> > >>> > Update tblStaff Set staff_id = ?, staff_initials = ?, staff_name = ?, >>> > staff_title = ?, staff_active = ? WHERE staff_id = ? >>> > >>> > and then add parameters for those (in that order) from the data row >>> > in >>> > your dataset. >>> > >>> > Does that make sense? >>> > >>> > Robin S. >>> > ------------------------------------- >>> > "Microsoft" <lee.clements.nospam@btopenworld.com> wrote in message >>> > news:%2380X%23$VRHHA.4448@TK2MSFTNGP04.phx.gbl... >>> >> Hi everyone, >>> >> >>> >> Before I begin I should point out that I am quite new to programming >>> >> and >>> >> VB.net and am using VS2003 with SQLExpress 2005. >>> >> >>> >> I have two data tables a staff table and an office table, the >>> >> linking >>> >> field is office_id. I have created a form and added a datagrid, when >>> >> the >>> >> form loads I create a new DataAdapter, Connection and DataSet and >>> >> select >>> >> the records from the staff table linking with the office table to >>> >> display >>> >> the office name rather than the ID. >>> >> >>> >> Private Sub frmStaff_Load(ByVal sender As System.Object, ByVal e As >>> >> System.EventArgs) Handles MyBase.Load >>> >> >>> >> Dim dcConn as New OleDB.OleDbConnection("Integrated >>> >> Security=SSPI;Packet >>> >> Size=4096;Data Source='SERVER\INSTANCE';Initial >>> >> Catalog=Planner;Persist >>> >> Security Info=False;Provider="SQLOLEDB.1") >>> >> >>> >> Dim daStaff = New OleDb.OleDbDataAdapter("SELECT a.staff_id, >>> >> a.staff_initials, a.staff_name, a.staff_title, a.staff_active, >>> >> b.office_name FROM tblStaff a, tblOffice b WHERE a.staff_office_id = >>> >> b.office_id", dcConn) >>> >> >>> >> Dim dsStaff as New System.Data.DataSet
Thanks Kerry for the pointers on the combobox, I will look into this in the
future once I have the basic data stuff sorted. I have come a little bit unstuck actually, I have modified my SaveChanges proc to reflect the new Update command and its parameters, but I am a bit lost in actually performing the update. I have multiple rows that have been modified so do I have to execute the DataAdapter.Update(x,x) multiple times for each row? The code I have so far is :- Private Sub SaveChanges() Dim UpdateStaffCmd As New OleDb.OleDbCommand Dim lngRetVal As Long Dim dsChanges As DataSet Dim dtTable As DataTable Dim myDataRow As DataRow ' create a parameterized query to perform the update UpdateStaffCmd.CommandType = CommandType.Text ' the '?' represent the parameters UpdateStaffCmd.CommandText = "Update tblStaff Set staff_id = ?, staff_initials = ?, staff_name = ?, staff_title = ?, staff_active = ?, staff_office_id = ? WHERE staff_id = ?" ' add the parameters in the same order as indicated the commandtext UpdateStaffCmd.Parameters.Add("staff_id", OleDb.OleDbType.Integer, 4) UpdateStaffCmd.Parameters.Add("staff_initials", OleDb.OleDbType.VarChar, 4) UpdateStaffCmd.Parameters.Add("staff_nane", OleDb.OleDbType.VarChar, 50) UpdateStaffCmd.Parameters.Add("staff_title", OleDb.OleDbType.VarChar, 50) UpdateStaffCmd.Parameters.Add("staff_active", OleDb.OleDbType.Binary) ' The datagrid would display LONDON but the ID value is actually 1, so do I need another datatable to hold this information and use a datarelation object? this is much harder than the old ADO!!! UpdateStaffCmd.Parameters.Add("staff_office_id", OleDb.OleDbType.Integer) ' assign the update command to the data adapter daStaff.UpdateCommand = UpdateStaffCmd ' get the changed datarows and store in new dataset dsChanges = dsStaff.GetChanges(DataRowState.Modified) ' get the datatable so that I can access the datarow dtTable = dsChanges.Tables("Staff") For Each myDataRow In dtTable.Rows Console.WriteLine(myDataRow.Item) Next lngRetVal = daStaff.Update(dsChanges, "Staff") If lngRetVal > 0 Then dsStaff.AcceptChanges() End If End Sub Sorry - this probably seems very trivial, but I cannot seem to find a comprehensive example in any documentation, just bits and pieces which I am trying to tie together. At least I am learning how to do it properly. I am reading an article on Data concurrency at the minute, very interesting - not something I had even considered yet. Oh boy - its going to be a long night. Thank you all for any assistance. Lee [quoted text, click to view] "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
news:9DC8D049-F072-44C4-8271-A92CFADBA983@microsoft.com... > Lee, > > Here is some information on placing a combobox into a datagrid in .Net > 1.1: > > http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q480q > > Also, keep in mind that the code that Robin showed you does not handle > concurrency issues. If your program will be used by multiple users all > hitting a central database then you must deal with concurrency in your SQL > update and delete statements. > > Kerry Moorman > > > "Microsoft" wrote: > >> Yes I think I undestand where your coming from, effectively I need to >> create >> a new parameterized query for the update and insert command which is then >> assigned to the dataadapter. >> >> I have found some articles on doing this so I will have a play around >> with >> it. The other problem I've recognised in doing this is that ideally I >> need a >> combobox in the dataqgrid for the user to select from the fields in the >> other table - which isn't supported with the datagrid in 1.1, not sure >> about >> 2.0. I will get to this later, for now I will concentrate on getting the >> updates working. >> >> Thanks for your help. >> Lee >> >> >> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >> news:DIednU8aQKZ3h1zYnZ2dnUVZ_r-onZ2d@comcast.com... >> > First of all, be aware that the CommandBuilder is only effective for >> > really simple updates, like if you have a list of fields with a primary >> > key. >> > >> > You can *not* do updates using the CommandBuilder if you have a join in >> > your SQL statement. >> > >> > You will have to write your own SQL for the update, insert, and delete >> > commands, so they update only the one table. >> > >> > Example: >> > >> > Update tblStaff Set staff_id = ?, staff_initials = ?, staff_name = ?, >> > staff_title = ?, staff_active = ? WHERE staff_id = ? >> > >> > and then add parameters for those (in that order) from the data row in >> > your dataset. >> > >> > Does that make sense? >> > >> > Robin S. >> > ------------------------------------- >> > "Microsoft" <lee.clements.nospam@btopenworld.com> wrote in message >> > news:%2380X%23$VRHHA.4448@TK2MSFTNGP04.phx.gbl... >> >> Hi everyone, >> >> >> >> Before I begin I should point out that I am quite new to programming >> >> and >> >> VB.net and am using VS2003 with SQLExpress 2005. >> >> >> >> I have two data tables a staff table and an office table, the linking >> >> field is office_id. I have created a form and added a datagrid, when >> >> the >> >> form loads I create a new DataAdapter, Connection and DataSet and >> >> select >> >> the records from the staff table linking with the office table to >> >> display >> >> the office name rather than the ID. >> >> >> >> Private Sub frmStaff_Load(ByVal sender As System.Object, ByVal e As >> >> System.EventArgs) Handles MyBase.Load >> >> >> >> Dim dcConn as New OleDB.OleDbConnection("Integrated >> >> Security=SSPI;Packet >> >> Size=4096;Data Source='SERVER\INSTANCE';Initial >> >> Catalog=Planner;Persist >> >> Security Info=False;Provider="SQLOLEDB.1") >> >> >> >> Dim daStaff = New OleDb.OleDbDataAdapter("SELECT a.staff_id, >> >> a.staff_initials, a.staff_name, a.staff_title, a.staff_active, >> >> b.office_name FROM tblStaff a, tblOffice b WHERE a.staff_office_id = >> >> b.office_id", dcConn) >> >> >> >> Dim dsStaff as New System.Data.DataSet >> >> daStaff.Fill(dsStaff, "tblStaff") >> >> >> >> ' set the bding context >> >> currManager = CType(Me.BindingContext(dsStaff.Tables("Staff")), >> >> CurrencyManager) >> >> >> >> ' Set the databindings >> >> DataGrid1.DataSource = dsStaff.Tables("Staff") >> >> >> >> .... >> >> Here I create all the DataGridTextBoxColumn and set the mapping to the >> >> relevant field, etc. >> >> .... >> >> >> >> End Sub >> >> >> >> ' The procedure for saving the changes is:- >> >> Private Sub SaveChanges() >> >> Dim cmdBuilder As New OleDb.OleDbCommandBuilder(daStaff) >> >> Dim lngRetVal As Long >> >> >> >> lngRetVal = daStaff.Update(dsStaff, "Staff") >> >> If lngRetVal > 0 Then >> >> dsStaff.AcceptChanges() >> >> End If >> >> End Sub >> >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |