What is wrong with my codes to connect VB.net to sql server 2005 Express database?

Curious Person

Hi am very new with VB.net (Visual Studio 2005) and the first time try to use the sql server 2005 express. I did create a trial.mdf (local server) and complete procedure to attach the trial1.mdf in Microsoft SQL Management Studio Express.

I try to use VB to connect /open/close it with the following codes: (but failed)

-----------------------------------------------

Imports System.Data.SqlClient

Public Class Form1
'Link database
Dim DB_Location = "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Trial1.mdf"
Dim MyConnection = "Server=localhost; DataBase= " & DB_Location & "Integrated Security=SSPI"
Dim sqlConnection As New SqlConnection(MyConnection)

'Use database
Dim strSQL As String = "SELECT FROM Trial1 WHERE col2 = 'trial' "
Dim cmd As New SqlCommand(strSQL, MyConnection)

sqlConnection.Open()  ' ----> error
cmd.ExecuteNonQuery() ' ----> error
sqlConnection.Close() ' ----> error

End Class

-------------------------------------------

At the last 3 line of codes I got the errors ... can someone explain why & help me to correct it

Thanks for any help

Jens K. Suessmeyer


Hi,

as of your explanation you are using (want to use) a server attached database. If you already attached the database to the server instance, you don¡¯t have to name the whole path of the database. Just use the name name that you specified for the database in SQL Server Managment Studio. the connection string would then be something like:

Server=localhost; DataBase=YourDatabaseName;Integrated Security=SSPI

If this does not work, please come back with the exception message you are getting.

HTH; Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Curious Person

I did modify as you suggested, the 3 error remain the same, and here is their message:

LinkSqlServer2005Express\Form1.vb(15) : error BC30188: Declaration expected.

That means I did have syntax on the following codes: (or missing Import)

sqlConnection.Open() ' ----> error

cmd.ExecuteNonQuery() ' ----> error

sqlConnection.Close() ' ----> error

Thanks

Arnie Rowland

You don't need the DB_Location variable, and the connectionstring 'should' be:

Dim MyConnection string = "Server=localhost; DataBase= Trial1; Integrated Security=SSPI"

And the following fails because 'MyConnection has not been defined as a connection object.

Dim cmd As New SqlCommand(strSQL, MyConnection)

Try using SQLConnection here, or redefine MyConnection as a connection object -not a string.

You don't need both MyConnection and SQLConnection.

Curious Person

Can you more specific, as you know I am new VB & SQL and now I am confusing:

I use:

Dim MyConnection string = "Server=localhost; DataBase= Trial1; Integrated Security=SSPI" (error)

If I use:

Dim MyConnection = "Server=localhost; DataBase= Trial1; Integrated Security=SSPI"

then it is OK

But I can not do the rest as you suggested, would you please provide some correct declarations, I will very appreciate it!

Arnie Rowland

My previous 'suggestion' was missing the keyword 'AS' in front of string causing the error.

Reviewing your original code:

Dim DB_Location = "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Trial1.mdf" (REMOVE -NOT NEEDED)

Dim ConnStr as String = "Server=localhost;DataBase=Trial1;Integrated Security=SSPI"
Dim sqlConnection As New SqlConnection(ConnStr)

'Use database
Dim strSQL As String = "SELECT FROM Trial1 WHERE col2 = 'trial' "
Dim cmd As New SqlCommand(strSQL, SQLConnection)

I suggest ConnStr is more understandable than MyConnection, since it is a connnection string and NOT a connection object.

Curious Person

Thanks for help, it compile OK now ... but might be you forget to help me some codes how to connect/open (to verify data) and close my database

I am very appreciate a lots that you are very patient to help with a new guy like me

Many thanks

Arnie Rowland

You are executing a query that returns data, and yet you specify 'ExecuteNonQuery'. That is incorrect. (ExecuteNonQuery does not return data.)

You will need a VB object to capture the qurey results, either a dataset or datareader object. I suggest that one of the VB.NET forums will be a better place to get the help you need.

Good luck.

Curious Person

Thank, I will go there