sql command problem

[gv]

Hi all,

Not sure if I'm posting in the right newsgroup

I have devolped a program in VB.NET and the first thing I do is check to see
if
the database Exist. Well the first time I run the program I get a error and
then breaks to
the line "CmdCheckSP.ExecuteNonQuery()".

I run it again and then presto, no problem. I'm confused?

Can someone please help me.


Public Sub SQLConnection()

Dim StrConn As String = "User ID=" & loginuser & ";" & _
"Password=" & loginpass & ";" & _
"Initial Catalog=" & loginDatabase & ";" & _
"Data Source=" & loginIPaddress

SQLConn = New SQLConnection(StrConn)

If SQLConn.State = ConnectionState.Open Then
SQLConn.Close()
End If

End Sub

Friend Function IsDatabaseExists(ByVal DBname) As Boolean

Dim SQLDataBase As String = _
" CREATE PROCEDURE CheckSQLDatabase
" & vbCrLf & _
"
" & vbCrLf & _
" @out Integer OUTPUT
" & vbCrLf & _
" AS
" & vbCrLf & _
" IF exists (select * from master.dbo.sysdatabases
where name = " & vbCrLf & _
"'" & DBname & "'" & " )
" & vbCrLf & _
" BEGIN
" & vbCrLf & _
" set @out = 1
" & vbCrLf & _
" END
" & vbCrLf & _
" ELSE
" & vbCrLf & _
" BEGIN
" & vbCrLf & _
" set @out = 0
" & vbCrLf & _
" END "


'Call main connection
SQLConnection()

Dim CmdCheckSP As New SqlCommand(SQLDataBase)
CmdCheckSP.Connection = SQLConn
SQLConn.Open()
CmdCheckSP.ExecuteNonQuery()

thanks
Gerry

 

[William \(Bill\) Vaughn]

Ah, I see a number of issues here: See >>>>>


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi all,

Not sure if I'm posting in the right newsgroup

I have devolped a program in VB.NET and the first thing I do is check to
see if
the database Exist. Well the first time I run the program I get a error
and then breaks to
the line "CmdCheckSP.ExecuteNonQuery()".

I run it again and then presto, no problem. I'm confused?

Can someone please help me.


Public Sub SQLConnection()

Dim StrConn As String = "User ID=" & loginuser & ";" & _
"Password=" & loginpass & ";" & _
"Initial Catalog=" & loginDatabase & ";" & _
"Data Source=" & loginIPaddress

SQLConn = New SQLConnection(StrConn)

If SQLConn.State = ConnectionState.Open Then
SQLConn.Close()
End If

End Sub

Friend Function IsDatabaseExists(ByVal DBname) As Boolean

Dim SQLDataBase As String = _
" CREATE PROCEDURE CheckSQLDatabase " & vbCrLf & _
" " & vbCrLf & _
" @out Integer OUTPUT " & vbCrLf & _
" AS " & vbCrLf & _
" IF exists (select * from master.dbo.sysdatabases
where name = " & vbCrLf & _
"'" & DBname & "'" & " )

If it returns a row, the DB is there, if not... it's not.

 

[gv]

Thanks for your help


I changed it to this but I get an error on SQLConn.Open()

"A first chance exception of type 'System.Data.SqlClient.SqlException'
occurred in system.data.dll
Additional information: System error."


Friend Function IsDatabaseExists(ByVal DBname)
Try
SQLConnection()
Dim CmdCheckDatabase As New SqlCommand("SELECT Name FROM
master.dbo.sysdatabases WHERE NAME = '" & DBname & "' ")
CmdCheckDatabase.Connection = SQLConn
SQLConn.Open()
CmdCheckDatabase.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

SQLConn.Close()

End Function

thanks
Gerry

 

[William \(Bill\) Vaughn]

Does your login have rights to Master?
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[gv]

yes

I actually fixed that problem but have a new error.

"A first chance exception of type 'System.NullReferenceException' occurred
in Test6.exe
Additional information: Object reference not set to an instance of an
object."

at this line "If CmdCheckDatabase.ExecuteScalar.ToString() = Nothing Then"

Public Function IsDatabaseExists(ByVal DBname) As Boolean
Try
SQLConnectionMaster()
Dim CmdCheckDatabase As New SqlCommand("SELECT Name FROM
master.dbo.sysdatabases WHERE NAME = '" & DBname & "' ")
CmdCheckDatabase.Connection = SQLConnMaster
SQLConnMaster.Open()
If CmdCheckDatabase.ExecuteScalar.ToString() = Nothing Then
IsDatabaseExists = True
End If

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

SQLConnMaster.Close()

End Function

thanks
Gerry

 

[gv]

I changed this and seams to be working now.

If CmdCheckDatabase.ExecuteScalar = Nothing Then
IsDatabaseExists = True
End If

thanks again
Gerry

 

[William \(Bill\) Vaughn]

Remember that the ExecuteScalar returns an object
myObj = Cmd.ExecuteScalar
is what I usually use


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________