Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: delete sql not working.

    hi

    win 2k and xp
    excel 2k
    sqlserver version 7
    the code below execute but when i query the table, the data is still in there. can anyone help?
    Code:
    Sub UPDATED_DELETE()
    
        Dim conn As ADODB.Connection
        Dim cmd As ADODB.Command
        dim MyDate As Date
        
        MyDate = Format(Date, "MM/DD/YYYY")
        
        Set conn = New ADODB.Connection
        Set cmd = New ADODB.Command
        
        conn.ConnectionString = "ODBC=SQL Server;DSN=LOGCALL_TABLE;UID=richard;APP=Microsoft® Query;WSID=RICHARD;Trusted_Connection=Yes"
        conn.ConnectionTimeout = 30
        conn.Open
        
        Set cmd.ActiveConnection = conn
        
        cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
        cmd.Execute
        conn.Close
    End Sub

  2. #2
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    delete sql not working

    I think it has to do with the date and the way it is formated in my sql statement.

    Code:
    .........and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
        cmd.Execute
    i had it formated this way before as required by ms access, but that does not work.

    Code:
    ...... and LOGCALL_DB.DateOnCall = # " & Date & " #;"
    any thoughts on how to format this baby?

    thanks in advance.

    Alex

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    delete sql not working

    I think it has to do with the date and the way it is formated in my sql statement.

    Code:
    .........and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
        cmd.Execute
    i had it formated this way before as required by ms access, but that does not work.

    Code:
    ...... and LOGCALL_DB.DateOnCall = # " & Date & " #;"
    any thoughts on how to format this baby?

    thanks in advance.

    Alex

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to begin with, LIKE should only be used with strings

    what is DateOnCall? datetime or varchar?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Be smart and create a stored procedure in you SQL Server database that accepts StopTime, EndTime, ClientName, Representative, and DateOnCall as parameters and deletes the records you want. Then just call the procedure with the values from your spreadsheet.

    ...and look up the syntax and usage of the LIKE operator too. I suspect it needs wildcards, or at the very least is inappropriate for Date values (as Rudy said).

    But I really think the problem is in your methodology, not your syntax...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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