VB.net Checking if database exists before connecting to it

Question

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'

Did you first run the query on your database before including it in your code? — jamiedanq, Apr 24 '16 at 23:36
Possible duplicate of [How to check if a database and tables exist in sql server in a vb .net project?](http://stackoverflow.com/questions/25162815/how-to-check-if-a-database-and-tables-exist-in-sql-server-in-a-vb-net-project) — MrGadget, Apr 24 '16 at 23:37
@MrGadget not applicable since I am also wanting to return a 'exists' or 'nope'. No solution in that way in the link to the other post. — StealthRT, Apr 24 '16 at 23:39
@StealthRT, Remove `()` following the `PRINT` keyword. I can't say how the script could work in SSMS due to the syntax error. — Dan Guzman, Apr 24 '16 at 23:47
@StealthRT The answer is there. `SELECT * FROM sys.tables WHERE name = 'YourTable' AND type = 'U'` If that returns zero rows, no database. Pass that into your command, and cmd.ExecuteNonQuery() returns an integer (not a string) of number of rows. — MrGadget, Apr 25 '16 at 00:25

Scott Hannen · Accepted Answer · 2016-04-25T00:35:17.310

2

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.

edited Apr 25 '16 at 00:35

answered Apr 24 '16 at 23:49

Scott Hannen

27,588
3
45
62

Thanks for spotting that. No error not but it keeps returning **-1** and not 'exists' or 'nope' – StealthRT Apr 24 '16 at 23:55
**Great Scott!** Ha. Thanks for the example code. It now works just fine! – StealthRT Apr 25 '16 at 02:25

score 0 · Answer 2 · answered Apr 25 '16 at 01:03

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'

VB.net Checking if database exists before connecting to it

2 Answers2