Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Querying a Access Database

Status
Not open for further replies.
jmurrayhead

jmurrayhead

Programmer
Feb 13, 2004
47
US
I have an access database that I want to create a query form for. I'm not sure how to do it. I want it to have default values in it based on session variables that I have set.
For example, First Name: <textbox value="<%=Session(appName&"x_firstname")%>"></textbox> Last Name: <textbox value="<%=Session(appName&"x_lastname")%>"></textbox>
Then they click the submit button and it retreives all records with the first and last name present.

All help is appreciated. Thanks
 
Sort by date Sort by votes
Hi jmurrayhead,
Sorry but is your question concerned with writing the SQL syntax to query the database or putting the default values into the blank form?
 
Upvote 0 Downvote
It's a little of both. What I'm more concerned with is how to query my database. I'll probably just put some hidden values in the form to take care of the other part.

Thanks
 
Upvote 0 Downvote
OK, so what specifically do you need help with? The basic steps are:
1. Establish a connection to the database
2. Open the Database
3. Write the SQL syntax string
4. Execute the query and store the results in a recordset
5. Use the recordset to show the results.
 
Upvote 0 Downvote
Okay, I already have a database and their is a connection from that database to a form that submits data to it. What I need to do is be able to pull certain fields from that database and then display it. This I have no idea on how to do. I'm also going to assume that from the page that queries the database, there will have to be some sort of programming to call on the database that I need the information from.

Thanks,
 
Upvote 0 Downvote
jmurrayhead,
This is a very brief snippet of code using VBscript. This file should be .asp.

Code: 
[red]<%@ Language=VBScript%>
<%
dim objRS
dim strSQL

[green]'objConn is your connection to your database which should be open. You can either open it in this file or use a server side include statement[/green]

[gray]<!--#include file="[I]include.asp[/I]"-->[/gray]

strSQL = "SELECT * FROM [I]table[/I]"
strSQL = strSQL & " WHERE Firstname = '" & Request("x_firstname") & '" 
strSQL = strSQL & " AND Lastname = '" & Request("x_lastname") & '" 

set objRS = objConn.execute(strSQL)
%>
[/red][blue]<html><body>[/blue]
FirstName: [red]<%=ObjRS("Firstname")%>[/red][blue]<br>
[/blue]LastName: [red]<%=ObjRS("Firstname")%>[/red][blue]<br>
[red]<%objRS.close%>[/red]
</body></html>[/blue]
Something like that should work. The Request Object is used to get the variable from your html form. I have a much better way to do all this using functions but it's slightly more advanced.

Hope that helps.
 
Upvote 0 Downvote
Okay...so here's the connection I established:

<%
Set oConn=Server.CreateObject("ADODB.Connection")
filePath = Server.MapPath("mydatabase.mdb")
oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myroor\fpdb\mydatabase.mdb")
%>

<% oConn.Close %>

What would I need to do if I wanted to call on the session variable <%=Session(appName&"UserName")%> to search the database for whatever value that variable holds?

Thanks for your help.
 
Upvote 0 Downvote
Hi jmurrayhead,
I guess you would just put it in the strSQL string variable, replacing the Request variable.

[red]strSQL = strSQL & " WHERE Firstname = '" & Session(appName&"UserName") & '"
[/red]
HTH
 
Upvote 0 Downvote
Okay when using that I get the following error message:
Code: 
Microsoft VBScript compilation error '800a0409' 

Unterminated string constant 

/testdata.asp, line 21 

strSQL = strSQL & " WHERE UserName = '" & Session(appName&"UserName")& "'

Here's what I have:
Code: 
<%
dim objRS
dim strSQL
Set oConn=Server.CreateObject("ADODB.Connection")
filePath = Server.MapPath("mydatabase.mdb") 
oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myroot\fpdb\mydatabase.mdb") 

set objRS = objConn.execute(strSQL)
%>
<%
strSQL = strSQL & " WHERE UserName = '" & Session(appName&"UserName")& "'
%>

Any other ideas....? Thanks for your help.
 
Upvote 0 Downvote
Hi,
Make sure you match up the double quotes correctly and put spaces before and after the ampersand symbols (&).

strSQL = strSQL & [red]"[/red] WHERE UserName = '[red]"[/red] & Session(appName&"UserName") & [blue]"[/blue]'[blue]"[/blue]
 
