Chapter 14 - Unable to list DB from the given sample program.

RedGuy · January 26th, 2014, 10:48 AM

Dear Sir,

Referring to the first Sample program from chapter 14.

I have encountered this error "InvalidArgument=Value of "0" is not valid for 'SelectedIndex' during run the Sample program "Ch14createDatabase" -> Database management Subsystem > click on file > click on Select Server.

The error is captured from frmserverSelect.cs file as below.

private void btnServer_Click(object sender, EventArgs e)
{
short i;

cmbServer.SelectedIndex = 0; // Use the server they selected to list databases
serverToUse = cmbServer.SelectedItem.ToString();

string conn = "Data Source=" + serverToUse + "; Integrated Security=True;";
cmbDatabase.Visible = true;
lblDb.Visible = true;

try
{
using (SqlConnection sqlConn = new SqlConnection(conn))
{
sqlConn.Open();
DataTable tblDbs = sqlConn.GetSchema("Databases");
sqlConn.Close();

foreach (DataRow row in tblDbs.Rows)
{
i = (short)row.ItemArray[1];
if (i > SYSTEMDBTYPES)
cmbDatabase.Items.Add(row["database_name"].ToString());
}
}
cmbDatabase.SelectedIndex = 0; error occur here....
} catch (SqlException ex)
{
MessageBox.Show("Error occurred while reading database data: " + ex.Message);
}
}

I have followed every steps from the book. From my understanding this code is used to display the available database on the machine(my PC). In my case, i think, it fail to list out the SQL server name...

By the way, is there any way to use C# to detect the available SQL server on the local PC?

Perhaps, you can advice what goes wrong the codes or the System configuration so that i can resolved.

My PC setup..
1.Installed Microsoft Visual Studio 2012.
2.Installed Microsoft SQL Server Developer
3.Created New rule for TCP port 1433.

Thanks ..

Regards,
ReGuy

DrPurdum · January 27th, 2014, 01:24 PM

Make sure you are using the correct Microsoft Framework. The code was compiled using the old 3.5 Framework and you are likely running a later version. Go to the Project menu, select Properties-->Application and see which framework is there. It should match your compiler's environment. If you still have problems, go to the Build properties option and change the platform target to x86. Let me know what you find out.

olo-B · August 19th, 2014, 08:58 PM

For anyone else who ran into this problem.
I too ran into the same problem and realized that it had to do with the named instance on my computer. what I did was to concatenate the server name and instance name in the combobox items. Like this:

