StealthRT StealthRT - 7 months ago 30
Vb.net Question

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'

Answer

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.