1 INSERTnot working from 1000 VB6 & MySQL 2

[wakkara]

Hi there i have a small system for sales.

Each sales make Minimum 2 INSERTS

The first one.
INSERT on sales table the IdCostumer, Date...etc among others.

The sencond.
INSERT the IdProduct, Cost...etc

Like a sourcery 1 INSERT is not executing of 1000.

Yes thats right......1 of aprox 1000 INSERT is not working.

History ....

I have 9 Computers accessing the same database on a Central Server.
Client server arquitecture.

8 Of the computers are executing some Inserts on diferent tables(not sales)
and SELECTs from all the tables on the DB.

Just one computer is INSERTINg to the sales table.

All the computers are working at the same time, The user for everyone in the database is the native "ROOT" user with password.

Everything is working just fine, just that small orphan of a 1000 is malfunctioning.

Does Any one has a Clue of what the "F%&/%#$%&K" is going on.

Ive checked my code several times, and i dont see a logical error.

CODE
*******First INSERT*************

Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient

Rs.Open "INSERT INTO venta VALUES ('" & VarFolio & " ','" & Combo1.ItemData(Combo1.ListIndex) & "','" & Menu.IdUsuario & "','" & idpacaux & "'" _
& ",'0','" & total & "','" & Format(Now, "yyyy-mm-dd hh:mm:ss") & "','" & VarSepagaV & "','" & VarCambioV & "','" & VarTpvV & "'" _
& ",'no','no','0')", conn, adOpenDynamic, adLockOptimistic


*******Second INSERT*************

Dim w, j As Integer
w = 1
j = n + 1

Do While Not w = j


Set Rs9 = New ADODB.Recordset
Rs9.CursorLocation = adUseClient

MSFlexGrid1.Row = w

MSFlexGrid1.Col = 1
a = MSFlexGrid1.text
MSFlexGrid1.Col = 2
b = MSFlexGrid1.text

MSFlexGrid1.Col = 3
C = MSFlexGrid1.text

Rs9.Open "INSERT INTO relser (IdSR, FolioVR, PrecioR, HonMedR) VALUES (" & b & "," & VarFolio & "," & a & "," & C & ")", conn, adOpenDynamic, adLockOptimistic
w = w + 1
Loop

' It makes a loop on the MSFlexGrid1 to chek all the products selected for that sale.

I really dont know if ist me, if its MySQL, MyODBC, The network........I really need help!

Thanks for the Help Iluminated people

 

[JoeAtWork]

Rs9.Open "INSERT INTO relser (IdSR, FolioVR, PrecioR, HonMedR) VALUES (" & b & "," & VarFolio & "," & a & "," & C & ")", conn, adOpenDynamic, adLockOptimistic

I've never seen a recordset used with an INSERT statement. I'm actually quite surprised that it works at all!

Recordsets are for retrieving data - don't use them for anything else.

Use the Execute method of your connection object to do UPDATE, INSERT and DELETE.


Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[AnuragShrivastava]

Instead of writing
Rs.Open "INSERT INTO venta VALUES ('" & VarFolio & " ','" & Combo1.ItemData(Combo1.ListIndex) & "','" & Menu.IdUsuario & "','" & idpacaux & "'" _
& ",'0','" & total & "','" & Format(Now, "yyyy-mm-dd hh:mm:ss") & "','" & VarSepagaV & "','" & VarCambioV & "','" & VarTpvV & "'" _
& ",'no','no','0')", conn, adOpenDynamic, adLockOptimistic

write down

conn.execute "INSERT INTO venta VALUES ('" & VarFolio & " ','" & Combo1.ItemData(Combo1.ListIndex) & "','" & Menu.IdUsuario & "','" & idpacaux & "'" _
& ",'0','" & total & "','" & Format(Now, "yyyy-mm-dd hh:mm:ss") & "','" & VarSepagaV & "','" & VarCambioV & "','" & VarTpvV & "'" _
& ",'no','no','0')

 

