ADO.NEt saving null values

  • Thread starter Thread starter Ivan Sammut
  • Start date Start date
I

Ivan Sammut

Hi,

I created an access database and I am using ado.net to access it. Now I am
trying to create an update statment but I am having some error when so
fields are empty.

Here is my statment which read a set of textboxes from a screen and tries to
save them.

string sSql;
OleDbConnection conn = new OleDbConnection(Form1.Dbase);
sSql = "Update Users Set ";
sSql = sSql + "Usr_login = \"" + textBox1.Text.Trim() + "\",";
sSql = sSql + "Usr_name = \"" + textBox2.Text.Trim() + "\",";
sSql = sSql + "Usr_desg = \"" + textBox3.Text.Trim() + "\",";
sSql = sSql + "Usr_mail = \"" + textBox4.Text.Trim() + "\",";
sSql = sSql + "Usr_mobile = \"" + textBox5.Text + "\",";
sSql = sSql + "Usr_disb = " + checkBox2.Checked.ToString();
sSql = sSql + " where Users.usr_code =";
sSql = sSql + pbusr;
//
//return;
conn.Open();
OleDbCommand cmd = new OleDbCommand(sSql,conn);
cmd.ExecuteNonQuery();
conn.Close();

This works fine until all the textboxes contain something. As soon as I
empty one of the textboxes the system gives an error. Any idea?
 
Hi Ivan,

You database probably doesn't accept null values (empty strings also).
Anyway, you should use parametrised sql commands rather than dynamically
concatenated strings.
BTW, what error are you receiving?
 
Can u pls give me an example of a parametrised sql commands .

Sorry if it's a stupid question, but i'm still a rookie

thanks
Ivan Sammut
 
Ivan,

Simple example:
instead of

