how to use if exists

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

39 Replies - 1939 Views - Last Post: 27 April 2012 - 02:27 AM Rate Topic: -----

#1 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

how to use if exists

Posted 15 April 2012 - 07:02 AM

hello i am trying to use the if exists command to check if a database exists but it seems i cant declare the "EXISTS"

its always giving me problems.

i am now using the code with the try and catch if there is an exception it will give a msgbox that will say db already exists.
but if there was no error it will say db created successfully the problem is now
even if the db exists or no the " finally " condition will excute and show the msg will appear which is db created successfully.
on both cases

thank you in adavnce

Is This A Good Question/Topic? 0
  • +

Replies To: how to use if exists

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: how to use if exists

Posted 15 April 2012 - 07:55 AM

Can you show us the code? Which DB Engine do you use? If you try to avoid the message box appearence in finally statement, put a boolean value in catch section and then in finally check that value
dim errorOccurred as Boolean = false
try
   'code
catch
   errorOccurred = true
finally
  if not errorOccurred then 
      'code
  end if 


NOTE: this code is just for information purpose.
Was This Post Helpful? 1
  • +
  • -

#3 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: how to use if exists

Posted 15 April 2012 - 01:57 PM

View PostIonut, on 15 April 2012 - 07:55 AM, said:

Can you show us the code? Which DB Engine do you use? If you try to avoid the message box appearence in finally statement, put a boolean value in catch section and then in finally check that value
dim errorOccurred as Boolean = false
try
   'code
catch
   errorOccurred = true
finally
  if not errorOccurred then 
      'code
  end if 


NOTE: this code is just for information purpose.



can i show u the code maybe you can help in this ?
Was This Post Helpful? 0
  • +
  • -

#4 DimitriV  Icon User is offline

  • >not activating your almonds
  • member icon

Reputation: 560
  • View blog
  • Posts: 2,666
  • Joined: 24-July 11

Re: how to use if exists

Posted 15 April 2012 - 02:02 PM

You should use IO.File.Exists, which returns a Boolean value.
Was This Post Helpful? 1
  • +
  • -

#5 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: how to use if exists

Posted 15 April 2012 - 02:03 PM

Of course, showing your code raises the chances to receive a helpful hint
Was This Post Helpful? 1
  • +
  • -

#6 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: how to use if exists