[wakkara]

I really Didnt know, Rs.Open "Insert...." could unleash a problem that big..


Thanks for correcting my stupidity.

I will correct all my code, and wait if this works.

Even we dont know if this will correct the issue, you both deserve a star for correcting me.

Ill let you know if this was the problem.

 

[gmmastros]

Minor point:

[tt][blue]Dim w, j As Integer[/blue][/tt]

In this line, w is created as a variant (not an integer like you probably think it is).

Instead, you should do this...

Dim w As Integer, j As Integer

While you're at it, stop using integers altogether. Instead, use the Long data type.

Dim w As Long, j As Long

And now... for the real point of my post.

Switching to conn.execute is certainly the way to go. However, you can add more to your code so that the database errors are displayed for you. Like this...

conn.execute "INSERT INTO venta VALUES ('" & VarFolio & " ','" & Combo1.ItemData(Combo1.ListIndex) & "','" & Menu.IdUsuario & "','" & idpacaux & "'" _
& ",'0','" & total & "','" & Format(Now, "yyyy-mm-dd hh:mm:ss") & "','" & VarSepagaV & "','" & VarCambioV & "','" & VarTpvV & "'" _
& ",'no','no','0')" 

Dim i As Long
If conn.Errors.Count > 0 Then
    For i = 0 conn.Errors.Count - 1
        MsgBox conn.Errors.Item(i).Description
    Next
End If

Lastly, I have no idea if this is affecting you or not, but you may have the dreaded 'Apostrophe Problem'. If your data (that you are trying to insert) contains an apostrophe, you will get an error. To prevent this from occurring, you should double the apostrophes. Like this...

conn.execute "INSERT INTO venta VALUES ('" & [!]Replace([/!]VarFolio[!], "'", "''")[/!] & " ','" & Replace(Combo1.ItemData(Combo1.ListIndex), "'", "''") & "','" & Replace(Menu.IdUsuario, "'", "''") & "','" & Replace(idpacaux, "'", "''") & "'" _
& ",'0','" & Replace(total, "'", "''") & "','" & Format(Now, "yyyy-mm-dd hh:mm:ss") & "','" & Replace(VarSepagaV, "'", "''") & "','" & Replace(VarCambioV, "'", "''") & "','" & Replace(VarTpvV, "'", "''") & "'" _
& ",'no','no','0')" 

Dim i As Long
If conn.Errors.Count > 0 Then
    For i = 0 conn.Errors.Count - 1
        MsgBox conn.Errors.Item(i).Description
    Next
End If

By replacing an apostrophe with 2 apostrophes, a single apostrophe will be inserted in to the database. This holds true for any string type of column in your database.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wakkara]

Thanks

This is really impressive ive being programing for 2 years know with VB6 and i had the idea that

Dim w, j As Integer

Was the way to declare 2 Integers in one line.

Well that the price to learn buy your self and be an idiot.

Anyway it makes me wonder.....do you really think the variable type (veriant) would mess with the loop in any circumstance?. If thats is so...How is that...

I know now i am missing more than i tought in my code.

 

[gmmastros]

do you really think the variable type (veriant) would mess with the loop in any circumstance?

No. I'm reasonably certain that it is not causing any problems whatsoever.

The only reason I mentioned it is because it's a pet peeve of mine. (and I thought you should know).

That other stuff was the real intent of my post. In fact, if I hadn't seen the other stuff, I would not have bothered mentioning the variant issue at all.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wakkara]

Upss....i thought for a momment you had discovered the problem i have with my code.

Anyway its always very important to learn good stuff, thats what you have gave me.

Thanks a lot gmmastros.


I let you all know if the ERROR vanishes with your CODE

Thanks people

 

[BobRodes]

I wouldn't be surprised if the problem was indeed due to the apostrophe issue. If you were to look at all the records that didn't successfully insert you would be easily able to tell, of course.