Problem inserting record into Access database

[Erica]

Hi,

I have searched everywhere, and I can't seem to find the answer to my
problem. I am trying to write a very simple piece of ASP code to
insert a record into a field in a database. Ultimately it will be for
an admin form that takes user input, but for now I just want to get
the most intrinsic part working. This is the code:

<% ' Declare variables
Dim strURL ' The URL of this page so the form will work no matter
what this file is named.
Dim myConn ' ADO connection
Dim strDBPath ' path to our Access database (*.mdb) file
Dim strSQL ' The query string
Dim strSummary ' The summary string

' Fill in value for strSummary, it doesnt really matter what it is
strSummary = "blahblah"

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")

' MapPath of virtual database file path to a physical path.
strDBPath = Server.MapPath("dvd_club.mdb")

'Create the ADO object
Set myConn = Server.CreateObject("ADODB.Connection")

' Create an ADO Connection to connect to the database.
Set cnnSearch = Server.CreateObject("ADODB.Connection")

myConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strDBPath & ";"
' Add movie summary to database
strSQL = "INSERT INTO Results (dvdSummary) VALUES ('"strSummary"');"

myConn.Execute(strSQL)
myConn.Close
Set myConn = Nothing %>

This just yields an HTTP 500 internal server error. When I open the
database table in Access, it is not updated. Does anyone know what I
am doing wrong?

Thanks,
Erica

[Pieter Linden]

> ' Add movie summary to database

strSQL = "INSERT INTO Results (dvdSummary) VALUES ('"strSummary"');"

myConn.Execute(strSQL)
myConn.Close
Set myConn = Nothing %>

shouldn't the strSQL line be something like:

strSQL = "INSERT INTO Results (dvdSummary) VALUES ('" & strSummary &
"');"

since you're building the SQL string on the fly? You should probably
do a Response.Write strSQL so you can see what the server is trying to
execute. If you copy that into a QBE grid in Access, and it doesn't
compile then there's your problem.