Connecting to SQL Server

Any help??

I am a newbie to this...I am trying to right a connection string to a SQL Server DB. I do not have the full blown SQL Server, but just the SQL desktop engine. I put together the following in Web Matrix and it works without a problem. However, when I copy this over into Visual Studio.net I get an error at the "myConnection.Open()" command. Any ideas why it would work in Web Matrix but not in Visual Studio. When I test the connection to the SQL DB in Studio it says that the test completed succesfully so I don't think I am having a problem actually connecting, so I am not sure what the problem is.

Code..............................
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim myConnectionString As String
myConnectionString= "server=Discovery3;database=dbDVD;trusted_Connection=true"

Dim myConnection As New Data.SqlClient.SqlConnection(myConnectionString)
Dim myInsertQuery As String = "INSERT INTO tblMaster(TITLE) values('" & TextBox1.Text & "')"
Dim myCommand As New Data.SqlClient.SqlCommand(myInsertQuery)
myCommand.Connection = myConnection
myConnection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

End SubPlease qualify "I get an error". We need exact error messages in order to help you.

Terri|||myConnectionString= "server=Discovery3;database=dbDVD;trusted_Connection=true"

Data Source instead of server.
Initial Catalog instead of database
trusted_connection... wtf? this looks a lot like an odbc connection string used in an oledb scenario.

I know you're new, and I'm not making fun of you too much. :)

To learn more about ado.net's connection strings, visit
http://able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider|||Actually, that connection string is fine forStandard Security using SqlConnection.

See alsoSqlConnection.ConnectionString Property for a description of the valid attributes.

Terri|||No offense taken! It probably looks like an ODBC connection because that is where I started my research.

Why would this work in Web Matrix, but not in Visual Studio? You would think if it is wrong it would be wrong in both?

I will give it a shot when I get home.

Thanks|||Again, we need to see the exact error message.

ASP.NET applications run under the ASPNET account if you are not using impersonation. Web Matrix runs under your own account. You either need to turn on impersonation in your web.config file, or you need to give appropriate SQL Server permissions to the ASPNET account.

Terri|||Terri,

Are you serious? Maybe it's muscle and visual memory cuz I swore anytime I ran it with an odbc similar connection string it didn't work for me. Learn something new every day.

I'm still more comfortable with the oledb method personally. It makes a lot more sense to me that way.|||Yes I'm serious, and don't call me Shirley.

"Data Source" and "Server" are interchangeable
"Initial Catalog" and "Database" are interchangeable
"Trusted_Connection=True" and "Integrated Security=SSPI" are interchangeable

I always usehttp://www.connectionstrings.com/ as my primary reference. Personally I can't keep any of it straight and always use that reference.

Terri|||Ok, here is the error message that I get: Line 40 is highlighted

Server Error in '/WebApplication1' Application.
------------------------

Login failed for user 'DISCOVERY3\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DISCOVERY3\ASPNET'.

Source Error:

Line 38: Dim myCommand As New Data.SqlClient.SqlCommand(myInsertQuery)
Line 39: myCommand.Connection = myConnection
Line 40: myConnection.Open()
Line 41: myCommand.ExecuteNonQuery()
Line 42: myCommand.Connection.Close()|||You are most probably using a trusted connection to your Sql Database here. You will need to add the 'DISCOVERY3\ASPNET' account to have access to the specific Database that Webapplication1 is using, and grant the account the necessary rights to access (and eventually Insert, Update and Delete) the Tables in this Database. If there are other objects, like Stored Procedures, that the ASPNET account needs to use, you will have to grant rights to EXECUTE these to this account as well.|||Sorry for being new...but when you say add the Discovery3\ASPNET account, where do I add this?|||In sql server, there's something under databases called manager or management. I forget. anyways, in there, you should see login. That's where you want to add that in. I suggest creating a different user, and applying priviledges there.

Terri, I know what you mean. however, with as much sql work that I do.. it's impossible not to remember a good connectionstring sequence, and just muscle memory.sqlsql