Cannot establish connection to DB

[Cort]

I'm just starting out here. Can't figure out why I cannnot establish a connection string.

Heres my code: Dim objConnection As New SqlConnection("server=localhost\database=SingleTreatmentReviews")

SingleTreatmentReviews is the name of my database. I don't have a password that I know of. It just says connection not established.

Here's from my app config code: "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SingleTreatmentReviews.mdf;Integrated Security=True;Connect Timeout=30;U

???

 

[jmcilhinney]

Um, you have a connection string in the database that looks like it's valid but you're not using it. You're using something else that certainly isn't a valid connection string. When you create your SqlConnection you need to pass it the connection string from the config file, which would be My.Settings.TheNameOfYourConnectionString.

 

[Cort]

Thanks - yes the connection works - I tested it. Here's my connection in the App Config file.

connectionStrings>
<add name="TreatmentReview.My.MySettings.SingleTreatmentReviewsConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SingleTreatmentReviews.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

I tried putting the name (and several iterations of it) in

=NewSqlConnection(.......................................) but got the same result. Can you advise further what to do given the information above?

Thanks!

(I have to say I still don't know why that first one didn't work. I looked at
ADO SQL Database Connection Strings - SQL Server, MS Access, MySQL, Oracle, PostgreSQL, DB2, ... page. It said for trusted connections the following are valid.

Trusted connection:
1. "Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
2."Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=True;"

I tried using localhost for DataSource, singletreatmentReviews for DB name and Integrated Security=SSPI and still nothing.

<

 

[jmcilhinney]

Did you use what I said? It should be My.Settings.SingleTreatmentReviewsConnectionString according to that.

The connection string you were using originally didn't match either of those two formats and, even if it did, it still wouldn't work because they connect to a database that is already attached to the server, whereas you want to attach a local data file and then connect to it.

 

[Cort]

Thanks. I think I've made some progress (I think). I was still getting hung up at

Dim objConnection as NewSQLConnection.....

With this code:

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

'declare objects needed to retrieve data from db

Dim objConnection As New SqlConnection("My.Settings.SingleTreatmentReviewsConnectionString")
Dim objDataAdapter As New SqlDataAdapter()
Dim objDataSet As New DataSet()

I changed My.Settings to My.MySettings and it stopped getting hung up there but it raised an InnerException and gave me this message:

"Format of the initialization string does not conform to specification starting at index 0."

Which appears to relate to the connection string(?). Perhaps I didn't make any progress.

Thanks for hanging with me. I am brand new at this.

Here's the connectionStrings from the app.config file again:

<connectionStrings>
<add name="TreatmentReview.My.MySettings.SingleTreatmentReviewsConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SingleTreatmentReviews.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

 

[jmcilhinney]

When you create a SqlConnection you are supposed to pass it a string and that string is supposed to contain the connection properties. This:

Dim objConnection As New SqlConnection("My.Settings.SingleTreatmentReviewsConnectionString")

is passing a literal string that does not contain connection properties. You are supposed to pass it the value of the My.Settings.SingleTreatmentReviewsConnectionString property, which is such a string. Get rid of the double quotes, which are delimiters for a literal string. You do not want a literal string.

 

[Cort]

Success!

Success is so sweet! Thank you very much. There's obviously alot to learn here but it's great to get going. Thanks again.

Cort