Pass a variable to a SQL statement

Getting Started
1

Good morning all,

Please forgive unsophisticated nature of this question, but I think I have thorough searched the forums and cannot find and answer. For background sake I am a very amateur programmer, but have a decent background with SQL statements, primarily on MS SQL Server, as well as a fair amount of work with Jasper Reports.

I have a very simple SQL table consisting of ID (primary key), CustomerName, Unit, Comments, Status, EntryDate, Branch, Dept for which I am trying to create a user front end. I have provided text fields for the user to enter information and added and button that runs an InsertRecord method when clicked. So far so good.

I then added a ListBox which displays the contents of the database. The fields in the ListBox are ID, CUSTOMER,UNIT, COMMENTS. Again this part is working as intended.

I have tried to add a remove record button and this where I am running into a roadblock. I am setting the celltag on the first column of each row in the listbox as it is populated. I am using the celltag to populate a variable which I can then use in other places - this also seems to be working. I have this code set on the CHANGE event handler:

Dim s As string If Me.ListIndex <> -1 Then s=(Me.CellTag(Me.ListIndex,0)) End If

What I want to do is remove the selected record from the SQL table when the user clicks the remove record button. I tried the following code without success.

[code] If mDB Is Nil Then Return

Dim sql As String

sql = “DELETE FROM CTS_PreWO where ID =”’+s //I think this is my problem.

mDB.SQLExecute(sql)

if db.error then //Check for an error
msgbox db.errormessage
//do something else like rollback
else
//No error then commit
db.commit
end[/code]

Yes, I realize that I could just use the celltag info directly in the SQL, however I am trying to learn for future situations where I may need to pass a variable in the future.

Thanks,

Joe

2

The way you’ve written it, s only exists within the Change event. Once the code for that event ends, s goes away. You would need to make it a property of the ListBox or Window instead, and remember to clear it if ListIndex = -1.

3

“DELETE FROM CTS_PreWO where ID =”‘+s+"’"

You need to complete the sql statement

4

The string only needs to be quoted if the database field is a string or text type… otherwise the resolved SQL statement should match the datatypes involved…

Kem I believe pointed out that perhaps “S” has no value when it gets to this code.

5

Thanks to all. Kem indeed hit it on the head.

I made “S” a property of the window and that did the trick.

6

Furthermore your given sql statement is having the (double) quotes wrong and is missing the closing (double) quotes.

sql = "DELETE FROM CTS_PreWO where ID ="'+s

should be

sql = "DELETE FROM CTS_PreWO where ID ='"+ s + "'"
7

This works also:
sql = “DELETE FROM CTS_PreWO WHERE ID = ‘[XX]’”
sql = replace(sql, “[XX]”, s)

Be careful with the table name “CTS_PreWO” is works on SQLite but with other SQL database you get problems. It is better to use always lowercase table names “cts_prewo”.