Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Deleting record from SQL

Posted on 2006-05-08
5
Medium Priority
?
239 Views
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
        conn.Open()
        comm.ExecuteNonQuery()
        conn.Close()

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

This is urgent.  Please help.
0
Comment
Question by:danorme
  • 3
5 Comments
 
LVL 7

Accepted Solution

by:
wtconway earned 1336 total points
ID: 16631355
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.
0
 
LVL 3

Assisted Solution

by:fullcontact
fullcontact earned 664 total points
ID: 16631392
Remove the * from DELETE *, only need "DELETE FROM etc......."
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16631416
Oh yeah. I totally overlooked that. You also don't require the parentheses or the semicolon.
0
 
LVL 7

Assisted Solution

by:wtconway
wtconway earned 1336 total points
ID: 16631463
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.
0
 

Author Comment

by:danorme
ID: 16631756
PERFECT!!!!!!  
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Loops Section Overview
Screencast - Getting to Know the Pipeline
Suggested Courses

578 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