update query that ignores nulls?

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

45 Replies - 4906 Views - Last Post: 11 June 2011 - 04:17 AM Rate Topic: -----

#1 svpam123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 01-April 11

update query that ignores nulls?

Posted 03 June 2011 - 11:07 PM

hey!
I'm writing a form and on submit it will go to an aspx page which will edit the values inserted by a user for every field.
for example -
UserID = "ABC"
Fname = "A"
Lname = "B"

and so on.
my form looks something like this -

<form id="f1" action="UpdateData.aspx" method="post">
<br />
<br />
    <div>
    Edit user information:
    <br />
    First name: 
        <input id="Fname" name="Fname" type="text" />
    <br />
    Last name: 
        <input id="Lname" name = "Lname" type="text" />
    <br />
    Email: 
        <input id="Email" name="Email" type="text" />
    <br />
    Age:
    <input id="Age" name="Age" type="text" />
    <br />

        <input id="Submit1" type="submit" value="Edit!" />

           </div>
    
    </form>

when user submits the aspx page will edit the values to the values inserted by the user.

now, how do I handle blanks?
what if the user clicks on submit but only wrote a value for Fname? all other values will be updated into blank.
how can I avoid this? how can I retain the original value?

thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: update query that ignores nulls?

#2 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: update query that ignores nulls?

Posted 03 June 2011 - 11:13 PM

try to use asp.net validation
ASP.NET Validation
Was This Post Helpful? 0
  • +
  • -

#3 svpam123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 01-April 11

Re: update query that ignores nulls?

Posted 03 June 2011 - 11:28 PM

yes but that will check if the field is blank or something like that, but I want to allow him to leave it blank, and if he decides to do so - the field in the table won't change.
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,766
  • Joined: 08-June 10

Re: update query that ignores nulls?

Posted 03 June 2011 - 11:41 PM

Hey.

Why not just construct the SET clause based on the fields you get?

I'm not fluent in ASP.NET, but I would imagine something like this would work:
// A list of parameters to find	
List<string> fields = { "Fname", "Lname", "Email", "Age" };
	
// A string to hold the set clause
string setClause = "";

// A list of values to be used in the set clause.
Dictionary<string, string> setValues = new Dictionary<string, string>();

// Loop through all the fields and collect passed fields,
// adding each to the set clause and storing the value.
foreach (string field in fields)
{
	if (Request.Form[field] != null)
	{
		setClause += field + " = @" + field +",";
		setValues.Add(field, Request.Form[field]);
	}
}

// Remove the trailing comma.
setClause = setClause.TrimEnd( {','} );

// Construct the query, using the set clause from above.
string sqlText = "UPDATE something SET " + setClause + " WHERE someID = @someID";
SqlCommand command = new SqlCommand(sqlText, connection);
command.Parameters.AddWithValue("@someID", Request.Form["someID"]);

// Add the parameters.
foreach (string field in setValues)
	command.Parameters.AddWithValue("@" + field, setValues[field]);


Was This Post Helpful? 1
  • +
  • -

#5 svpam123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 01-April 11

Re: update query that ignores nulls?

Posted 04 June 2011 - 12:29 AM

View PostAtli, on 03 June 2011 - 11:41 PM, said:

Hey.

Why not just construct the SET clause based on the fields you get?

I'm not fluent in ASP.NET, but I would imagine something like this would work:

hey
thanks a lot for your help, but I can't really use this :(
this is a project for school and you used things we didn't study in c# (it's called generics right?)
the teacher won't accept it.

is it possible to do it with an array though? because the idea is very nice.

thanks again!
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,766
  • Joined: 08-June 10

Re: update query that ignores nulls?

Posted 04 June 2011 - 01:40 AM

Yea, the List and Dictionary classes use generics in order to support whatever type you need.

Sure, you can use arrays instead of Lists and Dictionaries. I would probably try doing something like:
  • Set up two arrays, one to hold a list of the field names you are expecting, and another one to hold the field values you receive from those fields.
  • Also set up a string to hold your SQL query text, and add the query up to the SET clause. (Like: string sql = "UPDATE something SET ";)
  • Then loop through the field name array using a for (int i = 0; i < fieldNames.Lenght; i++) loop (you need the index), and check if the user passed a value for that field.
    • If he did, put the value into the values array, at the exact same index the field name is in in the field name array. (Using the index from the for loop). - Also add the field to the SET clause of the SQL query. (sql += key[i] + " = @" + key[i] + ",";)
    • If there is no value for that key, put a null value into the values array and do nothing to the SQL query.

  • Then once it comes to adding the parameters to the SqlCommand, all you have to do is loop through the values array and search for values that are not null and add those, again using the index from a for loop to fetch the correct field names and field values from the two arrays.

