Connect to sql database

kale_tushar · September 3rd, 2003, 12:46 PM

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........

owain · September 4th, 2003, 06:43 AM

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

kale_tushar · September 4th, 2003, 12:24 PM

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.....

gbianchi · September 4th, 2003, 12:39 PM

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

owain · September 5th, 2003, 03:50 AM

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

jlick · September 5th, 2003, 08:53 AM

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
[email protected]

kale_tushar · September 5th, 2003, 12:32 PM

hi John R Lick

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

jlick · September 5th, 2003, 12:45 PM

What specific part do you not understand how to do?

John R Lick
[email protected]

owain · September 8th, 2003, 03:27 AM

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