DataBase Connection

[fsqueeen]

i met 2 type of connection style:

1)
<%
'--- Connection to DataBase

Dim objConn
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0; &quot; & _
&quot;Data Source=C:\Inetpub\

http://wwwroot\kung.mdb&quot;

Const adOpenStatic = 3
Const adOpenForwardOnly = 3
Const adLockOptimistic = 3

%>

2)
<%
Dim MyDB, MyDSN, MyTable

MyTable = &quot;loan&quot;
set MyDB = Server.CreateObject(&quot;ADODB.Connection&quot

MyDSN = server.MapPath(&quot;database/cisco.mdb&quot
MyDB.Open &quot;driver={Microsoft Access Driver (*.mdb)};dbq=&quot; & MyDSN & &quot;;&quot;

%>

the 2nd one is my way whereas the other one was taken from a book.

my question:
- are these similar way?
- if so, y cant i use some function which used and worked properly with the 1 style?
eg: with 2 style, &quot;recordset.addnew&quot; is applicable, but for my way, it doesn't work. I use SQL.
- is it really the connection problem or is in fact the coding error?

 

[dds82]

Although both methods use ADO objects, the two connections are actually completely different.

Your connection uses the ADO drivers for Access. The book's connection uses the ODBC drivers. Both work with SQL, but there are some functions that ODBC supports (like AddNew) that ADO does not.

 

[fsqueeen]

is it?..but then i duno y i cant use the ODBC connection..i've tried but then the error page pop up and said the database is &quot;read-only&quot;, cant update. but then, i can retrieve data, like for login page, i can verify the user wif the DB, but when come to insert data, the error will pop up. i've check all the setting for the DB, it's ok.
i hv no choice but to use the ADO way.
but seems like hv some restrictions using the style.eg: i cant insert &quot;blank&quot; data into my table by &quot;insert into&quot; SQL..

 

[russellhancock]

hi,

you might not be able to use the &quot;insert into&quot; method with odbc connections if you do not have &quot;write&quot; permisions for the directory of the database.

Try to set the permissions for the directory to have &quot;write&quot; persisions, then try the ODBC method, it should work.

If this isn't clear then let me know.

Russell

 

[fsqueeen]

thanx..
but i'm still not so sure with ADO and ODBC concepts..as i know..we have ADO, DAO, RDO techniques..ODBC?. is it related to this?

Russell,
I dun get wat u mean by &quot;write &quot; permission. how am i going to set the permission?

Do u think the connection style by the book is better?

ps:I went to Macromedia MX launching seminar yesterday, COOL~~ q(^-^)p

 

[onpnt]

Check in this thread. It doesn't have a lot of answers but when you get down to the bottom there are some good facts on connection strings etc..
thread333-311100
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[fsqueeen]

ya..thanx..i read the tread..

so, wif the style u mentioned there, can i use function like &quot;addnew&quot; or &quot;update&quot; etc?..

 

[onpnt]

if you use the DSN connection then you will be able to with out a problem.
Like this
<%
set conn = server.createobject(&quot;adodb.connection&quot
conn.open &quot;DSN=MyDSN&quot;
Set Recordset=Server.CreateObject(&quot;ADODB.Recordset&quot
Recordset.Open &quot;loan&quot;, conn, 2, 2
'add a record to loan
Recordset.AddNew
Recordset(&quot;column&quot=(Request.Form(&quot;new&quot
Recordset.Update
Conn.Close
set Conn=nothing
%>


I've used this many times and it works well.
It's easy to trouble shoot do to being short and simple.

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[fsqueeen]

this is my dsn.inc file

<%
Dim MyDB, MyDSN,DBstr

set MyDB = Server.CreateObject(&quot;ADODB.Connection&quot

MyDSN = server.MapPath(&quot;database/cisco.mdb&quot
DBstr = &quot;driver={Microsoft Access Driver (*.mdb)};dbq=&quot; & MyDSN & &quot;;&quot;
MyDB.Open DBstr
%>

i think this is dsn-less connection, is it?
du u mean i can use addnew function as well as sql like insert into and so on?

 

[onpnt]

if you use the AddNew then there is no need for a insert statement. That's really all a AddNew does. The big diff here is you specify the field like this
Recordset(&quot;column&quot
and then tell it what to insert to that field
=Request.Form(&quot;or something&quot
then you do a update which actually inserts the records.

you are using a DSN-Less connection there but it's a little switched around.
try it like this
<%
Dim MyDB, MyDSN,DBstr
' try not to reference DB in the connectionstring.
' bad form and makes it a little confusing
set MyDB = Server.CreateObject(&quot;ADODB.Connection&quot
MyDSN = server.MapPath(&quot;database/cisco.mdb&quot
DBstr = &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; & MyDSN
MyDB.Open DBstr
%>
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[onpnt]

forgot to mention to add the / in the mapPath.
MyDSN = server.MapPath(&quot;/database/cisco.mdb&quot

it will owrk sometimes and I've seen it cause errors other times. jsut a good habit as well
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[fsqueeen]

ok. i understand wat u mean.
<%
Dim MyDB, MyDSN

set MyDB = Server.CreateObject(&quot;ADODB.Connection&quot

MyDSN = server.MapPath(&quot;/database/cisco.mdb&quot
MyDB.Open &quot;driver={Microsoft Access Driver (*.mdb)};dbq=&quot; & MyDSN & &quot;;&quot;

%>

is this correct?

 

[onpnt]

don't thin kthe ; is needed but I may be wrong. I usually write out the server.mappath here.
MyDB.Open &quot;driver={Microsoft Access Driver (*.mdb)};dbq=&quot; & MyDSN & &quot;;&quot;

we've actually gone through three different formats here but all will work.
just didn't use certain variables in this one.
I have not used the server.mappath in this format so if you have problems go back to the last way I wrote it.
I would think it will work though

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[onpnt]

I woudl also get used to the uppercase in the DRIVER and DBQ. I don't know if it matters but I really don't think I've seen it written lowercase before.
maybe someone will chime in and correct me.

&quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[fsqueeen]

ya..it works!!..=)
thanx a lot

 

[onpnt]

No Problem
I'm going to change my handle to DBconnBoy now I think
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[fsqueeen]

(^_^)

here i have another question..

how can i make sure that the pointer always go to the correct record? wat i mean is, an end user log on my system and start a new order. so, in his session, i always track on the order form he made, let say order no1.

every page he goes will bring the order no together.

Set rsLoan= Server.CreateObject (&quot;ADODB.RecordSet&quot
LoanSQL=&quot;SELECT * FROM order ORDER BY order_id DESC&quot; Set rsLoan = MyDB.execute(LoanSQL)

by this sql. the pointer will always go to the 1st record, which is the most recent record of the transaction, right?

but how about if the user delete all the items he ordered or delete the whole order form? if comes to the &quot;display record&quot; page, i worry the pointer will go to the next record, which is the second record in the table which had replace the 1st record since it has been deleted.

get wat i mean?

how can i make sure the pointer always remain at the order no1? even though the record been deleted, it will show the empty record?..

 

[onpnt]

well, when you add a new record it goes to the bottom of teh field. so if you had 20 records your next record added will be 21.
You can do a RS.MoveFirst which will move the curser to the first record but I don't think writing to it will work very well seeing as there will be records there already.
If your worried about deleting something or writing over it in a way that will not happen. either RS.addnew or SQL INSERT will always go to the next line to and then add it.

So if you wanted to stick with the record set just do this when the form gets submited in your add.asp (the action in the form tag)

Recordset.AddNew
Recordset(&quot;Name&quot=Request.Form(&quot;name&quot
Recordset(&quot;Number&quot=Request.Form(&quot;phone&quot
Recordset(&quot;Address&quot=Request.Form(&quot;add&quot
Recordset(&quot;City&quot=Request.Form(&quot;city&quot
Recordset.Update

now what this did is add four records to a table like this
AutoNum | Name | Number | Address | City
0001 name phone add city

then when another addnew is performed you will get this

AutoNum | Name | Number | Address | City
0001 name phone add city
0002 name phone add city
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[fsqueeen]

let say now i wana show the 1st record

set rsShow=Sever.CreateObject(&quot;ADODB.RecordSet&quot
strSQL=&quot;SELECT * FROM customer ORDER BY cust_id DESC&quot;
set rsShow=MyDB.execute(strSQL)

then start to show

<td>rsShow(&quot;name&quot</td> etc etc...

it will show the 2nd row of the table, right? as i know, the recordset always point to the 1st row in table by default, so now i arrange the cust_id to be descending. so the 1st record is 0002...i'll always get the latest record, which is the data input by the end user who login 'now', the latest record.

but then, if the user delete his record, in another page. let say delete.asp

sql= delete from customer where cust_id=0002

then now go back to show again.

now the recordset will point to 0001, and show the row, correct? but this is not the record !!

will this happen?

 

[onpnt]

If you're going to use SQL you use the SQl statement to create the recordset so you don't need to create it. You however do need your connection string in this bit.

Conn = Server.CreateObject(&quot;ADODB.Connection&quot
MyDSN = server.MapPath(&quot;/database/cisco.mdb&quot
MyDB.Open &quot;driver={Microsoft Access Driver (*.mdb)};dbq=&quot; & MyDSN
strSQL=&quot;SELECT * FROM customer ORDER BY cust_id DESC&quot;
set rsShow = MyDB.execute(strSQL)

not row...it would show the column

then to show them all you need to loop through them
<table>
<% Do While NOT rsShow.EOF %>
<td><%= rsShow(&quot;name&quot %></td>
<% rsShow.movenext
Loop %>
</table>

The recordset would be at 0001 BUT if the user caused another insert or addnew the records that would be added would not be 0001 but again 0002. thats the great thing about auto numbers.

did that make sense. I'm really not the best at explaining these

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com