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
– jamiedanq, Commented Apr 24, 2016 at 23:36
Possible duplicate of How to check if a database and tables exist in sql server in a vb .net project? — MrGadget
– MrGadget, Commented Apr 24, 2016 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
– StealthRT, Commented Apr 24, 2016 at 23:39

Scott Hannen · Accepted Answer · 2016-04-25 00:35:17Z

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, 2016 at 0:35

answered Apr 24, 2016 at 23:49

Scott Hannen

29.5k4 gold badges53 silver badges69 bronze badges

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

2 Comments

StealthRT Over a year ago

Thanks for spotting that. No error not but it keeps returning -1 and not 'exists' or 'nope'

StealthRT Over a year ago

Great Scott! Ha. Thanks for the example code. It now works just fine!

F0r3v3r-A-N00b · Accepted Answer · 2016-04-25 01:03:29Z

0

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'

answered Apr 25, 2016 at 1:03

F0r3v3r-A-N00b

3,0234 gold badges30 silver badges40 bronze badges

Collectives™ on Stack Overflow

VB.net Checking if database exists before connecting to it

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related