By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,559 Members | 1,150 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,559 IT Pros & Developers. It's quick & easy.

ASP and SQL help - I don't know what I'm doing

P: 14
I don't know a lot about ASP and I know even less about SQL. I have been trying to search the web for information to be able to get done what I need. I've tried piecing things together from many different sites, adding my own info in where needed, but it just isn't working.

What I want to do is, using an existing database setup for a calendar application, pull information out of the database and display a title of an entry that corresponds with the current date.

http://www.godfreyfritzs.com

if you go to the address above, you will see that there is a sign graphic which has text over it that says an ice cream flavor. Right now I am using AJAX scripting to display a different HTML file for each day of the month, because the Flavor of the day changes every day. I want to pull the flavor of the day from the calendar, since the manager will have the flavors of the day in the calendar as events.

here's what i've got in a test file.

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. Set conn = Server.CreateObject("ADODB.Connection")
  4. conn.Open "Driver={SQL Server};Server=SERVERLOCATION;Database=DATABASENAME;Uid=MYUSERNAME;Pwd='MYPASSWORD';"
  5.  
  6. SQL ="SELECT event_title FROM events"
  7.  
  8.     Dim objRec
  9. Set objRec = Server.CreateObject("ADODB.Recordset")
  10. objRec.Open SQL, objConn, 0, 1, 2
  11.  
  12. %>
  13.  
I had this setup to test if I could even pull data from the database, to no avail:

