Link to home
Start Free TrialLog in
Avatar of burntout
burntoutFlag for United States of America

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.
Avatar of Crin
Crin

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
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
Avatar of burntout

ASKER

Adjusted points from 350 to 500
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 :)))
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.
BTW the data base is a access 97 DB
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
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..
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.
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
 
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
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.
Avatar of Mark Franz
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*execTimeout+5

'connection parameters
userData="UID=admin;PWD=nimda"
'if you use access by provider
dbPath="DBQ=C:\Test.mdb;"
conn_string="PROVIDER=MSDASQL;" & _
             "DRIVER={Microsoft Access Driver (*.mdb)};" &_
             dbPath &_
             userData
'or if You use ODBC:
dbPath="DSN=SysDSNName;"
conn_str=dbPath &_
               userData

Set connObj=Server.CreateObject("ADODB.Connection")
connObj.Open conn_string

'example using select query
Set commandObj=Server.CreateObject("ADODB.Command")
commandObj.ActiveConnection=connObj
commandObj.CommandTimeout=execTimeout
commandObj.CommandType=adCmdStoredProc

Select Case request("defMode")
'test Select
case "0"
'------------------------------------------------------------------------
'our select query is
'Name: SelectByid
commandObj.CommandText="SelectByid"
'
'SELECT testTable.field1, testTable.field2, testTable.id
'FROM testTable
'WHERE (((testTable.id)=>:param1)) and (((testTable.id)<=:param2));

'create parameters for query
commandObj.Parameters.Append commandObj.CreateParameter("param1", _
                                                        adInteger, _
                                                                                    adParamInput, _
                                                                                    10, _
                                                                                    1)
commandObj.Parameters.Append commandObj.CreateParameter("param2", _
                                                        adInteger, _
                                                                                    adParamInput, _
                                                                                    10, _
                                                                                    100)
'create recordset object
Set rsObj=Server.CreateObject("ADODB.Recordset")

rsObj.CursorType=1 'forwardonly

'run query
rsObj.Open commandObj

response.write("<TABLE>")
response.write("<TR><TD>ID</TD><TD>Field1</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("field1"))
   response.write("</TD><TD>")
   response.write(rsObj("field2"))
   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.Delete "param2"
commandObj.Parameters.Delete "param1"
'------------------------------------------------------------------------

'test Insert
case "1"
'------------------------------------------------------------------------

'Name: Insert
commandObj.CommandText="InsertQuery"

'INSERT INTO testTable ( field1, field2 )
'VALUES ([:param1], [:param2]);

commandObj.Parameters.Append commandObj.CreateParameter(":param1", _
                                                        adVarchar, _
                                                                                    adParamInput, _
                                                                                    50, _
                                                                                    "1")
commandObj.Parameters.Append 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.Delete ":param2"
commandObj.Parameters.Delete ":param1"

'------------------------------------------------------------------------
'test Delete
case "2"
'------------------------------------------------------------------------

'Name: DeleteQuery
commandObj.CommandText="DeleteQuery"

'DELETE FROM testTable
'WHERE id=[:param1];

commandObj.Parameters.Append 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.Delete ":param1"

'------------------------------------------------------------------------

'test Delete
case "3"
'------------------------------------------------------------------------

'Name: UpdateByid
commandObj.CommandText="UpdateByid"

'UPDATE testTable SET field1 = [:param1], field2 = [:param2]
'WHERE id=[:param3];

commandObj.Parameters.Append commandObj.CreateParameter(":param1", _
                                                        adVarchar, _
                                                                                    adParamInput, _
                                                                                    50, _
                                                                                    "Z")
commandObj.Parameters.Append commandObj.CreateParameter(":param2", _
                                                        adInteger, _
                                                                                    adParamInput, _
                                                                                    10, _
                                                                                    0)
commandObj.Parameters.Append 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.Delete ":param3"
commandObj.Parameters.Delete ":param2"
commandObj.Parameters.Delete ":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.
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("defMode")?

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.
I don't want to use a web browser, If I did I could simply make an asp one the site.
ASKER CERTIFIED SOLUTION
Avatar of ATM
ATM

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
You can find adovbs.inc in your IIS's asp samples directory.