Re: posting to SQL

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: David Kyle (dkyle_at_chloemag.com)
Date: 11/15/04 

Date: Mon, 15 Nov 2004 11:51:34 -0800

Sorry about the late reply. I'm currently working right now. None the less
I would still suggest that you use the method of building your own sql
strings to insert/update/delete/select data from your database. If you
think that the data might be a bit much to keep updating all of it every
time you could use the private property on the Page object called ViewState.
(Page.ViewState)

This will hold the information you want in a compressed string that is then
encrypted and held in every page as a hidden control named "__VIEWSTATE".
here's an example of how I would use it:

On_PageLoad() {
DataTable dt = new DataTable("Customers");

if (!Page.IsPostBack) {
string sql = "select LoginID, Password from Customers where CustomerID=007";

SqlConnection con = new SqlConnection("connection string");
SqlDataAdapter da = new SqlDataAdapter(sql, con);

con.Open();
da.Fill(dt)
con.Close();

txtLoginID.Text = (string)dt.Rows[0][0];
ViewState["LoginID"] = dt.Rows[0][0];
txtPassword.Text = (string)dt.Rows[0][1];
ViewState["Password"] = dt.Rows[0][1];
}
}

On_Submit_Click() {
bool execute = false;
string sql;

sql = "update Customers set ";
if (txtLoginID.Text != (string)ViewState["LoginID"]) {
sql += "LoginID='" + txtLoginID.Text +"' ";
execute = true;
}
if (txtPassword.Text != (string)ViewStat["Password"]) {
if (execute)
sql += ", ";
sql += "Password='" + txtPassword.Text + "' ";
}
...

sql += "where CustomerID=" + lblCustomerID.Text;

if (execute) {
SqlConnection con = new SqlConnection("connection string");
SqlCommand command = new SqlCommand(sql, con);

con.Open();
command.ExecuteNonQ();
con.Close();
}
}

"louise raisbeck" <louiseraisbeck@discussions.microsoft.com> wrote in
message news:7150D98B-123C-48C5-BF76-9153A9F87FE8@microsoft.com...
>I did think about just doing an update for all the fields on the form..just
> seemed like overkill considering they may only change 1 in 20 field
> values. I
> know that .net is very powerful when it comes to view state, knowing when
> a
> control has changed value etc..therefore i wondered if there was an easy
> way
> of checking what has changed and what hasnt. Its almost as if I need a
> webcontrols collection and for each one check if the value has changed..
> I'm
> sure that is possible. But to be honest I have to get this done like
> yesterday and just dont have the time to do the research..
>
> "David Kyle" wrote:
>
>> Well personally I use a different technique to update my sql database. I
>> just write out my sql scripts manually.
>>
>> for instance:
>>
>> On_Submit_Click() {
>> string sql = "update Customers set LoginID='" + txtLoginID.Text + "',
>> Password='" + txtPassword.Text "' where CustomerID=" +
>> lblCustomerID.Text;
>>
>> SqlConnection con = new SqlConnection("connection string");
>> SqlCommand com = new SqlCommand(sql, con);
>>
>> con.Open();
>> com.ExecuteNonQuery();
>> con.close();
>> }
>>
>> I know this style is kind of old school but to me it just seems like you
>> can
>> customize your sql scripts even more and thus allow for more complicated
>> and
>> userfriendly forms.
>>
>> As for how this applies to your problem... it's quite simple. All fields
>> are always updated with one sql statement if they haven't changed they
>> are
>> just set to whatever they used to be if they have changed then they are
>> updated.
>>
>> This method should be considerably faster to execute and less processor
>> intensive for both your ASP.NET applicaiton and the SQL Server.
>>
>> These are just my thoughts on the subject.
>>
>> Cheers!
>>
>> David Kyle
>> www.chloemag.com
>>
>>
>> "louise raisbeck" <louiseraisbeck@discussions.microsoft.com> wrote in
>> message news:3A223C46-0555-4C8C-B305-6761CE0E457D@microsoft.com...
>> > Hi, I'm sure this is a standard thing. I have a web form with several
>> > input
>> > boxes/drop down lists and checkboxes. They all represent a field within
>> > a
>> > sql
>> > table and I need the user to update the values on hitting submit. I
>> > have
>> > put
>> > a submit button on the form and tested it with one field, so in the
>> > submit
>> > code I opened a sql connection and did an UPDATE mytable SET
>> > fieldintable
>> > =
>> > inputonwebform.text where customerid=x.
>> >
>> > This worked! However, it just so happened that I changed the value in
>> > 'fieldontable'. Should I be creating an update statement for EVERY
>> > field
>> > on
>> > my form, even though the user may only change one or two values at a
>> > time?
>> > I
>> > know there is a OnTextChanged event handler of a text box, so i could
>> > capture
>> > whether it has been changed or not (and then have to handle the drop
>> > downs/checkboxes too presumbably using a different handler), but how
>> > can I
>> > maintain a list of controls whose values have changed up until the
>> > point
>> > where the user hits submit. Looking for the best practice to do this.
>> > dont
>> > want to start keeping a comma seperated value in a hidden field, I find
>> > that
>> > really messy. I am sure there is a really good way to do this as this
>> > seems
>> > to be the power of web forms, I just need someone to tell me it!!!
>> >
>> > Many thanks.
>>
>>
>>

Quantcast