Expand|Select|Wrap|Line Numbers
  1. Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
  2.  
  3. [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'USERNAME'.
  4.  
  5. /testing.asp, line 4
Apparently I've done something wrong, but I have no idea what. I don't understand what I am trying to do. I have read many things on the internet about ASP and SQL but they are no help. The lady I made that website for really likes how the flavor of the day shows up there, but for her to change the 31 files that correspond with the days of the month individually would be a mess for her to deal with. It would be a lot easier to insert an event into a day with the category of "Flavor of the Day" and then pull that day's Flavor out of the database, cause she would have one thing to do instead of lots and lots of things to do.

Any help would be appreciated.
Mar 17 '07 #1
Share this Question
Share on Google+
20 Replies


P: 14
OK, fixed a problem....had my password in these things ' ' ......

but now i get this message in the browser:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/testing.asp, line 10



here's my code again:

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. Set conn = Server.CreateObject("ADODB.Connection")
  4. conn.Open "Driver={SQL Server};Server=SERVERNAME;Database=DATABASENAME;Uid=USERNAME;Pwd=PASSWORD;"
  5.  
  6. SQL ="SELECT event_title FROM events"
  7.  
  8.     Dim objRec
  9. Set objRec = Server.CreateObject("ADODB.Recordset")
  10. objRec.Open SQL, objConn, 0, 1, 2
  11.  
  12. %>


so, to try and fix it I tried this (which I found on some website telling about asp and sql):

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. Set conn = Server.CreateObject("ADODB.Connection")
  4. conn.Open "Driver={SQL Server};Server=SERVERNAME;Database=DATABASENAME;Uid=USERNAME;Pwd=PASSWORD;"
  5.  
  6. SQL ="SELECT event_title FROM events"
  7.  
  8.     Dim objRec
  9. Set objRec = objConn.Execute(SQL)
  10. %>
But then I get this:

Microsoft VBScript runtime error '800a01a8'

Object required: 'objConn'

/testing.asp, line 9
Mar 17 '07 #2

jhardman
Expert 2.5K+
P: 3,405
Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. Set conn = Server.CreateObject("ADODB.Connection")
  4. conn.Open "Driver={SQL Server};Server=SERVERNAME;Database=DATABASENAME;Uid=USERNAME;Pwd=PASSWORD;"
  5.  
  6. SQL ="SELECT event_title FROM events"
  7.  
  8.     Dim objRec
  9. Set objRec = objConn.Execute(SQL)
  10. %>
But then I get this:

Microsoft VBScript runtime error '800a01a8'

Object required: 'objConn'

/testing.asp, line 9
Some people like to use the word "objConn" as a default name for a db connection, and others like to call it "conn". It doesn't really matter, you could call it "george" if you wanted, you just have to use the same name throughout. When you declared the connection object, you called it "conn" so you need to change every reference to say "conn" instead of "objConn", or declare it as "objConn" to start.

Jared
Mar 20 '07 #3

P: 14
Thanks for helping out.

except now i get this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'SELECT'.

/testing.asp, line 11

on this code

Expand|Select|Wrap|Line Numbers
  1. Dim conn
  2. Set conn = Server.CreateObject("ADODB.Connection")
  3. conn.Open "Driver={SQL Server};Server=p3swhsql-v03.shr.phx3.secureserver.net;Database=DB_1003645;Uid=****;Pwd=****;"
  4.  
  5. SQL ="SELECT event_title FROM events WHERE"
  6.  
  7. Dim objRec
  8. Set objRec = Server.CreateObject ("ADODB.Recordset")
  9. objRec.Open SQL, conn, 0, 1, 2
  10.  
  11. Response.Write objRec
  12.  
Edited by iam_clint reason: Removed username and password from code.
Mar 25 '07 #4

P: 14
sorry, i changed back to this:

Expand|Select|Wrap|Line Numbers
  1. Dim conn
  2. Set conn = Server.CreateObject("ADODB.Connection")
  3. conn.Open "Driver={SQL Server};Server=p3swhsql-v03.shr.phx3.secureserver.net;Database=DB_1003645;Uid=****l;Pwd=****;"
  4.  
  5. SQL = "SELECT event_title FROM events"
  6.  
  7. Dim objRec
  8. Set objRec = conn.Execute(SQL)
  9.  
  10. Response.Write objRec

and then I get this error:

Response object error 'ASP 0185 : 8002000e'

Missing Default Property

/testing.asp, line 0

A default property was not found for the object.

And I even tried changing this line

Expand|Select|Wrap|Line Numbers
  1. Response.Write objRec
to this

Expand|Select|Wrap|Line Numbers
  1. Response.Write SQL
but all that did was give me this outcome in plain text:

SELECT event_title FROM events

I don't understand what I'm going wrong. How come I can't pull this info from the table and display it on the page?

Edited by iam_clint reason: Removed username and password from code.
Mar 25 '07 #5

P: 14
YAY something worked!

Ok, after studying a couple of other ASP pages that accessed data from a SQL database as well as reading this sites forums on default datasets or something like that, I edited my code to the below:

Expand|Select|Wrap|Line Numbers
  1. Dim conn
  2. Set conn = Server.CreateObject("ADODB.Connection")
  3. conn.Open "Driver={SQL Server};Server=p3swhsql-v03.shr.phx3.secureserver.net;Database=DB_1003645;Uid=****;Pwd=****;"
  4.  
  5. SQL = "SELECT event_title FROM events WHERE category_id='4'"
  6.  
  7. Dim objRec
  8. Set objRec = Server.CreateObject("ADODB.Recordset")
  9. objRec.Open SQL,conn,0,1
  10.  
  11. Response.Write objRec.Fields.Item(0).Value
  12.  
  13. objRec.close
  14.  
  15. conn.close
  16. set conn=nothing

I got a result from my database! A correct result, too. Now what I want to do is display the item from that table that matches today's date. How do I query that? Is it still in the WHERE part or is it something else?

thanks for any help!
Mar 25 '07 #6

jhardman
Expert 2.5K+
P: 3,405
I got a result from my database! A correct result, too. Now what I want to do is display the item from that table that matches today's date. How do I query that? Is it still in the WHERE part or is it something else?
yes, the "WHERE" clause tells which records you want to access. you can add new parts to the WHERE clause very easily:
Expand|Select|Wrap|Line Numbers
  1. SQL = "SELECT event_title FROM events WHERE category_id='4'"
  2. SQL = SQL & " AND entryDate = " & date()
By the way, the SELECT statement tells which fields in the record you want to access (if you want to see them all, say "SELECT *") and the FROM clause tells which table or tables you want to look in (to look in more than one, say "FROM events, locations").
Let me know if this helps.

Jared
Mar 26 '07 #7

P: 14
here's the code

Expand|Select|Wrap|Line Numbers
  1. Dim conn
  2. Set conn = Server.CreateObject("ADODB.Connection")
  3. conn.Open "Driver={SQL Server};Server=p3swhsql-v03.shr.phx3.secureserver.net;Database=DB_1003645;Uid=***;Pwd=***;"
  4.  
  5. SQL = "SELECT event_title FROM events WHERE category_id = '4'"
  6. SQL = SQL & " AND event_date = " & date()
  7.  
  8. Dim objRec
  9. Set objRec = Server.CreateObject("ADODB.Recordset")
  10. objRec.Open SQL,conn
  11.  
  12. Response.Write objRec.Fields.Item(0).Value
  13.  
  14. objRec.close
  15.  
  16. conn.close
  17. set conn=nothing

here's the error

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/testing.asp, line 14

Is my query setup wrong? I know I have an entry in that table and column for the date and all... Should I use some type of code to make sure that my dates are in the same format? I'm not exactly sure how the calendar I use puts the date into the database.

Thanks for your help! :)


