database line query

  harps1h 21:22 10 Apr 09
Locked

%
'Declare variables
Dim ConnectionString 'Holds the Connection Path
Dim AdoCon 'Holds the Database Connection Object
Dim rsPastWalk 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database

'Create an ADO Connection Object
Set AdoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Ado Connection object
ConnectionString= "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath ("admin.mdb")
AdoCon.Open ConnectionString

'Create an ADO recordset object
set rsPastWalk=Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT WalkNo, Venue, WalkDate, Leader, Report FROM walk WHERE WalkNo = "& Request("WalkNo")

'Set the cursor type so we can navigate through the recordset
rsPastWalk.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsPastWalk.LockType = 3

'Open the recordset with the SQL query
rsPastWalk.Open strSQL, AdoCon


Response.Write " TR> TD>" & rsPastWalk("WalkNo") & " /TD>"
Response.Write " TD>" & rsPastWalk("Venue") & " /TD>"
Response.Write " TD>" & rsPastWalk("WalkDate") & " /TD>"
Response.Write " TD>" & rsPastWalk("Leader") & " /TD>"
Response.Write " TD>" & rsPastWalk("Report") & " /TD> /TR>"


'Reset server objects
rsPastWalk.Close
Set rsPastWalk = Nothing
AdoCon.Close
Set adoCon = Nothing
%>

above is a simple code to connect to an asp and database. when i use this code it comes up with an error code "Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'WalkNo ='.
/erne/pastwalks.asp, line 229"
the line this refers to is rsPastWalk.Open strSQL, AdoCon >>
has anybody any suggestions as this is a steep learning curve

thanks

harpur

  harps1h 21:23 10 Apr 09

by the way i am using IE 8 and my other asp pages connect ok

  Chris the Ancient 11:54 11 Apr 09

A good source of help with Access is the dedicated forum at click here.

It's free (nice) and I have found it a reasonably quick, friendly and helpful forum.

Being based in the USA, a lot of the 'helpers' on that site tend to be on their time zones on that side of the Atlantic, so answers tend to be more proliferate in the evenings.

HTH

CtA

  CodeMeister 16:27 11 Apr 09

Only a guess, but "Report" is a reserved word in Microsoft Access and that may well be causing your problem.

Try enclosing Report in square brackets (e.g. "[Report]").

  stlucia 08:36 12 Apr 09

Just a guess because I use dBASE rather than Access, but your double-quote-marks are unbalanced in the line:-

... strSQL = "SELECT WalkNo, Venue, WalkDate, Leader, Report FROM walk WHERE WalkNo = "& Request("WalkNo") ...

Have you tried another " after the last bracket?

  harps1h 23:09 12 Apr 09

tried that but not the answer

  harps1h 13:47 13 Apr 09

I have manged to get htis sorted out by using a link code supplied by my tutor. He never intended this page to open automatically but rather via a link firom a table value. this is now displaying the correctly.
however there is another problem.
the code displayed beow is for submitting a password and member ref to add to the recordset and display it on the page of a future event, however whenever i click on the submit button it seems not to do this. any thoughts?

'set the server local (to change date to british)
Session.LCID = 2057
%>
</head>

<CENTER>
<table border = 1 width="800">
<TR>
<TD><B><font face="Arial">Walk No</font></TD>
<TD><B><font face="Arial">Venue</font></TD>
<TD><B><font face="Arial">WalkDate</TD>
<TD><B><font face="Arial">Leader</TD>
</TD>
</TR>

<%
'Declare variables
Dim ConnectionString 'Holds the Connection Path
Dim AdoCon 'Holds the Database Connection Object
Dim rsFutureWalk 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database

'Create an ADO Connection Object
Set AdoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Ado Connection object
ConnectionString= "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath ("admin.mdb")
AdoCon.Open ConnectionString

'Create an ADO recordset object
set rsFutureWalk=Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT WalkNo, Venue, WalkDate, Leader, Report FROM walk WHERE WalkNo = "& Request("WalkNo")

'Set the cursor type so we can navigate through the recordset
rsFutureWalk.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsFutureWalk.LockType = 3

