How do I update a database?

[Fubear]

Ok,
I'm playing with my first ASP.NET page, it is just a test page so I can familiarise myself with building Web Applications in VB.Net.

I have the page linked to an Access Databse, and I can get the data out fine.

I have a listbox bound to a table, and then a Dataview selecting the currently selected record in the listbox and displaying the field details in textboxes databound to the davaview.

How can I update the data in the databse?
I can change the field values, but if i select another item from the list, and then go back the changes have not been saved. I assume I need some code to save the new field value back into the databse, but I dont have a cluse what it is.

---
A side question, I need to select data from two tables, and currently have two sqlDataAdapters, each one selecting the data from differant tables. Is there any way I could select the data with just the one?

I am a proficient programmer, but I want to take full advantage of VS.Net and I am trying to do everything visually using the GUI to get a feel for how everything looks and feels (I find developing in a GUI much easier than writing loads of unnececary code).

 

[Fubear]

I am assuming that to update I would do soemthing like:
oleDbDataAdapter1.Update (dsCats1);

This does not seem to work at present. Do I need to update the dataSource manually?

Also Adapter1 takes data from one table, if I wanted to update the other table would Ihave to call the Update command for Adapter2?

This is a little differant to what I am used to.

 

[davecapone]

to update a DB record assuming you have a table a table with 2 columns ID and theField:

SqlConnection objSqlConn=new SqlConnection();
objSqlConn.ConnectionString="yourDSN";
objSQLConn.Open();
String mySQL="SELECT * FROM myTable";
SqlDataAdapter da=new SqlDataAdapter(mySQL,objSqlConn);
DataSet ds=new DataSet();
da.Fill(ds,"myTable"
SqlCommandBuilder cb=new SqlCommandBuilder(da);
ds.Tables["myTable"].Rows[0]["ID"]=yournewvalue;
ds.Tables["myTable"].Rows[0]["theField"]="yournewvalue";
da.Update(ds,"myTable"


Then re-bind your datagrid etc as usual and this should handle it.

 

[Fubear]

What would I do if I dont know which row of the data source I am updating?

I have a DataView filtered down to one particular record, which could be any record in the set, I only now the ID number not the row number.

 

[130478by]

this code is for updating a table. i have a little problem with it, but maybe you can use it
go to thread855-621771

 

[davecapone]

Sorry for the delay in response, if you know the IDNum of the Row and it is the primary key in the Database table change your initial SQL select statement to include a WHERE clause so it ONLY slects that row. Then the Row ID would have to be 0.

 

[Fubear]

So I can update teh data with the dataview? I have been trying through the dataset.

I got it working in the end:
String catVal = lstCategories.SelectedItem.Value.ToString ();
DataRow drUpdateRow = dsCats1.Tables["tblCats"].Rows.Find (catVal);
drUpdateRow.BeginEdit ();
drUpdateRow["CatDescription"] = txtCatDesc.Text;
drUpdateRow["CatName"]=txtCatName.Text;
drUpdateRow.EndEdit();
oleDbDataAdapter1.Update (dsCats1,"tblCats"
lstCategories.DataBind ();
lstCategories.SelectedIndex = lstCategories.Items.IndexOf(lstCategories.Items.FindByValue (catVal));