473,574 Members | 2,294 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Building an INSERT statement in ASP 30

I have an old web app that ues an Access database and ASP 3.0.

I need to build an INSERT statement based on the contents of a form.

What is the best way to handle blank text boxes that are submitted with the
form?

For example, I collect all my name/value pairs that are submitted with the
form like this...

sExample=Reques t.Form("txaExam ple")
sNote=Request.F orm("txtNote")
iSourceID=Reque st.Form("cboSou rceID")
iPageNo=Request .Form("txtPageN o")
sSourceRef=Requ est.Form("txtSo urceRef")
....and then I build my INSERT statement like this...

sSQL = "INSERT INTO example (example, sourceid, sourceref, pageno, note)"
sSQL = sSQL & " VALUES ('" & sExample & "', "
sSQL = sSQL & cstr(iSourceID) & ", "
sSQL = sSQL & "'" & sSourceRef & "', "
sSQL = sSQL & cstr(iPageNo) & ", "
sSQL = sSQL & "'" & sNote & "' "
sSQL = sSQL & ")"

....but if some of the controls are left blank, I get an INSERT atatement
llike this...

INSERT INTO example (example, sourceid, sourceref, pageno, note) VALUES
('asgfgdsfhg', 6, '', , '' )

What is the value when an empty control is submitted?

isempty and isnull both return false even though nothing was submitted with
the form. I can test for a zero length (IF len(Note)=0) but is this the
best way to test?

IOW, test each value for zero length and if true, set the value equal to
NULL to get something like this...

INSERT INTO example (example, sourceid, sourceref, pageno, note) VALUES
('asgfgdsfhg', 6, NULL,NULL ,NULL )

Thanks for any insights.


Feb 19 '07 #1
3 3885

"Dave" <da*******@news group.nospamwro te in message
news:u0******** ******@TK2MSFTN GP05.phx.gbl...
>I have an old web app that ues an Access database and ASP 3.0.

I need to build an INSERT statement based on the contents of a form.

What is the best way to handle blank text boxes that are submitted with
the form?

For example, I collect all my name/value pairs that are submitted with the
form like this...

sExample=Reques t.Form("txaExam ple")
sNote=Request.F orm("txtNote")
iSourceID=Reque st.Form("cboSou rceID")
iPageNo=Request .Form("txtPageN o")
sSourceRef=Requ est.Form("txtSo urceRef")
...and then I build my INSERT statement like this...

sSQL = "INSERT INTO example (example, sourceid, sourceref, pageno, note)"
sSQL = sSQL & " VALUES ('" & sExample & "', "
sSQL = sSQL & cstr(iSourceID) & ", "
sSQL = sSQL & "'" & sSourceRef & "', "
sSQL = sSQL & cstr(iPageNo) & ", "
sSQL = sSQL & "'" & sNote & "' "
sSQL = sSQL & ")"

...but if some of the controls are left blank, I get an INSERT atatement
llike this...

INSERT INTO example (example, sourceid, sourceref, pageno, note) VALUES
('asgfgdsfhg', 6, '', , '' )

What is the value when an empty control is submitted?

isempty and isnull both return false even though nothing was submitted
with the form. I can test for a zero length (IF len(Note)=0) but is this
the best way to test?

IOW, test each value for zero length and if true, set the value equal to
NULL to get something like this...

INSERT INTO example (example, sourceid, sourceref, pageno, note) VALUES
('asgfgdsfhg', 6, NULL,NULL ,NULL )

Thanks for any insights.
The values that are being passed can be checked using the TypeName()
function. There's no reason why you can't execute "NSERT INTO example
(example, sourceid, sourceref, pageno, note) VALUES ('asgfgdsfhg', 6, '', ,
'' )". The Access database will put default values into the empty fields.

An easier way to do this kind of thing is to create a saved parameter query
in access and use that. It saves having to delimit values, escaping quotes
etc.

--
Mike Brind
Feb 19 '07 #2
Dave wrote:
I have an old web app that ues an Access database and ASP 3.0.

I need to build an INSERT statement based on the contents of a form.

What is the best way to handle blank text boxes that are submitted
with the form?

For example, I collect all my name/value pairs that are submitted
with the form like this...

sExample=Reques t.Form("txaExam ple")
sNote=Request.F orm("txtNote")
iSourceID=Reque st.Form("cboSou rceID")
iPageNo=Request .Form("txtPageN o")
sSourceRef=Requ est.Form("txtSo urceRef")
...and then I build my INSERT statement like this...

