Deleting record from SQL

Posted on 2006-05-08
Last Modified: 2010-04-23
I have a program that allows employees to upload and delete files from a shared drive.  I got the upload to work 100% and the delete to work 50%.

The files are listed in a VB listbox which are stored in SQL.
My delete process does delete the file from the shared drive, but does not delete the record from SQL.  Here is my code THAT DOES NOT WORK.
        Dim sSQL As String
        Dim conn As SqlConnection = New SqlConnection("integrated security=SSPI;data source=SERVERNAME;persist security info=False;initial catalog=DATABASE")
        Dim comm As New SqlCommand
        sSQL = "DELETE * FROM loanfiles WHERE ([lnfile]=" & (lstUploadedFiles.Text) & ");"
        comm.Connection = conn
        comm.CommandText = sSQL

I am getting a syntax error on comm.ExecuteNonQuery()

This is urgent.  Please help.
Question by:danorme
    LVL 7

    Accepted Solution

    I am assuming that your field "lnfile" is a varchar field or nvarchar (text, regardless). You need to include a tick mark (single quote) around all values for that field. Like so:

    sSQL = "DELETE * FROM loanfiles WHERE ([lnfile]='" & (lstUploadedFiles.Text) & "');"

    notice the single quote to the right of the equal sign and just before your parentheses at the end of the sql statement.
    LVL 3

    Assisted Solution

    Remove the * from DELETE *, only need "DELETE FROM etc......."
    LVL 7

    Expert Comment

    Oh yeah. I totally overlooked that. You also don't require the parentheses or the semicolon.
    LVL 7

    Assisted Solution

    Another thing to keep a eye on is this. If there is an apostrophe in your filename then SQL will crash. To get around this you double up the apostrophe and SQL will read it as an apostrophe that is part of the value, not a single quote ending the value. For example:

    lstUploadedFiles.Text = "c:\john's file.txt"  '<- notice the apostrophe
    sSQL = "DELETE FROM loanfiles WHERE lnfile='" & lstUploadedFiles.Text & "'"

    this will cause SQL to error because you now have a single quote inside the value. To fix that, do this:
    lstUploadedFiles.Text = "c:\john's file.txt"  '<- notice the apostrophe
    sSQL = "DELETE FROM loanfiles WHERE lnfile='" & lstUploadedFiles.Text.Replace("'","''") & "'"

    It's hard to see it on here but you will notice in your code editor that the Replace() method is taking all apostrophes and making them double apostrophes. Now SQL Server will understand that you want to keep those as part of the value.

    Author Comment


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now