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

[johnnyboy7]

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.

<%
 
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=SERVERLOCATION;Database=DATABASENAME;Uid=MYUSERNAME;Pwd='MYPASSWORD';"
 
SQL ="SELECT event_title FROM events"
 
    Dim objRec
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open SQL, objConn, 0, 1, 2
 
%>
 

I had this setup to test if I could even pull data from the database, to no avail:

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
 
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'USERNAME'.
 
/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.

[johnnyboy7]

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:

<%
 
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=SERVERNAME;Database=DATABASENAME;Uid=USERNAME;Pwd=PASSWORD;"
 
SQL ="SELECT event_title FROM events"
 
    Dim objRec
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open SQL, objConn, 0, 1, 2
 
%>

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

<%
 
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=SERVERNAME;Database=DATABASENAME;Uid=USERNAME;Pwd=PASSWORD;"
 
SQL ="SELECT event_title FROM events"
 
    Dim objRec
Set objRec = objConn.Execute(SQL)
%>

But then I get this:

Microsoft VBScript runtime error '800a01a8'

Object required: 'objConn'

/testing.asp, line 9

[jhardman]

<%
 
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=SERVERNAME;Database=DATABASENAME;Uid=USERNAME;Pwd=PASSWORD;"
 
SQL ="SELECT event_title FROM events"
 
    Dim objRec
Set objRec = objConn.Execute(SQL)
%>

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

[johnnyboy7]

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

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=p3swhsql-v03.shr.phx3.secureserver.net;Database=DB_1003645;Uid=****;Pwd=****;"
 
SQL ="SELECT event_title FROM events WHERE"
 
Dim objRec
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open SQL, conn, 0, 1, 2
 
Response.Write objRec
 

Edited by iam_clint reason: Removed username and password from code.

[johnnyboy7]

sorry, i changed back to this:

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=p3swhsql-v03.shr.phx3.secureserver.net;Database=DB_1003645;Uid=****l;Pwd=****;"
 
SQL = "SELECT event_title FROM events"
 
Dim objRec
Set objRec = conn.Execute(SQL)
 
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

Response.Write objRec

to this

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.

[johnnyboy7]

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:

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=p3swhsql-v03.shr.phx3.secureserver.net;Database=DB_1003645;Uid=****;Pwd=****;"
 
SQL = "SELECT event_title FROM events WHERE category_id='4'"
 
Dim objRec
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open SQL,conn,0,1
 
Response.Write objRec.Fields.Item(0).Value
 
objRec.close
 
conn.close
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!

[jhardman]

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:

SQL = "SELECT event_title FROM events WHERE category_id='4'"
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

[johnnyboy7]

here's the code

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=p3swhsql-v03.shr.phx3.secureserver.net;Database=DB_1003645;Uid=***;Pwd=***;"
 
SQL = "SELECT event_title FROM events WHERE category_id = '4'"
SQL = SQL & " AND event_date = " & date()
 
Dim objRec
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open SQL,conn
 
Response.Write objRec.Fields.Item(0).Value
 
objRec.close
 
conn.close
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.

[johnnyboy7]

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:

SQL = "SELECT event_title FROM events WHERE category_id = '4'"
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!

[iam_clint]

SQL = SQL & " AND event_date = '" & date() & "'"

this will work.

never post usernames or passwords anywhere.

[johnnyboy7]

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! :) :) :)

[johnnyboy7]

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.

[jhardman]

try:

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

[markrawlingson]

Jareds example should work.

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

DateAdd("d",6,Date())
 

That will add 6 days to whatever day it is when the page loads.

[iam_clint]

I believe this should work... its what they said but put in the example i provided before.

SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
 

[johnnyboy7]

Ok, obviously I am doing something wrong...

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

here's the script

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=************;Database=************;Uid=************;Pwd=************;"
 
SQL = "SELECT event_title FROM events WHERE category_id = '4'"
SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
Dim objRec
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open SQL,conn,0,1
 
Response.Write objRec.Fields.Item(0).Value
 
objRec.close
 
conn.close
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.

[johnnyboy7]

I made some headway!

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

<%
 
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=************;Database=************;Uid=************;Pwd=************;"
 
SQL = "SELECT event_title, event_date, event_desc FROM events WHERE category_id = '4'"
SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
Dim objRec
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open SQL,conn,0,1
 
Response.Write objRec.Fields.Item(0).Value
Response.Write objRec.Fields.Item(1).Value
Response.Write objRec.Fields.Item(2).Value
 
objRec.close
 
conn.close
set conn=nothing
 
%>

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.

[johnnyboy7]

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:

<%
 
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=*****;Database=*****;Uid=*****;Pwd=*****;"
 
SQL = "SELECT event_title, event_date, event_desc FROM events WHERE category_id = '4'"
SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
Dim objRec
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open SQL,conn,0,1
 
Response.Write objRec.Fields.Item(0).Value
Response.Write objRec.Fields.Item(1).Value
Response.Write objRec.Fields.Item(2).Value
 
 
 
%>
 
<%
do until objRec.eof
%>
</p>
    <table width="414" border="0" cellspacing="0" cellpadding="0" align="center">
      <tr>
        <td width="829">
 
        <div align="left">
          <b><font face="Verdana" size="4">&nbsp;<%=objRec("event_title")%>
          </font></b>
        </div>
 
        <div align="left">
          <b><font face="Verdana" size="1">&nbsp;<%=formatdatetime(objRec("event_date"),1)%>
          </font></b>
        </div>
        </font></td>
      </tr>
      <tr>
        <td width="829">
        <div align="left">
          <font face="Verdana" size="2">&nbsp;<%=objRec("event_desc")%><br>
          <br>
<%
objRec.movenext
loop
%><%
objRec.close
set objRec=nothing
conn.close
set conn=nothing
%> 

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!

[johnnyboy7]

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?

<%
 
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=*****;Database=*****;Uid=*****;Pwd=*****;"
 
SQL = "SELECT event_title, event_date, event_desc FROM events WHERE category_id = '4'"
SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,Date())  & "'"
Dim objRec
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open SQL,conn,2,3
 
%>
 
<%
do until objRec.eof
%>
 
<table border="1" width="100%">
    <tr>
        <td><%=objRec("event_title")%></td>
    </tr>
    <tr>
        <td><%=formatdatetime(objRec("event_date"),1)%></td>
    </tr>
    <tr>
        <td><%=objRec("event_desc")%></td>
    </tr>
</table>
 
<%
objRec.movenext
loop
%><%
objRec.close
set objRec=nothing
conn.close
set conn=nothing
%>

[jhardman]

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:

" 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

[johnnyboy7]

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...