[RESOLVED] Need Help With SQLSTRING's (VB6)

How Do DROP as table with ADO if table exists and Create field if dont exist's

ADO Code:

sqlstr = "DROP TABLE Employees"

error's if it doesnot Exist's


similarly

ADO Code:

' Create the Employees table.
    conn.Execute _
        "CREATE TABLE Employees(" & _
            "EmployeeId INTEGER      NOT NULL," & _
            "LastName   VARCHAR(40)  NOT NULL," & _
            "FirstName  VARCHAR(40)  NOT NULL)"

error's if EmployeeID or else field's exist's


please help me out !!

You can use the EXISTS function to determine if the table exist before dropping it.

Code:

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO

---

To determine if a field exist before adding it.

Code:

---

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Employees’ AND COLUMN_NAME = ‘LastName’)
BEGIN
  ALTER TABLE Employees ADD LastName VARCHAR(40)  NOT NULL
END

---

Another aproach would be to just ignore that particular error by catching it with an 'On Error Go To' then issuing a 'Resume Next' if it's the particular error in question (nb, not the same as using On Error Resume Next). That would save you a server round trip.

Quote:

---

Originally Posted by FunkyDexter

Another aproach would be to just ignore that particular error by catching it with an 'On Error Go To' then issuing a 'Resume Next' if it's the particular error in question (nb, not the same as using On Error Resume Next). That would save you a server round trip.

---

Sorry but I am not sure why would that save a server round trip. :confused:

Because he won't have to make a call to see if the table exists so there's only ever one call whereas if he checks to see if the table exists and finds it doesn't he still has to make a call to create it.

If he puts the check and the create in a stored procedure then it would only need one call but looking at his original post it looks like he's issuing direct SQL and handling the error client side.

Can't he send the script together with the checking in one go? This works when I tested it.

Code:

---

Dim strSQL As String
strSQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " & _
        "drop table [dbo].[Test]"
adoConn.Execute strSQL
MsgBox "done"

---

Actualy that's true.:thumb:

Ill Post My Code Snippet, actually my project is on a different PC that dont had INTERNET access i got this problem last nite !!

ill post my code may be u guy's get some tweak in, i have written too much for small stuff :|

note : i am not using ADO Control, just using reference to MS ADO Component 2.7

vb Code:

strSQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " & _
        "drop table [dbo].[Test]"

have never even seen such complicated query :))

I did not have to think of that query, Enterprise Manager spews it out when you get the sql design of a table.

Quote:

---

Originally Posted by dee-u

You can use the EXISTS function to determine if the table exist before dropping it.

Code:

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO

---

To determine if a field exist before adding it.

Code:

---

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Employees’ AND COLUMN_NAME = ‘LastName’)
BEGIN
  ALTER TABLE Employees ADD LastName VARCHAR(40)  NOT NULL
END

---

---

these queries didnt worked :( !

thought i manage to get my program working.

i feel that i have written much for small thing, may be if any of u can tweak around codes :)

mADO.bas Code:

Option Explicit
 
Public ADOCON As ADODB.Connection
Public ADORECORD As ADODB.Recordset
Public Enum dbErrorType
    [Connected] = 1
    [PasswordChanged] = 2
    [InvalidFile] = 1001
    [InvalidPassword] = 1002
    [FieldNotExist] = 1003
    [NoTableFound] = 1005
    [AlreadyConnected] = 1004
    [zElseError] = 999
End Enum
 
Public Function ConnectToDB(ByVal dbPath As String, Optional ByVal Password As String = "") As dbErrorType
Dim conStr As String
On Error GoTo HanDle
 
GoTo DoConnect
 
 
DoConnect:
    conStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=" & Password & ";"
 
    ADOCON.CursorLocation = adUseClient
    ADOCON.Mode = adModeReadWrite
    ADOCON.Open conStr
    If ADOCON.State = 1 Then ConnectToDB = 1: Exit Function
    
HanDle:
    If Err.Number = 0 Then Exit Function
    ConnectToDB = GlobalError(Err.Number)
    Err.Clear
 
End Function
 
Public Function ChangeDatabasePassword(dbPath As String, oldPassword As String, newPassword As String) As dbErrorType
Dim sqlStr As String, conStr As String
On Error GoTo HanDle
 
    
    conStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=" & oldPassword & ";"
    
    If ADOCON.State <> 0 Then ADOCON.Close
    
    sqlStr = "ALTER Database Password " & newPassword & " " & oldPassword & ";"
    ADOCON.Mode = adModeShareExclusive
    ADOCON.Open conStr
    ADOCON.Execute sqlStr
    ADOCON.Close
    databasePassword = newPassword
    Call mADO.ConnectToDB(dbPath, newPassword)
    
    If ADOCON.State = 1 Then ChangeDatabasePassword = PasswordChanged
    