'Open the recordset with the SQL query
rsFutureWalk.Open strSQL, adoCon


Response.Write "<TR><TD>" & rsFutureWalk ("WalkNo") & "</TD>"
Response.Write "<TD>" & rsFutureWalk("Venue") & "</TD>"
Response.Write "<TD>" & rsFutureWalk("WalkDate") & "</TD>"
Response.Write "<TD>" & rsFutureWalk("Leader") & "</TD>"
Response.Write "<TD>" & rsFutureWalk("Report") & "</TD></TR>"


'Reset server objects
rsFutureWalk.Close
Set rsFutureWalk = Nothing
AdoCon.Close
Set adoCon = Nothing
%>
<CENTER>
<table border = 1>
<TR>
<TD><B><font face="Arial">MemberRef</font></TD>
<TD><B><font face="Arial">DateJoined</font></TD>

</TD>
</TR><%
'Declare variables

Dim ConnectionStringWalker'Holds the Connection Path
Dim AdoConWalker 'Holds the Database Connection Object
Dim rsWalker 'Holds the recordset for the new record to be added
Dim strSQLWalker 'Holds the SQL query to query the database for the walkers involved

'Create an ADO Connection Object
Set AdoConWalker = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Ado Connection object
ConnectionStringWalker= "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath ("admin.mdb")
AdoConWalker.Open ConnectionStringWalker

'Create an ADO recordset object
set rsWalker=Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQLWalker = "SELECT MemberRef, DateJoined FROM Walker WHERE WalkNo = "& Request("WalkNo")

'Set the cursor type so we can navigate through the recordset
rsWalker.CursorType = 2


'Set the lock type so that the record is locked by ADO when it is updated
rsWalker.LockType = 3

'Open the recordset with the SQL query
rsWalker.Open strSQLWalker, adoConWalker,conn%>
<%Do While Not rsWalker.EOF
Response.Write "<TR><TD>" & rsWalker ("MemberRef") & "</TD>"
Response.Write "<TD>" & rsWalker ("DateJoined") & "</TD></TR>"
rsWalker.MoveNext
Loop
Response.Write "</CENTER></TABLE>"

  harps1h 13:47 13 Apr 09

'Reset server objects
rsWalker.Close
Set rsWalker = Nothing
AdoConWalker.Close
Set adoCon = Nothing
%>
</TD></TR></TBODY></TABLE><!-- Begin form code --></FONT></U>
<P></P>
<P align=center>Please fill in all the fields below -  </P>
<CENTER>
<FORM name=form action=file:C:\inetpub\wwwroot\erne\admin.mdb method=post>
Walk No:      
<INPUT maxLength=5 name=WalkNo size="20">
<BR> Password:     <INPUT TYPE="password" SIZE=20 NAME="password">
<BR>Member Ref: <INPUT maxLength=5 name=MemberRef size="20"> <BR><INPUT type=submit value=Submit name=Submit> <INPUT type=reset value=Reset name=Reset>
</FORM><!-- End form code --></CENTER></CENTER></TR></TBODY></TABLE></CENTER></BODY></HTML>
</table>
</body>


</html>

  reddwarfcrew 14:53 13 Apr 09

As this appears to be in relation to course work, you need to be careful that you dont get done for plagarism.

If you are stuck seek help from your tutor group.

  harps1h 15:06 13 Apr 09

there is no chance of plagerism as this was provided by our course tutor for the class to use.
as we are on holiday at the momemt there is no chance of a class group and it is due monday 20th. also i am a part time student juggling this in between work family and university. our brief was to use supplied pices of code and build a relevant website from it. as such it is completed except i can't get this to work

  harps1h 15:08 13 Apr 09

i should also add that i have little knowledge of asp and database connections so this is an extremely steep learning curve

This thread is now locked and can not be replied to.

Qualcomm Snapdragon 835 benchmarks: Antutu, Geekbench 4, GFXBench and PCMark results

1995-2015: How technology has changed the world in 20 years

This stop-frame animation tells a moving story of domestic violence for Refuge

New iPad 2017 preview: Apple's affordable but underspecced new iPad may appeal to the education…