CODEWITHSUNDEEP
sqlsql-serverdatabasevb.netsql-server-2012
StealthRT
StealthRT

Reputation: 10542

VB.net Checking if database exists before connecting to it

I found the following query in order to find out if a database table was created already or not:

if db_id('thedbName') is not null
   --code mine :)
   print 'db exists'
else
   print 'nope'

Now I am wanting to use that same query within my VB.net application. This is the code I currently have elsewhere that connects to the database (that I am wanting to see if its there before doing all this):

Dim cn As SqlConnection = New SqlConnection("Data Source=DAVIDSDESKTOP;" & _
                                            "Initial Catalog=thedbName;" & _
                                            "Integrated Security=True;" & _
                                            "Pooling=False")

    Dim sql As String = "if db_id('thedbName') is not null " & vbCrLf & _
                            "Print() 'exists' " & vbCrLf & _
                        "else " & vbCrLf & _
                            "Print() 'nope'"

    Dim cmd As SqlCommand = New SqlCommand(sql, cn)

    cmd.Connection.Open()
    Dim blah As String = cmd.ExecuteNonQuery()
    cmd.Connection.Close()

Of course the issue with this is that I have to know the database name first in order to connect to the database.

I then seem to be able to connect to the master database using this:

Dim cn As SqlConnection = New SqlConnection("Data Source=DAVIDSDESKTOP;" & _
                                            "Integrated Security=True;" & _
                                            "Pooling=False")

    Dim sql As String = "if db_id('thedbName') is not null " & vbCrLf & _
                            "Print() 'exists' " & vbCrLf & _
                        "else " & vbCrLf & _
                            "Print() 'nope'"

    Dim cmd As SqlCommand = New SqlCommand(sql, cn)

    cmd.Connection.Open()
    Dim blah As String = cmd.ExecuteNonQuery()
    cmd.Connection.Close()

But that query seems to throw an error on Dim blah As String = cmd.ExecuteNonQuery() of:

Additional information: Incorrect syntax near ')'.

So I'm not all sure what I am missing in order to correct the issue with the query?

Need to know how to have the query come back and say 'exists' or 'nope'

Upvotes: 0

Views: 3269

Answers (2)

F0r3v3r-A-N00b
F0r3v3r-A-N00b

Reputation: 3003

or do it like this

select * from sys.databases where [name] = 'thedbName'

if it returns a row, then the database exists, if not then it doesn't.

To check if a table exists within a database, use this

select * from sys.objects where [name] = 'theTableName' and type_desc = 'USER_TABLE'

Upvotes: 0

Scott Hannen
Scott Hannen

Reputation: 29222

Change Print() to Print (remove the parentheses.)

Better, don't use Print at all, use select.

Dim sql As String = "if db_id('thedbName') is not null " & vbCrLf & _
                        "select 'exists' " & vbCrLf & _
                    "else " & vbCrLf & _
                        "select 'nope'"

Dim blah As String = CType(cmd.ExecuteScalar(), string)

ExecuteNonQuery returns the number of affected rows for updates and inserts. But what you are executing is a query.

ExecuteScalar returns the first column of the first row selected. The query above only returns one row with one value, so that's what it will return.

Upvotes: 2

Related Questions