Help with connection string & setup of database and maybe application 4

[assimang]

Hello everybody,

I have created an application in vb 2005 which interacts with a sql database (mdf) created on microsoft sql server management studio express 2005. Well, I want to deploy my application, it's my first time and I am not familiar with, maybe I ll need your help.
In my project my connection string is as follows:
PCServer = My.Computer.Name
ConnectionString = "Data Source=" & PCServer & "\SQLEXPRESS;Initial Catalog=typing;Integrated Security=True"

Well, I am just wandering, if I want to install the application on another machine, the other machine maybe will not have sql server (SQLEXPRESS) which means that will give error as expected and my application will not work.
Well what can I do to avoid this? Must I have to change the connection string? And how? What can I do generally? Any help will be much appreciated.

Thanks anyone
in advanced.

 

[macleod1021]

The recmmended way to do this is to store the connection string as a property within your config file. That way, you can set it to what it needs to be during runtime.

If you go to the Settings tab, you'll be able to add properties there. Then you can access them through My.Settings. When you configure the first one, an app.config file will be added to your project to hold these settings. This is an xml doc that you can change instead of changing code and then recompiling for every computer you want to install it on

im in ur stakz, overflowin ur heapz!

 

[assimang]

Thank you much macleod1021.
What can I set as value in settings tab of the connection string?
In my code as I mentioned before the connection string is as follows:
PCServer = My.Computer.Name
ConnectionString = "Data Source=" & PCServer & "\SQLEXPRESS;Initial Catalog=typing;Integrated Security=True"

But any other computer will not have SQLEXPRESS, so how will be to run in all the machines? Which means what value will be in the settings tab?

 

[assimang]

Well, I copied my database into the path:
C:\Documents and Settings\user\My documents\Visual Studio 2005\Projects\TypingApplicaton\TypingApplicaton

I went on settings tab added the property MyConnectionString and value: Data Source=C:\Documents and Settings\user\?? ??????? ???\Visual Studio 2005\Projects\TypingApplicaton\TypingApplicaton;Initial Catalog=typing;Integrated Security=True
Type: ConnectionString Scope:Application

I put this code to the form load event
MyConn.ConnectionString = My.Settings.MyConnectionString
MyConn.Open

and my form doesn't display, It doesn't give me errors but It doesn't display. I run it step over and it always stay to MyConn.Open statment. Any suggestions to solve my problems?

 

[Sorwen]

The computer you are installing to must have something on it capable of reading an .mdf file. Your connection string may then change depending on that program.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[assimang]

Thank you Sorwen,
Do you mean that other computers in which I will install the program must have a compatible program to read a .mdf file? And what about If they don't? Does this mean that I have return as my connection string was before?
Which was:
PCServer = My.Computer.Name
ConnectionString = "Data Source=" & PCServer & "\SQLEXPRESS;Initial Catalog=typing;Integrated Security=True"

 

[Sorwen]

You may already get what I'm saying, but I'm going to put it the way I think of it. You have 2 people that want to talk (one person your program and the other your database file) and they don't speak the same language. That means you need an interpreter. The connection string in your program tells your computer what interpreter to use, but if you don't have one installed it doesn't matter.

Do you mean that other computers in which I will install the program must have a compatible program to read a .mdf file?

Yes. It has to have something installed that understands that an mdf is something more than just random data.

Does this mean that I have return as my connection string was before?

No. It means there is nothing you can put in your connection string that is going to work.

And what about If they don't?

I would think there might be some type of 3rd party plugin or something you could have included with your program.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[assimang]

Thank you again so much Sorwen.
Is an interpreter for example SQLEXPRESS or entire the program microsoft sql server 2005?
Is there a way to include in the setup project an interpreter that can read the mdf file, if only the other computer doesn't have? And how?

 

[Sorwen]

Is an interpreter for example SQLEXPRESS or entire the program Microsoft sql server 2005?

Both. I'm not sure what other options may be available as well.

Is there a way to include in the setup project an interpreter that can read the mdf file, if only the other computer doesn't have?

If you are using VS 2005 or later you can have SQLEXPRESS be required before install and have it download from Microsoft's sites. In the Solution Explorer you right click and select properties on the setup project. Then select Prerequisites. Then either select "SQL Server 2005 Express Edition" in VS 2005 or "SQL Server 2005 Express Edition SP2 (x86)" in VS 2008. By default "Download prerequisites from the component vendor's web site" will be selected. That way the program will check if it is installed and if it isn't then it will get it from Microsoft's website and install it.


You may just ask around as well. I'm sure there must be other options available. Just remember 'interpreter' is just a word I'm using (for lack of a more correct word) so if you ask around others may not have a clue what you are talking about at first.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[assimang]

Thank you once more Sorwen,
I want to ask you something else. If someone downloads all the required componets which are .net frameword 2.0 and sql server 2005 express edition, I think that he could be able to edit the mdf database file. That means that he can delete or modify the records in my database through sql express and I don't want this, I just want user to have access to the mdf file, only via my application. Is it possible to do this? Also I have other one question, how to include the mdf file in the right location? In my computer the typing.mdf file locates in filepath: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data What can I do about the others? How to include it the setup?
If I add the file in application folder I am getting error:

