Click here to Skip to main content
15,900,907 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
Hi I Create C# windows form project and setup file for it. but I get the error after install my app on my friends system. the error is:

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: 26 - Error Locating Server/Instance Specified) 


I search and find this error come from connection string and different server name, username and password on each system. so how can create dynamic connection string to solve my problem?

What I have tried:

currently I save my connection string in App.config file.
this is my code for now:

XML
<connectionStrings>
		<add name="Con" connectionString="data source=.;          initial catalog=mydatabase; integrated security=true" providerName="system.data.sqlclient"/>

	</connectionStrings>
Posted
Updated 6-Jun-23 7:11am
v2
Comments
Member 15627495 4-Jun-23 2:47am    
Hello,

the error throw could be relevant and indicating where to fix.
source ??? an IP/ a host name

https://www.connectionstrings.com/sql-server/

or maybe one parameter is wrong, or missing.
PIEBALDconsult 4-Jun-23 11:40am    
Don't store connection strings.
Allow the user to specify the server and database names and possibly store those in a config file, but not the whole connection string.
I found recently that .net has a built-in facility which allows you to provide the values and it will dynamically create the connection string at run-time.
I will have to refresh my memory and get back to you.
Kelly Herald 6-Jun-23 15:16pm    
Are you thinking of the SqlConnectionStringBuilder class? That is what I use in my code.
PIEBALDconsult 6-Jun-23 16:13pm    
In part, yes. As in the solution I posted. But I discovered the SqlConnectionStringBuilder class only a few months ago.
Ever since I began learning ADO.net in 2002 (?) I have cobbled my own connection strings for whichever database I was using -- SQL Server (including CE), Oracle, MySQL, Teradata, Cache, Ingres, Excel via JET or ACE, etc. I don't know that all providers include a connection string builder.
Shahin Khorshidnia 5-Jun-23 19:19pm    
Have you tried these:
Adding a reference to the System.Configuration assembly in your project.
In your code, replace the hard-coded connection string with a call to ConfigurationManager.ConnectionStrings["Con"].ConnectionString. This will retrieve the connection string stored in the configuration file.
Storing the connection string in the configuration file, as you are already doing.

I recommend something along these lines:

C#
string server   = null ; // Fetch from configuration
string database = null ; // Fetch from configuration

System.Data.SqlClient.SqlConnectionStringBuilder constr =
  new System.Data.SqlClient.SqlConnectionStringBuilder()
  { { "Server"              , server   }
  , { "Database"            , database }
  , { "Integrated Security" , true     }
  } ;

System.Data.SqlClient.SqlConnection con =
  new System.Data.SqlClient.SqlConnection ( constr.ConnectionString ) ;
 
Share this answer
 
There is no single string that will work for all server instances, unless it always directs to one specific server - in which case you need two things:
1) The IP address or host name of the server: mySQLServber.mydomain.com for example.
2) The server instance will need to be configured to accept remote connections - and not all are for security reasons.
In addition, you will almost certainly need actual login information: it's very unusual for remote connections to accept integrated security.

If you are trying to work to his local instance, you need to find it as part of your installation program - you cannot assume that it shares any name or IP with yours. You should also test the connection and install the DB at that point as well.

Your existing connection data assumes that there is a DB server on the current machine - if that isn't the case it will fail, but we can't tell you what to use instead! We can't even tell if he has SQL Server installed!
If you are only using the DB as a single user each time (rather than sharing data) it's probably a better idea to use a single user DB such as Access or SqLite instead of SQL Server - that way you only need to add the DB itself to your installation and the DB engine will be included in your app assemblies.

You may find this helps if you are sharing a number of DB related apps with your friend: Instance Storage - A Simple Way to Share Configuration Data among Applications[^]
 
Share this answer
 
Comments
Member 11400059 4-Jun-23 5:15am    
thank you. I use the setup project of visual studio 2019 and also add sql express to setup file. so when user when install it and has not sql server the sql install as well (I think). but I do not know how to manage Connection string for the local database.
OriginalGriff 4-Jun-23 6:11am    
NO!
You should not include SQL Server in your installation. There are a couple of reasons:
0) You can only distribute SQL Server Express for copyright reasons - not SQL Server full version.
1) They may already have SQL Server installed on the network. If so, then they will presumably want to use that version.
2) If they do have SQL server installed and you start proliferating SQL server Express instances, you are going to annoy the heck out of the database administrator...
3) A single site installation of SQl Server is a lot more likely to be backed up than a number of scattered version under user control.
4) Sql server is quite complex for a "normal" user to install and administer - it is not a good idea!
5) It will destroy the primary advantage of using Sql Server over SqlCE or SQLite - multiuser access. If everyone installs their own copy of SQL server, then you will have multiple copies of your database, each used by a single person. This will cause some confusion, and (depending on how you wrote the original database) may take some considerable effort to combine into a single instance when the problem is realized.
Member 11400059 4-Jun-23 8:31am    
I understand know. but in properties of setup project you have "Prerequires" that let you add SQL express 2019 to your setup project. and i am set option for download it from main site if user want it. so how can solve this. can you make example for me to how manage connection string on my customer system for local database and data? or please give me the some link for Practice and learning of this subject.
PIEBALDconsult 4-Jun-23 9:12am    
Remove the prerequisite.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900