Easy ASP-Access DB question baffling me.

[WuCOng]

I'm able to save to my DB if I save to a test table that only has one field. But I'm trying to save to another table that has multiple fields. I get some odd things happening.

1st-I get an error message saying that the variables clash or are outside of acceptable limits. According to the Access DB setup they are fine (not too long, correct type etc).

The ASPP (where I alternate SQLstr and SQLstr1 in the oRS.open line in order to test the testtable and the users table):

<% SQLstr=&quot;INSERT INTO users (user_name, email, password, postcode) VALUES ('&quot;&create_name&&quot;','&quot;&create_email&&quot;','&quot;&create_password&&quot;','&quot;&create_postcode&&quot;')&quot;
SQLstr1=&quot;INSERT INTO testtable (testfield) VALUES ('&quot;&create_name&&quot;')&quot;
set oRS = server.createobject(&quot;ADODB.recordset&quot
oRS.open SQLstr,&quot;DSN=mydb&quot;
%>

2nd-If I include a close ASP line I'm told that it is illegal to do so on a closed object. I open it but don't close it before this line. Why does the machine think the DB is already closed? It works if I remove the line but I'm not really happy doing that.

 

[link9]

Well that's because you are trying to open a recordset with an INSERT -- it's counterintuitive to ADO. To open a recordset, you should only use a SELECT statement -- i.e. a statement that returns some data -- which then resides in your recordset.

If you want to execute a command on your database, use the .execute method of your connection object like so:

<%
dim sql
sql = &quot;INSERT INTO tableName ( colName ) VALUES ( 1 )&quot;

dim con
set con = server.createObject(&quot;ADODB.Connection&quot
con.open (&quot;DSN=myDSN;UID=uid;PWD=pwd&quot

con.execute sql

set con = nothing
%>

Much cleaner and more efficient -- only one object floating around -- your connection.

hope that helps!
Paul Prewett

 

[WuCOng]

Thanks. I'll try it this evening.
Very concise. I see the problem.