sql command problem

  • Thread starter Thread starter gv
  • Start date Start date
G

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
 
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.
__________________________________

gv said:
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)
Click to expand...
If SQLConn.State = ConnectionState.Open Then
SQLConn.Close()
End If

End Sub
Click to expand...
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 & "'" & " )
Click to expand...
If it returns a row, the DB is there, if not... it's not.
 
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
 
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.
__________________________________
 
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
 
I changed this and seams to be working now.

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

thanks again
Gerry
 
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.
__________________________________
 
Back
Top