none
Connection error. Unknown, Have no idea what to do. C# RRS feed

  • General discussion

  • I coded a program to connect and create a database and tables using c#. For some reason I keep getting the same error when running the program. I posted a screen shot.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Friday, March 1, 2019 3:21 PM

All replies

  • What is connection string you are using to connect to your SQL Server? 

    What is your SQL Server configuration? Default instance? Named instance? What is TCP/IP port assignment for the SQL instance? Do you have SQL Browser service running on the SQL Server machine? (check in SSCM)

    Are app server and SQL server on same network domain or if different domain are they 'trusted'? If there is firewall between app server and sql server, do you have correct port open on it so that app server can communicate with SQL Server? Can you ping SQL Server machine from app server machine? 

    HTH,

     

    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, March 1, 2019 6:26 PM
    Friday, March 1, 2019 6:25 PM
  • 1) conn.ConnectionString = "Data Source=(local)//MSSQL$SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";

    2) Using the default settings when I installed it.

    3) MSSQL$SQLEXPRESS

    4) None sense I'm localhost only. I'm not connecting to a sql server on another pc.

    5) Yes, The browser is running

    6) ? I have no idea what you mean here. There is no domain or server. Just the SQl Server that's installed on the local PC.

    7) No port needed sense I'm not trying to connect to a PC. again local

    8) No I can't ping from one to the other. Again just the local PC. The PC and sql server will be running on the same software.



    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Friday, March 1, 2019 8:19 PM
  • I'm connecting to a local SSE 2017 instance (sql1) using C# with following connection string:

        "ServerConnectString": "Server=dellpc2\\sql1;Database=MyDbName;User Id=sa;Password=1234567;Persist Security Info=True;",
    

    PC name = dellpc2

    I'm using Nuget package System.Data.SqlClient (4.5.1)

    Saturday, March 2, 2019 2:16 PM
  • Sorry that doesn't help.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Saturday, March 2, 2019 2:33 PM
  • I think your connection string isn't quite right. It looks like your slashes are slanted the wrong way. They should be backward slash, not forward slash.

    Also, for server name, you need to either use (local) or MSSQL$SQLEXPRESS, not both together.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Sunday, March 3, 2019 3:41 AM
    Sunday, March 3, 2019 1:17 AM
  • Phil. Thanks. How ever that didn't work all so. I tried

    conn.ConnectionString = "Data Source=(local); Initial Catalog=master;Trusted_Connection = true;";

    and I tried this

    conn.ConnectionString = "Data Source=MSSQL$SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";

    I'm getting the same connection error. From what I was told. I need to use this and from the sample code I fount. That's how they are using the connection string. so what your saying makes no sense.This should work and doesn't. Now I'm thinking I have an issue with SQL Server. and I just checked. Both sql server and the browser are running. so it again makes no sense.

    conn.ConnectionString = "Data Source=(local)\\MSSQL$SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 3, 2019 6:57 AM
  • What is the name of your machine?

    In connection string, enter server name like this:

    Server=<NameOfYourMachine>\SQLEXPRESS

    Replace <NameOfYourMachine> with the actual name of your machine.

    Here is example connection string syntax:

    Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

    Reference:
    SQL Server Connection Strings

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Sunday, March 3, 2019 2:21 PM
  • If I do this for my PC. how will I get this to work on other PC's, The names won't be the same. That's why I'm using (local), This should state for the local PC for any server name the PC is using. See this is how I connect to the sql server using SSMS

    DESKTOP-07RP926\SQLEXPRESS

    You see not everyone will have this same server name.

    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 3, 2019 2:34 PM
  • So, you're going to install SQL Server on each and every users machine and put a database on each of their machines? That's the only way (local) will work for server name in the connection string.

    Usually, we have one SQL Server machine that hosts a database, then many users can make remote connections to that server with client software and retrieve data or make changes to the data.

    I don't quite understand the design of your app.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Sunday, March 3, 2019 3:41 PM
  • No, anyone downloading my app can install it and use it. Just one part of it uses sql server to keep track of stuff that user adds in.

    The apps base on mission editing for novalogic games. If you know what that company is. The app does more then this one part. The idea behind it is that users are playing these can make missions for their games. My app helps them. The idea with sql server is to keep track of the missions they make. They can make "Bases" and then store their mission in that "Base". Bases sample

    Single Player

    Multiplayer

    Coop. and so on. Get the idea. The problem is that 1, 2 or 100 people could down load my app and run it on their PC to help with mission editing and so on. The App is called "Med Little Helper". does that help. Not everyone will take the time to explain their ideas. This app is free. It's hobby base app. that I been wanting to update before I retire it. That's why I wanted to be able to add more games to it and not just the 5 I have to set for.

    so, if c# requires the full server name of the sql server. Then (local) won't work. I have to find a way to get the full sql server name and use that for every PC that my app is installed on. Any ideas. My connection string won't work unless I can use it on more then 1 PC running sql server.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 3, 2019 5:02 PM
  • I may have missed it but I don't see if you've tried "(local)\SQLEXPRESS". I would also expect ".\SQLEXPRESS" to work. This of course assumes you've installed a SQL Express instance named SQLEXPRESS, which is the default for that edition unless you specify otherwise.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, March 3, 2019 5:19 PM
  • Yes I have tried all of them and nothing works. Strange thing. I don't know if you know C# code. Let me show you what I'm doing.

    public bool makeSQLdb()
            {
    
                bool result = false;
    
                SqlConnection conn = new SqlConnection();
    
                    conn.ConnectionString = "Data Source=(local)\\MSSQL$SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";
    
    
                try
                {
                    //
                    // Open the SqlConnection.
                    //
                    conn.Open();
    
                    string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = \'mlhelper\'";
    
                    using (SqlCommand sqlCmd = new SqlCommand(cmddb,conn))
                    {
                        //Create the database 1st.
                        int nRet = sqlCmd.ExecuteNonQuery();
    
                        if (nRet <= 0)
                        {
                            
    
                            using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                command.ExecuteNonQuery();
                        }
    
    
                    }
    
    
                    using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                    {
                        //int nRet = sqlCmd.ExecuteNonQuery();
                                            
                            conn.ChangeDatabase("mlhelper");
    
                            //Create the tables last.
                            using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int PRIMARY KEY,basename char(50),category char(10);", conn))
                                command2.ExecuteNonQuery();
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int PRIMARY KEY,mapname char(50),description char(500),filename (8),filedate datetime, baseid int,category char(50);", conn))
                                command3.ExecuteNonQuery();
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int PRIMARY KEY,cname char(50);", conn))
                                command4.ExecuteNonQuery();
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(cname) VALUES(jointops)", conn))
                                command5.ExecuteNonQuery();
    
                            result = true;
                        
                    }
                }
                catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message,Application.ProductName);
                        conn.Close();
                        this.Close();
                    }
                conn.Close();
                return result;                
            }

    This should connect to sql server and create the database and some tables and add in one category. Like in the screen shot I posted above. I get the same error.Just a note that I did try removing one of the \ and VS error out. I had to add  the 2nd \ to the connection string.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software


    • Edited by Joesoft11a Sunday, March 3, 2019 5:27 PM UPDATED
    Sunday, March 3, 2019 5:25 PM
  • Yes I have tried all of them and nothing works. Strange thing. I don't know if you know C# code. Let me show you what I'm doing.

    Try the connection string below. It should work. Specifying the Windows service name (MSSQL$SQLEXPRESS) as the instance name in the connection string will not work.

    You code should wrap the SqlConnection in a using block too to ensure it's properly disposed when it goes out of scope. That practice will also avoid the need to explicitly close the connection.

            public bool makeSQLdb()
            {
    
                bool result = false;
    
                //conn.ConnectionString = "Data Source=(local)\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";
    
                try
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                    {
                        conn.Open();
    
                        string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = \'mlhelper\'";
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //Create the database 1st.
                            int nRet = sqlCmd.ExecuteNonQuery();
    
                            if (nRet <= 0)
                            {
    
    
                                using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                    command.ExecuteNonQuery();
                            }
    
                        }
    
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //int nRet = sqlCmd.ExecuteNonQuery();
    
                            conn.ChangeDatabase("mlhelper");
    
                            //Create the tables last.
                            using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int PRIMARY KEY,basename char(50),category char(10);", conn))
                                command2.ExecuteNonQuery();
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int PRIMARY KEY,mapname char(50),description char(500),filename (8),filedate datetime, baseid int,category char(50);", conn))
                                command3.ExecuteNonQuery();
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int PRIMARY KEY,cname char(50);", conn))
                                command4.ExecuteNonQuery();
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(cname) VALUES(jointops)", conn))
                                command5.ExecuteNonQuery();
    
                            result = true;
    
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, Application.ProductName);
                    this.Close();
                }
                return result;
            }


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Sunday, March 3, 2019 5:47 PM
  • Thanks, how ever that didn't work. So I'm stump at why this doesn't work. Something is SQL server must have changed or something went wrong as I was installing it.

    Any ideas. I don't know what else to try


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 3, 2019 6:06 PM
  • Something is SQL server must have changed or something went wrong as I was installing it. 

    Run the PowerShell script below to see what SQL Services are installed and what the SQL Server Browser service reports.

    Function Get-SqlServerBrowerDatagramForAllInstances($hostNameOrIpAddress)
    {
        Write-Host "Querying SQL Browser for all instances on host $hostNameOrIpAddress ..."
    
        try
        {
            $udpClient = New-Object Net.Sockets.UdpClient($hostNameOrIpAddress, 1434)
            $bufferLength = 1
            $browserQueryMessage = New-Object byte[] 1
            $browserQueryMessage[0] = 2
            $bytesSent = $udpClient.Send($browserQueryMessage, $browserQueryMessage.Length)
            $udpClient.Client.ReceiveTimeout = 10000
            $remoteEndPoint = New-Object System.Net.IPEndPoint([System.Net.IPAddress]::Broadcast, 0)
            $browserResponse = $udpClient.Receive([ref]$remoteEndPoint)
            $payloadLength = $browserResponse.Length - 3
            $browserResponseString = [System.Text.ASCIIEncoding]::ASCII.GetString($browserResponse, 3, $payloadLength)
            $elements = $browserResponseString.Split(";")
    
            Write-Host "SQL Server Browser query results:`r`n"
    
            for($i = 0; $i -lt $elements.Length; $i = $i + 2)
            {
                if ($elements[$i] -ne "")
                {
                    Write-Host "`t$($elements[$i])=$($elements[$i+1])"
                }
                else
                {
                    Write-Host ""
                    # next instance
                    $i = $i - 1
                }
            }
        }
        catch [Exception]
        {
            Write-Host "ERROR: $($_.Exception.Message)" -ForegroundColor Red
        }
    }
    
    Get-Service | Where-Object -Property Name -Like "MSSQL*"
    Get-SqlServerBrowerDatagramForAllInstances -hostNameOrIpAddress "127.0.0.1"


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, March 3, 2019 7:18 PM
  • here's your report

    I guess this is what you wanted


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 3, 2019 7:22 PM
  • Looking good so far. Next troubleshooting step, run this PS script:

    $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=(local)\SQLEXPRESS;Integrated Security=SSPI")
    $connection.Open()


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, March 3, 2019 7:27 PM
  • Now I didn't see any results from this script.

    is that what it should have done


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 3, 2019 9:24 PM
  • No error means the connection was successful and the connection string correct. It seems the connection string the app is using is not the same as the one in this script.

    I suggest you run the app code in a debugger to see the actual string being used. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, March 3, 2019 9:36 PM
  • I copied the code you gave in your last post and pasted it in place where my old code was. Here's what you gave me.Why don't I give you the method that runs it all so.

    private void MapBase_Load(object sender, EventArgs e)
            {
                //Loads all the bms Files from the game directory
                //ChangedIt = false;
                btn_MapLocation.Enabled = false;
                catLabel.Text = catName;
                
                // this is for server based, not SQLEXPRESS,
                // try this for EXPRESS -> MSSQL$SQLEXPRESS
                var serviceName = "MSSQL$SQLEXPRESS";
                ServiceController controller = new ServiceController(serviceName);
    
                try
                {
    
                    if (controller.Status == ServiceControllerStatus.Running)
                    {
                        // Check to see if database exists
                        if (makeSQLdb())
                        {
                            MessageBox.Show("Database and tables created.", Application.ProductName);
                        }
                        else
                            this.Close();
                    }
                } 
                catch (Exception exc)
                {
                    // not found
                    MessageBox.Show(exc.Message + " Error SQL Server must be ruuning.", Application.ProductName);
                    this.Close();  return;              
                }
    
                loadDefaults();            
            }
    
            public bool makeSQLdb()
            {
    
                bool result = false;
    
                //conn.ConnectionString = "Data Source=(local)\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;";
    
                try
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=.; Initial Catalog=master;Trusted_Connection = true;"))
                    {
                        conn.Open();
    
                        string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = \'mlhelper\'";
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //Create the database 1st.
                            int nRet = sqlCmd.ExecuteNonQuery();
    
                            if (nRet <= 0)
                            {
    
    
                                using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                    command.ExecuteNonQuery();
                            }
    
                        }
    
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //int nRet = sqlCmd.ExecuteNonQuery();
    
                            conn.ChangeDatabase("mlhelper");
    
                            //Create the tables last.
                            using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int PRIMARY KEY,basename char(50),category char(10);", conn))
                                command2.ExecuteNonQuery();
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int PRIMARY KEY,mapname char(50),description char(500),filename (8),filedate datetime, baseid int,category char(50);", conn))
                                command3.ExecuteNonQuery();
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int PRIMARY KEY,cname char(50);", conn))
                                command4.ExecuteNonQuery();
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(cname) VALUES(jointops)", conn))
                                command5.ExecuteNonQuery();
    
                            result = true;
    
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, Application.ProductName);
                    this.Close();
                }
                return result;
            }

    Because I don't know what else to do.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 3, 2019 9:58 PM

  • using (SqlConnection conn = new SqlConnection(@"Data Source=.; Initial Catalog=master;Trusted_Connection = true;"))
     


    Change the above line to the one below.

    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, March 4, 2019 11:06 AM
  • Dan, thanks. when I tried to use that some connection string yesterday. I got a context error of some kind. So I never been able to figure it out. How ever I think I just did. I added it again and it worked. It's now creating the database. I think the issue was that I was using a / and not a \. So that's what was screwing me over. 

    I can get it to create the database. I just can't get it to create the tables. Can you please check the code below and fix any errors. 

    try
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                    {
                        conn.Open();
    
                        string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = 'mlhelper'";
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //Create the database 1st.
                            int nRet = sqlCmd.ExecuteNonQuery();
    
                            if (nRet <= 0)
                            {
    
    
                                using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                    command.ExecuteNonQuery();
                            }
    
                        }
    
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //int nRet = sqlCmd.ExecuteNonQuery();
    
                            conn.ChangeDatabase("mlhelper");
    
                            //Create the tables last.
                            using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int PRIMARY KEY,basename char(50),category char(10)", conn))
                                command2.ExecuteNonQuery();
    
                            using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int PRIMARY KEY,mapname char(50),description char(500),filename (8),filedate datetime, baseid int,category char(50)", conn))
                                command3.ExecuteNonQuery();
    
                            using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int PRIMARY KEY,cname char(50)", conn))
                                command4.ExecuteNonQuery();
    
                            using (SqlCommand command5 = new SqlCommand("INSERT INTO TABLE category(cname) VALUES(jointops)", conn))
                                command5.ExecuteNonQuery();
    
                            result = true;
    
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Creating Error :" + ex.Message, Application.ProductName);
                    this.Close();
                }
                return result;


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software


    • Edited by Joesoft11a Monday, March 4, 2019 12:19 PM updated it
    Monday, March 4, 2019 12:18 PM
  • This is a help forum, not a code writing service. That being said, there were many logic errors and T-SQL syntax errors in the code which I've corrected in the example below to get you going. Further changes will likely be needed. For example, you don't have an IDENTITY property on the ID columns so  inserts will fail when the value is not specified. I just hard-coded the value 1 in the insert.

     

    try
    {
        using (SqlConnection conn = new SqlConnection(@"Data Source=.; Initial Catalog=master;Trusted_Connection = true;"))
        {
            conn.Open();
    
            string cmddb = @"SELECT COUNT(*) FROM master.sys.databases where name = N'mlhelper';";
    
            using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
            {
                //Create the database 1st.
                int databaseCount = (int)sqlCmd.ExecuteScalar();
    
                if (databaseCount == 0)
                {
    
                    using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper;", conn))
                        command.ExecuteNonQuery();
    
                    conn.ChangeDatabase("mlhelper");
    
                    //Create the tables last.
                    using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int PRIMARY KEY,basename char(50),category char(10));", conn))
                        command2.ExecuteNonQuery();
    
                    using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int PRIMARY KEY,mapname char(50),description char(500),filename varchar(8),filedate datetime, baseid int,category char(50));", conn))
                        command3.ExecuteNonQuery();
    
                    using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int PRIMARY KEY,cname char(50));", conn))
                        command4.ExecuteNonQuery();
    
                    using (SqlCommand command5 = new SqlCommand("INSERT INTO category(id, cname) VALUES(1, 'jointops');", conn))
                        command5.ExecuteNonQuery();
                }
    
            }
    
        }
    
        result = true;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Creating Error :" + ex.Message, Application.ProductName);
        this.Close();
    }
    return result;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Monday, March 4, 2019 1:05 PM
  • Thanks Dan, I guess I went a nuts when it created the database, I tried your code and so I did a stacktrace in my message box. I'll go a head and start a new forum. Thank you very much, That's one I owe you.

    Joe


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Monday, March 4, 2019 1:40 PM