Edited by iam_clint reason: Removed username and password from code.
Mar 26 '07 #8

P: 14
OK, I found another article I think on this site about using dates and that person told the other guy to use the actual date of that day (or a date he wanted) and plug that into the WHERE to see if it even worked that way. I did this and it worked:

Expand|Select|Wrap|Line Numbers
  1. SQL = "SELECT event_title FROM events WHERE category_id = '4'"
  2. SQL = SQL & " AND event_date = '3/26/2007'"
So I know that I am on the right track with this. However, something isn't working when I try to use the current date (which would change each day). in the database, which is a SQL database, the 'type' for the date column is "datetime" if that helps any.

Thanks so much!
Mar 26 '07 #9

iam_clint
Expert 100+
P: 1,208
Expand|Select|Wrap|Line Numbers
  1. SQL = SQL & " AND event_date = '" & date() & "'"
this will work.

never post usernames or passwords anywhere.
Mar 26 '07 #10

P: 14
Thankyou all very much! It finally works... at least right now. I'll watch it to make sure that it works tomorrow and the next day etc. This is so great.

And the password thing... I totally forgot...just started copying my code and didn't even think about it. Thanks for tellin me and changing it.


Thanks again! :) :) :)
Mar 27 '07 #11

P: 14
If anyone is still out there and sees this thread, I need some more help.

I can't figure out how to change the script so I can display items from the database that match the current date as well as the next 5 or six days.

Can anyone tell me how to do this. My code is still the same as the last one posted here, above this post.

Thanks in advance.
Aug 29 '07 #12

jhardman
Expert 2.5K+
P: 3,405
try:
Expand|Select|Wrap|Line Numbers
  1. AND event_date BETWEEN #" & date() & "# AND #" '(put your other date here) & "#"
I'm not sure of the rules for when to use the "#" and when to use the single quote, but it seems like you are supposed to use "#" when enclosing dates in SQL, but it looks like you got it working with single quotes before, so you can try that as well.

Jared
Aug 29 '07 #13

markrawlingson
Expert 100+
P: 346
Jareds example should work.

For the second date i'd recommend using the dateAdd function

Expand|Select|Wrap|Line Numbers
  1. DateAdd("d",6,Date())
  2.  
That will add 6 days to whatever day it is when the page loads.
Aug 29 '07 #14

iam_clint
Expert 100+
P: 1,208
I believe this should work... its what they said but put in the example i provided before.
Expand|Select|Wrap|Line Numbers
  1. SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
  2.  
Aug 30 '07 #15

