DNS-Less connection and insert into command

[mavrick]

hi all

Here is my situation, trying to use DNS-less connection.
DB used is Access 2000.
Code used is ASP.

I am trying to add a database using the insert into command, the page is not displaying. I have tested the code with out the database look up and I do get the page to come up. So only one thing can be wrong, it’s the db lookup. I don’t know weather Access 2000 supports the insert into command, please help me on this as well.

Here is the code :

Dim Conn, dbPath
dbPath = "d:\accounts\organic\databases\go2000.mdb"
set conntemp=server.createobject("adodb.connection")
conntemp.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
set rstemp=conntemp.execute SQL_Stmt = "INSERT INTO tbluser (Title, Fname, Sname, Username, Password, Daterec)"
SQL_Stmt = SQL_Stmt & "VALUES ('" & Title
SQL_Stmt = SQL_Stmt & "','" & Fname
SQL_Stmt = SQL_Stmt & "','" & Sname
SQL_Stmt = SQL_Stmt & "','" & Username
SQL_Stmt = SQL_Stmt & "','" & Password
SQL_Stmt = SQL_Stmt & "','" & Daterec
SQL_Stmt = SQL_Stmt & "')"

set rstemp=conntemp.execute(SQL_Stmt)
set rstemp = Nothing

thanks in advance

mavrick

[aspdaddy]

The first execute is not needed, Build the sql then just do a single execute like this:

conntemp.execute(SQL_Stmt)

No recordset is needed for INSERT and you may need to add a space before the word VALUES :

SQL_Stmt = SQL_Stmt & " VALUES ('" & Title

[mattur]

Like aspdaddy says, no rst required. Access does use INSERT... statement. Comment out "On Error Resume Next" if you have it in your code, so that you can see any error messages.

To debug an SQL statement you can just response.write it to the page and then copy and paste into Access to check it is correct. HTH

[mavrick]

hi guys

please could u tell me what is wrong here

Title=Request.Form("Title")
Fname=Request.Form("Fname")
Sname=Request.Form("Sname")
Username=Request.Form("Username")
Password=Request.Form("Password")
Daterec=Request.Form("date")

Dim Conn, dbPath
dbPath = "d:\accounts\organic\databases\go2000.mdb"
set conntemp=server.createobject("adodb.connection")
conntemp.open & dbPath
SQL_Stmt = "INSERT INTO tbluser (Title, Fname, Sname, Username, Password, Daterec)"
SQL_Stmt = SQL_Stmt & " VALUES ('" & Title
SQL_Stmt = SQL_Stmt & "','" & Fname
SQL_Stmt = SQL_Stmt & "','" & Sname
SQL_Stmt = SQL_Stmt & "','" & Username
SQL_Stmt = SQL_Stmt & "','" & Password
SQL_Stmt = SQL_Stmt & "','" & Daterec
SQL_Stmt = SQL_Stmt & "')"

conntemp.execute(SQL_Stmt)
set rstemp = Nothing

[Zigire]

conntemp.open & dbPath

Instead of that line try the following

conntemp.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\accounts\organic\databases\go2000.mdb;" & _
"User Id=admin;" & _
"Password=;"

[mattur]

..and replace the set rstemp = Nothing with

conntemp.close
Set conntemp = Nothing

(Not doing this won't cause a problem, but it is good practice to free up server objects when you've finished with them)

[mavrick]

hi guys

still does not seem to work. it does not even seem to come up with an errror page. it just posts to a "Page cannot be displayed page", i dont understand WHY?
error on page is "HTTP 500 Internal Server error"

another thing i am hosting my site on an overseas server, so i have to use a DNS-Less connection, the code seems to work fine on my side if i use a DNS connection.

i dont no what to do now, hit a wall.

help!

[mattur]

Are you sure the database path is correct? Try a simple Response.write Server.MapPath to check this.

DSN or DSN-less connections shouldn't affect the operation of the page. Also if you're using IE, go into Tools > Internet Options > Advanced and make sure "Show friendly HTTP error messages" is no checked.

[mavrick]

hi all

thanks for the help. all seems to work fine. dns-less problem solved, it was a permission problem on the remote host side.

thanks

[Zaphod Beeblebrox]

Also, this statement should not have brackets:

conntemp.execute(SQL_Stmt)

[mattglet]

actually, it doesn't matter. it will work fine either way.

-Matt

[aspdaddy]

IMO, its good practice to use them, especially for a beginner. VB.NET requires them.

[Xoc]

Actually, it is only coincidence that it works in VBScript. The reason is that (expression) evaluates to expression. But if the method took two arguments and you did

conntemp.execute(arg1, arg2)

you would get a syntax error, because (expression1, expression2) isn't valid syntax. So the rule is either use the Call keyword with paren, or leave off the Call keyword, but then leave off the parens.

So use either

Call conntemp.execute(SQL_Stmt)

or

conntemp.execute SQL_Stmt

This is entirely different in VB.NET, where you leave off the Call keyword, but always use the parens.