Hello,
OK. It is not possible for me to get remote connection to a ssqlexpress database. I tried everything posted in this forum, MSDN site etc., I used named pipes, TCP/IP ports, etc. it is just not working. And I am trying to learn creating databases, tables, stored procedures etc. by using code samples given on MSDN site.
In at least two examples given, in order to study stored procedures, examples somehow try to use remote connections to SQLEXPRESS databases in the local computer. Since remote connection isn't working I have been trying to do same thing by connecting the database directly. I do not know how to do it. The part of the code that does the connection is below. Please help. I am stuck.
Athena
The following code is from:
http://msdn2.microsoft.com/en-us/library/5181xbwd(VS.80).aspx
' This routine executes a SQL statement that deletes the database (if it exists)
' and then creates it. Private Sub CreateDatabase() Dim sqlStatement As String = _ "IF EXISTS (" & _ "SELECT * " & _ "FROM master..sysdatabases " & _ "WHERE Name = 'HowToDemo')" & vbCrLf & _ "DROP DATABASE HowToDemo" & vbCrLf & _ "CREATE DATABASE HowToDemo" ' Display a status message saying that we're attempting to connect. ' This only needs to be done the very first time a connection is ' attempted. After we've determined that MSDE or SQL Server is ' installed, this message no longer needs to be displayed. Dim statusForm As New Status() If Not didPreviouslyConnect ThenstatusForm.Show(
"Connecting to SQL Server") End If ' Attempt to connect to the SQL server instance. Try ' The SqlConnection class allows you to communicate with SQL Server. ' The constructor accepts a connection string as an argument. This ' connection string uses Integrated Security, which means that you ' must have a login in SQL Server, or be part of the Administrators ' group for this to work. Dim connection As New SqlConnection(connectionString) ' A SqlCommand object is used to execute the SQL commands. Dim cmd As New SqlCommand(sqlStatement, connection) ' Open the connection, execute the command, and close the ' connection. It is more efficient to ExecuteNonQuery when data is ' not being returned.connection.Open()
cmd.ExecuteNonQuery()
connection.Close()
' Data has been successfully submitted.didPreviouslyConnect =
TruedidCreateTable =
TruestatusForm.Close()
' Show the controls for the next step.lblArrow1.Visible =
TruelblStep2.Enabled =
TruebtnCreateTable.Enabled =
TrueMsgBox(
"Database 'HowToDemo' successfully created.", MsgBoxStyle.OKOnly, "Database Creation Status") Catch sqlExc As SqlExceptionMsgBox(sqlExc.Message, MsgBoxStyle.OKOnly,
"SQL Exception Error") Catch exc As Exception ' Unable to connect to SQL Server or MSDEstatusForm.Close()
MsgBox(exc.Message, MsgBoxStyle.OKOnly,
"Connection failed.") End Try End Sub
How to connect a local SQL Database w/o using remote connection!?
David P Maynard
My thought is you connectionstring is wrong.... Its the only thing you aren't showing in your code.
I've trimmed your code down and you'll see I've a hardcoded connection string in here to connect to the master database in the SQL express and it works successfully.
As far as SQL Express is concerned its a local database, and remote connection functionality was removed for the express editions. Alternative may be to install MSDE which is SQL server 2000 which does allow remote functionality or get a non express version of SQL Server
Module
Module1 Sub Main()CreateDatabase()
End Sub
Private Sub CreateDatabase() Dim sqlStatement As String = _ "IF EXISTS (" & _ "SELECT * " & _ "FROM master..sysdatabases " & _ "WHERE Name = 'HowToDemo1')" & vbCrLf & _ "DROP DATABASE HowToDemo1" & vbCrLf & _ "CREATE DATABASE HowToDemo1" ' Display a status message saying that we're attempting to connect. ' This only needs to be done the very first time a connection is ' attempted. After we've determined that MSDE or SQL Server is ' installed, this message no longer needs to be displayed. ' Attempt to connect to the SQL server instance. Try ' The SqlConnection class allows you to communicate with SQL Server. ' The constructor accepts a connection string as an argument. This ' connection string uses Integrated Security, which means that you ' must have a login in SQL Server, or be part of the Administrators ' group for this to work. Dim connectionString As String = "Server=.\SQLExpress;database=master;Trusted_Connection=Yes;"
Dim connection As New System.Data.SqlClient.SqlConnection(connectionString) ' A SqlCommand object is used to execute the SQL commands. Dim cmd As New System.Data.SqlClient.SqlCommand(sqlStatement, connection) ' Open the connection, execute the command, and close the ' connection. It is more efficient to ExecuteNonQuery when data is ' not being returned.connection.Open()
cmd.ExecuteNonQuery()
connection.Close()
' Data has been successfully submitted. ' Show the controls for the next step.MsgBox(
"Database 'HowToDemo' successfully created.", MsgBoxStyle.OkOnly, "Database Creation Status") Catch sqlExc As System.Data.SqlClient.SqlExceptionMsgBox(sqlExc.Message, MsgBoxStyle.OkOnly,
"SQL Exception Error") Catch exc As Exception ' Unable to connect to SQL Server or MSDEMsgBox(exc.Message, MsgBoxStyle.OKOnly,
"Connection failed.") End Try End SubEnd
Module