Solved

Connecting to a database on the web

Posted on 2000-04-12
23
174 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

707 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

21 Experts available now in Live!

Get 1:1 Help Now