CODEWITHSUNDEEP
.netvb.netasp.net-mvc-4
user1799026
user1799026

Reputation: 45

How to Specifically Check if the SQL Database Has Gotten Opened? - VB.NET 2012 MVC 4

    Function SQLSelect() As ActionResult

    Dim theconnection As New SqlConnection("Data Source=(localdb);Database=test_drive_database;")
    Dim queryString As String = "SELECT * FROM ColorTable"
    Dim command As New SqlCommand(queryString)
    command.CommandTimeout = 15
    command.CommandType = CommandType.Text

    'Printing Out the SQL Result

    Return ViewData("command")

End Function

I am having an empty SQL result, with no error messages.

The URL to invoke the above function is: http://localhost:1812/Home/SQLSelect

The question is, is there a way to specifically check if the SQL database has gotten opened?

The Tools Used: Visual Studio 2012, VB.NET MVC 4, Microsoft SQL Server Compact 4.0

Notice: The database name is test_drive_database, and there is no password set for the database.

Upvotes: 0

Views: 2905

Answers (3)

Crack
Crack

Reputation: 11

You can try
If (theconnection.State = ConnectionState.Open) Then
//your connection is open

Consider not only the Open or Closed, but also the Connecting, Broken, Executing and Fetching options.

Upvotes: 0

nedm
nedm

Reputation: 163

As Steve said, you need to invoke the Open method of the SQLConnection object to open the connection.

Once you've done that, you can use the State property of the SQLConnection object to check the status of the connection at any time.

    Dim sConn As New SqlConnection(connectionString)
    If sConn.State = ConnectionState.Open Then
        Debug.Print("Your database connection is open")
    ElseIf sConn.State = ConnsectionState.Closed Then
        Debug.Print("Your database connection is closed")
    Else
        Debug.Print("Your database connection state: " & sConn.State.ToString)
    End If

Upvotes: 1

Steve
Steve

Reputation: 216293

It seems that there are some errors in your code

If you are connecting to a Sql Server Compact then connection string should be something like this

  Data Source=MyData.sdf;Persist Security Info=False;

see ConnectionStrings

Then the objects to work with are SqlCeConnection and SqlCeCommand from the namespace 

  System.Data.SqlServerCe

Two more problems are: you don't open the connection and don't associate the connection to the command

  theconnection.Open
  command.Connection = theconnection

Upvotes: 2

Related Questions