SQL Problem

[dcarlson]

I'm trying to log query strings that have been executed but having no luck.

Public Sub LogTransaction(strSql As String)
    Dim strSqlString As String
    
    strSqlString = "INSERT INTO Transtable (querystring, querydate) "
    strSqlString = strSqlString & "VALUES ('" & strSql & "', {" & Now & "})"

    fCnn1.Execute strSqlString

End Sub

The problem is with the strSql in the insert statement. This is a sample string.

INSERT INTO Transtable (querystring, querydate) VALUES ('INSERT INTO invoice (file_num, invoice, amount, date) VALUES ('NR9999', '444555', 3000.00, {01/01/2000})', {07/13/2000 2:29:17 PM})

I have a feeling it's because of the commas.

Querystring is a memo field.

I'm pretty sure the problem is that strSQL has single quotes in it. Try replacing all the single quotes with two single quotes (This is read as a literal single quote by a SQL engine).

Replace(strSQL, "'", "''")

[dcarlson]

My back end is FoxPro 5.0 and I don't think FoxPro recognizes 2 single quotes. Well I got a little furthur.

Public Sub LogTransaction(strSql As String)
    Dim strSqlString As String
    
    strSql = Chr(34) & strSql & Chr(34)
    
    strSqlString = "INSERT INTO Transtable (querystring, querydate) "
    strSqlString = strSqlString & "VALUES (" & strSql & ", {" & Now & "})"
    
    fCnn1.Execute strSqlString


End Sub

This work's sometimes, depending on the strSql string.

[Paul Warren]

Can you print an example of one string that works and one string that fails ? It should make it easier to identify the problem.

[dcarlson]

Ok this string inserts fine

INSERT INTO invoice (file_num, invoice, amount, date) VALUES ('NR9999', '332443', 300.00, {01/01/2000})

strSqlString looks like this

INSERT INTO Transtable (querystring, querydate) VALUES ("INSERT INTO invoice (file_num, invoice, amount, date) VALUES ('NR9999', '332443', 300.00, {01/01/2000})", {07/13/2000 4:49:16 PM})

----------------------------------------------------

The one that doesn't work is a long one:

UPDATE openclose!nrcs SET client_num='WSI450', infile_num='123456789', file_type='Life', inj_class='Soft Tissue', claimant='Derick Carlson', sex='', age=0, dob={ / / }, injuryid=1, employid=3, officeid='TB', staffid='DBBC', d_loss={ / / }, d_referral={01/01/2000}, d_closure={ / / }, closureid=14, cons_type=2, hold='F', hold_lgth=0, acc_cost=0, case_task='Task', ref_stat=0, piwage=0, pihours='', pititle='', pinoc=0, piemploy='', srnotes='', losstoref=0, reftoclose=0, occ='' WHERE file_num=='NR9999'

I know that the UPDATE statement itself works and update my table all I want to do is store the query string.

[dcarlson]

Is it possible that foxpro is mistaking 2 single quotes for 1 double quote? If so that's the problem. (sex=")