Dear all,

         My program is as follows:

         dim Conn as new ADODB.connection
         conn.conectionstring =''......"
         conn.open
         strsql = "insert into tableA values('test')"
         conn.execute strsql

         strsql2 = "insert into event_log values ('" & strsql & " ') "
         conn.execute strsql2

         The problem is that sometimes I can find in the event_log
table of strsql. But there is no record added to TableA. It is clear
that Conn.execute strsql does not insert a record or the
conn.execute is skipped. I want to use conn.execute strsql,recaffeted
statement. Then I will check the recaffeted variable. If the
recaffeted is = 0. Then I will loop the conn.execute strsql again. But
I am afraid if the recaffted is still equal to 0, the loop will become
a dead loop. So does anyone encounter the same problem as me? And how
you solve it. If I set a limited for next loop for the conn.execute
strsql. e.g
for i =0 to 5
conn.execute strsql,recaffeted
next i

I think the above is not a good solution to this problem, because if
all 5 times for next loop is executed, and the recaffted is still 0.
So it will be useless. So what I worried is if I use
while loop to loop the conn.execute until the recaffeted returns 1, it
may entered a dead loop if the recaffted is always equal to 0. But if
I used for next loop, the recaffected is still equal to 0 after the
for loop, then still no record is added. How can I solve that?
Thanks.

Is it possible that strsql might contain an apostrophe ( ' )? Because the
simple string concatenation you're doing there is the worst possible way to
build a dynamic SQL string.

Oh, wait, after typing the code below I see that you're logging your
previous SQL statement itself, and it of course has apostrophes in it.
There's the problem!

I'm guessing you have On Error Resume Next in there somewhere. Because since
strsql contains an unescaped apostrophe, the INSERT statement will fail, and
with error handling turned off you wouldn't know about it.

You don't even need to create a stored procedure (which would be the best
route, but I don't know if you're using SQL Server or Access, since you
didn't tell us), you can just do something like this:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandText = "insert into event_log values (@LogText)"
cmd.Parameters.Add cmd.CreateParameter("@LogText", adVarChar, <whatever the
size of the event_log column is>)
cmd.Parameters("@LogText") = strsql
cmd.Execute Options:=adExecNoRecords

I typed this by hand, so there my be a typo (I'm especially not sure about
adExecNoRecords), but you should be able to figure out what's going on.

The full code is as follows:

strSQL = "insert into carton
(inbound_no,customer_invoice_no,carton_number) values ('" & _
                  Trim(txtInboundNumber.Text) & "','" & Trim
(txtInvoiceNo.Text) & "','" & _
                  Trim(txt_Barcode.Text) & "')"

        Cnn.Execute strSQL

        strSQL2 = "insert into event_log
(UserName,Update_Date,Update_time,sql_query) values ('" & _
                   Global_User_Name & "','" & Date & "','" & Time &
"','" & Replace(strSQL, "'", "''") & "')"
        Cnn.Execute strSQL2

If I use On Eroor Resume 0, I am afraid it will become a dead loop if
the recaffeted
is still 0 all the time.

Thanks

Rubberband Line with ScrollBar
Active X and Vista
Why there is a limit of 65,536 bytes when writing to file?
Value > Long Data Type
How to make this call from form to control
Differences between VB amd VBA and VBA Education
Knowing what launched an EXE that used CreateProcess()?
ShellExecute to html file in VB.net
VB 6 IDE on 64-bit Windows
ACCURACY of making templates on a printer