Hope I'm making myself somewhat understandable :)
Was This Post Helpful? 1
  • +
  • -

#7 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: update query that ignores nulls?

Posted 04 June 2011 - 01:45 AM

first load data in variable and check if the textbox is empty then assign value from variable.

example:
string field1 = 'value from database'
if(!string.IsNullOrEmpty(txtfield1.Text)){
      field1 = txtfield1.Text;
}
// assign fiedl1 variable to your query...



Hope this help.

This post has been edited by noorahmad: 04 June 2011 - 01:46 AM

Was This Post Helpful? 1
  • +
  • -

#8 svpam123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 01-April 11

Re: update query that ignores nulls?

Posted 04 June 2011 - 02:23 AM

lots of thanks to both of you!
I'll go with what noorahmad suggested because it's less complex.
I've got it working but I have a little problem, with the Age -
string Age = Request.Form["Age"];
        if (string.IsNullOrEmpty(Age))
        {
            Age = (string)(Session["Age"]);
        }

and it generates this error -
Unable to cast object of type 'System.Int32' to type 'System.String'.
the Age in my table is from data type int, how do I cast it so this will work?


and just out of curiosity, Atli -
sql += key[i] + " = @" + key[i] + ",";
I've seen the '@' a couple of times, what does it mean? what's the use of the operator?


and also another question - is it okay to use many sessions? our teacher told us he wants us to use as less as possible.. why is that? any problem with using them?

thanks again!! really helped out
Was This Post Helpful? 0
  • +
  • -

#9 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: update query that ignores nulls?

Posted 04 June 2011 - 02:31 AM

You can convert int to string
string Age = Request.Form["Age"];
        if (string.IsNullOrEmpty(Age.ToString()))
        {
            Age = (string)(Session["Age"]);
        }


Was This Post Helpful? 1
  • +
  • -

#10 svpam123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 01-April 11

Re: update query that ignores nulls?

Posted 04 June 2011 - 02:42 AM

still getting the same error

Unable to cast object of type 'System.Int32' to type 'System.String'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.

Source Error:

Line 35: if (string.IsNullOrEmpty(Age.ToString()))
Line 36: {
Line 37: Age = (string)(Session["Age"]);
Line 38: }
Was This Post Helpful? 0
  • +
  • -

#11 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,766
  • Joined: 08-June 10

Re: update query that ignores nulls?

Posted 04 June 2011 - 04:01 AM

Ahh yes, of course. I forgot how the old-school pre-MVC ASP.NET applications liked to pretend to be Win forms :)


As to your error, try something like this:
Age = Session["Age"].ToString();



Or even this:
Age = String.Format("{0}", Session["Age"]);



View Postsvpam123, on 04 June 2011 - 09:23 AM, said:

and just out of curiosity, Atli -
sql += key[ i] + " = @" + key[ i] + ",";
I've seen the '@' a couple of times, what does it mean? what's the use of the operator?

It's used to denote named placeholders in a Parameterized Statement.

For example, you can do this in C#:
var sqlText = "INSERT INTO myTable ([name], [email], [password])" +
              "VALUES (@name, @email, @password)";
SqlCommand command = new SqlCommand(sqlText, connection);

command.Parameters.AddWithValue("@name", nameField.Text);
command.Parameters.AddWithValue("@name", emailField.Text);
command.Parameters.AddWithValue("@name", passwordField.Text);


The @name, @email and @password placeholders in the sqlText would then be replaced by the values of the text fields specified in the AddWithValue calls.

I'm not sure how you are doing your SQL interaction, but this is most like what I usually do. - I mostly work in other languages though, so .NET developers may be more used to something else.

View Postsvpam123, on 04 June 2011 - 09:23 AM, said:

and also another question - is it okay to use many sessions? our teacher told us he wants us to use as less as possible.. why is that? any problem with using them?

Session bloating is never a good thing. It's way to common to see people pile things into the session that shouldn't really be there. Or worse, putting temporary data in there and forgetting to clear it out afterwards.

If you need something to be available across requests, then you should of course put it into the session. But just don't be putting things in there that you won't be needing again, or only rarely use. Those sort of things are better left in a more permanent storage and retrieved when needed. No point having it loaded for ever request.

