questions about using asp variables to insert value into numeric column(bigint,int,money)

I have a sql server database and want to access data using asp form.
But i always get trouble with inserting/updating data in numeric type
columns.Troubles described as belows.
I used one textbox in asp form to accept int type input (like age). And
i defined a variable in asp code to fetch submitted value of a textbox.
Dim variable1
variable1= request.form("textbox1")
By using Connection_Name.execute "Insert into tablename(column1) values
("&variable1&")"
i wanted to insert values into database.
I got error message like "there are syntax error near..." if the
textbox was blank when the form was submitted
I try to print the sql command by using response.write"Insert into
tablename(column1) values ("&variable1&")"
i get Insert into tablename(column1) values
nothing was there, or just blank between the comma
I think the problem is that i can not insert a Null into a int type
column. But i don't know how to resolve it. Anyone can help? Thanks a
lot

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

"jane" wrote in message
news:OoaR4kSCHHA.3540@TK2MSFTNGP03.phx.gbl...
>I have a sql server database and want to access data using asp form.
> But i always get trouble with inserting/updating data in numeric type
> columns.Troubles described as belows.
> I used one textbox in asp form to accept int type input (like age). And
> i defined a variable in asp code to fetch submitted value of a textbox.
> Dim variable1
> variable1= request.form("textbox1")
> By using Connection_Name.execute "Insert into tablename(column1) values
> ("&variable1&")"
> i wanted to insert values into database.
> I got error message like "there are syntax error near..." if the
> textbox was blank when the form was submitted
> I try to print the sql command by using response.write"Insert into
> tablename(column1) values ("&variable1&")"
> i get Insert into tablename(column1) values
> nothing was there, or just blank between the comma
> I think the problem is that i can not insert a Null into a int type
> column. But i don't know how to resolve it. Anyone can help? Thanks a
> lot

The absence of a value where one is expected is not equivilent to passing a
null value, it is (as your system has correctly pointed out) bad syntax.
What you need to do is validate the input, and conditionally execute SQL,
e.g.,

variable1 = Trim(variable1)
If Len(variable1) > 0 Then
If IsNumeric(variable1) Then
Connection_Name.execute "Insert into tablename(column1) values
("&variable1&")"
End If
End If

Note however that the example above is by no means adequate for the IT
security climate of the 21st century, google for SQL Injection, or search
this NG for helpful links.


-Mark



>
>

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

The thing is i want to insert null to that numeric field if nothing got
from the form rather than refusing the whole insert command to be
executed.Like when i use '"&variable2&"' to insert into char type
column the value from textbox2, i'll get '' actually when the textbox
is blank on submission. Thanks alot


Mark McGinty wrote:
> "jane" wrote in message
> news:OoaR4kSCHHA.3540@TK2MSFTNGP03.phx.gbl...
> >I have a sql server database and want to access data using asp form.
> > But i always get trouble with inserting/updating data in numeric type
> > columns.Troubles described as belows.
> > I used one textbox in asp form to accept int type input (like age). And
> > i defined a variable in asp code to fetch submitted value of a textbox.
> > Dim variable1
> > variable1= request.form("textbox1")
> > By using Connection_Name.execute "Insert into tablename(column1) values
> > ("&variable1&")"
> > i wanted to insert values into database.
> > I got error message like "there are syntax error near..." if the
> > textbox was blank when the form was submitted
> > I try to print the sql command by using response.write"Insert into
> > tablename(column1) values ("&variable1&")"
> > i get Insert into tablename(column1) values
> > nothing was there, or just blank between the comma
> > I think the problem is that i can not insert a Null into a int type
> > column. But i don't know how to resolve it. Anyone can help? Thanks a
> > lot
>
> The absence of a value where one is expected is not equivilent to passing a
> null value, it is (as your system has correctly pointed out) bad syntax.
> What you need to do is validate the input, and conditionally execute SQL,
> e.g.,
>
> variable1 = Trim(variable1)
> If Len(variable1) > 0 Then
> If IsNumeric(variable1) Then
> Connection_Name.execute "Insert into tablename(column1) values
> ("&variable1&")"
> End If
> End If
>
> Note however that the example above is by no means adequate for the IT
> security climate of the 21st century, google for SQL Injection, or search
> this NG for helpful links.
>
>
> -Mark
>
>
>
> >
> >

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

