locked
Allow User To Specify Location Of Database On Startup

    Question

  •  

    Hi All,

     

    I have nearly finished creating my program in visual studio express (visual basic). 

     

    My program contains one database and is 90% created in design view.  What I want to be able to do is when the program starts - allow the user to specify the location of the database.

     

    Can someone point me in the right direction.

     

    Thanks in advance.

     

    Reafidy.

    Sunday, March 30, 2008 8:07 PM

Answers

  • OK....

     

    Now then... do you have the database attached or do you create an instance with the connection string?

     

    If you attach the database, you don't care about the 'location" of the database, you just connect to the server.

     

    SQLE will support remote network connections.

     

    http://msdn2.microsoft.com/en-us/library/ms165647.aspx

     

    But the VBE designer will not. You have to connect in software.

     

    What this means is the following, once you get this setup, you wont have to have the user tell where to connect. Use a single connection string to a single server.

    Monday, March 31, 2008 1:25 AM

All replies

  •  

    What kind of database? SQLCE? SQL? Access? Does it have a server? Are there servers on each node?

     

     

    Sunday, March 30, 2008 8:56 PM
  • Hi Renee,

     

    Thanks for the reply.

     

    Its an SQL database.  I created it using SQL Managment studio express.  Then created a connection to it in VS Express.  Um at the moment I just have it running on my laptop which has sql server installed, but yes I am planning to get a dedicated server so that my program can run on multiple PC's. 

     

    Im sorry im not sure what you mean by are there servers on each node.

     

    Sunday, March 30, 2008 9:20 PM
  • OK....

     

    Now then... do you have the database attached or do you create an instance with the connection string?

     

    If you attach the database, you don't care about the 'location" of the database, you just connect to the server.

     

    SQLE will support remote network connections.

     

    http://msdn2.microsoft.com/en-us/library/ms165647.aspx

     

    But the VBE designer will not. You have to connect in software.

     

    What this means is the following, once you get this setup, you wont have to have the user tell where to connect. Use a single connection string to a single server.

    Monday, March 31, 2008 1:25 AM
  • Hi Renee,

     

    As far as creating an instance Im not entirely sure.  When creating the program in VS Studio I made a connection via right clicking data connections in the database explorer, selecting add new connection  then datasource = "Microsoft SQL Server Database File", and I selected the location of my database when I clicked ok the connection appeared in the database explorer and I made my program. 

     

    The problem now is once i have published my program, if i move the database original database file on my harddrive i get an error when loading the program.  I thought there would be a way to specify its location when opening the program.

     

    So Ive read your post 10 times and I have to admit im obviously out of my league. Could you explain futher.

     

    Im about to fire up an old laptop and install SQL server on it so I can do some more testing with more than one computer.

     

    Sorry about the spoon feeding.

    Monday, March 31, 2008 2:54 AM
  •  

    Here are the steps you need to do.

     

    Absolutely forget the datadesigner. That will not support you in VBE.....

     

    The overall database has logins create a login with lots of privileges. Make sure you can connect to the database with this login.

     

    Using SMSSE on your development node connect to the server and then on your server instance right click (I think) and

    look for ATTACH. It will ask for the default owner of the database... Select the new login as the owner.

     

    Attach the database.

     

    I hope you installed with Mixed mode authentication.

     

    Your next task using VBE is to attach to the database using VBE. ALL you want to do is to get a working connection string and nothing more.

     

     

    Then create a new project.

    Add this Imports statement: Imports System.data and Imports system.data.SqlClient

    Add a Form Load event

     

    In the forn load event add the following code

     

    Dim con as new SQLConnection("Your connection string here")

     

    Con.Open

     

     

    And that's all. This won't be easy. You will probably have a lot of exceptions which you will have to debug,

    Once you have this.... you will be on your way.....

     

    There are models of connection strings for SQLE

     

    It's probably going to be of this general format:

     

    Server=myServerAddress;Database=myDataBase;
    User ID=myUsername;Password=myPassword;Trusted_Connection=False;

     

    For more forms see:

     

    http://connectionstrings.com/?carrier=sqlserver

     

     

     

     

     

     

     

     

     

     

     

    Monday, March 31, 2008 3:44 AM
  • Hi Renee,

     

    Thanks for the detailed reply,  I will get onto that now.

     

    However when you say forget the datadesigner are you saying that I have to start again in regard to setting up my datagridviews and the like which are poulated from table adapters made in the desginer set them all up programtically?

     

     

    Monday, March 31, 2008 4:05 AM
  •  

    You won't be using table adapters for this. VBE designer code will only connect to a local server.

     

    You're going to have to do the project the way that more experienced designers do it if you want remote node support.

     

    We're going to a different architecture, one that is necessary. The advantage is that you will have a single connection string that will work anywhere that your server node is visible.

    Monday, March 31, 2008 4:10 AM
  •  

    "VBE designer code will only connect to a local server"

     

    Guess I should have done my homework properly! Just when I thought i was nearly finished too. Boo Hoo.

     

    Renee, you have been very helpful.  Thank you.

     

     

    Monday, March 31, 2008 4:23 AM
  •  

    You are very welcome and welcome to software learningsville.

     

    You are gettng ready to get into database security and that can take a while to learn. Another new user also wants to learn exactly this. Her/His name is YP.

     

    Both of you are beginning at the same place the two of you can help each other and I am sure experienced people will join us too.

    Monday, March 31, 2008 4:29 AM
  • Sounds good.  Thanks for the heads up. 

     

     

     

     

    Monday, March 31, 2008 4:41 AM
  •  

    Your welcome. YP is off downloading SQLE with advanced services. Assuming that we get you through this, and we will, this may make for a great tutorial. Smile
    Monday, March 31, 2008 4:57 AM
  •  

    Hi Renee,

     

    I have now setup SQLE on another laptop (server) and connected to the database on the server through my client latop.

     

    Your right there was many hurdles along the way for the benefit of others I post them here:

     

    Prob:    I could not see the server from the client computer due to windows firewall

    Solution:  Create an exception for sqlexpress in the firewall

     

    http://support.microsoft.com/kb/914277

     

     

    Prob: Server was not configured to allow remote connections

    Solution: Enable Remote Connections in the sql server surface area configuration and enable the sql server browsing service

     

    http://support.microsoft.com/kb/914277

     

    Prob: Server was not configured to allow remote connections

    Solution: Enable Remote Connections in the sql server surface area configuration and enable the sql server browsing service

     

    I could not get windows authentication mode to work because I could not setup the login name.  Therefore I used SQL Server authentication.

     

    Prob:   SQL Server Authentication not enabled

    Sloution: Right click server - the properties.  On the security tab hit SQL Server and Windows Authentication mode.

     

    Other problems were that when I set up the login I did not add sufficient permissions to allow the connection.

     

    My connection string looks like this:

     

    "Server=NITRO\SQLEXPRESS;Database=DATABASE2.mdf;User ID=Sam;Password=Sam;Trusted_Connection=False;"

     

    Obviously only a temporary password.

     

    Thanks again Renee.

     

    All Done, now to start again with the programming!!

    Monday, March 31, 2008 11:48 PM
  •  

    Ths is good.

     

    "Server=NITRO\SQLEXPRESS;Database=DATABASE2.mdf;User ID=Sam;Password=Sam;Trusted_Connection=False;"

     

     

    Did you attach the database?

    Tuesday, April 01, 2008 1:51 AM
  • Hi Renee

     

    Im not sure if my understanding of attaching the database is what you mean but this is what i did.

     

    Through the client computer I connected to the server via SQL Server Management Studio Express.  I then expanded the server, right clicked on databases and attached the database.  The database "DATABASE2.mdf" is now listed under databases underneath the server. 

     

    Is that what you mean?  I would have thought that the connection string wouldnt work if the database was not attached to the server.

     

    Or do you mean I should somehow attach the database in visual studio?

     

    I ran this little sub to populate a treeview and it worked:

     

       Private Sub LdTreeView()

            Dim sqlCon As New SqlConnection(GetConString)

            Dim sqlCom As New SqlCommand("Select * FROM Templates", sqlCon)

            Dim DataAdapter1 As SqlDataAdapter = New SqlDataAdapter()

            Dim dt As New DataTable

     

            Dim sqlCom2 As New SqlCommand("Select * FROM Templates", sqlCon)

            Dim DataAdapter2 As SqlDataAdapter = New SqlDataAdapter()

            Dim dt2 As New DataTable

     

            DataAdapter1.SelectCommand = sqlCom

            DataAdapter1.Fill(dt)

     

            DataGridView1.DataSource = dt

            Dim tvnNode As TreeNode

            Dim tvnFind As TreeNode()

     

            With Me.TreeView1

                .Nodes.Clear()

                .Font = New Font(.Font, FontStyle.Bold)

                For Each rowA As DataRow In dt.Rows()

                    If Not TypeOf rowA("Name") Is DBNull Then

                        tvnNode = New TreeNode

                        tvnNode.Name = (rowA("TID"))

                        tvnNode.Text = (rowA("Name"))

                        .Nodes.Add(tvnNode)

                    End If

                Next rowA

     

                sqlCom.CommandText = "Select * FROM Aircraft"

                dt.Clear()

                DataAdapter1.Fill(dt)

     

                For Each rowB As DataRow In dt.Rows()

                    tvnNode = New TreeNode

                    tvnNode.Name = (rowB("AID"))

                    tvnNode.Text = (rowB("Registration"))

                    tvnNode.NodeFont = New Font(.Font, FontStyle.Regular)

                    tvnFind = .Nodes.Find(rowB("TID"), True)

                    tvnFind(0).Nodes.Add(tvnNode)

                    tvnFind(0).Expand()

                Next rowB

            End With

     

        End Sub

     

    Tuesday, April 01, 2008 2:58 AM
  •  

    Nice subroutine.

     

    In your connection string, specify the name of the server and use the InitialCatalogue = databasename

     

    Form in the connection string. With an attached database, you don't specifiy a file name - ever...

     

    I suspect since you are doing it that way, it reattaches the file and that takes time.

     

    With an Attached  database,  the connection time is much shorter.

    Tuesday, April 01, 2008 5:56 AM
  • Hi Renee,

     

    When you say Im doing it that way im not quite sure what im doing wrong.  I changed the connection string to the code below, is it what you meant?

     

    Im not sure it is because I am still specifing the filename, so how do I attach the database without specifying the filename?

     

    Thanks.

     

    Private Sub LdTreeView()

            Dim sqlCon As New SqlConnection("Data Source=NITRO\SQLEXPRESS;Initial Catalog=DATABASE2.MDF;User Id=Sam;Password=Sam;")

            Dim sqlCom As New SqlCommand("Select * FROM Templates", sqlCon)

            Dim DataAdapter1 As SqlDataAdapter = New SqlDataAdapter()

            Dim dt As New DataTable

     

            DataAdapter1.SelectCommand = sqlCom

            DataAdapter1.Fill(dt)

     

            Dim tvnNode As TreeNode

            Dim tvnFind As TreeNode()

     

            With Me.TreeView1

                .Nodes.Clear()

                .Font = New Font(.Font, FontStyle.Bold)

                For Each rowA As DataRow In dt.Rows()

                    If Not TypeOf rowA("Name") Is DBNull Then

                        tvnNode = New TreeNode

                        tvnNode.Name = (rowA("TID"))

                        tvnNode.Text = (rowA("Name"))

                        .Nodes.Add(tvnNode)

                    End If

                Next rowA

     

                sqlCom.CommandText = "Select * FROM Aircraft"

                dt.Clear()

                DataAdapter1.Fill(dt)

     

                For Each rowB As DataRow In dt.Rows()

                    tvnNode = New TreeNode

                    tvnNode.Name = (rowB("AID"))

                    tvnNode.Text = (rowB("Registration"))

                    tvnNode.NodeFont = New Font(.Font, FontStyle.Regular)

                    tvnFind = .Nodes.Find(rowB("TID"), True)

                    tvnFind(0).Nodes.Add(tvnNode)

                    tvnFind(0).Expand()

                Next rowB

            End With

     

        End Sub

    Tuesday, April 01, 2008 6:45 AM
  •  

    BUMP
    Wednesday, April 02, 2008 8:21 PM
  •  

    This

     

    Catalog=DATABASE2.MDF

     

    should be

     

    Catalog=DATABASE2

     

     

    Wednesday, April 02, 2008 9:14 PM
  • Hi Renee,

     

    If I make the change you posted above I get an error

     

    Cannot open database "DATABASE" requested by the login. The login failed.
    Login failed for user 'Sam'.

     

    Thanks.

    Wednesday, April 02, 2008 9:21 PM
  •  

    Sometimes errors like that are your friend.

     

    When the server is setup correctly you never have to supply a file name, only a database name.......ergo.......

     

    You have to setup a user and a password for the database under security.

    I always setup an identical username and password to the server first. Until you can connect as I showed you above, you haven't set your server up correctly.

     

     

    Wednesday, April 02, 2008 11:13 PM
  • Ah ha the plot thickens.  Everytime I think everything is sorted Renee comes along and foils my plan. Smile

     

    So this is what I did:

     

    I expanded the database tab, then my database "SAM" tab, then the security tab.

    I right clicked users and selected "New User".

    I added "Sam" under the username and selected the login name "Sam" as well.

    I then checked all the role members.

     

    But alas when I try connecting with the string

     

    "Server=NITRO\SQLEXPRESS;Database=SAM;User ID=Sam;Password=Sam;Trusted_Connection=False;"

     

    I get a login failed error.

     

    Any ideas?

     

    Something I dont understand is there is a password for the login name but I dont see anywhere to put a password for the user name?

     

    Wednesday, April 02, 2008 11:45 PM
  •  

    This is the part that I said would be difficult. It's also why i recommended a program with two lines of code.

    Connection creation and a connection open.

     

    It's best that you initally set this up on the node that is running the server. This will diagnose login problems.

     

    Then move the program to another node and attempt to connect. That will diagnose server visibility.

    Thursday, April 03, 2008 12:14 AM
  • Yes I did start with two lines of code as you say.  Problem was I thought I had a successful connection, and I thought the problem was fixed so I jumped the gun and began to change the code and carry on with my coding.  Woops.

     

    I didnt realise that a successful login meant you need to only specify the name without the extension .mdf. 

     

    So back to stage 1. Smile

     

    I have installed the program on the computer running the server and I still get the same problem (login failed). This is my only lines of code in the program:

     

    Imports System.data

    Imports system.data.SqlClient

    Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim con As New SqlConnection("Server=NITRO\SQLEXPRESS;Database=SAM;User ID=Sam;Password=samsam;Trusted_Connection=False;")

    con.Open()

    End Sub

    End Class

     

    Thursday, April 03, 2008 12:34 AM
  •  

    I know you enabled the server. If you are certain you have enabled it for TCP/IP, then your problem is a security problem.

     

    What i normally do when creating a database is that i have a user defined on the main server logins, and I create the database making that user the default owner. Then I spcify that user as a database specific login and I make sure they have datareader and datawriter priviledges.

     

    Another thing to try is to create a new project and make a dataconnection to your server - on the hosting node.

     

    Play with the dataconnection and it will generate a connection string when you ask it to.

    Thursday, April 03, 2008 1:27 AM
  • Okay,  I have a feeling I may have led us on a while goose chase, I hope not.

     

    Can I pause us at you last post and check something first.

     

    Yesterday when I was testing I decided to rename the database from "Database2" to "Sam". I noticed that in the MSSMSE the attached database is called "Sam.mdf".  But if i create a new database it doesnt have the extension .mdf on it. Im thinking that I named the database with its extension.

    So I renamed the database to just "Sam".    

    Could this have been the problem?  If so I feel very silly!  If not well carry on from above.

     

    This is the connection Im using and it does not thro an error:

     

    Imports System.data

    Imports system.data.SqlClient

    Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim con As New SqlConnection("Server=NITRO\SQLEXPRESS;Database=SAM;User ID=Sam;Password=samsam;Trusted_Connection=False;")

    con.Open()

    End Sub

    End Class

     

    Thursday, April 03, 2008 1:53 AM
  •  

    A long time ago, I learned a lesson which is that highly complex and specialized systems like a database server, should be used as intended without shortcuts, because there are things in the database itself that are self describing and I have manged to do things that disrupt the relationship between the files internal descriptors and what the database knows about it. I would include renaming a database to be one of those things especially now that it is behaving that way.

     

    If I were you, I might make as script of that database (I don't know if SQLE has this capability) and after I was sure I had a working script, to delete the database and reload the script. I would just do that for safies sake.

     

    At one time I think I detached a database and made a copy of it called test. So I could use the copy. The only thing is the internal desciptors were still has the old name. It's best not to play with databases like that. it confuses servers.

    Thursday, April 03, 2008 2:16 AM
  • Point taking, sounds like good advice.  I wont be renaming databases like that anymore.

     

    Atleast I guess I have the database attached properly.

     

    Thanks for your help and Patience! Smile

    Thursday, April 03, 2008 2:25 AM
  • Readify,

     

    I don't think want to attach a db to your application because the data will not replicate. The problem with this is that if you have 2 or more users, they'll be looking at different information as the records change.

     

    Typically, you would you use DTS to move the database from your local PC to the network SQL Server and you change the connection string of your app to point to the network SQL server before you deploy the app.

     

    Adam

    Thursday, April 03, 2008 3:20 AM
  •  

    You don't attach a database to your application. You attach it to the server.
    Thursday, April 03, 2008 3:27 AM
  • Hi Adam,

     

    Thanks again for your replys.

     

    Too make sure where on the same page, this is what i intend to do:

     

    I will have a computer acting as a server, the database file will be held on the same computer and attached to the server.

     

    The program will be installed on multiple user computers.  The program will query the database on the server to populate things like datagridviews.

     

    Is that ok?  The database will not be on the clients computers - which I think is what you thought in your post or did I misunderstand?

     

     

     

    Thursday, April 03, 2008 3:31 AM
  • What happens when the 'computer acting as a server' is disconnected from the network?

     

    Adam

    Thursday, April 03, 2008 3:36 AM
  • Well that would be what I call a "Slight Technical Difficulty" Smile

     

    I was trying hard to hide my inexperience, alas it is shining on thru now.

     

    I guess I figured that the server would never really be disconected and Im not sure of any other way to do it.

    Thursday, April 03, 2008 3:40 AM
  • As long as your connection string points to the database on the server, you can move it there anyway you like. Attaching it is typically the biggest hair-pulling approach when you can just use SSIS or DTS to do most of the work for you.

     

    But it's up to you.

     

    Good luck,

     

    Adam

    Thursday, April 03, 2008 3:45 AM
  •  

    Attaching is the highest performance approach. The what the professional shops do.
    Thursday, April 03, 2008 3:48 AM
  •  ReneeC wrote:

     

    Attaching is the highest performance approach. The what the professional shops do.

     

    Although this is utterly false, I won't be baited in to an argument that is fruitless.

     

    Adam

    Thursday, April 03, 2008 3:52 AM
  • Hi Renee,

     

    Im quite confused now.  Could you explain what you mean by attaching the database? because I thought thats what i have done already.

    Thursday, April 03, 2008 3:53 AM
  • Readify,

     

    Do you have a DBA? Are you the DBA? When you begin attaching and detaching databases, there are many checks and balances that need to match in order to ensure success. If something goes wrong, i.e. permissions or version conflicts during the mount, it quickly becomes a nightmare.

     

    If you have a DBA, ask him/her to use DTS and move the database to the server. Save yourself the headache.

     

    Adam

    Thursday, April 03, 2008 4:16 AM
  •  

    "Attached" describes a relationship between the server and a database. The server has the database file open and essentially owns the database. It maintains information about the database such as indexes and keys in it's in memory cache, for rapid lookup. Attached means... ready to go, the instant you connect.
    Thursday, April 03, 2008 4:18 AM
  • Hi Adam,

     

    I am everything. Smile As I am still in the process of writing my program I have an old laptop acting as the server, and are using my laptop to do the programing.  I have no idea what DTS is.  At the moment I can attach the database without to much difficulty.

     

    Renee,

     

    Thanks for the excellent explanation.

     

    Friday, April 04, 2008 12:22 AM