469,927 Members | 1,627 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

ADO.NEt saving null values

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?
Nov 15 '05 #1
12 1779
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?

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

"Ivan Sammut" <sa*****@nextgen.net.mt> wrote in message
news:ei**************@TK2MSFTNGP12.phx.gbl...
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?

Nov 15 '05 #2
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
"Miha Markic" <miha at rthand com> wrote in message
news:u4**************@TK2MSFTNGP12.phx.gbl...
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?

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

"Ivan Sammut" <sa*****@nextgen.net.mt> wrote in message
news:ei**************@TK2MSFTNGP12.phx.gbl...
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?


Nov 15 '05 #3
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" <sa*****@nextgen.net.mt> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...
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
"Miha Markic" <miha at rthand com> wrote in message
news:u4**************@TK2MSFTNGP12.phx.gbl...
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?

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

"Ivan Sammut" <sa*****@nextgen.net.mt> wrote in message
news:ei**************@TK2MSFTNGP12.phx.gbl...
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?



Nov 15 '05 #4
Hm, the same wrong information voer and over again...

"Miha Markic" <miha at rthand com> wrote in message
news:e8**************@TK2MSFTNGP11.phx.gbl...
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.
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)

Nov 15 '05 #5
Thanks a lot
"Miha Markic" <miha at rthand com> wrote in message
news:e8**************@TK2MSFTNGP11.phx.gbl...
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" <sa*****@nextgen.net.mt> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...
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
"Miha Markic" <miha at rthand com> wrote in message
news:u4**************@TK2MSFTNGP12.phx.gbl...
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?

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

"Ivan Sammut" <sa*****@nextgen.net.mt> wrote in message
news:ei**************@TK2MSFTNGP12.phx.gbl...
> 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?
>
>



Nov 15 '05 #6
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 [MVP]" <t.********@thona-consulting.com> wrote in message
news:Om**************@TK2MSFTNGP09.phx.gbl...
Hm, the same wrong information voer and over again...

"Miha Markic" <miha at rthand com> wrote in message
news:e8**************@TK2MSFTNGP11.phx.gbl...
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.


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)


Nov 15 '05 #7
No.

Read what I said.

Read the documentation.

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

"Miha Markic" <miha at rthand com> wrote in message
news:em**************@TK2MSFTNGP12.phx.gbl...
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 [MVP]" <t.********@thona-consulting.com> wrote in message
news:Om**************@TK2MSFTNGP09.phx.gbl...
Hm, the same wrong information voer and over again...

"Miha Markic" <miha at rthand com> wrote in message
news:e8**************@TK2MSFTNGP11.phx.gbl...
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.


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)



Nov 15 '05 #8
On 2004-01-03, Thomas Tomiczek [MVP] <t.********@thona-consulting.com> wrote:
No.

Read what I said.
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.
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


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.
--
David
dfoster at
hotpop dot com
Nov 15 '05 #9
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. Maybe you're just having a bad day.


No, it is Thomas.
Read the documentation.
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


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.


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.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com
Nov 15 '05 #10

"Thomas Tomiczek [MVP]" <t.********@thona-consulting.com> wrote in message
news:eo****************@TK2MSFTNGP11.phx.gbl...
No.
So, you are saying that there is a speed difference?
Read what I said.
I am struggling :)
Read the documentation.


I did.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com
Nov 15 '05 #11
On 2004-01-03, Miha Markic <> wrote:

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.


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.

--
David
dfoster at
hotpop dot com
Nov 15 '05 #12
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.


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

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
Nov 15 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Marcus | last post: by
26 posts views Thread by Agoston Bejo | last post: by
8 posts views Thread by manning_news | last post: by
10 posts views Thread by Toby Gallier | last post: by
2 posts views Thread by Matt MacDonald | last post: by
10 posts views Thread by =?Utf-8?B?R3JlZw==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.