Publishing a VB.NET Application with SQL Express DB (using LocalDB)

Question

I have written a VB.Net application that uses an SQL Express DB file containing a single table and a handful of stored procedures. I have successfully built and exported the application to my VPS. The problem comes when knowing what to do concerning the database file, there is a wealth of stuff online but not specifically to suit my needs. I plan to use LocalDB on the VPS but being commandline - it is hard to know if the scripts that I have run have been successful after creating an instance , starting it... etc,

I want to keep installation requirements to an absolute minimum on my VPS machine and (in time other end users machines)... hence using LocalDB and not SQL Express

So, what do I have to do on the VPS to enable my application to connect to the database.. ? This was simple when it was Access - (supply the MDB file and run the AccessDatabaseEngine(redistributable) - job done)

The connection on my devt. machine runs as expected.

The connection string in my code is:

Const strSQLConnection As String = "Data Source=    (localdb)\v11.0;Database=SoccerTrader;Trusted_Connection=True"

Can anyone help please.. this is driving me around the bend.. surely it cant be that difficult..?

===========================

I have found the following in an MSDN blog which says:

Database as a File: LocalDB connection strings support AttachDbFileName property that allows attaching a database file during the connection process. This lets developers work directly with databases instead of the database server. Assuming a database file (*.MDF file with the corresponding *.LDF file) is stored at “C:\MyData\Database1.mdf” the developer can start working with it by simply using the following connection string: “Data Source=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\MyData\Database1.mdf”.

================ ADDED 12th June =====================

OK, this is really bugging me now... I have read around this till it is coming out of my ears and nothing specifically seems to target what I am trying to do. All the blogs I read refer to installing / running SQL Server and changing permissions etc. As I have mentioned I am using a VPS and propose to use LocalDB on the VPS to access a simple/small database file for a VB.Net application I am writing. This is the story so far. 1) I have built a working prototype on my development PC and connected using SQL Express to a database file SoccerTrader.mdf - no problem. In the Visual Studio Project properties I have added a requirement to the project that checks for SQL Server ..and if it is missing, installs it...

2) I install the project on the VPS and as expected SQL Server 2012 LocalDB is installed .... see here..

3) I have copied the SoccerTrader.MDF and SoccerTrader.LDF files into "C:\BESTBETSoftware\SoccerBot" on the VPS

4) for practical reasons given the problems I am having getting this to work, I have implemented an inputbox for me to specify the connection string when the application runs.... the connection strings I have used give the following...

1]: https://i.sstatic.net/i2tro.png

I have not changed any file permissions on the development PC and the database state is NOT read only.... So, the question is where do I go from here...? What have I missed.. why is it not working..?

Answer 1

I have managed to sort the problem.

Seemingly, the connection string I was using was OK. It was my error handling that wasnt 'clean' enough. It transpired the connection was being made on my VPS but when the application attempted to update the table , the directory I had created and put the MDF file into, would not permit write access.

I moved the MDF into the C:\Users\Public\Documents folder and all works as it should.

Answer 2

You have to specify the full path of the Db file with folder name/ip-address