[C#]
sqlcommand.CommandText = "SELECT * FROM Category WHERE CategoryID = 1";

you would use
sqlcommand.commandtext = "SELECT * FROM Category WHERE CategoryID =
@CategoryID ";
sqlcommand.Parameters.Add("@CategoryID", SqlDbType.Int);
sqlcommand.Parameters["@CategoryID"].Value = 1;

So, instead of putting parameter values into sql command, you use
parameters.
This way is faster if you need to execute more same commands with different
parameters (server parses command only once) and way more secure.

There is plenty of info in .net help.
For beginning you might check
Using Parameters with a DataAdapter
help topic.
 
Hm, the same wrong information voer and over again...

Miha Markic said:
Ivan,

Simple example:
instead of

[C#]
sqlcommand.CommandText = "SELECT * FROM Category WHERE CategoryID = 1";

you would use
sqlcommand.commandtext = "SELECT * FROM Category WHERE CategoryID =
@CategoryID ";
sqlcommand.Parameters.Add("@CategoryID", SqlDbType.Int);
sqlcommand.Parameters["@CategoryID"].Value = 1;

So, instead of putting parameter values into sql command, you use
parameters.
This way is faster if you need to execute more same commands with different
parameters (server parses command only once) and way more secure.
Click to expand...

This is wrong, and it has been prooven over and over. SQL Server 2000
parameterizes non-parameter sql statements internally and reuses the cached
query path. Treatment is identical with a parameterized query AND A STORED
PROECEDURE.

Read the documentation.

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
 
Thanks a lot
Miha Markic said:
Ivan,

Simple example:
instead of

[C#]
sqlcommand.CommandText = "SELECT * FROM Category WHERE CategoryID = 1";

you would use
sqlcommand.commandtext = "SELECT * FROM Category WHERE CategoryID =
@CategoryID ";
sqlcommand.Parameters.Add("@CategoryID", SqlDbType.Int);
sqlcommand.Parameters["@CategoryID"].Value = 1;

So, instead of putting parameter values into sql command, you use
parameters.
This way is faster if you need to execute more same commands with different
parameters (server parses command only once) and way more secure.

There is plenty of info in .net help.
For beginning you might check
Using Parameters with a DataAdapter
help topic.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com
Ivan Sammut said:
Can u pls give me an example of a parametrised sql commands .

Sorry if it's a stupid question, but i'm still a rookie

thanks
Ivan Sammut
Click to expand...
Now
I
Click to expand...
as
I
Click to expand...
Click to expand...
 
Hi Thomas,

Are you saying that executing two queries:
SELECT * FROM Category WHERE CategoryID = 1
SELECT * FROM Category WHERE CategoryID = 2

is the same as executing
SELECT * FROM Category WHERE CategoryID = @CategoryId
passing 1 and 2 as @Category
?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Thomas Tomiczek said:
Hm, the same wrong information voer and over again...

Miha Markic said:
Ivan,

Simple example:
instead of

[C#]
sqlcommand.CommandText = "SELECT * FROM Category WHERE CategoryID = 1";

you would use
sqlcommand.commandtext = "SELECT * FROM Category WHERE CategoryID =
@CategoryID ";
sqlcommand.Parameters.Add("@CategoryID", SqlDbType.Int);
sqlcommand.Parameters["@CategoryID"].Value = 1;

So, instead of putting parameter values into sql command, you use
parameters.
This way is faster if you need to execute more same commands with different
parameters (server parses command only once) and way more secure.
Click to expand...

This is wrong, and it has been prooven over and over. SQL Server 2000
parameterizes non-parameter sql statements internally and reuses the cached
query path. Treatment is identical with a parameterized query AND A STORED
PROECEDURE.

Read the documentation.

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
Click to expand...
 
No.

Read what I said.

Read the documentation.

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)

Miha Markic said:
Hi Thomas,

Are you saying that executing two queries:
SELECT * FROM Category WHERE CategoryID = 1
SELECT * FROM Category WHERE CategoryID = 2

is the same as executing
SELECT * FROM Category WHERE CategoryID = @CategoryId
passing 1 and 2 as @Category
?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Thomas Tomiczek said:
Hm, the same wrong information voer and over again...

Miha Markic said:
Ivan,

Simple example:
instead of

[C#]
sqlcommand.CommandText = "SELECT * FROM Category WHERE CategoryID = 1";

you would use
sqlcommand.commandtext = "SELECT * FROM Category WHERE CategoryID =
@CategoryID ";
sqlcommand.Parameters.Add("@CategoryID", SqlDbType.Int);
sqlcommand.Parameters["@CategoryID"].Value = 1;

So, instead of putting parameter values into sql command, you use
parameters.
This way is faster if you need to execute more same commands with different
parameters (server parses command only once) and way more secure.
Click to expand...

This is wrong, and it has been prooven over and over. SQL Server 2000
parameterizes non-parameter sql statements internally and reuses the cached
query path. Treatment is identical with a parameterized query AND A STORED
PROECEDURE.

Read the documentation.

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
Click to expand...
Click to expand...
 
No.

Read what I said.
Click to expand...

Sigh. Unfortunately your original post was scrambled because of your
poor English skills. Now, poor English skills are no big deal, it's an
international forum after all, but getting haughty and rude about it
seems a bit misplaced, IMHO. Miha was simply asking for a clarification
of a point that your poor grammar and spelling made ambiguous and
difficult to understand. Personally I don't see the need for the
rudeness in response.

Maybe you're just having a bad day.
Read the documentation.
Click to expand...

Apparently what Thomas is trying to point out is that SQL Server 2000
will auto-parameterize statements like the above and re-use the
execution plan. So, yes, the performance of the two above is likely to
be identical. Of course, there's lots of other good reasons to use
parameterized queries rather than building sql strings.

Check out "auto-parameterization" and "Parameters and Execution Plan
Reuse" in books online for the details.
 
Hi David,
Sigh. Unfortunately your original post was scrambled because of your
poor English skills. Now, poor English skills are no big deal, it's an
international forum after all, but getting haughty and rude about it
seems a bit misplaced, IMHO. Miha was simply asking for a clarification
of a point that your poor grammar and spelling made ambiguous and
difficult to understand. Personally I don't see the need for the
rudeness in response.
Click to expand...
Maybe you're just having a bad day.
Click to expand...

No, it is Thomas.
Apparently what Thomas is trying to point out is that SQL Server 2000
will auto-parameterize statements like the above and re-use the
execution plan. So, yes, the performance of the two above is likely to
be identical. Of course, there's lots of other good reasons to use
parameterized queries rather than building sql strings.

Check out "auto-parameterization" and "Parameters and Execution Plan
Reuse" in books online for the details.
Click to expand...

Thanks for info.

It also states:
"When processing complex SQL statements, the relational engine may have
difficulty determining which expressions can be auto-parameterized. To
increase the ability of the relational engine to match complex SQL
statements to existing, unused execution plans, explicitly specify the
parameters using either sp_executesql or parameter markers. "

which is logical enough. However, even in the simple cases it should be
slightly slower because it has to determine that stataments are the same
(except for parameters), plus it has to retrieve the value of parameter.
 
Thanks for info.

It also states:
"When processing complex SQL statements, the relational engine may have
difficulty determining which expressions can be auto-parameterized. To
increase the ability of the relational engine to match complex SQL
statements to existing, unused execution plans, explicitly specify the
parameters using either sp_executesql or parameter markers. "

which is logical enough. However, even in the simple cases it should be
slightly slower because it has to determine that stataments are the same
(except for parameters), plus it has to retrieve the value of parameter.
Click to expand...

With a big emphasis on *slightly*. Since the construction "column_name
= literal" is such an obvious candidate for parameterization and so easy
to recognize during the parse, it's an open question as to whether it
would take longer to parameterize the statement or to look up the
parameter value in the function parameters. Both actions are trivial,
and I doubt the difference is measurable either way.

Likewise for the difference between creating a SqlParameter and adding
it to the Parameters collection vs. parsing a value and concatening a
string. I suspect the second might be ever so slightly faster, but in
practice it just doesn't matter.
 
With a big emphasis on *slightly*. Since the construction "column_name
= literal" is such an obvious candidate for parameterization and so easy
to recognize during the parse, it's an open question as to whether it
would take longer to parameterize the statement or to look up the
parameter value in the function parameters. Both actions are trivial,
and I doubt the difference is measurable either way.
Click to expand...

Yeah, probably on this sample select.
However, you should see the selects my co-worker is creating. :)
 
Back
Top