Results 1 to 12 of 12

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2008
    Posts
    84

    Resolved [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:
    1. sqlstr = "DROP TABLE Employees"

    error's if it doesnot Exist's


    similarly

    ADO Code:
    1. ' Create the Employees table.
    2.     conn.Execute _
    3.         "CREATE TABLE Employees(" & _
    4.             "EmployeeId INTEGER      NOT NULL," & _
    5.             "LastName   VARCHAR(40)  NOT NULL," & _
    6.             "FirstName  VARCHAR(40)  NOT NULL)"

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


    please help me out !!

  2. #2
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Need Help With SQLSTRING's (VB6)

    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
    Last edited by dee-u; Aug 20th, 2009 at 07:48 AM.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Need Help With SQLSTRING's (VB6)

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Need Help With SQLSTRING's (VB6)

    Quote Originally Posted by FunkyDexter View Post
    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.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Need Help With SQLSTRING's (VB6)

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Need Help With SQLSTRING's (VB6)

    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"
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Need Help With SQLSTRING's (VB6)

    Actualy that's true.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Aug 2008
    Posts
    84

    Re: Need Help With SQLSTRING's (VB6)

    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 :|

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Aug 2008
    Posts
    84

    Re: Need Help With SQLSTRING's (VB6)

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

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

    have never even seen such complicated query )

  10. #10
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Need Help With SQLSTRING's (VB6)

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


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Aug 2008
    Posts
    84

    Re: Need Help With SQLSTRING's (VB6)

    Quote Originally Posted by dee-u View Post
    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 !

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Aug 2008
    Posts
    84

    Re: Need Help With SQLSTRING's (VB6)

    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:
    1. Option Explicit
    2.  
    3. Public ADOCON As ADODB.Connection
    4. Public ADORECORD As ADODB.Recordset
    5. Public Enum dbErrorType
    6.     [Connected] = 1
    7.     [PasswordChanged] = 2
    8.     [InvalidFile] = 1001
    9.     [InvalidPassword] = 1002
    10.     [FieldNotExist] = 1003
    11.     [NoTableFound] = 1005
    12.     [AlreadyConnected] = 1004
    13.     [zElseError] = 999
    14. End Enum
    15.  
    16. Public Function ConnectToDB(ByVal dbPath As String, Optional ByVal Password As String = "") As dbErrorType
    17. Dim conStr As String
    18. On Error GoTo HanDle
    19.  
    20. GoTo DoConnect
    21.  
    22.  
    23. DoConnect:
    24.     conStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=" & Password & ";"
    25.  
    26.     ADOCON.CursorLocation = adUseClient
    27.     ADOCON.Mode = adModeReadWrite
    28.     ADOCON.Open conStr
    29.     If ADOCON.State = 1 Then ConnectToDB = 1: Exit Function
    30.    
    31. HanDle:
    32.     If Err.Number = 0 Then Exit Function
    33.     ConnectToDB = GlobalError(Err.Number)
    34.     Err.Clear
    35.  
    36. End Function
    37.  
    38. Public Function ChangeDatabasePassword(dbPath As String, oldPassword As String, newPassword As String) As dbErrorType
    39. Dim sqlStr As String, conStr As String
    40. On Error GoTo HanDle
    41.  
    42.    
    43.     conStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=" & oldPassword & ";"
    44.    
    45.     If ADOCON.State <> 0 Then ADOCON.Close
    46.    
    47.     sqlStr = "ALTER Database Password " & newPassword & " " & oldPassword & ";"
    48.     ADOCON.Mode = adModeShareExclusive
    49.     ADOCON.Open conStr
    50.     ADOCON.Execute sqlStr
    51.     ADOCON.Close
    52.     databasePassword = newPassword
    53.     Call mADO.ConnectToDB(dbPath, newPassword)
    54.    
    55.     If ADOCON.State = 1 Then ChangeDatabasePassword = PasswordChanged
    56.    
    57. HanDle:
    58.     If Err.Number = 0 Then Exit Function
    59.     ChangeDatabasePassword = GlobalError(Err.Number)
    60.     Err.Clear
    61.  
    62. End Function
    63.  
    64. Public Function GlobalError(ByVal eNumber As Long) As dbErrorType
    65.     Select Case eNumber
    66.         Case -2147467259
    67.             GlobalError = InvalidFile
    68.         Case -2147217843
    69.             GlobalError = InvalidPassword
    70.         Case -2147217904
    71.             GlobalError = FieldNotExist
    72.         Case 3705
    73.             GlobalError = AlreadyConnected
    74.         Case -2147217865
    75.             GlobalError = NoTableFound
    76.         Case Else
    77.             MsgBox "Global - " & Err.Description
    78.             Clipboard.Clear
    79.             Clipboard.SetText Err.Number
    80.             GlobalError = zElseError
    81.     End Select
    82. End Function

    LoginButtonClick Code:
    1. Private Sub cmdLogin_Click()
    2. On Error GoTo HanDle
    3. Dim cRet As dbErrorType, sqlStr As String, onError As dbErrorType, tSql As String
    4.    
    5.     If Dir(txtDbLoc) = "" Then
    6.         Status "Select Proper Database Location"
    7.         Exit Sub
    8.     End If
    9.     If Len(txtPass) <= 0 Then
    10.         txtPass.SetFocus
    11.         Status "Input Database Password"
    12.         Exit Sub
    13.     End If
    14.     If Len(txtKey) = 0 Then
    15.         txtKey.SetFocus
    16.         Status "Input Encryption Key"
    17.         Exit Sub
    18.     End If
    19.     If Len(txtKey) < 6 Then
    20.         txtKey.SelStart = 0
    21.         txtKey.SelLength = Len(txtKey)
    22.         txtKey.SetFocus
    23.         Exit Sub
    24.     End If
    25.    
    26.     databasePassword = txtPass
    27.     masterPassword = txtKey
    28.     databasePath = txtDbLoc
    29.    
    30.     Call sSetting("dbLocation", txtDbLoc)
    31.  
    32.    
    33.     cRet = ConnectToDB(databasePath, databasePassword)
    34.        
    35.    
    36. AUTH:
    37.        
    38.         If ADOCON.State = 1 Then
    39.        
    40.                 With ADORECORD
    41.                     If .State <> 0 Then .Close
    42.                     sqlStr = "Select * From testTable Where testUser='USERNAME'"
    43.                     Call .Open(sqlStr, ADOCON, adOpenStatic, adLockOptimistic)
    44.                     If .RecordCount = 1 Then
    45.                         If DecodeStr(!testPass, masterPassword) = "PASSWORD" Then
    46.                             Status "Logged In Sucessfully"
    47.                             isLoggedIn = True
    48.                             Call ShowFrame(mainFrame)
    49.                             Call PopulateList("ALL")
    50.                             Call PopulateLoginType
    51.                             Call LoadMenu
    52.                         Else
    53.                             Status "Invalid Private Key"
    54.                             txtKey.SelStart = 0
    55.                             txtKey.SelLength = Len(txtKey)
    56.                             txtKey.SetFocus
    57.                             ADOCON.Close
    58.                         End If
    59.                     Else
    60.                         Status "Corrupt Database"
    61.                     End If
    62.                 End With
    63.         Else
    64.             If cRet = 1002 Then
    65.                 Status "Database Login Failed"
    66.                 txtPass.SelStart = 0
    67.                 txtPass.SelLength = Len(txtPass)
    68.                 txtPass.SetFocus
    69.             Else
    70.                 Status "Error Connecting To Database"
    71.                 Status cRet
    72.             End If
    73.         End If
    74.  
    75.    
    76.    
    77. HanDle:
    78.  
    79.     If Err.Number = 0 Then Exit Sub
    80.     MsgBox "Raised"
    81.     onError = GlobalError(Err.Number)
    82.     If onError = FieldNotExist Then
    83.         ADOCON.Execute "DROP TABLE testTable"
    84.         'On Error GoTo 0
    85.         ADOCON.Execute "CREATE TABLE testTable(" & _
    86.             "testUser   VARCHAR(50)  NOT NULL," & _
    87.             "testPass   VARCHAR(50)  NOT NULL)"
    88.         ADOCON.Execute "INSERT INTO testTable VALUES ('USERNAME', '" & EncodeStr("PASSWORD", masterPassword) & "')"
    89.         GoTo AUTH
    90.     ElseIf onError = AlreadyConnected Then
    91.         Resume Next
    92.     ElseIf onError = InvalidPassword Then
    93.         Status "Invalid Database Password"
    94.     ElseIf onError = NoTableFound Then
    95.         On Error Resume Next
    96.         'ADOCON.Execute "DROP TABLE testTable"
    97.         'On Error GoTo 0
    98.         ADOCON.Execute "CREATE TABLE testTable(" & _
    99.             "testUser   VARCHAR(50)  NOT NULL," & _
    100.             "testPass   VARCHAR(50)  NOT NULL)"
    101.         ADOCON.Execute "INSERT INTO testTable VALUES ('USERNAME', '" & EncodeStr("PASSWORD", masterPassword) & "')"
    102.        
    103.        
    104.         GoTo AUTH
    105.        
    106.     Else
    107.         MsgBox onError
    108.     End If
    109.     Err.Clear
    110.    
    111. End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width