For instance, do you use your Session["Age"] value frequently, or is it perhaps only displayed on one profile page? If it's the latter (which is common), it really shouldn't be in the session. It would be better to load it on that one page from your SQL server. (Or wherever it's stored.)

This post has been edited by Atli: 04 June 2011 - 04:02 AM

Was This Post Helpful? 1
  • +
  • -

#12 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,766
  • Joined: 08-June 10

Re: update query that ignores nulls?

Posted 04 June 2011 - 04:13 AM

I've moved this topic from MySQL to the ASP.NET section, by the way. It's not so much about the actual database as it is about the ASP.NET code to interact with the database.
Was This Post Helpful? 0
  • +
  • -

#13 svpam123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 01-April 11

Re: update query that ignores nulls?

Posted 04 June 2011 - 06:29 AM

View PostAtli, on 04 June 2011 - 04:01 AM, said:

Ahh yes, of course. I forgot how the old-school pre-MVC ASP.NET applications liked to pretend to be Win forms :)


As to your error, try something like this:
Age = Session["Age"].ToString();



Or even this:
Age = String.Format("{0}", Session["Age"]);



View Postsvpam123, on 04 June 2011 - 09:23 AM, said:

and just out of curiosity, Atli -
sql += key[ i] + " = @" + key[ i] + ",";
I've seen the '@' a couple of times, what does it mean? what's the use of the operator?

It's used to denote named placeholders in a Parameterized Statement.

For example, you can do this in C#:
var sqlText = "INSERT INTO myTable ([name], [email], [password])" +
              "VALUES (@name, @email, @password)";
SqlCommand command = new SqlCommand(sqlText, connection);

command.Parameters.AddWithValue("@name", nameField.Text);
command.Parameters.AddWithValue("@name", emailField.Text);
command.Parameters.AddWithValue("@name", passwordField.Text);


The @name, @email and @password placeholders in the sqlText would then be replaced by the values of the text fields specified in the AddWithValue calls.

I'm not sure how you are doing your SQL interaction, but this is most like what I usually do. - I mostly work in other languages though, so .NET developers may be more used to something else.

View Postsvpam123, on 04 June 2011 - 09:23 AM, said:

and also another question - is it okay to use many sessions? our teacher told us he wants us to use as less as possible.. why is that? any problem with using them?

Session bloating is never a good thing. It's way to common to see people pile things into the session that shouldn't really be there. Or worse, putting temporary data in there and forgetting to clear it out afterwards.

If you need something to be available across requests, then you should of course put it into the session. But just don't be putting things in there that you won't be needing again, or only rarely use. Those sort of things are better left in a more permanent storage and retrieved when needed. No point having it loaded for ever request.

For instance, do you use your Session["Age"] value frequently, or is it perhaps only displayed on one profile page? If it's the latter (which is common), it really shouldn't be in the session. It would be better to load it on that one page from your SQL server. (Or wherever it's stored.)

first of all thanks again, really helped out!

1. error was solved, working now :)

2. understood that too.

3. well, in this case I'm using the session in the following way -
in order to edit a user, these steps have to be taken -
a. click on a "search link", enter a user name and search for it. (Search.html)
b. on submit, the form retrieves data from the database -
"Select * from tbl2 where ( UserId = '" + UserId + "')"
and writes it on the screen-
"First name: " + ds.Tables[0].Rows[0]["Fname"]
and so on.
if user is logged in as an admin (I still don't know how to have users log to the site, we'll probably learn it this week, so right now everybody can edit), then this form appears on the bottom of the page, with text boxes for the users info - Fname Lname Age and so on.
if he wants to edit info he'll write the new details in the text box and submit the form.
in order to be able to retrieve the information we've pulled out of the database from the user, its saved by sessions -
Session["Fname"] = ds.Tables[0].Rows[0]["Fname"]
(all this is done by a page called SearchASP.aspx)

if he submits, another page is called, named UpdateData.aspx.
this page retrieves the session from the last page, SearchASP.aspx and does the following -
string UserId = (string)(Session["UserId"]); 
        string Fname = Request.Form["Fname"];
        if (string.IsNullOrEmpty(Fname))
        {
            Fname = (string)(Session["Fname"]);
        }

(and so on for the other info)
and then
"Update tbl2 set  Age = ' " + Age + "', Fname = ' " + Fname + " ', Lname = ' " + Lname + " ', Email = ' " + Email + " ' where ( UserId = '" + UserId + "')";


what do you think? is it problematic? any other solution?
do I just have to 'clear' the sessions after the use? if so how?

thanks a lot for your time, really appreciated!

This post has been edited by svpam123: 04 June 2011 - 06:30 AM

Was This Post Helpful? 0
  • +
  • -

#14 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,766
  • Joined: 08-June 10

Re: update query that ignores nulls?

Posted 04 June 2011 - 08:13 AM

I wouldn't use the session that way. As far as I understand it, the only reason you are putting the results of your SQL query into the session is to use it as default, non-updated values for your UPDATE query on the next page. But like I was getting at earlier in the thread, you don't actually have to include all the fields in the UPDATE query. You just have to include the fields that change.

