I already published my application, most of the pages works fine, but I got an error with the pages that I manually created a connection to a database through code
Dim DT As New DataTable
Dim cmd As SqlCommand = New SqlCommand("dbo.SearchWOByAddressTF", New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\databasename.mdf;Integrated Security=True;User Instance=True"))
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@address", SearchWOTB.Text)
cmd.Parameters.AddWithValue("@From", TBTFFrom.Text)
cmd.Parameters.AddWithValue("@to", TBTMTo.Text)
Dim MyAdapter As New SqlDataAdapter(cmd)
I would appreciate if somebody help me telling me what I need to do fix this issue with my code.
I would recommend placing that connection string in your Web.Config file rather than hard coding it into the page. As far as the Data Source, make sure you are spelling the database name and instance properly. While .\ can work, sometimes you are better
off doing Server\Instancename
Thanks for the quick response, my error is as follow
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)
Ok, that means the connection is being blocked because remote connections are not allowed. You have to use Sql Server Management Studio to turn that on.
As default SQL 2008 Express installs with the SQL Browser service and SQL Agent disabled and as the browser service listens for incoming requests for Microsoft SQL Server resources and also provides information on the available instances to the
network, we need to enable and start it.
OpenSQL Server Configuration Manager from the Start menu and highlight
SQL Server Services
Right click onSQL Server Browser and select Properties
Under theService tab change the Start Mode to
Automatic
Click apply then return to theLog On tab and click
Start then Ok
That is basically it, once TCP/IP is enabled and the SQL Browser is running you will be able to see the instance from other machines on your network. </ol</ol
that happend just when in my code I have created manually a connection, I have other pages where I call the conection string that I have in my web.config and it works perfect.
Example of my connections string created manually in my code
Dim DT1 As New DataTable
Dim cmd As SqlCommand = New SqlCommand("dbo.SPNewWOStat", New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Services.mdf;Integrated Security=True;User Instance=True"))
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SPUser", User.Identity.Name)
Dim MyAdapter As New SqlDataAdapter(cmd)
MyAdapter.Fill(DT1)
DDLNewWO.DataSource = DT1
DDLNewWO.DataTextField = "Assoc"
DDLNewWO.DataBind()
finally
Will be possible replace the connection string in my code in this way?
Dim cmd As SqlCommand = New SqlCommand("dbo.SPNewWOStat", New SqlConnection("ConnectionString "))
The connection string itself should be fine. The problem you are encountering is the fact you can't connect to sql server itself. You have to get a copy of Sql Server Management Studio (free), connect to your database and make the modifications or ask your
DBA to do this for you. Either way, SQL always blocks remote connections (even local web connections) right out the starting gate. You have to tell it that this is ok.
jpuga
Member
69 Points
103 Posts
Connection string issues
May 08, 2012 06:28 PM|LINK
Hi All:
I already published my application, most of the pages works fine, but I got an error with the pages that I manually created a connection to a database through code
my release connection string is :
connectionString="Data Source=servername\instance;Initial Catalog=databasename.mdf;Integrated Security=False;User ID=username;Password=password;Application Name=Application"
The connection string in my code is like this:
Dim DT As New DataTable Dim cmd As SqlCommand = New SqlCommand("dbo.SearchWOByAddressTF", New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\databasename.mdf;Integrated Security=True;User Instance=True")) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@address", SearchWOTB.Text) cmd.Parameters.AddWithValue("@From", TBTFFrom.Text) cmd.Parameters.AddWithValue("@to", TBTMTo.Text) Dim MyAdapter As New SqlDataAdapter(cmd)I would appreciate if somebody help me telling me what I need to do fix this issue with my code.
Thanks in advance
bbcompent1
All-Star
32982 Points
8508 Posts
Moderator
Re: Connection string issues
May 08, 2012 06:42 PM|LINK
I would recommend placing that connection string in your Web.Config file rather than hard coding it into the page. As far as the Data Source, make sure you are spelling the database name and instance properly. While .\ can work, sometimes you are better off doing Server\Instancename
bbcompent1
All-Star
32982 Points
8508 Posts
Moderator
Re: Connection string issues
May 08, 2012 06:43 PM|LINK
By the way, what is your error??
jpuga
Member
69 Points
103 Posts
Re: Connection string issues
May 08, 2012 06:47 PM|LINK
Thanks for the quick response, my error is as follow
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)
bbcompent1
All-Star
32982 Points
8508 Posts
Moderator
Re: Connection string issues
May 08, 2012 06:50 PM|LINK
Ok, that means the connection is being blocked because remote connections are not allowed. You have to use Sql Server Management Studio to turn that on.
bbcompent1
All-Star
32982 Points
8508 Posts
Moderator
Re: Connection string issues
May 08, 2012 06:53 PM|LINK
Steps (from http://www.wikihow.com/Enable-Remote-Connections-SQL-2008-Express)
jpuga
Member
69 Points
103 Posts
Re: Connection string issues
May 08, 2012 06:53 PM|LINK
Honestly I don't know how to do that, could you please show me what I need to change in my code and what I need to add to my web.config.
sorry but this is my first application working with asp.net and SQL server.
really appreciate your help..
jpuga
Member
69 Points
103 Posts
Re: Connection string issues
May 08, 2012 06:57 PM|LINK
that happend just when in my code I have created manually a connection, I have other pages where I call the conection string that I have in my web.config and it works perfect.
jpuga
Member
69 Points
103 Posts
Re: Connection string issues
May 08, 2012 08:56 PM|LINK
Let me explain a little bit more my problem:
My web.config connection string is:
Example of my connections string created manually in my code
Dim DT1 As New DataTable Dim cmd As SqlCommand = New SqlCommand("dbo.SPNewWOStat", New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Services.mdf;Integrated Security=True;User Instance=True")) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@SPUser", User.Identity.Name) Dim MyAdapter As New SqlDataAdapter(cmd) MyAdapter.Fill(DT1) DDLNewWO.DataSource = DT1 DDLNewWO.DataTextField = "Assoc" DDLNewWO.DataBind()finally
Will be possible replace the connection string in my code in this way?
Dim cmd As SqlCommand = New SqlCommand("dbo.SPNewWOStat", New SqlConnection("ConnectionString "))any suggestion will be appreciated
Thanks
bbcompent1
All-Star
32982 Points
8508 Posts
Moderator
Re: Connection string issues
May 10, 2012 10:53 AM|LINK
The connection string itself should be fine. The problem you are encountering is the fact you can't connect to sql server itself. You have to get a copy of Sql Server Management Studio (free), connect to your database and make the modifications or ask your DBA to do this for you. Either way, SQL always blocks remote connections (even local web connections) right out the starting gate. You have to tell it that this is ok.
Link to download SQL Management Studio 2008 R2