none
Issues with SQL database connection string RRS feed

  • Question

  • Hello all,

    I am working on a project which will feature a web site for managing certain information and a console application for also managing the certain information. I created a Visual Studio 2017 project for creating a class library api, and also for storing the database itself. In preparation for this work I installed SQL Server 2008 Express. When designing the database in VS 2017 I used the installed SQL Server as my mechanism for connecting to the database.

    Now I am using the WebMatrix.Data database class for connecting to the database. When connecting via the web everything seems to work fine. However for my console features I need to connect using a connection string, and there is my problem - I can't seem to craft the correct connection string. Here is my latest attempt:

    string localconnectionstring = "Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=C:\\Users\\Owner\\Documents\\Visual Studio 2017\\Projects\\FTFFLibrary\\FTFFLibrary\\FTFFSite.mdf;Integrated Security=True";

    try
       {
        var db = Database.OpenConnectionString(localconnectionstring);
        db.Execute("INSERT INTO FTFFAudit (AuditDatetime, AuditText) VALUES (@0, @1)", localdate, localaudittext);
        db.Close();
        db.Dispose();

       } catch (Exception e)
       {
         Console.WriteLine("{0} Exception caught.", e);
       }

    which results in:

    System.ArgumentException: Keyword not supported: 'initial catalog'.
       at System.Data.SqlServerCe.SqlCeConnectionStringBuilder.GetIndex(String keyword)
       at System.Data.SqlServerCe.SqlCeConnectionStringBuilder.set_Item(String keyword, Object value)
       at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
       at System.Data.SqlServerCe.SqlCeConnectionStringBuilder..ctor(String connectionString)
       at System.Data.SqlServerCe.SqlCeConnection.set_ConnectionString(String value)
       at WebMatrix.Data.DbProviderFactoryWrapper.CreateConnection(String connectionString)
       at WebMatrix.Data.Database.<>c__DisplayClass15.<OpenConnectionStringInternal>b__14()
       at WebMatrix.Data.Database.get_Connection()
       at WebMatrix.Data.Database.EnsureConnectionOpen()
       at WebMatrix.Data.Database.Execute(String commandText, Object[] args)
       at FTFFConsole.Program.Main(String[] args) in C:\Users\Owner\Documents\Visual Studio 2017\Projects\FTFFConsole\FTFFConsole\FTFFConsole.cs:line 39 Exception caught.

    Help ?

    Thanks

    Rich

    Saturday, June 3, 2017 4:28 PM

Answers

  • Hi Cole,

    By uninstalling SQL Server 2012 Express (which was running concurrently with SQL Server 2008 R2) and after re-installing Visual Studio 2017, my problem has gone away and I can connect to the database. I am still having a problem with table indices, but that is not a topic for this thread.

    BTW, after this "clean up" my installation of Web Matrix stopped working. I have no interest in trying to get it to work again. Onward and upward.

    Thanks so much for your help.

    Rich

    • Marked as answer by irondog1 Wednesday, June 7, 2017 9:20 PM
    Wednesday, June 7, 2017 9:20 PM

All replies

  • Hi Rich,

    I help you move this case to the ADO.NET Managed Providers forum for dedicated support.

    Thanks,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 5, 2017 1:46 AM
  • Hi irondog1,

    If you use SQL Server Experss, the connection string should be like this:

    string localconnectionstring = "Server=.\\SQLExpress;AttachDbFilename=C:\\Users\\Owner\\Documents\\Visual Studio 2017\\Projects\\FTFFLibrary\\FTFFLibrary\\FTFFSite.mdf;Database=FTFFSite;
    Trusted_Connection=Yes";

    If you use localDb, the connection string should be like this:

    string localconnectionstring = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\Owner\\Documents\\Visual Studio 2017\\Projects\\FTFFLibrary\\FTFFLibrary\\FTFFSite.mdf;Integrated Security=True";

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 5, 2017 5:52 AM
    Moderator
  • You have WebMatrix configured to use SQL Server Compact, you must change to use SQL Server.

    And "initial catalog" is not a file path


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Monday, June 5, 2017 7:10 AM
  • Thank you Jack
    Wednesday, June 7, 2017 12:21 AM
  • Thanks for your reply Cole. When I tried the first suggested string (for sql server express) and changed to server name to SQLEXPRESS12 and pointing at a version of FTFFSite.mdf that is not under source control I receive the following error:

    System.Data.SqlClient.SqlException (0x80131904): Directory lookup for the file "C:\Users\Owner\Documents\Visual Studio 2017\Projects\FTFFConsole\FTFFConsole\bin\FTFFSite.mdf" failed with the operating system error 5(Access is denied.).
    Cannot attach the file 'C:\Users\Owner\Documents\Visual Studio 2017\Projects\FTFFConsole\FTFFConsole\bin\FTFFSite.mdf' as database 'FTFFSite'.

    Using the second suggested string I received the following error:

    System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Unexpected error occurred inside a LocalDB instance API method call. See the Windows Application event log for error details.
    )

    Wednesday, June 7, 2017 12:42 AM
  • Hi irondog1,

    Based on your error message, it seems that you have no permission to access the mdf file.

    Please give full permission on folder in which you want to create the mdf file to the logon account with which the SQL Server service is running. Usually:  NT Service\MSSQL$

    You could look that up Control Panel-> Administrative Tools-> Services=> SQL Server -> Prperties-> Logon Tab-> Note the account

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 7, 2017 5:42 AM
    Moderator
  • Hi Cole,

    Just to be clear I am not trying to create a database, but rather access a database that I created with VS 2017 Designer. Interestingly enough, I can no longer access that database via VS 2017 anymore, possibly for the same reason.

    The service is managed by the account NT Service\MSSQL$SQLEXPRESS12. I am having trouble finding a way to change the permissions for that account, but I did open up the permissions for all the folders that lead to the database - no luck.

    I will let you know if I make any further progress.

    Rich

    • Marked as answer by irondog1 Wednesday, June 7, 2017 9:14 PM
    • Unmarked as answer by irondog1 Wednesday, June 7, 2017 9:14 PM
    Wednesday, June 7, 2017 2:45 PM
  • Hi Cole,

    By uninstalling SQL Server 2012 Express (which was running concurrently with SQL Server 2008 R2) and after re-installing Visual Studio 2017, my problem has gone away and I can connect to the database. I am still having a problem with table indices, but that is not a topic for this thread.

    BTW, after this "clean up" my installation of Web Matrix stopped working. I have no interest in trying to get it to work again. Onward and upward.

    Thanks so much for your help.

    Rich

    • Marked as answer by irondog1 Wednesday, June 7, 2017 9:20 PM
    Wednesday, June 7, 2017 9:20 PM