Solved

Connecting to a database on the web

Posted on 2000-04-12
23
175 Views
Last Modified: 2011-09-20
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.
0
Comment
Question by:burntout
  • 9
  • 5
  • 3
  • +4
23 Comments
 
LVL 2

Expert Comment

by:Crin
ID: 2708007
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
0
 
LVL 2

Expert Comment

by:Crin
ID: 2708011
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
0
 
LVL 2

Author Comment

by:burntout
ID: 2708025
Adjusted points from 350 to 500
0
 
LVL 2

Author Comment

by:burntout
ID: 2708026
I would like to know both ways.... I will increase points to 500 for your trouble.
0
 
LVL 2

Expert Comment

by:Crin
ID: 2708193
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 :)))
0
 
LVL 2

Author Comment

by:burntout
ID: 2708230
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.
0
 
LVL 2

Author Comment

by:burntout
ID: 2708248
BTW the data base is a access 97 DB
0
 
LVL 2

Expert Comment

by:DanAvni
ID: 2708349
0
 
LVL 2

Expert Comment

by:Crin
ID: 2708375
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
0
 
LVL 2

Author Comment

by:burntout
ID: 2708687
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..
0
 
LVL 2

Expert Comment

by:DanAvni
ID: 2708787
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:Crin
ID: 2708860
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
 
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2708870
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
0
 
LVL 2

Author Comment

by:burntout
ID: 2708950
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.
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 2710038
So delete the question and repost it in the ASP section.
0
 
LVL 1

Expert Comment

by:ATM
ID: 2710139
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.
0
 
LVL 2

Author Comment

by:burntout
ID: 2710178
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.
0
 

Expert Comment

by:ozrs
ID: 2710806
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.
0
 
LVL 2

Author Comment

by:burntout
ID: 2710814
I don't want to use a web browser, If I did I could simply make an asp one the site.
0
 
LVL 1

Accepted Solution

by:
ATM earned 500 total points
ID: 2711149
request("defMode")
comming from
mypscript.asp?defMode=2&otherParam=a
(remember? URL address or HTML form)
To pass parameters You must use HTTP rules, GET command enought but for security issues POST can be used, review RFC 1945, You can visit www.tair.freeservers.com where download and review HTTP via winsock samples in VB and other languages.
0
 
LVL 2

Author Comment

by:burntout
ID: 2711193
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
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2712934
0
 
LVL 1

Expert Comment

by:ATM
ID: 2713049
You can find adovbs.inc in your IIS's asp samples directory.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
z = x + y – 1 6 67
Paint/Redraw window while dragging 16 68
Run code from text file in vb 1 56
MS Date Picker 64 bit 32 bit issue 12 49
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now