sSQL = "INSERT INTO example (example, sourceid, sourceref, pageno,
note)" sSQL = sSQL & " VALUES ('" & sExample & "', "
sSQL = sSQL & cstr(iSourceID) & ", "
sSQL = sSQL & "'" & sSourceRef & "', "
sSQL = sSQL & cstr(iPageNo) & ", "
sSQL = sSQL & "'" & sNote & "' "
sSQL = sSQL & ")"
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/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
--
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"
Feb 19 '07 #3
Thanks guys. The parameter queries work well.
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:ed******** ******@TK2MSFTN GP03.phx.gbl...
Dave wrote:
>I have an old web app that ues an Access database and ASP 3.0.

I need to build an INSERT statement based on the contents of a form.

What is the best way to handle blank text boxes that are submitted
with the form?

For example, I collect all my name/value pairs that are submitted
with the form like this...

sExample=Reque st.Form("txaExa mple")
sNote=Request. Form("txtNote")
iSourceID=Requ est.Form("cboSo urceID")
iPageNo=Reques t.Form("txtPage No")
sSourceRef=Req uest.Form("txtS ourceRef")
...and then I build my INSERT statement like this...

sSQL = "INSERT INTO example (example, sourceid, sourceref, pageno,
note)" sSQL = sSQL & " VALUES ('" & sExample & "', "
sSQL = sSQL & cstr(iSourceID) & ", "
sSQL = sSQL & "'" & sSourceRef & "', "
sSQL = sSQL & cstr(iPageNo) & ", "
sSQL = sSQL & "'" & sNote & "' "
sSQL = sSQL & ")"
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/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
--
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"

Feb 19 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
7592
by: Bennett Haselton | last post by:
I'm looking for a PHP tutorial that specializes in how to build sites that are based around user logins. i.e. the user logs in on the front page, and are taken to a main login page where fields on the page are populated with values from some server-side database. Ideally, there would be a server-side "user" database table, with fields such...
8
5511
by: Sans Spam | last post by:
Greetings! I have a table that contains all of the function permissions within a given application. These functions are different sections of a site and each has its own permissions (READ, WRITE, UPDATE, DELETE) which are controlled by a web frontend and the table records are manipulated to control the permissions. Example: The Press...
1
3418
by: Stephen Patten | last post by:
Hi All, While in the process of building my table (40 or so Insert statments) can I then query ("select * from @Table_variable") and use the results up to theat point for another insert into @Table_varible? If you look for stepID -15 I have commented that section out due to it not retuning the correct values. Thank you in advance
3
1415
by: Jack | last post by:
Hi, I have a sql statement in asp page as below sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense, " sql = sql & "TravelExpense, PersonnelExpense)" sql = sql & " VALUES('" & l_ENO & "', '" & l_Date & "', '" & l_contractedserviceexpense & "', '" & l_travelexpense & "', '" & personnelexpense & "')" The...
7
2084
by: MLH | last post by:
Building Applications with Microsoft Access 97 is a publication I think I need. Is it available in book form? Is MicroSoft the sole vendor? Anybody got a copy they wanna sell???
14
13007
by: Chris Ochs | last post by:
The documentation doesn't have any examples of using an sql language function to do an insert, andI am at loss as to I am doing wrong here. The error I get trying to create the function is: ERROR: syntax error at or near "$1" at character 148 CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns integer AS ' insert into...
2
3183
by: Geoffrey KRETZ | last post by:
Hello, I'm wondering if the following behaviour is the correct one for PostGreSQL (7.4 on UNIX). I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching the following request : INSERT INTO temp_tab VALUES (1,2,3)
2
1198
by: Mossy | last post by:
I am currently moving a table from a msSQL DB to a MySQL DB. I am trying to build the bones of an insert statement by adding strings to a select statement. SELECT '(' + job_referenceno + ', ''' + job_title + ''', ''' + job_category + ''', ''' + job_type + ''', ''' + job_location + ''', ''' + job_duaration + ''', ' + job_salary + ', ''' +...
17
4425
by: john | last post by:
All: I'm a long-time developer, new to PHP.... Is there an idiom used in PHP to construct SQL statments from $_POST data? I would guess that in many applications, the data read from $_POST are used to build SQL statements. Certainly, we can do the following:
0
7826
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8085
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8259
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7838
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8126
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6491
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5328
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3781
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2257
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.