burntout
asked on
Connecting to a database on the web
I have a database on my website and I want to write a program that can connect to it. I have never used any of the database features of VB so you will have to keep it simple. The only Idea I have so far it that I could make and ASP on the server that my program could talk to. But I'd rather not do that unless I have to.
If your database contain some secure data best solution to have server side and client side application to prevent unauthorized database usage...
Please inform what kind of database you plan to use, of course I can show you both solutions but it will takes time...
There are some tricks in remote database access :))
Sincerely yours,
Crin
Please inform what kind of database you plan to use, of course I can show you both solutions but it will takes time...
There are some tricks in remote database access :))
Sincerely yours,
Crin
ASKER
Adjusted points from 350 to 500
ASKER
I would like to know both ways.... I will increase points to 500 for your trouble.
Oops :)) Thank you for extra points, but I was able to help for less :))
Also I asked about database usage because implementation is really depends on it (sorry, english is not my native language)
Do you need to write to db? or just to read?
How big is your db?
The problem is that "connect to database" means random access to data, http doesn't provide it. So:
If your db is REALLY small and have no secure data - just download it with client app and work with local copy.
Otherwise you NEED to design server application.
Secure notes:
Dont forget to place db to locked for everyone but administrator folder under wwwroot to prevent db download. Grant administrator permissions to your server application to allow it to work with your db.
If your db store ANY information about users or ANY money - use https instead http.
Sincerely yours,
Crin
PS. The topic is clear for me, but there is a lot of information that COULD be interesting for you, so I don't know what REALLY interesting for you, please specify more questions :)))
Also I asked about database usage because implementation is really depends on it (sorry, english is not my native language)
Do you need to write to db? or just to read?
How big is your db?
The problem is that "connect to database" means random access to data, http doesn't provide it. So:
If your db is REALLY small and have no secure data - just download it with client app and work with local copy.
Otherwise you NEED to design server application.
Secure notes:
Dont forget to place db to locked for everyone but administrator folder under wwwroot to prevent db download. Grant administrator permissions to your server application to allow it to work with your db.
If your db store ANY information about users or ANY money - use https instead http.
Sincerely yours,
Crin
PS. The topic is clear for me, but there is a lot of information that COULD be interesting for you, so I don't know what REALLY interesting for you, please specify more questions :)))
ASKER
I need to be able to read/Write and I know that I don't want to download the datbase every time (too big).. I want to be able to give the users of my website the program, but not access to everything in the db.
Do you know anything about RDO, I was browsing thru the VB help file, it seems like it might be what im looking for but im not sure. If it would work I could use an example.
Do you know anything about RDO, I was browsing thru the VB help file, it seems like it might be what im looking for but im not sure. If it would work I could use an example.
ASKER
BTW the data base is a access 97 DB
have a look at this sample:
http://msdn.microsoft.com/code/default.asp?PP=/MSDN-FILES/EN-US/Code%20Examples/Visual%20Studio__%20Tools%20%26%20Languages/Visual%20Basic/NTier%20Internet%20App/toc.xml&tocPath=CC-2-3-1-33&URL=/code/sample.asp?url=/MSDN-FILES/EN-US/Code%20Examples/Visual%20Studio__%20Tools%20%26%20Languages/Visual%20Basic/NTier%20Internet%20App/MsdnCompositeDoc.xml
it enables accessing a database on a web server through the web. i implemented it on our software and it works great
http://msdn.microsoft.com/code/default.asp?PP=/MSDN-FILES/EN-US/Code%20Examples/Visual%20Studio__%20Tools%20%26%20Languages/Visual%20Basic/NTier%20Internet%20App/toc.xml&tocPath=CC-2-3-1-33&URL=/code/sample.asp?url=/MSDN-FILES/EN-US/Code%20Examples/Visual%20Studio__%20Tools%20%26%20Languages/Visual%20Basic/NTier%20Internet%20App/MsdnCompositeDoc.xml
it enables accessing a database on a web server through the web. i implemented it on our software and it works great
No matter what db type exact, the question is 'technology'...
RDO provide you access to ODBC server...
In my opinion, it's better to write own server... just a feeling... also some experience in this field (see http://www.inetlab.com WebShop that stores db the way I said)
By the way, forgot to say in previous message: set the folder permissions where script will be placed to executable, not readable for everyone.
set database folder permissions to:
everyone - no access
admin - read/write
set your script as admin
Sincerely yours,
Crin
RDO provide you access to ODBC server...
In my opinion, it's better to write own server... just a feeling... also some experience in this field (see http://www.inetlab.com WebShop that stores db the way I said)
By the way, forgot to say in previous message: set the folder permissions where script will be placed to executable, not readable for everyone.
set database folder permissions to:
everyone - no access
admin - read/write
set your script as admin
Sincerely yours,
Crin
ASKER
DanAvni, in the samples when they say
HTTP://MyComputerName
Do they want the server URL or am I supose to put HTTP://MyIP
I am looking through the sample projects now, they are a little over my head, I don't really understand how the information is getting send back and forth, or how the DLL knows which fuctions to call on a request..
HTTP://MyComputerName
Do they want the server URL or am I supose to put HTTP://MyIP
I am looking through the sample projects now, they are a little over my head, I don't really understand how the information is getting send back and forth, or how the DLL knows which fuctions to call on a request..
read the documentation. it's all there.
the code is intended for advanced users because it involves alot of RDO and VB and classes and changing the registry and knowing the IIS and how to work with it.
the code is intended for advanced users because it involves alot of RDO and VB and classes and changing the registry and knowing the IIS and how to work with it.
burntout, that is why I asked you about db appliance...
On my opinion for you it's better to create own server app that will manage your client app requests...
Sincerely yours,
Crin
On my opinion for you it's better to create own server app that will manage your client app requests...
Sincerely yours,
Crin
dan,
for this amount of points you should take the time to explain it, or otherwise leave your answer as a comment so other experts see it and can provide sound advice
for this amount of points you should take the time to explain it, or otherwise leave your answer as a comment so other experts see it and can provide sound advice
ASKER
Sorry, I know your answer is right, but azrasound is right too.
This is why i set the points to 500 that way you can spend a little extra time and get the same amount of points that you could get for 3 or more questions.
This is why i set the points to 500 that way you can spend a little extra time and get the same amount of points that you could get for 3 or more questions.
So delete the question and repost it in the ASP section.
Here is samples how can You access db from ASP, more likely as in VB.
<!--#include file="adovbs.inc"-->
<%
'for one query we give max 60sec
execTimeout=60
'we have 1 query will be executed
execCount=1
'max +5 sec for script to execute
scriptTimeout=execCount*ex ecTimeout+ 5
'connection parameters
userData="UID=admin;PWD=ni mda"
'if you use access by provider
dbPath="DBQ=C:\Test.mdb;"
conn_string="PROVIDER=MSDA SQL;" & _
"DRIVER={Microsoft Access Driver (*.mdb)};" &_
dbPath &_
userData
'or if You use ODBC:
dbPath="DSN=SysDSNName;"
conn_str=dbPath &_
userData
Set connObj=Server.CreateObjec t("ADODB.C onnection" )
connObj.Open conn_string
'example using select query
Set commandObj=Server.CreateOb ject("ADOD B.Command" )
commandObj.ActiveConnectio n=connObj
commandObj.CommandTimeout= execTimeou t
commandObj.CommandType=adC mdStoredPr oc
Select Case request("defMode")
'test Select
case "0"
'------------------------- ---------- ---------- ---------- ---------- -------
'our select query is
'Name: SelectByid
commandObj.CommandText="Se lectByid"
'
'SELECT testTable.field1, testTable.field2, testTable.id
'FROM testTable
'WHERE (((testTable.id)=>:param1) ) and (((testTable.id)<=:param2) );
'create parameters for query
commandObj.Parameters.Appe nd commandObj.CreateParameter ("param1", _
adInteger, _
adParamInput, _
10, _
1)
commandObj.Parameters.Appe nd commandObj.CreateParameter ("param2", _
adInteger, _
adParamInput, _
10, _
100)
'create recordset object
Set rsObj=Server.CreateObject( "ADODB.Rec ordset")
rsObj.CursorType=1 'forwardonly
'run query
rsObj.Open commandObj
response.write("<TABLE>")
response.write("<TR><TD>ID </TD><TD>F ield1</TD> <TD>Field2 </TD></TR> ")
If not(rsObj.EOF) then
Do While Not(rsObj.EOF)
response.write("<TR><TD>")
response.write(rsObj("id") )
response.write("<TD>")
response.write(rsObj("fiel d1"))
response.write("</TD><TD>" )
response.write(rsObj("fiel d2"))
response.write("</TD></TR> ")
rsObj.MoveNext
Loop
'close recordset
rsObj.Close
End if
response.write("</TABLE>")
'deallocate rs object
Set rsObj=Nothing
'delete allocated parameters
commandObj.Parameters.Dele te "param2"
commandObj.Parameters.Dele te "param1"
'------------------------- ---------- ---------- ---------- ---------- -------
'test Insert
case "1"
'------------------------- ---------- ---------- ---------- ---------- -------
'Name: Insert
commandObj.CommandText="In sertQuery"
'INSERT INTO testTable ( field1, field2 )
'VALUES ([:param1], [:param2]);
commandObj.Parameters.Appe nd commandObj.CreateParameter (":param1" , _
adVarchar, _
adParamInput, _
50, _
"1")
commandObj.Parameters.Appe nd commandObj.CreateParameter (":param2" , _
adInteger, _
adParamInput, _
10, _
2)
i=0
Do While i<5
commandObj(":param1")=chr( 65+i)
commandObj(":param2")=(65+ i)
commandObj.Execute
i=i+1
Loop
'delete allocated parameters
commandObj.Parameters.Dele te ":param2"
commandObj.Parameters.Dele te ":param1"
'------------------------- ---------- ---------- ---------- ---------- -------
'test Delete
case "2"
'------------------------- ---------- ---------- ---------- ---------- -------
'Name: DeleteQuery
commandObj.CommandText="De leteQuery"
'DELETE FROM testTable
'WHERE id=[:param1];
commandObj.Parameters.Appe nd commandObj.CreateParameter (":param1" , _
adInteger, _
adParamInput, _
10, _
"1")
i=0
Do While i<5
commandObj(":param1")=14
commandObj.Execute
i=i+1
Loop
'delete allocated parameters
commandObj.Parameters.Dele te ":param1"
'------------------------- ---------- ---------- ---------- ---------- -------
'test Delete
case "3"
'------------------------- ---------- ---------- ---------- ---------- -------
'Name: UpdateByid
commandObj.CommandText="Up dateByid"
'UPDATE testTable SET field1 = [:param1], field2 = [:param2]
'WHERE id=[:param3];
commandObj.Parameters.Appe nd commandObj.CreateParameter (":param1" , _
adVarchar, _
adParamInput, _
50, _
"Z")
commandObj.Parameters.Appe nd commandObj.CreateParameter (":param2" , _
adInteger, _
adParamInput, _
10, _
0)
commandObj.Parameters.Appe nd commandObj.CreateParameter (":param3" , _
adInteger, _
adParamInput, _
10, _
0)
i=0
Do While i<5
commandObj(":param1")="14"
commandObj(":param2")=14
commandObj(":param3")=15
commandObj.Execute
i=i+1
Loop
'delete allocated parameters
commandObj.Parameters.Dele te ":param3"
commandObj.Parameters.Dele te ":param2"
commandObj.Parameters.Dele te ":param1"
'------------------------- ---------- ---------- ---------- ---------- -------
End Select
'deallocate commandObj
Set commandObj=Nothing
'close connection
connObj.Close
'deallocate connection object
Set connObj=Nothing
%>
If about RDO, i dont like it. In requirements you must open some access rights to ODBC directly from the web, playing with registry settings. Other way, is Remote Scripting, where you call objects created in ASP pages which returns data. Review this topic in MSDN. But I prefer to use my own small HTTPD additions working on server and return me data in simple text format, that's fields separated by commas and CRLF. Its or ISAPI extensions or ASP components. That's one asp return me recordset from database in text format, or commit INSERT, UPDATE or DELETE use parameters sending from client's VB application. VB application connects to HTTP server using winsock.ocx or win32 sockets and call asp script with parameters, asp returns response code or text(without HTML tags, like you read data from diskette or HD, but more slow)and VB client use that response ... May be this help.
<!--#include file="adovbs.inc"-->
<%
'for one query we give max 60sec
execTimeout=60
'we have 1 query will be executed
execCount=1
'max +5 sec for script to execute
scriptTimeout=execCount*ex
'connection parameters
userData="UID=admin;PWD=ni
'if you use access by provider
dbPath="DBQ=C:\Test.mdb;"
conn_string="PROVIDER=MSDA
"DRIVER={Microsoft Access Driver (*.mdb)};" &_
dbPath &_
userData
'or if You use ODBC:
dbPath="DSN=SysDSNName;"
conn_str=dbPath &_
userData
Set connObj=Server.CreateObjec
connObj.Open conn_string
'example using select query
Set commandObj=Server.CreateOb
commandObj.ActiveConnectio
commandObj.CommandTimeout=
commandObj.CommandType=adC
Select Case request("defMode")
'test Select
case "0"
'-------------------------
'our select query is
'Name: SelectByid
commandObj.CommandText="Se
'
'SELECT testTable.field1, testTable.field2, testTable.id
'FROM testTable
'WHERE (((testTable.id)=>:param1)
'create parameters for query
commandObj.Parameters.Appe
adInteger, _
adParamInput, _
10, _
1)
commandObj.Parameters.Appe
adInteger, _
adParamInput, _
10, _
100)
'create recordset object
Set rsObj=Server.CreateObject(
rsObj.CursorType=1 'forwardonly
'run query
rsObj.Open commandObj
response.write("<TABLE>")
response.write("<TR><TD>ID
If not(rsObj.EOF) then
Do While Not(rsObj.EOF)
response.write("<TR><TD>")
response.write(rsObj("id")
response.write("<TD>")
response.write(rsObj("fiel
response.write("</TD><TD>"
response.write(rsObj("fiel
response.write("</TD></TR>
rsObj.MoveNext
Loop
'close recordset
rsObj.Close
End if
response.write("</TABLE>")
'deallocate rs object
Set rsObj=Nothing
'delete allocated parameters
commandObj.Parameters.Dele
commandObj.Parameters.Dele
'-------------------------
'test Insert
case "1"
'-------------------------
'Name: Insert
commandObj.CommandText="In
'INSERT INTO testTable ( field1, field2 )
'VALUES ([:param1], [:param2]);
commandObj.Parameters.Appe
adVarchar, _
adParamInput, _
50, _
"1")
commandObj.Parameters.Appe
adInteger, _
adParamInput, _
10, _
2)
i=0
Do While i<5
commandObj(":param1")=chr(
commandObj(":param2")=(65+
commandObj.Execute
i=i+1
Loop
'delete allocated parameters
commandObj.Parameters.Dele
commandObj.Parameters.Dele
'-------------------------
'test Delete
case "2"
'-------------------------
'Name: DeleteQuery
commandObj.CommandText="De
'DELETE FROM testTable
'WHERE id=[:param1];
commandObj.Parameters.Appe
adInteger, _
adParamInput, _
10, _
"1")
i=0
Do While i<5
commandObj(":param1")=14
commandObj.Execute
i=i+1
Loop
'delete allocated parameters
commandObj.Parameters.Dele
'-------------------------
'test Delete
case "3"
'-------------------------
'Name: UpdateByid
commandObj.CommandText="Up
'UPDATE testTable SET field1 = [:param1], field2 = [:param2]
'WHERE id=[:param3];
commandObj.Parameters.Appe
adVarchar, _
adParamInput, _
50, _
"Z")
commandObj.Parameters.Appe
adInteger, _
adParamInput, _
10, _
0)
commandObj.Parameters.Appe
adInteger, _
adParamInput, _
10, _
0)
i=0
Do While i<5
commandObj(":param1")="14"
commandObj(":param2")=14
commandObj(":param3")=15
commandObj.Execute
i=i+1
Loop
'delete allocated parameters
commandObj.Parameters.Dele
commandObj.Parameters.Dele
commandObj.Parameters.Dele
'-------------------------
End Select
'deallocate commandObj
Set commandObj=Nothing
'close connection
connObj.Close
'deallocate connection object
Set connObj=Nothing
%>
If about RDO, i dont like it. In requirements you must open some access rights to ODBC directly from the web, playing with registry settings. Other way, is Remote Scripting, where you call objects created in ASP pages which returns data. Review this topic in MSDN. But I prefer to use my own small HTTPD additions working on server and return me data in simple text format, that's fields separated by commas and CRLF. Its or ISAPI extensions or ASP components. That's one asp return me recordset from database in text format, or commit INSERT, UPDATE or DELETE use parameters sending from client's VB application. VB application connects to HTTP server using winsock.ocx or win32 sockets and call asp script with parameters, asp returns response code or text(without HTML tags, like you read data from diskette or HD, but more slow)and VB client use that response ... May be this help.
ASKER
I Understand what your trying to say but I have a few questions about it.
Select Case request("defMode")
where is the defMode comming from?
shouldn't it be request.SomeObject("defMod e")?
and how will my vb app pass the param's to this script?
should I use winsock tcip? or what, and exactly how
if you can answer these to where I can understand the points are yours.
Select Case request("defMode")
where is the defMode comming from?
shouldn't it be request.SomeObject("defMod
and how will my vb app pass the param's to this script?
should I use winsock tcip? or what, and exactly how
if you can answer these to where I can understand the points are yours.
How about you go into Access2000, and create a data access page, which utilises the Office 2000 Web components to access the database from a web browser.
ASKER
I don't want to use a web browser, If I did I could simply make an asp one the site.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I'm gonna go ahead and gif you the points, but I would like to know where I can get the include file adovbs.inc
just some additional example that might assist you
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=1&txtCodeId=1687
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=1&txtCodeId=1687
You can find adovbs.inc in your IIS's asp samples directory.
Please inform what kind of database you plan to use, of course I can show you both solutions but it will takes time...
There are some tricks in remote database access :))
Sincerely yours,
Crin