The thing is i want to insert null to that numeric field if nothing got
from the form rather than refusing the whole insert command to be
executed.Like when i use '"&variable2&"' to insert into char type
column the value from textbox2, i'll get '' actually when the textbox
is blank on submission. Thanks alot


Mark McGinty wrote:
> "jane" wrote in message
> news:OoaR4kSCHHA.3540@TK2MSFTNGP03.phx.gbl...
> >I have a sql server database and want to access data using asp form.
> > But i always get trouble with inserting/updating data in numeric type
> > columns.Troubles described as belows.
> > I used one textbox in asp form to accept int type input (like age). And
> > i defined a variable in asp code to fetch submitted value of a textbox.
> > Dim variable1
> > variable1= request.form("textbox1")
> > By using Connection_Name.execute "Insert into tablename(column1) values
> > ("&variable1&")"
> > i wanted to insert values into database.
> > I got error message like "there are syntax error near..." if the
> > textbox was blank when the form was submitted
> > I try to print the sql command by using response.write"Insert into
> > tablename(column1) values ("&variable1&")"
> > i get Insert into tablename(column1) values
> > nothing was there, or just blank between the comma
> > I think the problem is that i can not insert a Null into a int type
> > column. But i don't know how to resolve it. Anyone can help? Thanks a
> > lot
>
> The absence of a value where one is expected is not equivilent to passing a
> null value, it is (as your system has correctly pointed out) bad syntax.
> What you need to do is validate the input, and conditionally execute SQL,
> e.g.,
>
> variable1 = Trim(variable1)
> If Len(variable1) > 0 Then
> If IsNumeric(variable1) Then
> Connection_Name.execute "Insert into tablename(column1) values
> ("&variable1&")"
> End If
> End If
>
> Note however that the example above is by no means adequate for the IT
> security climate of the 21st century, google for SQL Injection, or search
> this NG for helpful links.
>
>
> -Mark
>
>
>
> >
> >

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

I noticed that i can use
Insert into tablename(numeric_column_name) values(null)
to insert null into table under query analyzer.
how can i do that through asp?

jane wrote:
> I have a sql server database and want to access data using asp form.
> But i always get trouble with inserting/updating data in numeric type
> columns.Troubles described as belows.
> I used one textbox in asp form to accept int type input (like age). And
> i defined a variable in asp code to fetch submitted value of a textbox.
> Dim variable1
> variable1= request.form("textbox1")
> By using Connection_Name.execute "Insert into tablename(column1) values
> ("&variable1&")"
> i wanted to insert values into database.
> I got error message like "there are syntax error near..." if the
> textbox was blank when the form was submitted
> I try to print the sql command by using response.write"Insert into
> tablename(column1) values ("&variable1&")"
> i get Insert into tablename(column1) values
> nothing was there, or just blank between the comma
> I think the problem is that i can not insert a Null into a int type
> column. But i don't know how to resolve it. Anyone can help? Thanks a
> lot

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

if then
Insert into tablename(numeric_column_name) values(null)
else
insert the variable

bidepan@gmail.com wrote:
> I noticed that i can use
> Insert into tablename(numeric_column_name) values(null)
> to insert null into table under query analyzer.
> how can i do that through asp?
>
> jane wrote:
>> I have a sql server database and want to access data using asp form.
>> But i always get trouble with inserting/updating data in numeric type
>> columns.Troubles described as belows.
>> I used one textbox in asp form to accept int type input (like age).
>> And i defined a variable in asp code to fetch submitted value of a
>> textbox. Dim variable1
>> variable1= request.form("textbox1")
>> By using Connection_Name.execute "Insert into tablename(column1)
>> values ("&variable1&")"
>> i wanted to insert values into database.
>> I got error message like "there are syntax error near..." if the
>> textbox was blank when the form was submitted
>> I try to print the sql command by using response.write"Insert into
>> tablename(column1) values ("&variable1&")"
>> i get Insert into tablename(column1) values
>> nothing was there, or just blank between the comma
>> I think the problem is that i can not insert a Null into a int type
>> column. But i don't know how to resolve it. Anyone can help? Thanks
>> a lot

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

