simple database question.

[pnj]

ok, I'm NOT a programmer and never will be. I have built a few different apps using both VB 6 and ASP but those were years ago and very simple.


Now I have been asked to build a somewhat simple project for work.......(great:rolleyes: )

I have an access DB called 'Checker' and in it I have two tables.

the tables are called 'check' and 'stream'

I have an asp page that is mostly HTML but I need to open the DB and first check to see if a row has been filled. if it has, grab that data and display it in the page. if not, use the html that is hard coded. simple enough. I will do something like this
<% if rs.userName<>"" then
response.write rs.userName
else
'html goes here....

but I don't really know how to get my data out of the DB. we have a programer here at work but he's gone today so I can't ask him. but this is what he said I should do yesterday..


dim UserCommand, rs, connectionstring


connectionstring = "DSN=checker"

set usercommand=server.CreateObject("adodb.command")
usercommand.ActiveConnection = connectionstring
usercommand.CommandText = "select * from stream"
usercommand.CommandType = adCmdText

rs = usercommand.Execute



when I try and do a response.write and write out one of the fields from the DB I get an error that says
Error Type:
ADODB.Fields (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.


I'm sure that is not enough information for anyone to help me but if you can tell me what else you need, I will get it for you.

Thanks

[ZoSoo7]

We all know that error too much, hahaha. It means that in your asp page, you are referencing a column that doens't exist (probly a typo). For example if your column in your db is called employeeKey, and in your asp page you typed out Response.write oRs("employeeeeKey"), you will get that error. So check all your column names and make sure they are spelled correctly in your asp page. Good luck.

[pnj]

thanks for the reply.

ok. I scrapped that whole idea. the names were all the same though.

now all I'm trying to is use and INSERT to add records to a table.

one table with five fields.

I have been trying for hours and getting NOWHERE. aarrg.


all I need to do is open a DB connection and insert.

but nothing I try works. I've got a book next to me (wrox) and the code in it isn't working. I've searched the web and got nothing that works. I keep getting an error in my sql statement.

but it's a simple statement.

sql = "INSERT INTO streamCheck(username) values=('" & username & "')"

what is wrong w/ that?

strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("checker.mdb")
'Create the Connection object

set conn = server.createobject("adodb.connection")

conn.open strconn

'Create the recordset object
set rs = server.createobject("adodb.recordset")
'This statement opens the table so we can add a record notice the addnew
'The 2, 2 is how the table is opened there are many ways it can be opened
rs.open "check", conn, 2, 2


'Use the addnew method of the recordset object to add a record
rs.addnew



rs("username") = username
rs.update
rs.movelast




right now I get an error at the rs.open "check" line
it says "expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

huh? i'm so lost....

[ZoSoo7]

Ok, you have everything right, but try using the Access jet driver, change you're connection string to this

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = " & server.MapPath("checker.mdb")

This will work!!! Good job.