Inserting data into a MDB with ASP - why can't I do it in this case?

[WuCOng]

I'm taking values from a previous page via request.form and using them to updata a DB.

One of the values is the name of a service but Idon't want to insert that into the table, but rather the primary key (which is a number). In order to do this I need to SELECT the primary key WHERE the service name is equal to the name in the service table.

Problem is once I've done this am not allowed to use this number in my INSERT statement. I get a "type mismatch error". Why?

Here's the code:

<%
username=request.form(&quot;user&quot
servicename=request.form(&quot;service&quot
comment_txt=request.form(&quot;commenttxt&quot
cur_date=Date()

dim SQLstr
SQLstr=&quot;SELECT service_no AS testval FROM services WHERE name ='&quot;&servicename&&quot;'&quot;

dim SQLstr1
SQLstr1=&quot;INSERT INTO complaints (user_name,service_no,complaint,date_sub) VALUES ('&quot;&username&&quot;','&quot;&testval&&quot;','&quot;&comment_txt&&quot;','&quot;&cur_date&&quot;')&quot;

dim oRS
set oRS = server.createObject(&quot;ADODB.Connection&quot
oRS.open &quot;DSN=power&quot;

oRS.execute SQLstr
oRS.execute SQLstr1

set oRS = nothing
%>

 

[link9]

You first need to create a recordset to hold your values -- which will be returned from your first sql statement...

Then, you will build up your INSERT statement -- try this:

<%
dim oRS
set oRS = server.createObject(&quot;ADODB.Connection&quot
oRS.open &quot;DSN=power&quot;

username=request.form(&quot;user&quot
servicename=request.form(&quot;service&quot
comment_txt=request.form(&quot;commenttxt&quot
cur_date=Date()

dim SQLstr, thisRS
set thisRS = server.createObject(&quot;ADODB.Recordset&quot
SQLstr=&quot;SELECT service_no AS testval FROM services WHERE name ='&quot;&servicename&&quot;'&quot;

thisRS.open SQLstr, oRS

dim SQLstr1
SQLstr1=&quot;INSERT INTO complaints (user_name,service_no,complaint,date_sub) VALUES ('&quot; & username & &quot;','&quot; & thisRS(&quot;testval&quot & &quot;','&quot; & comment_txt & &quot;','&quot; & cur_date & &quot;')&quot;

oRS.execute SQLstr1

set thisRS = nothing
set oRS = nothing
%>

Problem is that you execute that SELECT statement, but you were building your INSERT statment not only before you executed your statement, but with some non-existent value (testval), which only exists in the context of a recordset that would be created from executing your SELECT statment...

So now you make your recordset BEFORE you make your INSERT statement -- and then use the resulting value to build your statement.

Does that make sense?


Paul Prewett

 

[WuCOng]

Yep, makes sense to me Paul. Thanks a heap. Guess I've been looking at the code too long to be able to work it out myself (or maybe those late night AvP sessions have fried my brain ). Thanks again.