I got a way
seperate the update String into condition statement instead of placing the
If statement before the whole update command
updateStr="Insert into tablename(...) values(...,"
If variable1="" then
updateStr=updateStr&"Null)"
else
updateStr=updateStr&variable1&")"
end if
it works just as i wish, thanks guys
"jane" wrote in message
news:OoaR4kSCHHA.3540@TK2MSFTNGP03.phx.gbl...
>I have a sql server database and want to access data using asp form.
> But i always get trouble with inserting/updating data in numeric type
> columns.Troubles described as belows.
> I used one textbox in asp form to accept int type input (like age). And
> i defined a variable in asp code to fetch submitted value of a textbox.
> Dim variable1
> variable1= request.form("textbox1")
> By using Connection_Name.execute "Insert into tablename(column1) values
> ("&variable1&")"
> i wanted to insert values into database.
> I got error message like "there are syntax error near..." if the
> textbox was blank when the form was submitted
> I try to print the sql command by using response.write"Insert into
> tablename(column1) values ("&variable1&")"
> i get Insert into tablename(column1) values
> nothing was there, or just blank between the comma
> I think the problem is that i can not insert a Null into a int type
> column. But i don't know how to resolve it. Anyone can help? Thanks a
> lot
>
>

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

"jane" wrote in message
news:e94luqdCHHA.204@TK2MSFTNGP04.phx.gbl...
> I got a way
> seperate the update String into condition statement instead of placing the
> If statement before the whole update command
> updateStr="Insert into tablename(...) values(...,"
> If variable1="" then
> updateStr=updateStr&"Null)"
> else
> updateStr=updateStr&variable1&")"
> end if
> it works just as i wish, thanks guys

And if the user types the following into the text box instead:-

Null ); DROP TABLE TableName;


Does that work too?

Google for 'SQL Injection'

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

I don't think that would happen,because the user account has no such right
as delete or drop, but thanks a lot. I'll consider that kind of possibility.
"Anthony Jones" wrote in message
news:ej$C2zlCHHA.4428@TK2MSFTNGP04.phx.gbl...
>
> "jane" wrote in message
> news:e94luqdCHHA.204@TK2MSFTNGP04.phx.gbl...
>> I got a way
>> seperate the update String into condition statement instead of placing
>> the
>> If statement before the whole update command
>> updateStr="Insert into tablename(...) values(...,"
>> If variable1="" then
>> updateStr=updateStr&"Null)"
>> else
>> updateStr=updateStr&variable1&")"
>> end if
>> it works just as i wish, thanks guys
>
> And if the user types the following into the text box instead:-
>
> Null ); DROP TABLE TableName;
>
>
> Does that work too?
>
> Google for 'SQL Injection'
>
>
>

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

jane wrote:
> I don't think that would happen,because the user account has no such
> right as delete or drop, but thanks a lot. I'll consider that kind of
> possibility.

Well, here's a starting point:
Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures.
http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/5d3c9d4409dc1701?hl=en&
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: questions about using asp variables to insert value into numeric column(bigint,int,money)

Thanks Bob, I prefer using SP too.

"Bob Barrows [MVP]" wrote in message
news:%23lEr6hyCHHA.4224@TK2MSFTNGP06.phx.gbl...
> jane wrote:
>> I don't think that would happen,because the user account has no such
>> right as delete or drop, but thanks a lot. I'll consider that kind of
>> possibility.
>
> Well, here's a starting point:
> Further points to consider:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Personally, I prefer using stored procedures.
> http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/5d3c9d4409dc1701?hl=en&
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: questions about using asp variables to insert valu...

My problem is similar to this,
but im not still clear as to how can i insert a null value into an integer column.
i fetch fax no from the form, and store into DB,
but incase user doesent have it then i want to insert null into the DB.
Please help me.
Thanks in advance

Re: questions about using asp variables to insert valu...

My problem is similar to this,
but im not still clear as to how can i insert a null value into an integer column.
i fetch fax no from the form, and store into DB,
but incase user doesent have it then i want to insert null into the DB.
Please help me.
Thanks in advance

Re: questions about using asp variables to insert valu...

My problem is similar to this,
but im not still clear as to how can i insert a null value into an integer column.
i fetch fax no from the form, and store into DB,
but incase user doesent have it then i want to insert null into the DB.
Please help me.
Thanks in advance