By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,375 Members | 5,304 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,375 IT Pros & Developers. It's quick & easy.

db open

OuTCasT
100+
P: 374
Hi all,
ive got the payroll solution that i am busy with, now when it is first run the user creates a company which is a sql database that is saved in the application.startupPath. every company will have its own db
Now i have created the New company form, and it creates the company/db. and the main form is shown.

what my problem is. say there are 2 companies created. i have displayed them in a listbox so u can choose which company you want to use.
i have used the stored proc sp_databases to display the databases/companies that are present in sqlExpress.

all the databases will have the same tables and names etc, just the database name will differ.

i want to be able to select a database in the listbox and when i click open thats the database that i will be using in the application, so if i closed the application and i selected another database from the listbox and clicked open then it would use the selected database.

is that possible ?
May 26 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Hi all,
ive got the payroll solution that i am busy with, now when it is first run the user creates a company which is a sql database that is saved in the application.startupPath. every company will have its own db
Now i have created the New company form, and it creates the company/db. and the main form is shown.

what my problem is. say there are 2 companies created. i have displayed them in a listbox so u can choose which company you want to use.
i have used the stored proc sp_databases to display the databases/companies that are present in sqlExpress.

all the databases will have the same tables and names etc, just the database name will differ.

i want to be able to select a database in the listbox and when i click open thats the database that i will be using in the application, so if i closed the application and i selected another database from the listbox and clicked open then it would use the selected database.

is that possible ?

Yes.

First, you have to place what the user chooses into a variable. If this is a browser based apps, you have to keep the value of this variable visible on all pages. During your creation of connection object or data object you have to build it dynamically to connect to that database.

So your query would look like:

Expand|Select|Wrap|Line Numbers
  1. t-sqlvar = 'select column from ' + dbvar + '..table'
-- CK
May 26 '08 #2

OuTCasT
100+
P: 374
Yes.

First, you have to place what the user chooses into a variable. If this is a browser based apps, you have to keep the value of this variable visible on all pages. During your creation of connection object or data object you have to build it dynamically to connect to that database.

So your query would look like:

Expand|Select|Wrap|Line Numbers
  1. t-sqlvar = 'select column from ' + dbvar + '..table'
-- CK
Hi CK

What i did was put in a module to the project to carry the value that i chose from the listbox to the next page.
then in the connectionstring i used

Expand|Select|Wrap|Line Numbers
  1. Server=(local);Data Source=.\sqlexpress;Initial Catalog='" & txtDatabase.Text & "';Integrated Security=True"
then all the information from the commands that i built would be called from the database name that was in the textbox.

Is that the best way to do it ?
May 27 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Yes and No ;)

I've used some of tools that revert back the db connection to my default db. If you have created a lot of query already you can use your technique without changing anything. So, yes, that would be fine.If you don't have any other db on the server with the same object name, you should be fine.

If you just started, try to specify which database you are running your t-sql from by placing the db name on the object, so No, to make sure, just place the dbname.

-- CK
May 27 '08 #4

OuTCasT
100+
P: 374
Yes and No ;)

I've used some of tools that revert back the db connection to my default db. If you have created a lot of query already you can use your technique without changing anything. So, yes, that would be fine.If you don't have any other db on the server with the same object name, you should be fine.

If you just started, try to specify which database you are running your t-sql from by placing the db name on the object, so No, to make sure, just place the dbname.

-- CK
When the user chooses to create a company/database i place and if statement
If exists then notify the user and exit the sub so there is not chances of duplication.
and i have a delete Company button which deletes the selected company/database from the listbox.selecteditem but i want to display a msgbox saying u are now about to delete the selected company.
ive used msgbox("blablabla", msgboxstyle.yesno)
if msgbox.result = yes then
delete company
else
if msgbox.result = no then
exit sub
end if
end if
but that doesnt seem to work

how do i process the choices made in the msgbox, i knw its a stupid question.
May 28 '08 #5

OuTCasT
100+
P: 374
When the user chooses to create a company/database i place and if statement
If exists then notify the user and exit the sub so there is not chances of duplication.
and i have a delete Company button which deletes the selected company/database from the listbox.selecteditem but i want to display a msgbox saying u are now about to delete the selected company.
ive used msgbox("blablabla", msgboxstyle.yesno)
if msgbox.result = yes then
delete company
else
if msgbox.result = no then
exit sub
end if
end if
but that doesnt seem to work

how do i process the choices made in the msgbox, i knw its a stupid question.
i got it no worries

Expand|Select|Wrap|Line Numbers
  1. Try
  2.             Select Case MsgBox("Delete Selected Company", MsgBoxStyle.YesNo)
  3.                 Case MsgBoxResult.Yes
  4.                     sqlCon.Open()
  5.                     sqlDeleteCompanycommand.ExecuteNonQuery()
  6.                     lbExistingCompanies.Items.Remove(lbExistingCompanies.SelectedItem)
  7.                     sqlCon.Close()
  8.                 Case MsgBoxResult.No
  9.                     Exit Sub
  10.             End Select
  11.         Catch ex As Exception
  12.             MsgBox(ex.Message)
  13.         End Try
May 28 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.