The way I would approach this, in the searchASP.aspx file after printing the results of the user search, I would pass the UserID through to the next page with the edited data, either as hidden input field inside the form or as a GET parameter in the URL.

I'm not sure exactly how an ASP.NET form would go about populating a hidden input field, but based on the form in your first post, you would want the end result looking something like this:
<form id="f1" action="UpdateData.aspx" method="post">
	<input type="hidden" name="UserID" value="USER_ID_GOES_HERE" />
	<br /><br />
	<div>
		Edit user information: <br />
		First name: <input id="Fname" name="Fname" type="text" /><br />
		Last name:  <input id="Lname" name = "Lname" type="text" /><br />
		Email:      <input id="Email" name="Email" type="text" /><br />
		Age:        <input id="Age" name="Age" type="text" /><br />
		<input id="Submit1" type="submit" value="Edit!" />
	</div> 
</form>



Then in the UpdateData.aspx file I would fetch the UserID from the form/GET data, and use that in the UPDATE query, which I would build using only the form data I received, ignoring fields that were left out.

So instead of doing stuff like this in the UpdateData.aspx code:
string UserId = (string)(Session["UserId"]); 

string Fname = Request.Form["Fname"];
if (string.IsNullOrEmpty(Fname))
{
	Fname = (string)(Session["Fname"]);
}

string Lname = Request.Form["Lname"];
if (string.IsNullOrEmpty(Lname))
{
	Lname = (string)(Session["Lname"]);
}

string sql = "Update tbl2 set  Fname = ' " + Fname + " ', Lname = ' " + Lname + " ' WHERE ( UserId = '" + UserId + "')";


I would try to make it like this:
// Fetch the UserID from the form, first making sure
// it's actually there. (Which you should always do.)
if (Request.Form["UserID"] != null)
{
	string UserID = Request.Form["UserID"];

	// Construct the SET clause of the UPDATE query
	// based on the fields you are receiving.
	string setClause = "";
	if (Request.Form["Fname"] != null)
	{
		setClause += "Fname = '" + Request.Form["Fname"] + "',";
	}
	if (Request.Form["Lname"] != null)
	{
		setClause += "Lname = '" + Request.Form["Lname"] + "',";
	}
	setClause = setClause.TrimEnd(',');

	// Make sure at least one field was sent.
	if (setClause != "")
	{
		string sql = "UPDATE tbl2 SET " + setClause + " WHERE UserID = '" + UserID + "'";
		
		// And then carry on with whatever it is you do with
		// the UPDATE query.
	}
	else
	{
		// Shown an error about there not being any fields.
	}
}
else
{
	// Show an error about the missing UserID.
}



There would be no need to involve the session at all. All the required data would come either from the request itself, or from the database.


By the way, all this code, and the one you posted earlier, is EXTREMELY insecure. Depending on the database type and connection method, it could allow a malicious user to do anything from clearing out the table you call tbl2, to completely taking control of the host computer. - Look up SQL Injection to see what I mean.
(This is the reason we use the Parameterized Statement syntax I was explaining earlier, to protect against this kind of an attack.)


Also, in your UPDATE query you have this: Fname = ' " + Fname + " '.
The extra space between the ' and the " will cause there to be an extra around the actual value stored in the field.
Was This Post Helpful? 1
  • +
  • -

#15 svpam123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 01-April 11

Re: update query that ignores nulls?

Posted 04 June 2011 - 12:45 PM

hey again

thank you very much for your time and help, really nice of you to help out so much.
learned a lot from your post and read about the SQL injection, boy that was really interesting!
thanks :)

okay so I have a couple of questions about what you've suggested -
1. if I cleared the sessions after using them, will it be fine?

2. is it safe to put data like this?
<input type="hidden" name="UserID" value="USER_ID_GOES_HERE" />
I can't point out why but it looks like it's less 'secure' than with sessions.
what if I'm passing passwords like this? is it fine too?
I got the idea of passing parameters from one ASP page to another from the MSDN site... it had an article about passing parameters and it was suggested there, so I thought it's a good idea.

3. about the security -
this is actually a project we have to hand in, and I've mailed my teacher to inquire about the security issues. he said we don't have to worry about SQL injections and that what I'm doing is fine (the whole query part where I didn't use parametrized statement syntax).
however, I'll keep reading about it and make adjustments because its really interesting and its also a good practice.

4. when is it okay to use sessions?


thank you very much for your help once again
great and helpful forum with nice people :)

This post has been edited by svpam123: 04 June 2011 - 12:47 PM

Was This Post Helpful? 0
  • +
  • -

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »