Help: Programmatically update data from an SQLDataSource (C#)

ello all

Would someone be so kind as to save me from getting balder through pulling my hair out.

My aim is to extract data from a database using SQLDataSource, then edit the data and update the database using the SQLDataSource.

I have achieve the problem of retrieving the data from the sqlDataSource:
DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
//Int32 openRemindingSeats = SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView;

foreach (DataRowView rowProduct in openReminding)
{
//Output the name and price
lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString();

}

Within the sqlDataSource the sql code is as follows:
SELECT [refNumber], [refRemaining] FROM [refFlights] WHERE ([refNumber] = @.Number)

So at the moment my problems is being able to edit and update data to the same SELECTed data.
Thank you for any help that you might have...

SynDrome

Do you want to control everything from your code or you are good enough using the existing controls. If the latter suits you, you can use gridview control and bind it with your datasource and allow editing etc. If you are more interested in former, let me know and I can take you forward from there.

|||

I'm fine using DataGrides etc, thats not a problem, I just need to use code to manage some of the data from a form and others... Theres only 1-2 pages where this needs to be done, but its driving me crazy.

Thanks for the reply

|||

For editing, you need to have some input from the user (from textboxes, dropdownlists etc) and then use that data to update your table. This article might help you.

http://www.codeguru.com/Csharp/Csharp/cs_data/article.php/c4211/

P.S.: Keep you sanity, we programmers suffer from this syndrome a lot.

|||

Thanx m8,

I'll have a look, now I'm still finding it hard to get the correct code for using the sqlDataSource.
For example the following code uses sqlDataSource2.Select command but I can't find anywhere that explains how to use the update code, which would be similar to the following code.
I have read through so much code and web sites but for the live of me I can find how to 'update' data using the sqlDataSource.

DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);

Don't you just h8 it where one simple thing can, just one bloody bit of code can hold you up,

Thanks
SynDrome

|||

Did you have a look at this one:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.update.aspx

|||

Working through it now thanks. I'l give you I-O-U on a Pint...

Looks like I might have to changes a few things around, but it looks work able.

Thanks m8

|||

You are welcome...and thanx for the offer but I don't drink...

|||

Again thank you buddy.

Incase anyone else is having the same problem I feel I should return the help, to help other, and also in order to reduce the growing population of bald people lol.

First the problem, I wanted to programmatically edit data that is being managed by an SqlDataSource (C#).

First I want to extract data from a SqlDataSource programmatically, the following code does this, and if you wanted it will gather multiple record data, by use the FOREACH loop, but this example I am just extracting one record field:

DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
foreach (DataRowView rowProduct in openReminding)
{
//Output the name and price
lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString();
}

Now that I have the required data from the database, I can now do anything you need to it, now all you need to do is insert it back into same place within the database.
The following code is an SqlDataSource, with this I can add an "asp:ControlParameter ", this obtains data from a control, in this case I have used both txt & lbl, then using the "Name" from the "asp:ControlParameter", you can pass the varible into the "UpdateCommand" in this example "@.numSeats" .

Example.aspx

<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB_Home %>"
SelectCommand="SELECT FlightNumber, Num_Seat_Class1 FROM Flights)"
UpdateCommand="UPDATE Flights SET Num_Seat_Class1 = @.numSeats WHERE FlightNumber = @.flighNum">
<UpdateParameters>
<asp:Parameter Name="FlightNumber" />
<asp:ControlParameter Name="numSeats" ControlId="txt_totalNumPass" PropertyName="Text"/>
<asp:ControlParameter Name="flighNum" ControlId="lbl_flighNum" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>

To insert the the data you need an OnClick, to fire the "UpdateCommand" as shown below.
Example.aspx.cs

protected void On_Click(object sender, EventArgs e)
{
// executes the Update sql command from the SqlDataSource5
// which takes data from txt boxes and updates them into the correct class
SqlDataSource5.Update();
}

I hope this might help someone, and I also hope that you can understand the code lol.

SynDrome