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

Question

    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.

Steve · Accepted Answer · 2012-11-21 00:03:36Z

2

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

answered Nov 21, 2012 at 0:03

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nedm · Accepted Answer · 2012-11-21 00:34:34Z

1

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

answered Nov 21, 2012 at 0:34

nedm

1637 bronze badges

Comments

Crack · Accepted Answer · 2023-10-12 03:47:12Z

0

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.

answered Oct 12, 2023 at 3:47

Crack

112 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related