ADODB connection

PFairhurst · Jul 30, 2002

I have a web page which loads up information from an Access database with an ADODB connection, but everytime I load up the page it asks me whether I want to connect to the database. Is there a way to do this automatically and not have to click on 'Yes' everytime I load the page up?

Any help would be great. Thanks
Pete

cornboy88 · Jul 30, 2002

This code uses odbc, but it might help you. Also here is a link to some examples.

http://www.able-consulting.com/ADO_Conn.htm

Set Conn = Nothing
Set Conn = Server.CreateObject("ADODB.Connection&quot

Conn.ConnectionTimeout = 300
Conn.Open "dsn=dsnname",
"username",
"password"
Set rs=Server.CreateObject("ADODB.Recordset&quot

Don't forget to close what you open.

cornboy88
[noevil]

PFairhurst · Jul 30, 2002

Sorry, should have put this in my first message. My connection connects fine and loads up of the correct information from my recordsets but when I first load up the page I get 'This page is accessing a data source on another domain. Do you want to allow this?' and I do but I want to do it automatically without clicking yes everytime.
Pete

onpnt · Jul 30, 2002

give up the code PFairhurst, I help at your own risk, if I brake it sorry! But if I fixed it, let me know with a

admin@onpntwebdesigns.com

PFairhurst · Jul 30, 2002

Here's the code for my database connection

<html>
<head>
<title>Planner</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script type="text/vbscript">
dim rsSet
dim rsWeek1
dim rsWeek2
sub loadDB ()
set cnn = CreateObject("ADODB.connection&quot

cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.open "\\hill\vb\emp.mdb"
set rsSet = CreateObject("ADODB.recordset&quot

rsSet.CursorType = adOpenDynamic
rsSet.open "Employees",cnn
set rsWeek1 = CreateObject("ADODB.recordset&quot

rsWeek1.activeConnection = cnn
rsWeek1.LockType = 4
rsWeek1.CursorType = adOpenDynamic
rsWeek1.open "week1",cnn
set rsWeek2 = CreateObject("ADODB.recordset&quot

rsWeek2.CursorType = adOpenDynamic
rsWeek2.LockType = 4
rsWeek2.open "week2",cnn
end sub

onpnt · Jul 30, 2002

are you trying to do this on the client side script?

Can't do it that way. has to be a server side scripting.

looks like all you need to do is change the <script tags to <% %> and save the page as a .asp I help at your own risk, if I brake it sorry! But if I fixed it, let me know with a

admin@onpntwebdesigns.com

PFairhurst · Jul 30, 2002

Can't seem to get it to work. Do you mean to change
<script type="text/vbscript">
</script>

to

<%

%>?

onpnt · Jul 30, 2002

yes but there's a little more to it then that.

In order to test asp pages you will need to have IIS installed or PWS. then you will need to test the pages from the

http://wwwroot

folder.

Go to administrative tools in your control panel and see if you have a icon for IIS ()internet information services) if you do not you will need to install this with your windows installation cd via add/remove programs.

let me know if you need assistance with that. What OS are you running? I help at your own risk, if I brake it sorry! But if I fixed it, let me know with a

admin@onpntwebdesigns.com

PFairhurst · Jul 31, 2002

I've tried what you said (I think!) and it's not connecting to the database anymore. I'm pretty new to VBscript and haven't quite got the hang of it yet. Can you see if it is something to do with my code. Thanks for all your help.
Pete

<html>
<head>
<title>Planner</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<% type="text/vbscript"

dim rsSet
dim rsWeek1
dim rsWeek2
sub awayLoad ()
if monAMaway.value = "" then
msgbox "Away text box is empty please fill it in",64
Exit sub
End if
monPMaway.value = monAMaway.value
tueAMaway.value = monAMaway.value
tuePMaway.value = monAMaway.value
wedAMaway.value = monAMaway.value
wedPMaway.value = monAMaway.value
thursAMaway.value = monAMaway.value
thursPMaway.value = monAMaway.value
friAMaway.value = monAMaway.value
friPMaway.value = monAMaway.value
End sub
sub loadDB ()
set cnn = CreateObject("ADODB.connection&quot

cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.open "\\hill\vb\emp.mdb"
set rsSet = CreateObject("ADODB.recordset&quot

rsSet.CursorType = adOpenDynamic
rsSet.open "Employees",cnn
set rsWeek1 = CreateObject("ADODB.recordset&quot

rsWeek1.activeConnection = cnn
rsWeek1.LockType = 4
rsWeek1.CursorType = adOpenDynamic
rsWeek1.open "week1",cnn
set rsWeek2 = CreateObject("ADODB.recordset&quot

rsWeek2.CursorType = adOpenDynamic
rsWeek2.LockType = 4
rsWeek2.open "week2",cnn
end sub
sub allClick()

if allWeek(0).checked Then
monAM.value = "away"
monPM.value = "away"
tueAM.value = "away"
tuePM.value = "away"
wedAM.value = "away"
wedPM.value = "away"
thursAM.value = "away"
thursPM.value = "away"
friAM.value = "away"
friPM.value = "away"

End if
if allWeek(1).checked Then
monAM.value = "holiday"
monPM.value = "holiday"
tueAM.value = "holiday"
tuePM.value = "holiday"
wedAM.value = "holiday"
wedPM.value = "holiday"
thursAM.value = "holiday"
thursPM.value = "holiday"
friAM.value = "holiday"
friPM.value = "holiday"
End if
if allWeek(2).checked Then
monAM.value = "home"
monPM.value = "home"
tueAM.value = "home"
tuePM.value = "home"
wedAM.value = "home"
wedPM.value = "home"
thursAM.value = "home"
thursPM.value = "home"
friAM.value = "home"
friPM.value = "home"
End if
if allWeek(3).checked Then
monAM.value = "office"
monPM.value = "office"
tueAM.value = "office"
tuePM.value = "office"
wedAM.value = "office"
wedPM.value = "office"
thursAM.value = "office"
thursPM.value = "office"
friAM.value = "office"
friPM.value = "office"
End if

End sub
sub displayRes

if monAll.checked Then
monPM.value = monAM.value
monPMaway.value = monAMaway.value
End if
if tueAll.checked Then
tuePM.value = tueAM.value
tuePMaway.value = tueAMaway.value
End if
if wedAll.checked Then
wedPM.value = wedAM.value
wedPMaway.value = wedAMaway.value
End if
if thursAll.checked Then
thursPM.value = thursAM.value
thursPMaway.value = thursAMaway.value
End if
if friAll.checked Then
friPM.value = friAM.value
friPMaway.value = friAMaway.value
End if

End sub
Function returnValue (strName,strAway)
if strAway = "home" Then
returnValue = "home"
Exit Function
End if
if strAway = "holiday" Then
returnValue = "holiday"
Exit Function
End if
if strAway = "office" Then
returnValue = "office"
Exit Function
End if
if strAway = "away" Then
select case (strName)
case "mon am"
returnValue = monAMaway.value
case "mon pm"
returnValue = monPMaway.value
case "tues am"
returnValue = tueAMaway.value
case "tues pm"
returnValue = tuePMaway.value
case "wed am"
returnValue = wedAMaway.value
case "wed pm"
returnValue = wedPMaway.value
case "thurs am"
returnValue = thursAMaway.value
case "thurs pm"
returnValue = thursPMaway.value
case "fri am"
returnValue = friAMaway.value
case "fri pm"
returnValue= friPMaway.value
case else
returnValue = "away"
End select
End if

End Function
Function check_pin(pin)
check_pin = false
if cdbl(pin) = cdbl(code.value) Then
check_pin = true
end if
End Function
sub submit_OnClick()
strNo = searchExec(nameSelect.value)
if check_pin(rsSet("pin number&quot

) = false Then
msgbox "Pin Code Invalid",64
Code.value = ""
Exit sub
End if
Code.value = ""
if Cstr(rsWeek1("date&quot

) = weekselect.options(0).value Then

searchWeek(strNo)
rsWeek1("mon am&quot

= returnValue ("mon am",monAM.value)
rsWeek1("mon pm&quot

= returnValue ("mon pm",monPM.value)
rsWeek1("tues am&quot

= returnValue ("tues am",tueAM.value)
rsWeek1("tues pm&quot

= returnValue ("tues pm",tuePM.value)
rsWeek1("wed am&quot

= returnValue ("wed am",wedAM.value)
rsWeek1("wed pm&quot

= returnValue ("wed pm",wedPM.value)
rsWeek1("thurs am&quot

= returnValue ("thurs am",thursAM.value)
rsWeek1("thurs pm&quot

= returnValue ("thurs pm",thursPM.value)
rsWeek1("fri am&quot

= returnValue ("fri am",friAM.value)
rsWeek1("fri pm&quot

= returnValue ("fri pm",friPM.value)
rsWeek1.UpdateBatch
msgbox "Successfully Updated",64
Else
searchWeekA(strNo)
rsWeek2("mon am&quot

= returnValue ("mon am",monAM.value)
rsWeek2("mon pm&quot

= returnValue ("mon pm",monPM.value)
rsWeek2("tues am&quot

= returnValue ("tues am",tueAM.value)
rsWeek2("tues pm&quot

= returnValue ("tues pm",tuePM.value)
rsWeek2("wed am&quot

= returnValue ("wed am",wedAM.value)
rsWeek2("wed pm&quot

= returnValue ("wed pm",wedPM.value)
rsWeek2("thurs am&quot

= returnValue ("thurs am",thursAM.value)
rsWeek2("thurs pm&quot

= returnValue ("thurs pm",thursPM.value)
rsWeek2("fri am&quot

= returnValue ("fri am",friAM.value)
rsWeek2("fri pm&quot

= returnValue ("fri pm",friPM.value)
rsWeek2.updateBatch
msgbox "Successfully Updated",64
End if

End sub

Function searchExec (name)
strPos = InStr(name," &quot

surname = Right(name,Len(name) - strPos)
first = Left(name, strPos)
rsSet.moveFirst
do
if rsSet("LastName&quot

= Trim(surname) AND rsSet("FirstName&quot

= Trim(first) Then
searchExec = rsSet("EmployeeNumber&quot

Exit Function
End if
rsSet.moveNext
loop until rsSet.EOF
searchExec = 0

End Function

sub searchWeek(strNo)
rsWeek1.MoveFirst
do
if rsWeek1("EmployeeNumber&quot

= strNo Then
Exit sub
End if
rsWeek1.moveNext
loop until rsWeek1.EOF

End sub
sub searchWeekA(strNo)

rsWeek2.moveFirst
do
if rsWeek2("EmployeeNumber&quot

= strNo Then
Exit sub
End if
rsWeek2.moveNext
loop until rsWeek2.EOF

End sub
%>
</head>

onpnt · Jul 31, 2002

this is what you need to do.
I'm assuming this is only intended for IE users seeing as you are using client side vbscript for your validation.

first thing
you cannot use the msgbox function via server side.

take this line out of the <% %> type="text/vbscript"
asp defaults to VBScript

Take all of your validation and place it in your form checking the conditions that need to be met in order to submit the form. You can use the msgbox function via client side.

You cannot reference the record set information unless you connect to the database in the beginning of the page. Not neccesary though as you'll see in a minute

take all of the server side code that is to be run. (all code within the <% %>
save it as a seperate file with the .asp extension.

place in your form tag the action attribute
<form method="post" action="db.asp" onSubmit="do val first()">

still with me? [lol]

here's the syntax of what you are doing
form filled out by user -->
validation is run via client side and returns false on conditions -->
all good submit values to DB and run the DB.asp to insert etc.-->
here's where if you need to do any validation while submiting to the DB comes in.
If you have a condition that is not met or met in error do
<%
Response.write "A error has occured, check entries!"
Response.write("<input type='button' value='OK'&quot

Response.write("onClick='vbscript:history.go(-1)'>&quot

or you can use
Response.redirect "form.asp"

this writes the the page A error has occured, check entries! and on clicking the OK button sends them back to the form or the last page to correct as needed.

I'll take a look at the code and change it in a bit and post it if you havn't done so already. Got to work though!

hope that helps I help at your own risk, if I brake it sorry! But if I fixed it, let me know with a

admin@onpntwebdesigns.com

onpnt · Jul 31, 2002

try this to open the DB
cnn.open "Driver=Microsoft Access Driver (*.mdb); DBQ=" & server.mappath("\hill\vb\emp.mdb&quot

& ";"

remember though to test this on IIS you need to exact path on your system then change to the server path when uploading
so

on your system
cnn.open "Driver=Microsoft Access Driver (*.mdb); DBQ=" & server.mappath("C:\hill\vb\emp.mdb&quot

& ";" I help at your own risk, if I brake it sorry! But if I fixed it, let me know with a

admin@onpntwebdesigns.com

PFairhurst · Aug 1, 2002

Thank you! You've solved most of my problems and it's starting to look better, but when I load up the db.asp (the server side) it doesn't connect to the database it just says
'[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'

so I then used microsft jet as the provider and I got the message 'Could not find installable ISAM'. Sorry to be such a pain. Pete

onpnt · Aug 1, 2002

teh only time I've ever seen this error or actually jsut heard of it is a result in the driver files missing for access.

try reinstalling access or office.

you can also try putting the DB in the

http://wwwroot

folder with the asp page. Just in case the path is incorrect. then all you need is
cnn.open "Driver=Microsoft Access Driver (*.mdb); DBQ=" & server.mappath("\emp.mdb&quot

& ";" I help at your own risk, if I brake it sorry! But if I fixed it, let me know with a

admin@onpntwebdesigns.com

onpnt · Aug 1, 2002

you can also try this conenction string.
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("\emp.mdb&quot

& ";"

this is also a dsn-less connection string I help at your own risk, if I brake it sorry! But if I fixed it, let me know with a

admin@onpntwebdesigns.com

PFairhurst · Aug 2, 2002

Finally got a connection string that SEEMS to connect in

set conn = Server.CreateObject("ADODB.connection&quot

conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=\\hill\vb\emp.mdb; DefaultDir=\\hill\vb; uid=sa;pwd=;DriverID=25;FIL=MSAccess;"
set rsSet = server.CreateObject("ADODB.recordset&quot

rsSet.open "Employees", conn
set rsWeek1 = server.CreateObject("ADODB.recordset&quot

set rsWeek2 = server.CreateObject("ADODB.recordset&quot

rsWeek1.LockType = adLockBatchOptimistic
rsWeek2.LockType = adLockBatchOptimistic
rsWeek1.open "week1" , conn
rsWeek2.open "week2" , conn

but .LockType = adLockBatchOptimistic does not seem to be recognised. Is this anything to do with the connection or just VBscript? If so, is there any other way of saving changes to the database? Aslo, how do I access the variable from the client side form in the server side asp file?
Any help would be great, thank you.
Pete

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ADODB connection

PFairhurst

Programmer

cornboy88

Programmer

PFairhurst

Programmer

onpnt

Programmer

PFairhurst

Programmer

onpnt

Programmer

PFairhurst

Programmer

onpnt

Programmer

PFairhurst

Programmer

onpnt

Programmer

onpnt

Programmer

PFairhurst

Programmer

onpnt

Programmer

onpnt

Programmer

PFairhurst

Programmer

Similar threads

Part and Inventory Search

Sponsor