Code:

 foreach (DataRow row in dbTable.Rows)
            {
                SqlSL = new clsSqlServerList();
                serverName = row[0].ToString();
                

	Quote:
	
	
		
			
				cmbServer.Items.Add(serverName +"\\" + row["InstanceName"])

Also you could use the following console application code to list SQL server instances:

Code:

Hope this helps. Good luck!

using System.Data;

using System.Data.Sql;

using System;

public class ListSqlInstances
{

    public static int Main(string[] args)
    {

        SqlDataSourceEnumerator sqldatasourceenumerator1 = SqlDataSourceEnumerator.Instance;

        DataTable datatable1 = sqldatasourceenumerator1.GetDataSources();

        foreach (DataRow row in datatable1.Rows)
        {

            Console.WriteLine("****************************************");

            Console.WriteLine("Server Name:" + row["ServerName"]);

            Console.WriteLine("Instance Name:" + row["InstanceName"]);

            Console.WriteLine("Is Clustered:" + row["IsClustered"]);

            Console.WriteLine("Version:" + row["Version"]);

            Console.WriteLine("****************************************");
         
        }
        Console.ReadLine();
        return 1;

C#Learner · November 30th, 2014, 10:40 PM

Unfortunately, nothing works. I can use SQL Server, I made a new database Cards with three tables. I filled them with the info, however neither Chapter14ProgramCreateDatabase program nor this console application can see my SQL Server.

What should I try?
Would be very grateful for help

My PC setup..
1.Installed Microsoft Visual Studio 2014.
2.Installed Microsoft SQL 2014
3.Created New rules for TCP port 1433 and dynamic ports.
4. Turned off Microsoft Fire Wall.
5. OS is Windows 7 Professional 64-bit

DrPurdum · December 1st, 2014, 01:06 AM

Saying "nothing works" is not helpful in figuring out what's wrong. You are the first person who has not been able to get the code working, which suggests there's something wrong on your end. What are the error messages you are getting? What does happen when you run the program? Is anything displayed on the screen?

C#Learner · December 1st, 2014, 06:19 PM

If I start the program and try to select server (File-Select server) in 10 or so seconds I can see cmbServer with the only name, which is the name of my computer (Toshiba). Actual SQL Server name is: Toshiba\SQLEXPRESS.

Of course if I select it I have an error window with the message:
=================================================
Error occurred while reading database data: 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 connection. (provider: Named Pipes Provider, error: 40-Could not open a connection to SQL Server.)
=================================================

After I close window (named: Select server and Database) this line:

this.mdiParent.getDatabaseName = cmbDatabase.SelectedItem.ToString();

in frmServerSelect throws an exception:
=================================================

Microsoft Visual Studio Express 2013 for Windows Desktop

An unhandled exception of type 'System.NullReferenceException' occurred in Ch14CreateDatabase.exe

Additional information: Object reference not set to an instance of an object.
=================================================

If I hardcode I can establish the connection with database.

Console application only proofs that SqlDataSourceEnumerator Class does not work properly on my computer.
=================================================

using System.Data;

using System.Data.Sql;

using System;

public class ListSqlInstances
{

public static int Main(string[] args)
{

SqlDataSourceEnumerator sqldatasourceenumerator1 = SqlDataSourceEnumerator.Instance;

DataTable datatable1 = sqldatasourceenumerator1.GetDataSources();

foreach (DataRow row in datatable1.Rows)
{

Console.WriteLine("******************************* *********");

Console.WriteLine("Server Name:" + row["ServerName"]);

Console.WriteLine("Instance Name:" + row["InstanceName"]);

Console.WriteLine("Is Clustered:" + row["IsClustered"]);

Console.WriteLine("Version:" + row["Version"]);

Console.WriteLine("******************************* *********");

}
Console.ReadLine();
return 1;
}
}
=================================================

The result is:
================================
Server Name:Toshiba
Instance Name:
Is Clustered:
Version:
================================

What should I do to get it working?
I really appreciate your help.

Best regards.

DrPurdum · December 1st, 2014, 08:24 PM

It sounds like your connection string may be wrong. Check that it is as specified, with the necessary commas and (perhaps) empty fields. Also, if your server requires a user ID and password, that must be figured in, too.

C#Learner · December 6th, 2014, 07:50 PM

Thank you VERY MUCH to all who helped me to solve the problem!!! A few days of frustration and struggleâ¦I would like to share my experience and might help somebody else who are in the same trap.

If you experience a problem with the program see the answer of olo-B above and concatenate the server name:

Code:

cmbServer.Items.Add(serverName +"\\" + row["InstanceName"]);

If it still does not help then,

In Microsoft Visual Studio open Database Explorer and try to add a new connection.

In a list of server names could be only the name of your computer, even if you hit the âRefreshâ button.

After you type the name of the server, there is a list of databases. Chose a database which you need and test the connection (there is the button). You can receive a confirmation that test connection succeeded.

However, when you hit OK, you got an error message:
-------------------------------------------------------------------------------------
Database Explorer

Unable to add data connection. Could not load file or assembly âMicrosoft.SqlServer, Management. Sdk.Sfc, Version=11.0.0.0, Cuture=neutral, PublickeyToken=89845dcd8080cc91â or one of its dependencies. The system cannot find the file specified.
--------------------------------------------------------------------------------------
It means that you need to install two missing components, which you can find in:
MicrosoftÂ® SQL ServerÂ® 2012 SP2 Feature Pack
http://www.microsoft.com/en-us/downl....aspx?id=43339
These components are:
1) SQL System CLR types
and
2) SQL 2012 Shared Management Objects
[SQL 2012 Shared Management Objects] has dependency on [SQL System CLR types] component, so you have to install SQL Server System CLR Types first.

For more info go to:
http://sqlbeyond.blogspot.co.uk/2012...d-file-or.html
My specs:
OS Windows 7 Professional
Microsoft Visual Studio Express 2013 for Windows Desktop
Microsoft SQL Server Express 2014
Microsoft Security Essentials
Microsoft Fire Wall

Good luck!

Cryotic · December 14th, 2014, 12:52 PM

Im getting the same error as someone else did: "InvalidArgument=Value of "0" is not valid for 'SelectedIndex'.

http://i.imgur.com/h6LdKRC.png
http://i.imgur.com/G4d8ywS.png
http://i.imgur.com/HOkY1jK.png

I've tried the following suggestions:
- cmbServer.Items.Add(serverName +"\\" + row["InstanceName"]);
- Changed configuration to Net 3.5 with and without x86:
http://i.imgur.com/SL6xoR4.png
http://i.imgur.com/fMx922X.png

Anyone have any suggestions?

"If I manually add an item to the combobox with my servername: ""(localdb)\MSSQLLocalDB" I'm able to connect and get the Cards database to show in the combobox on the form.

C#Learner · December 16th, 2014, 08:10 PM

Quote:

Originally Posted by C#Learner

Thank you VERY MUCH to all who helped me to solve the problem!!! A few days of frustration and struggleâ¦I would like to share my experience and might help somebody else who are in the same trap.

If you experience a problem with the program see the answer of olo-B above and concatenate the server name:

Code:

cmbServer.Items.Add(serverName +"\\" + row["InstanceName"]);

If it still does not help then,

In Microsoft Visual Studio open Database Explorer and try to add a new connection.

In a list of server names could be only the name of your computer, even if you hit the âRefreshâ button.

After you type the name of the server, there is a list of databases. Chose a database which you need and test the connection (there is the button). You can receive a confirmation that test connection succeeded.

However, when you hit OK, you got an error message:
-------------------------------------------------------------------------------------
Database Explorer

Unable to add data connection. Could not load file or assembly âMicrosoft.SqlServer, Management. Sdk.Sfc, Version=11.0.0.0, Cuture=neutral, PublickeyToken=89845dcd8080cc91â or one of its dependencies. The system cannot find the file specified.
--------------------------------------------------------------------------------------
It means that you need to install two missing components, which you can find in:
MicrosoftÂ® SQL ServerÂ® 2012 SP2 Feature Pack
http://www.microsoft.com/en-us/downl....aspx?id=43339
These components are:
1) SQL System CLR types
and
2) SQL 2012 Shared Management Objects
[SQL 2012 Shared Management Objects] has dependency on [SQL System CLR types] component, so you have to install SQL Server System CLR Types first.

For more info go to:
http://sqlbeyond.blogspot.co.uk/2012...d-file-or.html
My specs:
OS Windows 7 Professional
Microsoft Visual Studio Express 2013 for Windows Desktop
Microsoft SQL Server Express 2014
Microsoft Security Essentials
Microsoft Fire Wall

Good luck!

Hi to all,

I need to make a note to my previous post. After youâve downloaded and installed two additional components you also should start SQL Browser (it does not start by default).

Go to all programs
Microsoft SQL Server 2014
Configuration Tools
SQL Server 2014 Configuration Manager and start it.

On the left side left-click SQL Server Services.
On the right site you can see SQL Server Browser. If it has a red square, it means it does work at the moment.

Right âclick it and go to Properties. In a check box Built-in Account should be a Local Service.
Go to Service (itâs on the top of window) and in the Start Mode choose Automatic.
Push Apply.

If you restart SQL Server 2014 Configuration Manager, SQL Server Browser should have a green arrow.

Now your program should work.
Good luck!