Upvote 0 Downvote
Getting closer....here's what I have now after making the adjustments to the &...
Code: 
<% 
dim objRS 
dim strSQL 
Set oConn=Server.CreateObject("ADODB.Connection") 
filePath = Server.MapPath("mydatabase.mdb") 
oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myroot\fpdb\mydatabase.mdb") 

set objRS = oConn.execute(strSQL) 
%> 
<% 
strSQL = strSQL & " WHERE UserName = '" & Session(appName & "UserName")& "'" 
%>
Here's the message I get...
Code: 
Microsoft VBScript runtime error '800a01a8' 

Object required: '' 

/testdata.asp, line 18
What do ya think?
 
Upvote 0 Downvote
I think Session(appName & "UserName") is blank. So you need to trap it before this line is reached.
Something like:
Code: 
Dim userName
userName = Session(appName & "UserName")

If userName <> "" Then
   do the query thing
Else
   tell the user to enter their name
End If

Cheers.
 
Upvote 0 Downvote
Now I get....

Code: 
Microsoft JET Database Engine error '80040e0c' 

Command text was not set for the command object. 

/testdata.asp, line 21

Seems to be a problem with this line:

Code: 
set objRS = oConn.execute(strSQL)

I'm trying to study up on this stuff so I can understand it a little better. I've taken a visual basic class and this whole database thing is something I've just recently been able to get into. I really appreciate your help.

Thanks

 
Upvote 0 Downvote
The problem is always with that line!

Did you substitute the userName variable for the Session one in the query string? Also, where are you getting this Session variable from and how?


 
Upvote 0 Downvote
Yes I did substitute the UserName variable. The session variable comes from when the user logs in to the protected pages. The script sets the session variable
Code: 
<%=Session(appName&"UserName")%>

thanks
 
Upvote 0 Downvote
Hi there,
Could you post the whole code again?
Cheers.
 
Upvote 0 Downvote
Yup, here it is

Code: 
<%
dim objRS
dim strSQL
dim UserName
%>
<%
UserName = <%Session(appName&"UserName")%>
<%
Set oConn=Server.CreateObject("ADODB.Connection")
filePath = Server.MapPath("database.mdb") 
oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myroot\fpdb\database.mdb") 

set objRS = oConn.execute(strSQL)
%>
<%
If UserName <> "" Then
strSQL = strSQL & "WHERE UserName = '" & UserName & "'"
End If
%>

Thanks
 
Upvote 0 Downvote
There must be more to your sql. So far the only sql we have seen is "...WHERE UserName = ..."

I would bet that the entire sql includes a "Select..." statement. In our office we don't use conn.execute with a select statement. We use the syntax "[recordset name].open [sql string name], [connection name]". I'm not sure if that's universal, or something that just applies to us. We have our own library of .asp functions.

I sense that you are just getting started with this technology. I would start by running an sql without a session variable. Then when you have mastered that, then add the variable. I think your syntax for the variable is right, but the variable might be null.
 
Upvote 0 Downvote
Yeah, that seems to be the route I'll have to go. Thanks for your help.
 
Upvote 0 Downvote
jmurrayhead,
Yep, looks like you've forgotten to put the first SQL back line in:

[red]strSQL = "SELECT * FROM table"[/red]
strSQL = strSQL & " WHERE UserName = '" & UserName & "'"

where table should be replaced by your table name. Don't forget the space before the WHERE.

Also, I think you've gone overboard with the <% tags. I'm sure you could get away with just the first and last ones.

I've always used the execute function, which works for me, but then again I haven't brushed up on my asp for a while.

Good luck.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Using Access Database Without Having MS Access
Replies
9
Views
6K
grnzbra
grnzbra
ameedoo3000
  • Locked
  • Question
switch between two open database
Replies
12
Views
1K
Andrzejek
Andrzejek
renigar
  • Locked
  • Question
Access VBA Syntax Error 1
Replies
5
Views
5K
Andrzejek
Andrzejek
OCM
  • Locked
  • Question
Populate access table from excel
Replies
6
Views
5K
OCM
OCM
renigar
  • Locked
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
1K
renigar
renigar

Part and Inventory Search

Sponsor

Back
Top