IT SOLUTIONS
Your full service technology partner! 
-Collapse +Expand
ASP Classic
Search ASP Classic Group:

Advanced
-Collapse +Expand ASP Classic Store

Prestwood eMagazine

December Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyASP Classic Coding BoardASP Classic - Handling Data Topic   Print This     

view table info

view table info in ASP Classic - Handling Data topic (part of our ASP Classic Coding group).

Quick Search: view table   view table info  
aka51
MD

I am using ASP and this is the code I am using


<%
dim rsmail, sql

Sql = "SELECT "& request.form("SELECT") &" FROM "& request.form("DATABASE") &" where "& request.form("WHERE_FIELD") &" = '"& strwhere &"';"


set rsmail = Server.CreateObject("ADODB.Recordset")

rsmail.Open sql, db
if not rsmail.EOF then
do while not rsmail.EOF
%>

<% Response.Write rsmail("Action") %>


<% Response.Write rsmail("can") %>


<% Response.Write rsmail("emp_name") %>


<%
rsmail.MoveNext
loop
end if
%> However I will recieve anerror if I try to write the info to the screen as each select statement will select different things from the database so if one of the recordset (rsmail) was not selected it will cause an error.
Is there a way to write it without specificing which recordset
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9572, 23 replies
Thread Started 5/13/2003 10:59:00 AM
View Counter=4412
Last Reply Posted 5/14/2003 11:29:00 AM)
Location=MD  
Joined=19 years ago   MB Posts=25  
jdschram
MN
***** just keep you post in one thread ******

well..here is one issue...

rsmail("Action"),rsmail("can") and rsmail("emp_name") may not be in your sql query...
If you always want those three to be shown, your sql will need to be
Sql = "SELECT action,can,emp_name,"& request.form("SELECT") & _
" FROM "& request.form("DATABASE") & _
" where "& request.form("WHERE_FIELD") &" = '"& strwhere &"';"
set rsmail = Server.CreateObjec("ADODB.Recordset")
An easy and good way to debug your sql is to do a this right after you initialize the sql variable.
response.write sql
response.end
then copy/paste the sql you gerated into query analyzer/ms access and run it against the database.

I'm pretty sure I can help you get this running, i'm still a little confused. IF you want the select statement generated dynamically (like you have it) you cannot hardcode the RSMAIL column names (also like you are doing) without a guarentee that it will not break. So if you have more questions, or a site where i can look at what you are inputting and expecting back, please post it Big Grin!

*** one more note...you dont need to use the do while loop...or the if not rsmail.eof...it can be simplified to:
while not rsmail.eof
rsmail.movenext
wend
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9574 (Level 1.1)  Reply to 9572
Thread Started 5/13/2003 11:31:00 AM
View Counter=2
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
There are a couple options...

How are you building the Select list? Radio? Checkbox? Textbox? Is the select generated dynamically from your database or is it hardcoded?

...the most important thing is that you always generate valid SQL.

In any event here is a generic script to return all selected columns and thier headers.

You will do this after you open your recordset

dim rsmail, sql  
Sql = "SELECT "& request.form("SELECT") & _
" FROM "& request.form("DATABASE") & _
" where "& request.form("WHERE_FIELD") &" = '"& strwhere &"';"

set rsmail = Server.CreateObjec("ADODB.Recordset")

rsmail.Open sql, db

Response.Write ""
For fnum = 0 To rsmail.Fields.Count-1
Response.Write ""
Next
Response.Write ""

While not rsmail.EOF
Response.Write ""
For fnum = 0 To rsmail.Fields.Count-1
Response.Write ""
Next
Response.Write ""
RS.MoveNext
Wend
Response.Write "
" & rsmail.Fields(fnum).Name & "
" & rsmail.Fields(fnum).Value & "
"
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9577 (Level 1.2)  Reply to 9572
Reply Posted 5/13/2003 12:00:00 PM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
response.write your sql statement and post it here...
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9579 (Level 1.3)  Reply to 9572
Reply Posted 5/13/2003 12:18:00 PM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
add this code right after you initialize the SQL variable...

' right after SQL = ....
response.write "
" & sql & "
"
response.end
run the page again and your sql statement will be printed to the screen...copy paste that here...
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9581 (Level 1.4)  Reply to 9572
Reply Posted 5/13/2003 12:25:00 PM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
im pretty sure you have an SQL syntax problem, so you need to debug the SQL statement..the ASP code should be fine.
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9582 (Level 1.5)  Reply to 9572
Reply Posted 5/13/2003 12:28:00 PM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
remove the ; and the lines i had you add..
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9584 (Level 1.6)  Reply to 9572
Reply Posted 5/13/2003 12:31:00 PM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
i know its part of the sql..it may be causing the error though...
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9586 (Level 1.7)  Reply to 9572
Reply Posted 5/13/2003 12:37:00 PM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
show me your connection string
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9587 (Level 1.8)  Reply to 9572
Reply Posted 5/13/2003 12:40:00 PM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
i think it may actually be your connection string that is bad...try commenting out all code except for setting and opening the connection and then run the page.

If you still get an error try and change the connection to:

Set Conn = Server.CreateObjec("ADODB.Connection") 