HanDle:
    If Err.Number = 0 Then Exit Function
    ChangeDatabasePassword = GlobalError(Err.Number)
    Err.Clear
 
End Function
 
Public Function GlobalError(ByVal eNumber As Long) As dbErrorType
    Select Case eNumber
        Case -2147467259
            GlobalError = InvalidFile
        Case -2147217843
            GlobalError = InvalidPassword
        Case -2147217904
            GlobalError = FieldNotExist
        Case 3705
            GlobalError = AlreadyConnected
        Case -2147217865
            GlobalError = NoTableFound
        Case Else
            MsgBox "Global - " & Err.Description
            Clipboard.Clear
            Clipboard.SetText Err.Number
            GlobalError = zElseError
    End Select
End Function

LoginButtonClick Code:

Private Sub cmdLogin_Click()
On Error GoTo HanDle
Dim cRet As dbErrorType, sqlStr As String, onError As dbErrorType, tSql As String
    
    If Dir(txtDbLoc) = "" Then
        Status "Select Proper Database Location"
        Exit Sub
    End If
    If Len(txtPass) <= 0 Then
        txtPass.SetFocus
        Status "Input Database Password"
        Exit Sub
    End If
    If Len(txtKey) = 0 Then
        txtKey.SetFocus
        Status "Input Encryption Key"
        Exit Sub
    End If
    If Len(txtKey) < 6 Then
        txtKey.SelStart = 0
        txtKey.SelLength = Len(txtKey)
        txtKey.SetFocus
        Exit Sub
    End If
    
    databasePassword = txtPass
    masterPassword = txtKey
    databasePath = txtDbLoc
    
    Call sSetting("dbLocation", txtDbLoc)
 
    
    cRet = ConnectToDB(databasePath, databasePassword)
        
    
AUTH:
        
        If ADOCON.State = 1 Then
        
                With ADORECORD
                    If .State <> 0 Then .Close
                    sqlStr = "Select * From testTable Where testUser='USERNAME'"
                    Call .Open(sqlStr, ADOCON, adOpenStatic, adLockOptimistic)
                    If .RecordCount = 1 Then
                        If DecodeStr(!testPass, masterPassword) = "PASSWORD" Then
                            Status "Logged In Sucessfully"
                            isLoggedIn = True
                            Call ShowFrame(mainFrame)
                            Call PopulateList("ALL")
                            Call PopulateLoginType
                            Call LoadMenu
                        Else
                            Status "Invalid Private Key"
                            txtKey.SelStart = 0
                            txtKey.SelLength = Len(txtKey)
                            txtKey.SetFocus
                            ADOCON.Close
                        End If
                    Else
                        Status "Corrupt Database"
                    End If
                End With
        Else
            If cRet = 1002 Then
                Status "Database Login Failed"
                txtPass.SelStart = 0
                txtPass.SelLength = Len(txtPass)
                txtPass.SetFocus
            Else
                Status "Error Connecting To Database"
                Status cRet
            End If
        End If
 
    
    
HanDle:
 
    If Err.Number = 0 Then Exit Sub
    MsgBox "Raised"
    onError = GlobalError(Err.Number)
    If onError = FieldNotExist Then
        ADOCON.Execute "DROP TABLE testTable"
        'On Error GoTo 0
        ADOCON.Execute "CREATE TABLE testTable(" & _
            "testUser   VARCHAR(50)  NOT NULL," & _
            "testPass   VARCHAR(50)  NOT NULL)"
        ADOCON.Execute "INSERT INTO testTable VALUES ('USERNAME', '" & EncodeStr("PASSWORD", masterPassword) & "')"
        GoTo AUTH
    ElseIf onError = AlreadyConnected Then
        Resume Next
    ElseIf onError = InvalidPassword Then
        Status "Invalid Database Password"
    ElseIf onError = NoTableFound Then
        On Error Resume Next
        'ADOCON.Execute "DROP TABLE testTable"
        'On Error GoTo 0
        ADOCON.Execute "CREATE TABLE testTable(" & _
            "testUser   VARCHAR(50)  NOT NULL," & _
            "testPass   VARCHAR(50)  NOT NULL)"
        ADOCON.Execute "INSERT INTO testTable VALUES ('USERNAME', '" & EncodeStr("PASSWORD", masterPassword) & "')"
        
       
        GoTo AUTH
        
    Else
        MsgBox onError
    End If
    Err.Clear
    
End Sub