Posted 15 April 2012 - 02:06 PM

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles btnnext.Click


        If cmbserver.Text = "" Then
            MsgBox("please choose a database")
        End If
        If radiowin.Checked = True Then
            objCon = New SqlConnection("Data Source=" & Trim(TextBox1.Text) & ";integrated security=SSPI;")
        ElseIf radiSQlauth.Checked = True Then
            objCon = New SqlConnection("Data Source=" & Trim(TextBox1.Text) & ";Uid=" & userid.Text & ";Pwd=" & pwid.Text & "")
        End If
        Try
            objCon.Open()
            cmbserver.Items.Clear()
        Catch ex As Exception
            MsgBox(ex.Message)
            objCon.Close()
            Me.Close()

        End Try
        obj = New SqlCommand("select * from sysdatabases", objCon)
        'obj= New sqlcommand ( IFEXISTS(SELECT * FROM sys.sysdatabases where name=@db_name)
        Try
            dread = obj.ExecuteReader
            While dread.Read
                cmbserver.Items.Add(dread(0))
            End While
        Catch ex As Exception
            MsgBox(ex.Message)

            dread.Close()
        End Try
        strSQL = "CREATE DATABASE """ & cmbserver.Text & """"

        obj.CommandText = strSQL

        Try
            obj.ExecuteNonQuery()


        Catch ex As Exception


            MsgBox("you are choosing a Database that already exists,do you wish to proceed with the selected database?", MsgBoxStyle.OkCancel)
            'MessageBox.Show(ex.Message)
            If vbOK Then
                objCon = New SqlConnection("Data Source=" & Trim(TextBox1.Text) & ";database= """ & cmbserver.Text & """;integrated security=SSPI;")
                Dim a As MsgBoxResult = MsgBox("you are now connected to """ & cmbserver.Text & """ Database, please press ok to proceed or cancel to cancel the operation ", vbOKCancel)
                If a = vbOK Then
                    Form2.Activate()
                    Form2.Show()
                    Me.Visible = False
                Else
                    Me.Close()
                End If

            Else

                objCon.Close()
                TextBox1.Clear()
                cmbserver.ResetText()
                Me.Close()
            End If
        End Try


        MsgBox("database created/selected successfully")
        Form2.Activate()
        Form2.Show()
        Me.Visible = False


        objCon.Close()


    End Sub

thank you this is the code
Was This Post Helpful? 0
  • +
  • -

#7 DimitriV  Icon User is offline

  • >not activating your almonds
  • member icon

Reputation: 560
  • View blog
  • Posts: 2,666
  • Joined: 24-July 11

Re: how to use if exists

Posted 15 April 2012 - 02:06 PM

View Post.nethelp, on 16 April 2012 - 01:02 AM, said:

hello i am trying to use the if exists command to check if a database exists but it seems i cant declare the "EXISTS"

its always giving me problems.

i am now using the code with the try and catch if there is an exception it will give a msgbox that will say db already exists.
but if there was no error it will say db created successfully the problem is now
even if the db exists or no the " finally " condition will excute and show the msg will appear which is db created successfully.
on both cases

thank you in adavnce

Hence the name Finally. It will execute regardless of what happened in the Try…Catch part. Misusing it undermines the usefulness of the Try…Catch block which is supposed to catch errors. Errors aren't caught in Finally, so it will raise an exception.
Was This Post Helpful? 1
  • +
  • -

#8 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: how to use if exists

Posted 15 April 2012 - 02:08 PM

i wrote the last msgbox MsgBox("database created/selected successfully") because i couldnt seperate them if selected or created this msg will show :(
Was This Post Helpful? 0
  • +
  • -

#9 DimitriV  Icon User is offline

  • >not activating your almonds
  • member icon

Reputation: 560
  • View blog
  • Posts: 2,666
  • Joined: 24-July 11

Re: how to use if exists

Posted 15 April 2012 - 02:12 PM

On this blog, another method is suggested: http://blogs.msdn.co...lse-insert.aspx
Was This Post Helpful? 1
  • +
  • -

#10 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: how to use if exists

Posted 15 April 2012 - 02:17 PM

Quote

'obj= New sqlcommand ( IFEXISTS(SELECT * FROM sys.sysdatabases where name=@db_name)

Here you were very close.
The correct SQL statement is
IF EXISTS(SELECT * FROM sys.sysdatabases where name=N'databaseName')


As any IF statement, it has to continue with then and else(optional). This is why i suggest to create a stored procedure.
CREATE PROCEDURE usp_CheckDatabaseExistance(@dbName varchar(100))
AS 
IF EXISTS(SELECT * FROM sys.sysdatabases where name=@dbName)
   RETURN 1
ELSE 
   RETURN 0
GO


now you change your sqlCommand to execute stored procedures and get the value. I haven't used much ADO, so I'm not sure if you have to use ExecuteScalar instead of ExecuteQuery. but, I'm sure will figure it out.
Was This Post Helpful? 1
  • +
  • -

#11 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: how to use if exists

Posted 15 April 2012 - 02:19 PM

View PostDimitriV, on 15 April 2012 - 02:12 PM, said:

On this blog, another method is suggested: http://blogs.msdn.co...lse-insert.aspx

thank you so much i am reading it now
Was This Post Helpful? 0
  • +
  • -

#12 DimitriV  Icon User is offline

  • >not activating your almonds
  • member icon

Reputation: 560
  • View blog
  • Posts: 2,666
  • Joined: 24-July 11

Re: how to use if exists

Posted 15 April 2012 - 02:22 PM

View PostIonut, on 16 April 2012 - 08:17 AM, said:

Quote

'obj= New sqlcommand ( IFEXISTS(SELECT * FROM sys.sysdatabases where name=@db_name)

Here you were very close.
The correct SQL statement is
IF EXISTS(SELECT * FROM sys.sysdatabases where name=N'databaseName')


As any IF statement, it has to continue with then and else(optional). This is why i suggest to create a stored procedure.
CREATE PROCEDURE usp_CheckDatabaseExistance(@dbName varchar(100))
AS 
IF EXISTS(SELECT * FROM sys.sysdatabases where name=@dbName)
   RETURN 1
ELSE 
   RETURN 0
GO


now you change your sqlCommand to execute stored procedures and get the value. I haven't used much ADO, so I'm not sure if you have to use ExecuteScalar instead of ExecuteQuery. but, I'm sure will figure it out.

Of course, there isn't a command for IfExists, only If Exists.
Was This Post Helpful? 1
  • +
  • -

#13 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: how to use if exists

Posted 15 April 2012 - 02:30 PM

View PostIonut, on 15 April 2012 - 02:17 PM, said:

Quote

'obj= New sqlcommand ( IFEXISTS(SELECT * FROM sys.sysdatabases where name=@db_name)

Here you were very close.
The correct SQL statement is
IF EXISTS(SELECT * FROM sys.sysdatabases where name=N'databaseName')


As any IF statement, it has to continue with then and else(optional). This is why i suggest to create a stored procedure.
CREATE PROCEDURE usp_CheckDatabaseExistance(@dbName varchar(100))
AS 
IF EXISTS(SELECT * FROM sys.sysdatabases where name=@dbName)
   RETURN 1
ELSE 
   RETURN 0
GO


now you change your sqlCommand to execute stored procedures and get the value. I haven't used much ADO, so I'm not sure if you have to use ExecuteScalar instead of ExecuteQuery. but, I'm sure will figure it out.

i am still thinking how :D maybe i will figure it out maybe no :D hha thank you for ur support
Was This Post Helpful? 0
  • +
  • -

#14 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: how to use if exists

Posted 16 April 2012 - 05:09 AM

i can not seem to declare the EXISTS i put imports system.io and everything it seems i cant use this command if exists
Was This Post Helpful? 0
  • +
  • -

#15 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: how to use if exists

Posted 16 April 2012 - 05:22 AM

Well, I'm confused. Do you want to use File.Exists method or do you want to use an sql Statement? If you want to use File.Exists you have to import System.IO namespace. Otherwise, all you have to do is to use SqlClient objects(SqlCommand and others).
IF EXISTS as I used in my previous posts is an SQL statement and it can be used only in an SQL Server context(SQL Server Management Studio or through ADO - SQLClient namespace).
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3