Urgent Help Needed

[bluelotus]

Hello

I am currently working on an application in VB 6.0 and the database is SQL Yog.

Here's the code

The purpose of this code is to calculate the number of non empty fields in the table DailyQCRed from columns 4 to 40 ignoring in between columns 34 to 37.

Please note I have tried both Dynamic, Keyset recrodsets and optimistic and pessimistic locking. Still I get errors.

In the code below I get the error that says

“Query based update failed because the row to update could not be found”

rstRecordset.Open “Select * from DailyQCRed”, cn, adOpenDynamic, adLockPessimistic

Do While Not rstRecordset.EOF

ifld = 0

For i = 4 To 40

If i < 34 Or i > 37 Then

If rstRecordset.Fields(i).Value <> “” Then

ifld = ifld + 1

End If

End If

Next i

rstRecordset!FillFields = ifld

rstRecordset.Update

rstRecordset.MoveNext

Loop

rstRecordset.Close

Can anyone help me incase there is something wrong with the logic though I think the logic is OK. I tried to get the figures by putting message boxes and they came correct.

Is there any Mysql version issue. I am working on 4.0.

Thanks

Manish

[peterlaursen]

You write “and the database is SQL Yog”.

Well … sqlyog is not a database. Sqlyig is a Client for MySQL (that is a database server). And I don't think this problem has anything to do with sqlyog at all. It' a pure MySQL/VB interface interface problem.

But undoubtedly your calculation is correct. And your SELECT statment works too.

so it must be this code

rstRecordset!FillFields = ifld

rstRecordset.Update

rstRecordset.MoveNext

that is not the correct syntax to write to a MySQL database from VB.

Don't you think that the “rstRecordset.Update” should have a SQL-string as parameter ? That would be my guess …

But it's about 8 years ago I sniffed a little to VB …

[bluelotus]

Peter

Thanks for your reply.

Yes SQLYog is an interface. I actaully meant MySql only but the yog was dancing in my head so much that I wrote yog instead. I am very new to MySQL though not RDBMS.

Incase you come to remember the correct syntax please let me know. A very imp piece of work here is getting delayed becasue of this.

Manish

[peterlaursen]

the syntax for update is (very basically)

UPDATE tbl_name SET col_name1=expr1, [ col_name2=expr2, … ] [ WHERE where_definition ]

But what is going to happen with the calculated value. Should it be written back to the database somewhere or what ever ??

If not just 'output it' somehow! and delete the line “rstRecordset.Update” if there shall be no update to the database. You write

QUOTE

The purpose of this code is to calculate the number of non empty fields in the table DailyQCRed from columns 4 to 40 ignoring in between columns 34 to 37.

if the calculated value is “7” what shall you do with that “7” ?? You have already been able to output it to a messagebox.

[bluelotus]

Peter

I can use a SQL Update query only if you can tell me the syntax for a row id or rownum in MySql.

Without this I cannot use a Sql update query as it will update the entire table if not prevented from doing so by a clause stating the rownum or rowid. I hope you have got what I am trying to say.

Yes the calculated value is to be written in the last field of the table DailyQCRed.

Please go through my first post again. After calculating the count of the non null fields (from 4 to 40 excluding the fields from 34 to 37) in a record, it is placing the count in the last field of the record.

This is done for every record. So if I do not have a rownum, the SQL Update query will affect the entire table even when it has to update record by record. In other words when say for ex the count of the fields in the first record/row is taken it should be put in the last fiel;d (named FillFields) of the table but a SQL Update query without the rownum clause will put the fig in all the rows in the last field.

That's the reason I used the update method and it is not working.

Thanks

[peterlaursen]

Isn't there any other way to identfy the correct row in DB/Table DailyQCRed other them “in the last field of the table DailyQCRed” . An key/index or a timestamp or anything that you can use in the WHERE clause

Besides I think this statement “rstRecordset!FillFields = ifld” could be put after the loop.

Like

rstRecordset.Open “Select * from DailyQCRed”, cn, adOpenDynamic, adLockPessimistic

Do While Not rstRecordset.EOF

ifld = 0

For i = 4 To 40

If i < 34 Or i > 37 Then

If rstRecordset.Fields(i).Value <> “” Then

ifld = ifld + 1

End If

End If

Next i

Loop

rstRecordset!FillFields = ifld

rstRecordset.Update (with appropriate SQL)

rstRecordset.MoveNext

rstRecordset.Close

.. then only the last one is changed as far as i can se. But that is my last word in this conversation because

first I have to go now, second I'm not a coding expert at all.

[peterlaursen]

Or rather

rstRecordset.Open “Select * from DailyQCRed”, cn, adOpenDynamic, adLockPessimistic

Do While Not rstRecordset.EOF

ifld = 0

For i = 4 To 40

If i < 34 Or i > 37 Then

If rstRecordset.Fields(i).Value <> “” Then

ifld = ifld + 1

End If

End If

Next i

rstRecordset.MoveNext

Loop

rstRecordset!FillFields = ifld

rstRecordset.Update (with appropriate SQL)

rstRecordset.Close

[bluelotus]

I am searching for a timestamp, row num function.

The code

rstRecordset!FillFields = ifld

CANNOT be put after the do loop as it will then just perform the action on the first row of the table and leave the rest of the rows.

[peterlaursen]

then I think it comes down to basic programming teqnique.

something like

Do While Not rstRecordset.EOF

..

ifld = ifld + 1

..

if EOF rstRecordset!FillFields = ifld

else **do nothing**

..

loop

[peterlaursen]

try again

something like

Do While Not rstRecordset.EOF

..

ifld = ifld + 1

..

if EOF rstRecordset.EOF rstRecordset!FillFields = ifld // 'twas what I meant.

else **do nothing**

..

loop

REALLY GOOT TO GO NOW!

[peterlaursen]

How goes ??? 🙂

Did it help you to write

QUOTE

IF rstRecordset.EOF rstRecordset!FillFields = ifld ENDIF

instead of just

QUOTE

rstRecordset!FillFields = ifld

I still believe that would be a solution since that wil only change the value of the last data in the array.

Then you can safely write back the whole array to the base.

But still I don't understand why the “rstRecordset!FillFields = ifld” must be inside the loop.

That meaans that you will write to the base 32 times.

That would make the routine 32 * slower tha just writing once!

[Ritesh]

Hope this helps.

OpenWin.org

[Shadow]

The whole problem is results from not reading ADO reference… Recordset object's Update() method does not need to be called after changing a field's value! It commits changes made since the last AddNew() call (INSERT statement…)! In case of ADO, changing a filed's value is automatic, just assign a new value and ADO will take care of the rest.

[willy]

<_<

[Ritesh]

Sorry but I have no working knowledge of VB. I have never used it :huh:

[Shadow]

Yeah, but I do! 😀 That's what I'm doing right now – coding an app in VB that uses MySql as db server…

[Author]

Posts