Error 1 Unable to find source file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\typing.mdf' for file 'typing.mdf', located in '[TARGETDIR]', the file may be absent or locked. C:\Documents and Settings\user\?? ??????? ???\Visual Studio 2005\Projects\TypingApplicaton\TypingApplicationSetup\TypingApplicationSetup.vdproj TypingApplicationSetup

Thank you again
for your effort

 

[woogoo]

Can't you use the AttachDBFilename of the SqlConnectionStringBuilder to do this?
--

woogoo

 

[assimang]

Sorry, what is SqlConnectionStringBuilder? I don't understand.

 

[woogoo]

From the MSDN:

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref4/html/T_System_Data_SqlClient_SqlConnectionStringBuilder.htm

Either that or I've mis-interpreted (not unusual) the intention.



woogoo

 

[assimang]

The error I metioned before when I reboot my computer disappears. I don't understand...

 

[woogoo]

This implies that the file is locked as opposed to missing, and the lock is being released due to the update.
You'll need to make sure that you properly close your connection(s) to the database to ensure the database doesn't remain locked.
--


woogoo

 

[assimang]

Thanks woogoo.

I think there are some problems in sorwen's solution.
If someone downloads all the required componets which are .net frameword 2.0 and sql server 2005 express edition, he could be able to edit the typing.mdf file, which means that he can delete or modify the records in the database through sql express and I don't want this, I just want user to have access to the mdf file, only via my application. Is there a way to avoid this?
Well now, if I include the typing.mdf file in file system's application folder of setup solution, the file as it's expected copied to the application folder if run the setup.
The point is that it isn't under the SQLEXPRESS server, the what I mean is that the user has to open microsoft sql server management studio express and attach it under sql server in databases folder. What about if the user is unexperienced, why to put him into this process? If he doen't do this the program will produce error because it will serch the typing.mdf there: PCServer = My.Computer.Name
ConnectionString = "Data Source=" & PCServer & "\SQLEXPRESS;Initial Catalog=typing;Integrated Security=True" and it will not find it, as it is the application folder? So what can I do about this?

Thanks again
to all of you.

 

[woogoo]

Well you could always encrypt the data in the database, if it's that important to you, .NET has lots of options for this, as does SQL Server.

Or depending on the complexity of your application it is a trivial task to roll your storage using a Class to define the record structure.

I know it's overkill for some but all of our applications are protected by dongle and we use custom codes stored within the dongle to encrypt / decrypt the data to and from files, and the database, so no two applications produce the same results.

woogoo

 

[assimang]

What does mean "dongle"? What do you mean? Sorry for my english!
The problem is not only how to disable user editing the data in the database via sql management system 2005, is also how to connect right in database...
As I explained before, in my computer, I have created the typing.mdf. Although I've added it in application folder in the setup project solution it has no effect. When i try to run the setup to other computer, even if it has installed sql server, it cannot communicate with the database file typing.mdf because of connection string.

PCServer = My.Computer.Name
ConnectionString = "Data Source=" & PCServer & "\SQLEXPRESS;Initial Catalog=typing;Integrated Security=True"

Which means that it searches it to sqlexpress and then it produces error. How can I modify the connection string to connect to the c:\author\productname\mytyping.mdf (application folder) via sqlexpress? What can I do? I have created a setup for my application and there is no possibility to run to other computers. And it must run without put into troubles every unexperienced user. I am so confused and I don't know what to do.
Thanks again for your help

 

[Sorwen]

Let me see if I can explain this another way.

This is a basic connection string to an simple Access Database:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\location\Access.mdb'"

This will work if you have Microsoft Jet (this uses version 4.0) installed. You can get this from Microsoft as just Jet or it is also installed when you install Access. So you can get Jet that has the ability to read/write to .MDB files or Access that can do everything. Sorry, Jet cannot read MDF files.

Is there a solution like this for SQL? I have no clue. The only time I use SQL is when connecting to a remote host which is a whole different situation.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[earthandfire]

A couple of points.

I don't use SQL Server Express so I can't give you step by step instructions, but hopefully this will show you what you need to look for. (Maybe someone from forum183 (George / Alex if you are watching ) can step in.


First - security:
The easiest way would be to create a new account in SQL Server and set the "owner" of the database to that account. Include that in your connection string (which you may want to encrypt) so that only your program can access the database.

Second - using the .mdf file:
With .mdb files (Access) you can copy the file to another computer which has Access installed and open the file on that new computer. Not with .mdf files. They are not "opened" in the same way. They need to be attached to the the appropriate instance of SQL Server. Again I don't have any SQL Server 2005 (Express or otherwise) code sample, but the process is straightforward.


As I said, unlike Access, with SQL Server you can not simply move the data files around and expect it just to work.



Hope this helps.