posting to SQL

[louise raisbeck]

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.

[Peter Rilling]

As a side note, I am not sure of your complete architecture you might want
to read the document
http://www.spidynamics.com/papers/SQ...WhitePaper.pdf. If you are
sending your form field values directly into your query, then your system
can be vulnerable to attacks.
"louise raisbeck" <lo************@discussions.microsoft.com> wrote in
message news:3A**********************************@microsof t.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.

[David Kyle]

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" <lo************@discussions.microsoft.com> wrote in
message news:3A**********************************@microsof t.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.

[louise raisbeck]

404 file not found

"Peter Rilling" wrote:

As a side note, I am not sure of your complete architecture you might want
to read the document
http://www.spidynamics.com/papers/SQ...WhitePaper.pdf. If you are
sending your form field values directly into your query, then your system
can be vulnerable to attacks.
"louise raisbeck" <lo************@discussions.microsoft.com> wrote in
message news:3A**********************************@microsof t.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.

[louise raisbeck]

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" <lo************@discussions.microsoft.com> wrote in
message news:3A**********************************@microsof t.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.

[Scott Simons]

Please google sql injection attacks. Your app is vulnerable to them.

[David Kyle]

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" <lo************@discussions.microsoft.com> wrote in
message news:71**********************************@microsof t.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" <lo************@discussions.microsoft.com> wrote in
message news:3A**********************************@microsof t.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.

[louise raisbeck]

thanks I havent read it yet but will do so in the morning. i should be at home!

"David Kyle" wrote:

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" <lo************@discussions.microsoft.com> wrote in
message news:71**********************************@microsof t.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" <lo************@discussions.microsoft.com> wrote in
message news:3A**********************************@microsof t.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.