Wrox Programmer Forums
|
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 3rd, 2003, 12:46 PM
Authorized User
 
Join Date: Jul 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Connect to sql database

I am making an application in VB. I have one database in SQL
of 5 companies. In my application there is one button "Connect to atabase". What shud i write(code) so that whenever any company clicks on Connect to database button they shud get connected to database and shud be able to access or retrieve the information from their computer?
For this company has to be connected to inernet.

Please tell me the solution as soon as possible.

Tushar........

 
Old September 4th, 2003, 06:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am afraid I do not understand you question.

What do you mean when you say 'I have one database in SQL of 5 companies?' Do you mean you have a database with 5 tables, one for each company, or do you mean you have one table with 5 rows, one for each company, or do you mean something completely different?

If you only have one button in your application then how are you going to know which company has clicked the button? Will the user have to select their company name from a drop down list, will their company name be stored in the Windows registry or in an INI file, will the user have to log on with a username, etc?

Also what do you mean when you say 'whenever any company clicks on Connect to database button they shud get connected to database?' Do you want them to connect to the database you mentioned at the beginning of your post, or do you mean a different database?

It may help if you could provide details of you database and table structures, and what your application is supposed to do.

Regards
Owain Williams
 
Old September 4th, 2003, 12:24 PM
Authorized User
 
Join Date: Jul 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have created a database name info. in which there r total 5 tables.
one table for each company.(com1 com2 com 3 com4 com5-table names)
Each company will have my application on their PC.
Consider 5 companies com_1 com_2 com_3 com_4 com_5
Now suppose com1 start my application and want to access the information from table com1 how do i achieve this?
I mean what shud be the code so that whenever any of the company start my application and want to access info from their respective tables.
If u still did not get what i mean to say please let me know
Thanks for ur reply
Tushar.....

 
Old September 4th, 2003, 12:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there..

i understand your problem.. but what do you need.. all the code???

this can be a long code, depending how you are trying to connecting..

what do you have already???

Gonzalo Bianchi
 
Old September 5th, 2003, 03:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You could add a combo box (cboCom) and a list box (lstCom) to your form. The combo box will contain a list of the company names and the list box will contain a list of the company’s table names in the database. In the code listing below I am assuming your connect button is called cmdConnect and that you will be using ADO.

Code:
Option Explicit

Dim conConnection   As ADODB.Connection
Dim rstCompany      As ADODB.Recordset

Private Sub Form_Load()

    Set conConnection = New ADODB.Connection
    conConnection.Open YourConnectionString, UserID, Password

End Sub

Private Sub cmdConnect_Click()

Dim strTableName    As String

    strTableName = lstCom.List(cboCom.ListIndex)
    Set rstCompany = conConnection.Execute("SELECT * FROM " & strTableName)

End Sub

Private Sub Form_Unload(Cancel As Integer)

    conConnection.Close
    Set conConnection = Nothing
    Set rstCompany = Nothing

End Sub
In this example the company would select their name from the drop down list and then click the connect button. Once clicked the form wide recordset variable rstCompany would be set to their respective table. You will then be able to access this recordset and perform your reads, updates, adds and deletes.

Regards
Owain Williams
 
Old September 5th, 2003, 08:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 101
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jlick
Default

You may want to protect the information, so that one company cannot see the data of another company. One way to do this is to have a table that "joins" the user name(s) from company A to the table for company A. Your code could then have them log in, look up the table name based on the user name, then build your SQL statement based on the return. This would do a few things, 1) Stop one company from seeing the data from another company, 2) Stop one company from knowing what other companies are using your system, 3) Make it easier for the users because they have one less thing to select.

Since you said they were connecting over the internet, I would have the client application (at the company's site) cache the table name, so they do not have to do two calls for each function.


John R Lick
JohnRLick@hotmail.com
 
Old September 5th, 2003, 12:32 PM
Authorized User
 
Join Date: Jul 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi John R Lick

How do i achieve that can u give me some hints?
Thanks for your suggestion.
Tushar


 
Old September 5th, 2003, 12:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 101
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jlick
Default

What specific part do you not understand how to do?


John R Lick
JohnRLick@hotmail.com
 
Old September 8th, 2003, 03:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You would add another table called for example Users, this would have a structure similar to this:

Code:
username varchar(20) PK
password varchar(64)
table varchar(10)
Then when the user logged on, you would look up their username in the table, if it exists then check the password is correct, if it is then you would build your SQL statement from the table field. Here is a simplified code example of how to do this:

Code:
Set rstUsers = conConnection.Execute("SELECT * FROM Users WHERE username = '" & _
    UCase(txtUsername.Text) & "';")
If Not rstUsers.EOF Then
    If txtPassword.Text = rstUsers("password") Then
        Set rstCompany = conConnection.Execute("SELECT * FROM " & rstUsers("table"))
    Else
        MsgBox "Invalid Password"
    End If
Else
    MsgBox "Invalid Username"
End If
Regards
Owain Williams





Similar Threads
Thread Thread Starter Forum Replies Last Post
Chpt 3 - Connect to SQL Server 2000 Database BennyHill BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 6 June 7th, 2007 03:30 PM
Connect to Remote SQL database via Code jscammell VB How-To 1 May 20th, 2007 12:00 AM
how to connect to MDF sql server database? hertendreef Classic ASP Databases 1 March 25th, 2007 02:02 AM
Need to connect SQL database to Crystal Reports vbmazza Crystal Reports 0 May 13th, 2005 06:24 AM
How to connect SQL Database eapsokha Classic ASP Professional 1 March 10th, 2004 04:18 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.