Access Database and ASP please helpppp 1

[zick]

Hi

I am strugling with it over 3 month and nobody can help me so maybe u can.

Ok. I want to creat database on internet. I already got web hosting with ASP support. But i don't know how to intergrate(in details) MS Access database file with my ASP. I did prototype (simple) search engin on my PWS but i don't know how to set up it on internet. What about ODBC on server, I am not allow to set my Access ODBC conection. I can do on my PWS because i can go to ODBC and do it and then in my ASP link to that database through the ODBC but I can not do it on server (I have no idea what to do with it).

Please, please help me I am realy nervous, please forgive me but it bothers me so much.

 

[link9]

You can set up a DSN on the server, just as you have on PWS -- well, not EXACTLY the same way.

If your webhosting company does not offer an online control panel for setting them up, then you will need to upload the database to the webserver, note where you have put it, and then call their tech support to request them to set up the DSN for you --

As long as you set it up with the same DSN that you have set up on your local machine, then you won't even have to modify what you put on the server to work with that copy of the database.

good luck!
Paul Prewett

 

[zick]

Stone falt from my heart. Thanks to you link9 (Programmer)

You are my hero.!!!!! I got answere that i was looking for 3 longggg monthssss. YOU ARE THE BEST everybody should know that. Thank you, thank you, and one more time thank you.

I have still one more question to ask. Is it posible to use my PWS as Database source accessable from internet????/
I got rogers ISP and i have steady IP address so it should be possible to let people access my MS Access db on my computer. Any idea?? I am student and I want to lear the ASP very much like nothing else but i have not enought money to go to school but if you can only give me some hints I would be very appreciated.

I hope you understand my situation.

Your beginner ASP programmer zick

Thanks for all!!

 

[link9]

I think it's safe to say that that isn't a very good idea.

PWS is basically a *hack* to make Windows98 act like a server, but make no mistake... Windows98 is NOT a network operating system, and will crash and burn if you try to put too much of a load on it. Heck, it crashes and burns enough if you don't put too much of a load on it, don't ya think? ;-)

Your best bet, if you do have a static ip, and would like to play around, is just to run up to ye ole college book store (the undisputed king of killer software deals) and pick yourself up a student discounted copy of Windows2000 and set it up that way.

However, I caution you to be careful about allowing internet access to the pc that you use for your everyday comings and goings -- suffice it to say that it could cause problems.

You say you have a web service that supports ASP -- just stick with that. Develop on your machine - upload - repeat.

It's probably the safest bet.

good luck!
Paul Prewett

 

[zick]

Hi
Fast question,

I want to make database with ASP that will accept input from my ASP form. THe input will be ex.(hieght, age, location, and ""picture&quot. I have rought idea how to do it. Do you have any advice with codes or ex.?? How to upload pictures to my DB and how to retrive them back???
Thanks for your time. I hope
i was clear in my question??

Thanks a lot

 

[link9]

Yes, you are clear. It's fairly agreed upon (although I can't remember why) that it's not good to put actual pictures into a database, rather to store the address of the pictures where they can be found on a web server...

And as far as updating a database, there are a few things to think about...
First, you have to make sure that your recordset has the proper attributes set so that it is updateable...

