Re: sql command problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 15:09:56 -0800

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.
__________________________________
"gv" <viatorg@musc.edu> wrote in message 
news:uhTOmUW4EHA.2592@TK2MSFTNGP09.phx.gbl...
>I changed this and seams to be working now.
>
> If CmdCheckDatabase.ExecuteScalar = Nothing Then
>                IsDatabaseExists = True
> End If
>
> thanks again
> Gerry
>
>
>
> "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message 
> news:%231a5rKW4EHA.2608@TK2MSFTNGP10.phx.gbl...
>> 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" <viatorg@musc.edu> wrote in message 
>> news:%23bnzxoV4EHA.1396@tk2msftngp13.phx.gbl...
>>> 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" <billvaRemoveThis@nwlink.com> wrote in message 
>>> news:%235R2eBV4EHA.2568@TK2MSFTNGP11.phx.gbl...
>>>> 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" <viatorg@musc.edu> wrote in message 
>>>> news:%23xgMW4T4EHA.3380@TK2MSFTNGP09.phx.gbl...
>>>>> 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 you create a new SqlConnection object, it is always closed. No 
>>>>>>> need to test the state.
>>>>
>>>>>        If SQLConn.State = ConnectionState.Open Then
>>>>>            SQLConn.Close()
>>>>>        End If
>>>>>
>>>>>  End Sub
>>>>>
>>>>>>>>> Why create a stored procecure to do this? You can't create a SP if 
>>>>>>>>> one already exists anyway. Why do all of this VBCRLF 
>>>>>>>>> concatenation? CRLFs are only needed for human readability. They 
>>>>>>>>> are costing you processing time and adding needless complexity.
>>>>
>>>>> 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 & "'" & " )
>>>>>>>> Why not just execute this query as the CommandText? "SELECT Name 
>>>>>>>> FROM master.dbo.sysdatabases WHERE NAME = '" & DBName & "' "
>>>> If it returns a row, the DB is there, if not... it's not.
>>>>> " & 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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Relevant Pages