inserting data into a table

299 views
Skip to first unread message

Geoff Wickens

unread,
Aug 25, 2003, 5:52:59 AM8/25/03
to
I am quite new to all this but am trying to create a database driven site. I
have been able to use information from my sample database but I now want to
be able to insert data into it. At present I put the data into my local copy
of the access database and then upload it again to my server - not an ideal
solution!

I have tried using the INSERT command and it works fine if I enter the data
into the statement. What I really want to do is collect the data from a form
and then insert this into the database. My code is as follows:

Page for collecting the data:

<form name="useradd" method="post" action="testadd.asp">
<p>Username:
<input type="text" name="username">
</p>
<p>Password:
<input type="text" name="password">
</p>
<p> Category:
<select name="select">
<option>member</option>
<option>committee</option>
<option>admin</option>
<option>guest</option>
</select>
</p>
<p>
<input type="submit" name="Submit" value="Submit">
<input type="reset" name="Reset" value="Reset">
</p>
</form>


I then have the following code on the page (testadd.asp) to add this
information to my database.

<%
u = Request.Form("username")
p = Request.Form("password")
c = Request.Form("category")

Set Catalog=Server.CreateObject("ADODB.Recordset")
Catalog.open "INSERT INTO tbltest (username, password, category) VALUES (u ,
p, c)", "DSN=members"
%>

The thing which goes wrong is the VALUES part of the statement. It seems to
want 'u' etc but then puts in this text rather than the contents of my
variable.

Any help most welcome.

Geoff Wickens

Bob Barrows

unread,
Aug 25, 2003, 7:32:25 AM8/25/03
to
Geoff Wickens wrote:
>
> Set Catalog=Server.CreateObject("ADODB.Recordset")

First of all, do not use a recordset for running queries that do not return
data! This is extremely wasteful!

Insterad, create a connection object, like this:

Dim cn
Set cn=Server.CreateObject("ADODB.Connection")

I notice you are using a DSN to connect to the database. This is also not
recommended. You should use the native Jet OLEDB provider instead. See
www.connectionstrings.com for examples of connection strings for various
databases. With Jet, the string takes this form:

cn.open "provider=microsoft.jet.4.0.oledb;" & _
"p:\ath\to\my\database.mdb"

Now, use the connection's Execute method to run the query. In addition, tell
ADO that you are running a SQL statement being passed as text (adCmdText)
and that you will not be getting records back from the query
(adExecuteNoRecords). "adCmdText" is an ADO constant. If you use the
metadata tag per this article, http://www.aspfaq.com/show.asp?id=2112, or
#include the adovbs.inc file, you will be able to use the ADO constants like
this:

cn.Execute "<statement>",,adCmdText + adExecuteNoRecords

If you decide not to bring the ADO definitions into your page, then you will
need to look up the values of the constants in online Help and use the
values instead. adCmdText =1 and adExecuteNoRecords = 128, so your statement
will be:

cn.Execute "<statement>",,129


> Catalog.open "INSERT INTO tbltest (username, password, category)
> VALUES (u , p, c)", "DSN=members"
> %>

You are attempting to use dynamic sql here. This is not recommended. Even
if it was, this is not the recommended way to do it. When you create a
dynamic sql statement, the goal is to create a statement that will run as-is
in the database. Remember: the database engine has no knowledge of what the
client program contains. It will not know anything about any variables that
have been created in the client. Do yourself a favor. Open the database in
Access and create a query using the "Design view" option. Close the "Show
Tables" dialog without choosing a table. Switch to SQL View. Copy and paste
the above statement into the SQL window and attempt to run it. You get an
error, right? The reason is, the database has no way of knowing what u, p
and c are. All it knows is that it needs to see three strings separated by
commas in the VALUES clause, because the three columns listed are text
columns. If they were Number columns, the database engine would need to see
three numbers separated by commas. And it they were Date/time columns, it
would expect three date values.

Literal string values are distinguished by using string delimiters. In Jet
(Access), you can use either quotes (") or apostrophes (') to delimit
strings.

Numbers are recognized by 1) being numbers and 2) having no delimiters at
all

Date literals in Jet must be delimited by hash marks (#)

To put this together, here is an imaginary Insert statement that meets these
requirements:

INSERT into MyTable(TextField, NumberField, DateField)
VALUES ('some text', 23,#2003-08-25#)

We are not done here. What if you want to pass John O'Malley into the text
field. This would not work:
'John O'Malley'. When the parser encounters the apostrophe after the O, it
thinks the string is fineshed and looks for a comma. When it sees the M
instead of a comma, it raises an error. One way to get around this is to use
quotes to delimit this string: "John O'Malley". But this will be a problem
if you need to pass a string containing a literal quote character: "9"
Wrench".

What most people do is stick to the single quote delimiters, and escape
literal apostrophes by doubling them up: 'John O''Malley' - two apostrophes
instead of one. In vbscript, you can use the Replace statement for this:
dim u
u=request...
u = replace(u,"'","''")

So now let's put this together in your vbscript code: since you can't send
the variable names to the Jet engine, you must send the variable values
instead. You do this via concatenation. In addition, you need to be able to
debug this, so always assign your concatenation to a variable, like this:

dim sSQL
sSQL = "<statement>"
Response.Write sSQL

That Response.Write is very important: it allows you to see the result of
your concatenation to make sure it is correct. You can also copy and paste
the result from the browser window into the SQL View of an Access Query
window to make sure it runs.

Your query should be done like this (don't forget to concatenate the
delimiters into the statement):

sSQL = "INSERT INTO tbltest (username, password, category) " & _
"VALUES (" & _
"'" & u & "','" & p & "','" & c & "')"
Response.Write sSQL
cn.Execute sSQL,,129


As I said earlier, dynamic sql is not recommended due to performance issues
and due to the difficulty in accomplishing it. Instead, you should use saved
parameter queries. Go back to Access and create a new query, again switching
to the SQL View. Enter this query:

INSERT INTO tbltest (username, password, category)

VALUES ([p1] , [p2], [p3])

The brackets tell the parser to accept the enclosed data as-is. When Jet
sees [p1], it looks in tbltest to see if there's a field called p1. When it
does not find one, it treats [p1] as a parameter.

Run the query. Notice that you will be prompted to enter values for p1, etc.
This is because Access is getting involved to help you run the query, by
forcing you to enter the values for the parameters. When you've satisfied
yourself that the query works, save the query as "qInsUser"

In vbscript, you provide the parameter values in your code. It's very
simple - you do not have to worry at all about delimiters, literal or
otherwise. simply do this:

cn.qInsUser u,p,c

You're done!

The beauty of this is:
1) Since you've created and tested the query in the native Jet environment,
you know you have a query that works. If you subsequently have a problem
running the query from your client app, you'll know the problem is not with
the query.
2) No need to worry about delimiters.

HTH,
Bob Barrows

Reply all
Reply to author
Forward
0 new messages