Usually, this means cursorType = adOpenKeyset (though that's not the only one), and lockType = adLockOptimistic (again, not the only one) -- once you have those two options set, you can assign values to the recordset just like you would any other variable.

If you are wanting to add new records to the database, rather than update existing ones, you have to call the rs.addnew method before you go updating... and then...

rs.addnew
rs("fieldName1&quot = value1
rs("fieldName2&quot = value2
rs.update

If you are only updating, then just leave out the .addnew and you will write over whatever values are stored at the current cursor location in the recordset.

paul

 

[scroce]

I'd like to jump in here - maybe this thread could help a lot of people with a nice discussion here.

I'm trying to do something almost identical to Zick, and having similar issues. I'll post it here, and hopefully this might be able to help everyone. Anyone trying to update a database from a web page will most likely encounter what I'm about to write.

So: Issue#1

Link9, in your above example:

rs.addnew
rs("fieldName1&quot = value1
rs("fieldName2&quot = value2
rs.update

Suppose "value1" and "value2" are being pulled by a form that the user fills in. - typical right? then would the code look something like:

rs.addnew
rs("FieldName1&quot = Request.Form("Value1&quot
rs("FieldName2&quot = Request.Form("Value2&quot
rs.Update

Is this legal - will it work? it should shouldn't it?

Issue#2 - When you establish your DSN, you have to write code in order to access it and make updates to your database. I have written the following code:

Dim Connect
Dim rsComments
Dim Query

Set Connect = server.CreateObject ("ADODB.Connection&quot
Connect.Open "Affinity"
Set rsComments = server.CreateObject ("ADODB.Recordset&quot
Query = "SELECT * FROM tblContacts"
rsComments.Open query, connect, adOpenStatic, _
adLockOptimistic

Well, the problem is that it keeps blowing up, with the error message saying "variable adOpenStaic not recognized".

What's wrong with this code? I have a feeling it's a syntax error but I'm pulling hair out trying to find it.

steve

 

[link9]

You need the adovbs include file -- it has all the contant declarations in it -- either that or you need to declare your constants explicitly before you use them -- the server doesn't understand adOpenStatic until it is told what it is (3) --

Just as an aside, I have sometimes had a connection attempt blow up if I didn't tell it "DSN=myDSN" -- not all the time, but better safe than sorry, right?

you can get that file here:

http://www.adodb.com/adovbs.asp

and it's a good one to have around --

you can include it in your page by saying:
<!-- #include file=path/adovbs.inc -->

hope it helps!
paul

 

[zick]

Hi that's me again

I have read scroce (MIS) message and I can not understand the quoted lins:

Set Connect = server.CreateObject (&quot;ADODB.Connection&quot
Connect.Open &quot;Affinity&quot;
Set rsComments = server.CreateObject (&quot;ADODB.Recordset&quot
Query = &quot;SELECT * FROM tblContacts&quot;

&quot;&quot;rsComments.Open query, connect, adOpenStatic, _
adLockOptimistic&quot;&quot;

Do you thinks you can explain them in details for me?
if it is no problem for you guys??

I have so many question to ask and if I bored you bye asking them please let me know and I will stop!

Any way Thanks!!

 

[link9]

The quoted line you have referenced is a shortcut to the following -- scroce is declaring the attributes of the recordset at the same time it's being opened... here is the longform...

rsComments.ActiveConnection = connect
rsComments.CursorType = adOpenStatic
rsComments.LockType = adLockOptimistic
rsComments.Open query

First, he says, I want my recordset to use the connection object, 'connect'
Then, he states that he would like a static cursor type
Then, he asks for the lock type to be optimistic
And finally, he passes the query string to the recordset object, and retrieves his data

It's just a shortcut, but I think it's better to do it the long way until you have a good feel for what each means and how you use them to achieve what you want --

 

[scroce]

Hi link9.

Thanks for the reply. I didn't know that was even a shortcut for longer code. I thought that the syntax dictated the arguments be nested in the parenthases. I agree with your idea of doing things the long way first. I think

And I do appreciate the info on the adovbs file b/c I have been confused on what exactly that was supposed to do. I'll give that a looksee and post back with results.

Also , I request some clarification on what's below:

rs.addnew
rs(&quot;fieldName1&quot = value1
rs(&quot;fieldName2&quot = value2
rs.update

Suppose &quot;value1&quot; and &quot;value2&quot; are being pulled by a form that the user fills in. - typical right? then would the code look something like:

rs.addnew
rs(&quot;FieldName1&quot = Request.Form(&quot;Value1&quot
rs(&quot;FieldName2&quot = Request.Form(&quot;Value2&quot
rs.Update

I haven't had a chance to try it yet, but I should be able to pull values from fields on a form and dump them into my recorset, shouldn't I?


and to Zick,

to give you a more general idea of what I'm trying to do with that code, think of it this way (somebody correct me if I'm wrong or feel free to elaborate)

A web site can't talk directly to an access database. You need to set up a &quot;translator&quot; called a DSN short for Data Source Name (i think). You do this by going into control panel ODBC applet. The DSN points to your database. In my example, the name of the DSN is called &quot;affinity&quot;, which in turn points to an access database called &quot;affinity.mdb&quot;

The code tells the database to open, and to run a query that opens a table which is called &quot;tblContacts&quot;, just as if I were operating the database manually.

An open query is called a recordset, and it has characteristics like &quot;Static&quot; and &quot;Optimistic&quot; which have to do with whether or not other people can see your changes or can make changes to a record while someone else is in the database at the same time.

Anyway, you have to tell the computer all of this information, and if you leave any out, it won't work, as seems to be the case with me and the adovbs file.

 

[link9]

Here's a FAQ on database connections -- faq333-178

And watch for another one that I'm currently writing on the three most important recordset properties -- lockType, cursorType, and cursorLocation -- the &quot;optimistic&quot; and all that jazz -- hopefully will be up today, and if not, then I'll definitely get it written this weekend...

As far as your reading the form values, scroce, yes, you can certainly do it that way. It poses a few issues, however, that you should think about before you go assigning it directly to the recordset.

You have no control over what a user enters into the form fields... but you do have control over what you assign to your recordset. See where I'm going with this?

You can allow nullable fields in your database, and if you are assigning the values straight from the form fields and what the user entered, then you should do that. However, you have to think if you really WANT to allow nulls in those fields, and if not, then what you would like to enter in there if the user didn't enter a value for that field (if anything at all!).

So, the way that I normally assign values to fields is to say something like this:

dim thisValue
thisValue = request.form(&quot;thisValue&quot
if thisValue = &quot;&quot; then
thisValue = &quot;notEntered&quot;
end if
rs(&quot;thisValue&quot = thisValue

See, so you made sure that there was something in there before you assigned it to your recordset. If you don't, and you have fairly stringent controls on your data, then it can show your user a real nice ODBC error message that they can print and hang onto. ;-)

 

[scroce]

OK - I promised I would post back.

link9 's response about the absence of adovbs.inc was right on it.

Before this, I was unclear of the exact purpose of this file.

For anyone who might be feeling the same way, I also found a link you might find helpful.

http://www.asp101.com/articles/john/adovbs/default.asp

 

[esearing]

Zick,

Invest in the following books: &quot;Sam's Teach Yourself ASP in 21 Days&quot; or &quot;Instant ASP Scripts&quot;

Both of these books give excellent examples of what you are trying to do and even provide the code to do it. Sams dedicates 5 chapters to Databases and Forms.

Eric Searing
eric.searing@wcom.com