P: 14
Ok, obviously I am doing something wrong...

oh ya, and it has been a while hasn't it...

here's the script

Expand|Select|Wrap|Line Numbers
  1. Dim conn
  2. Set conn = Server.CreateObject("ADODB.Connection")
  3. conn.Open "Driver={SQL Server};Server=************;Database=************;Uid=************;Pwd=************;"
  4.  
  5. SQL = "SELECT event_title FROM events WHERE category_id = '4'"
  6. SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
  7. Dim objRec
  8. Set objRec = Server.CreateObject("ADODB.Recordset")
  9. objRec.Open SQL,conn,0,1
  10.  
  11. Response.Write objRec.Fields.Item(0).Value
  12.  
  13. objRec.close
  14.  
  15. conn.close
  16. set conn=nothing
What I would like to do is display information from a table, from one or more cells in the row, which is coming up in the next 7 to 10 days or so. For example:

Title of event
Date of event
Description of event


and then

Title of next event
Date of next event
Description of next event


The code above will bring up today's event but will not display the next 5 or so. How do I do this? I am thinking I need to do something with the "response.write" part right? But I can't figure it out.

Any more help would be greatly appreciated.
Feb 21 '08 #16

P: 14
I made some headway!

here's my code now, notice what I have done with the "response.write" stuff and my selection (SELECT).

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. Dim conn
  4. Set conn = Server.CreateObject("ADODB.Connection")
  5. conn.Open "Driver={SQL Server};Server=************;Database=************;Uid=************;Pwd=************;"
  6.  
  7. SQL = "SELECT event_title, event_date, event_desc FROM events WHERE category_id = '4'"
  8. SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
  9. Dim objRec
  10. Set objRec = Server.CreateObject("ADODB.Recordset")
  11. objRec.Open SQL,conn,0,1
  12.  
  13. Response.Write objRec.Fields.Item(0).Value
  14. Response.Write objRec.Fields.Item(1).Value
  15. Response.Write objRec.Fields.Item(2).Value
  16.  
  17. objRec.close
  18.  
  19. conn.close
  20. set conn=nothing
  21.  
  22. %>
so now I still need to know how to get the next couple of day's info out there. But I also need to know how to get this information into the body of the page so I can edit the HTML or use CSS and such to format it nicely, or maybe place it in a table and such.
Feb 21 '08 #17

P: 14
You'll all be proud of me (I hope). I got something working out. check it out here. I know there is some stuff I should probably move or delete but I'll do that later: http://www.shiversfrozencustard.com/testing2.asp

so here's my code:
Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. Dim conn
  4. Set conn = Server.CreateObject("ADODB.Connection")
  5. conn.Open "Driver={SQL Server};Server=*****;Database=*****;Uid=*****;Pwd=*****;"
  6.  
  7. SQL = "SELECT event_title, event_date, event_desc FROM events WHERE category_id = '4'"
  8. SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
  9. Dim objRec
  10. Set objRec = Server.CreateObject("ADODB.Recordset")
  11. objRec.Open SQL,conn,0,1
  12.  
  13. Response.Write objRec.Fields.Item(0).Value
  14. Response.Write objRec.Fields.Item(1).Value
  15. Response.Write objRec.Fields.Item(2).Value
  16.  
  17.  
  18.  
  19. %>
  20.  
  21. <%
  22. do until objRec.eof
  23. %>
  24. </p>
  25.     <table width="414" border="0" cellspacing="0" cellpadding="0" align="center">
  26.       <tr>
  27.         <td width="829">
  28.  
  29.         <div align="left">
  30.           <b><font face="Verdana" size="4">&nbsp;<%=objRec("event_title")%>
  31.           </font></b>
  32.         </div>
  33.  
  34.         <div align="left">
  35.           <b><font face="Verdana" size="1">&nbsp;<%=formatdatetime(objRec("event_date"),1)%>
  36.           </font></b>
  37.         </div>
  38.         </font></td>
  39.       </tr>
  40.       <tr>
  41.         <td width="829">
  42.         <div align="left">
  43.           <font face="Verdana" size="2">&nbsp;<%=objRec("event_desc")%><br>
  44.           <br>
  45. <%
  46. objRec.movenext
  47. loop
  48. %><%
  49. objRec.close
  50. set objRec=nothing
  51. conn.close
  52. set conn=nothing
  53. %> 
