ASP DSN-less connection to Access

[SurenR]

Dear All,

I have IIS running on Windows XP Pro. On the server is a Access database
and my ASP pages all under the
'wwwroot folder'. I have a form on one of the pages(lets say Form1),
where users input data. I am trying to add
this data to the database DSN-lessly. I open the connection on a
seperate page and include it in Form1 somewhat
like this: <!-- #INCLUDE FILE="conn_QuoOpen.asp"-->

And in "conn_QuoOpen.asp", I have this to open then connection:

<%
dim conn

Set conn = Server.CreateObject("ADODB.Connection")
demoFilePath = Server.MapPath("Quo.mdb")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & demoFilePath & ";"
%>

Now for grabbing the data on Form1 and inserting into the database, this
is what I have:

function submitme()
Dim RsdesApp , strSQLdesApp
strSQLdesApp = "INSERT INTO Quot (Name,Surname)"
strSQLdesApp = strSQLdesApp & "Values('" &
Request.form("txtName") & "','" & Request.form("txtSurname") & "')"

Response.Write strSQLdesApp
demoConn.execute strSQLdesApp
demoConn.commit
Set RsdesApp = Nothing
end function

I can't seem to get the data in. What could I be getting wrong or
missing out here? Appreciate any help given.
Thanks in advance.

Regards,
Suren

[McKirahan]

"SurenR" <suren81@singnet.com.sg> wrote in message
news:409C6993.5020905@singnet.com.sg...

> Dear All,
>
> I have IIS running on Windows XP Pro. On the server is a Access database
> and my ASP pages all under the
> 'wwwroot folder'. I have a form on one of the pages(lets say Form1),
> where users input data. I am trying to add
> this data to the database DSN-lessly. I open the connection on a
> seperate page and include it in Form1 somewhat
> like this: <!-- #INCLUDE FILE="conn_QuoOpen.asp"-->
>
> And in "conn_QuoOpen.asp", I have this to open then connection:
>
> <%
> dim conn
>
> Set conn = Server.CreateObject("ADODB.Connection")
> demoFilePath = Server.MapPath("Quo.mdb")
> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
> "Data Source=" & demoFilePath & ";"
> %>
>
> Now for grabbing the data on Form1 and inserting into the database, this
> is what I have:
>
> function submitme()
> Dim RsdesApp , strSQLdesApp
> strSQLdesApp = "INSERT INTO Quot (Name,Surname)"
> strSQLdesApp = strSQLdesApp & "Values('" &
> Request.form("txtName") & "','" & Request.form("txtSurname") & "')"
>
> Response.Write strSQLdesApp
> demoConn.execute strSQLdesApp
> demoConn.commit
> Set RsdesApp = Nothing
> end function
>
> I can't seem to get the data in. What could I be getting wrong or
> missing out here? Appreciate any help given.
> Thanks in advance.
>
> Regards,
> Suren

Does the Response.Write look okay?

What error are you getting?

If the error is "Operation must use an updateable query.", then under XP
Pro:

1) Open Control Panel then click Folder Options then scroll to the
bottom and turn off "Simple File Sharing"

2) Open Windows Explorer, right-click on the database's folder, click
Properties, click on the (now revealed) Security tab then select Everyone
then click the "Write" checkbox the click "Apply".

[Aaron Bertrand [MVP]]

> I can't seem to get the data in.

What does that mean?

Are you getting an error message? If so, WHAT IS IT?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[SurenR]

Hey guys!

Sorry for the late reply!
Ive managed to get data into the Access table at the moment. But right
now I have another problem, here's
the error message:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Expected query name after EXECUTE.
/EnquiryInsert.asp, line 19

Heres the line of code where the error was reported:

Ln10 <%
Ln11 Dim RsdesApp , strSQLdesApp
Ln12 Set strSQLdesApp = CreateObject("Adodb.Recordset")

Ln14 strSQLdesApp = "INSERT INTO Quot (Name,Surname) "
Ln15 strSQLdesApp = strSQLdesApp & "Values('" &
Request.form("txtName") & "','" & Request.form("txtSurname") & "')"

Ln17 'Response.Write strSQLdesApp
Ln18 conn.execute(strSQLdesApp)
Ln19 conn.commit
Ln20 Response.Redirect("Enquiry.asp")
Ln21 conn.close
Ln22 Set conn = Nothing
Ln23 Set RsdesApp = Nothing
Ln24 %>

Thanks.

Regards,
Suren

Aaron Bertrand [MVP] wrote:

>>I can't seem to get the data in.
>>

>
>What does that mean?
>
>Are you getting an error message? If so, WHAT IS IT?
>

[Aaron Bertrand [MVP]]

(a) Why are you using Set strSQLdesApp? As your variable naming explains,
this is a STRING, not an object.

(b) Always make sure to prevent SQL injection and syntax errors by replacing
apostrophes in incoming data with double apostrophes (see
[url]http://www.aspfaq.com/2035[/url]).

(c) What the heck do you have conn.commit for? Conn.execute already does
whatever you think conn.commit will do. Also, you should make sure to tell
the connection object what kind of command you are using. 129 designates it
as adCommandText with adExecuteNoRecords (the former improves efficiency
because the provider knows exactly what kind of command is being sent, and
doesn't have to translate it by sniffing it out; the latter because it knows
it will not have to retrieve any data).

(d) Name is a reserved word. Have you considered using columns like
FirstName and LastName, which have three advantages: (1) they're far more
descriptive than Name and Surname; (2) they avoid a reserved word problem;
and (3) they are far more standard. As it is, you will have to surround
this column name with [square brackets].

Taking only (a) (c) and (d) into account:

<%
set conn = CreateObject("ADODB.Connection")
conn.open "<...connection string...>"
strSQL = "INSERT INTO Quot([Name], Surname) <...the rest here...> "
conn.execute strSQL, , 129
conn.close: set conn = nothing
%>

(In the future, please post in plain text.)

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]





"SurenR" <suren81@singnet.com.sg> wrote in message
news:409F05FF.7070505@singnet.com.sg...
Hey guys!

Sorry for the late reply!
Ive managed to get data into the Access table at the moment. But right now I
have another problem, here's
the error message:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Expected query name after EXECUTE.
/EnquiryInsert.asp, line 19

Heres the line of code where the error was reported:

Ln10 <%
Ln11 Dim RsdesApp , strSQLdesApp
Ln12 Set strSQLdesApp = CreateObject("Adodb.Recordset")

Ln14 strSQLdesApp = "INSERT INTO Quot (Name,Surname) "
Ln15 strSQLdesApp = strSQLdesApp & "Values('" & Request.form("txtName") &
"','" & Request.form("txtSurname") & "')"

Ln17 'Response.Write strSQLdesApp
Ln18 conn.execute(strSQLdesApp)
Ln19 conn.commit
Ln20 Response.Redirect("Enquiry.asp")
Ln21 conn.close
Ln22 Set conn = Nothing
Ln23 Set RsdesApp = Nothing
Ln24 %>

Thanks.

Regards,
Suren

Aaron Bertrand [MVP] wrote:

I can't seem to get the data in.
What does that mean?Are you getting an error message? If so, WHAT IS IT?