Conn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.Mappath("budget_demo.MDB") & ";"
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9589 (Level 1.9)  Reply to 9572
Reply Posted 5/14/2003 6:46:00 AM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
no problem...just out of curioisity what was the problem?
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9592 (Level 1.10)  Reply to 9572
Reply Posted 5/14/2003 8:57:00 AM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
haha..now just move that connection string off into an include file and you'll never have connection problems again
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9594 (Level 1.11)  Reply to 9572
Reply Posted 5/14/2003 9:44:00 AM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
actually you SHOULD put it in an include file so you NEVER have to touch it.
 Posted 18 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9596 (Level 1.12)  Reply to 9572
Reply Posted 5/14/2003 11:27:00 AM
Location=MN  
Joined=20 years ago   MB Posts=259  
aka51
MD
I would love to have you look at the site but it is on an intranet, so public access is not possible.
But you do have the jist of the situation. The SQL statement is being generated dynamically, so I know that I can't hradcode rsmail as it will change with every run of the SQL statement, so I need to know how to get the info that was selected from the databse to be written out.
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9576 (Level 1.13)  Reply to 9572
Reply Posted 5/13/2003 11:40:00 AM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD
I tried that before and I did it again using your code and I keep getting an error message of

Microsoft VBScript runtime error '800a01a8'

I figured I just didn't do it right but I guess I am missing something but I can't see what it is.
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9578 (Level 1.14)  Reply to 9572
Reply Posted 5/13/2003 12:17:00 PM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD




%>

Peace love and happiness
this is what i have and the sql statement is coming from a select list
 


<%
dim rsmail, sql

Sql = "SELECT "& request.form("SELECT") &" FROM "& request.form("DATABASE") &" where "& request.form("WHERE_FIELD") &" = '"& strwhere &"';"

set rsmail = Server.CreateObject("ADODB.Recordset")

rsmail.Open sql, db



Response.Write ""


For fnum = 0 To RS.Fields.Count-1
Response.Write ""

Next
Response.Write ""
While not RS.EOF
Response.Write ""
For fnum = 0 To RS.Fields.Count-1
Response.Write "" & vbNewLine
Next
Response.Write ""
RS.MoveNext
Wend
Response.Write "
" & RS.Fields(fnum).Name & "
" & RS.Fields(fnum).Value & "
"


%>
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9580 (Level 1.15)  Reply to 9572
Reply Posted 5/13/2003 12:22:00 PM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD
the sql statement is running fine I tested i hard coded the columns i selected and it wrote it out prefectly. But this is the statment i got after running it again with the line you had me insert
--------------------------------------------------------------------------------
SELECT emp_name, action, Can, hnb, total1 FROM personnel where emp_name = 'JOSEPH BOFRONE';
--------------------------------------------------------------------------------
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9583 (Level 1.16)  Reply to 9572
Reply Posted 5/13/2003 12:30:00 PM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD
Ia m not sure I understand that last one. The ; comes from the sql statement.

 Sql = "SELECT "& request.form("SELECT") &" FROM "& request.form("DATABASE") &" where "& request.form("WHERE_FIELD") &" = '"& strwhere &"';" 

I did remove it and still got Microsoft VBScript runtime error '800a01a8'

Object required: ''
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9585 (Level 1.17)  Reply to 9572
Reply Posted 5/13/2003 12:35:00 PM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD
My connection string is

dim db
set db = Server.CreateObject("ADODB.Connection")
db.Open "budget_demo"
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9588 (Level 1.18)  Reply to 9572
Reply Posted 5/13/2003 2:01:00 PM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD
Still end up with this error

Microsoft VBScript runtime error '800a01a8'
Object required: ''
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9590 (Level 1.19)  Reply to 9572
Reply Posted 5/14/2003 7:21:00 AM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD
TO jdschram
I would like to thank you for all of your assistance. You were so helpful and I would like to let you know that it is fixed and working perfectly. Thank you again greatly.
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9591 (Level 1.20)  Reply to 9572
Reply Posted 5/14/2003 8:40:00 AM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD
Not sure Confused
I started a fresh new file (I took your advise) recreated the connection to the datbase, and rewrote the code and it worked.
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9593 (Level 1.21)  Reply to 9572
Reply Posted 5/14/2003 9:02:00 AM
Location=MD  
Joined=19 years ago   MB Posts=25  
aka51
MD
LOL
NO way.
I am not touching it again it is working and that's all that I want.
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9595 (Level 1.22)  Reply to 9572
Reply Posted 5/14/2003 9:46:00 AM
Location=MD  
Joined=19 years ago   MB Posts=25  
Most Recent Post
aka51
MD
that is where it was then when you suggested I look into the connection string being the problem i moved it out and it worked. All my other files are using an include file, I am just a little apprehensive to mess with it again.
Peace love and happiness
 Posted 18 years ago (Thread Starter)
Comment Quote
About aka51 -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #9597 (Level 1.23)  Reply to 9572
Reply Posted 5/14/2003 11:29:00 AM
Location=MD  
Joined=19 years ago   MB Posts=25  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P184A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #9572 Counter
4412
Since 4/2/2008
Go ahead!   Use Us! Call: 916-726-5675  Or visit our new sales site: 
www.prestwood.com


©1995-2021 Prestwood IT Solutions.   [Security & Privacy]