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:
[code=asp]AND event_date BETWEEN #" & date() & "# AND #" '(put your other date here) & "#"[/CODE]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

[CODE=asp]
DateAdd("d",6,D ate())
[/CODE]

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.
[CODE=asp]
SQL = SQL & " AND event_date BETWEEN '" & date() & "' AND '" & DateAdd("d",6,D ate()) & "'"
[/CODE]