any tips or tricks would be much appreciated. I may have accomplished this but I am always open to suggestions from the pros.

Thanks for the help you gave me earlier. You kept me working on it and I finally got something! Yay us!
Feb 21 '08 #18

P: 14
problem...

I got it pretty much working how I want it to, except for one thing. I had some entries in the database (which is my calendar's database). I put in an entry for 2/20, 2/21, 2/22, 2/23, 2/25, 2/26, and 2/29. The code (below) has successfully pulled the first six entries out of the database. However, when I put in an entry for 2/24 (which I had skipped earlier) it did not act as I thought it should. I figured the last entry, for 2/26, would not be seen anymore and the entry for 2/24 would be stuck in right under the entry for 2/23. Instead, the code kept the first six entries and stuck the entry for 2/24 at the bottom. It will not place it at the top. Even after removing the entry for 2/20. What could be going wrong? Is it arranging them by the date and time they were placed into the database?

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. Dim conn
  4. Set conn = Server.CreateObject("ADODB.Connection")
  5. conn.Open "Driver={SQL Server};Server=*****;Database=*****;Uid=*****;Pwd=*****;"
  6.  
  7. SQL = "SELECT event_title, event_date, event_desc FROM events WHERE category_id = '4'"
  8. SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
  9. Dim objRec
  10. Set objRec = Server.CreateObject("ADODB.Recordset")
  11. objRec.Open SQL,conn,2,3
  12.  
  13. %>
  14.  
  15. <%
  16. do until objRec.eof
  17. %>
  18.  
  19. <table border="1" width="100%">
  20.     <tr>
  21.         <td><%=objRec("event_title")%></td>
  22.     </tr>
  23.     <tr>
  24.         <td><%=formatdatetime(objRec("event_date"),1)%></td>
  25.     </tr>
  26.     <tr>
  27.         <td><%=objRec("event_desc")%></td>
  28.     </tr>
  29. </table>
  30.  
  31. <%
  32. objRec.movenext
  33. loop
  34. %><%
  35. objRec.close
  36. set objRec=nothing
  37. conn.close
  38. set conn=nothing
  39. %>
Feb 21 '08 #19

jhardman
Expert 2.5K+
P: 3,405
problem...

I got it pretty much working how I want it to, except for one thing. I had some entries in the database (which is my calendar's database). I put in an entry for 2/20, 2/21, 2/22, 2/23, 2/25, 2/26, and 2/29. The code (below) has successfully pulled the first six entries out of the database. However, when I put in an entry for 2/24 (which I had skipped earlier) it did not act as I thought it should. I figured the last entry, for 2/26, would not be seen anymore and the entry for 2/24 would be stuck in right under the entry for 2/23. Instead, the code kept the first six entries and stuck the entry for 2/24 at the bottom. It will not place it at the top. Even after removing the entry for 2/20. What could be going wrong? Is it arranging them by the date and time they were placed into the database?
Yes, pretty much. If you want to sort by date you need to add this phrase to the end of the query:
Expand|Select|Wrap|Line Numbers
  1. " ORDER BY event_date"
you can also add key words "ASC" or "DESC" to specify which way you want it to sort, but the default should be earliest date at the top, and I think that is how you want it. Does this answer your question? Let me know if it works.

Jared
Feb 21 '08 #20

P: 14
I'm sorry I never came back and thanked you all for your help. I did get that site working, but soon handed it off to someone else...
Mar 8 '11 #21

Post your reply

Sign in to post your reply or Sign up for a free account.