adding to access database - what's wrong?

leeolive · Jun 5, 2003

Hi

I am new to asp. I have managed to retrieve from an access database, but can't seem to get the simplest adding to database page working. The connection is fine, but it comes up with an 'error adding to db' error message

Can anyone spot the problem?? Here's the code

Thanks! Lee

<%@ Language=VBScript %>
<%Response.Buffer=True%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">

</HEAD>
<body>



<%
Dim strname, strcity
Dim cndb, cmdaddtodatabase
Response.Expires=0
%>

<%
strname=Request.Form("name&quot

Response.Write strname

Set cndb = server.CreateObject("adodb.connection&quot

Set cmdaddtodatabase = server.CreateObject("adodb.command&quot

cndb.CommandTimeout = 15
cndb.CursorLocation = adUseServer
cndb.ConnectionString = "DSN=cjrcls"
on error resume next
cndb.open

if err <> 0 then
response.write "Error connecting to the database"
response.end
end if

With cmdaddtodatabase
.CommandTimeout = 15
set .ActiveConnection = cndb
.CommandType = adCmdText
.CommandText = "INSERT INTO testtable (name) VALUES ([pname]);"
.Parameters.Append(.CreateParameter ("pname",adVarChar,adParamInput,50,strname))
on error resume next
.Execute

if err.number <> 0 then
Response.Write "error adding to db"
Response.End
end if
End with

%>

</body>
</HTML>

NoCoolHandle · Jun 5, 2003

How about replacing response.write "error etc...."
with response.write err.description and telling us what the error is?

Rob

leeolive · Jun 5, 2003

Thanks for getting back, Rob.

This is the error description

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Doesn't seem to be passing the parameters.

Genimuse · Jun 5, 2003

I'm not spotting the parameter problem, but whatever it is you should be able to eliminate it by changing:

Code:

.CommandText = &quot;INSERT INTO testtable (name) VALUES ([pname]);&quot;
.Parameters.Append(.CreateParameter (&quot;pname&quot;,adVarChar,adParamInput,50,strname))

to

Code:

.CommandText = &quot;INSERT INTO testtable (name) VALUES ('&quot; & strname & &quot;');&quot;

Alternately, I don't recall if using .Parameters correctly adds single-quotes to strings, so if it doesn't you could instead change your .CommandText line to:

Code:

.CommandText = &quot;INSERT INTO testtable (name) VALUES ('[pname]');&quot;

(and keep your .Parameters line).

leeolive · Jun 6, 2003

Thanks for getting back. I tried your suggestions and this seems to add to the db (we are getting somewhere!) but it is adding 'strname' and not the actual value. Must be something to do with the syntax. I've played around but still can't get it to work.

.CommandText = "INSERT INTO testtable (name) VALUES ('[strname]');"

Thanks, I appreciate this.
Lee

vbkris · Jun 6, 2003

what Genimuse tried to say was:
strname must be a varialbe that stores the value.
strname=request("the_field&quot

so try this query:
INSERT INTO testtable (name) VALUES ('["&strname&"]')

Known is handfull, Unknown is worldfull

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

adding to access database - what's wrong?

leeolive

Programmer

NoCoolHandle

Programmer

leeolive

Programmer

Genimuse

Programmer

leeolive

Programmer

vbkris

Programmer

Similar threads

Part and Inventory Search

Sponsor