MySQL Updating and Image storing....

Okay heres the code:


Private Sub Closesave_Click()
'On Error Resume Next
Dim intnum As Integer

' Open the picture file
intnum = FreeFile
Open strImageName For Binary As #intnum
' size the byte array to appropriate size
ReDim BytImage(FileLen(strImageName))
' Read data and close file
Get #intnum, , BytImage
Close #1

DBConnect
MyRecSet.Open "Members", MyConn, adOpenDynamic, adLockPessimistic, adCmdTable

Do Until MyRecSet.Fields.Item("lastname").Value & ", " & MyRecSet.Fields.Item("Firstname").Value = Username.Text

MyRecSet.MoveNext

If MyRecSet.EOF = True Then
MsgBox ("Member not found.")
MyConn.Close
Exit Sub
End If


Loop

With MyRecSet
.Fields("ID") = IDnum.Text
.Fields("Lastname") = Last.Text
.Fields("Firstname") = First.Text
.Fields("Middle") = Middle.Text
.Fields("Email") = Email.Text
.Fields("Areacode_1") = Areacode_1.Text
.Fields("Phone1_1") = Phone1_1.Text
.Fields("Phone2_1") = Phone2_1.Text
.Fields("Areacode_2") = Areacode_2.Text
.Fields("Phone1_2") = Phone1_2.Text
.Fields("Phone2_2") = Phone2_2.Text
.Fields("Address") = Address.Text
.Fields("City") = City.Text
.Fields("Health") = HealthStatus.Text
.Fields("Debit") = Debitammount.Text
Select Case Male.Value
Case Is = True
.Fields.Item("Gender").Value = "M"
Case Else
.Fields("Gender").Value = "F"
End Select
If strImageName <> "" Then
.Fields("Picture").AppendChunk BytImage
End If
.Fields("Birth_date").Value = BirthDate.Text
.Fields("Height").Value = MemHeight.Text
.Fields("Weight").Value = Weight.Text
.Fields("Health_Insurance").Value = Healthinsurance.Text
.Fields("Company").Value = Company.Text
.UpdateBatch
End With

MyConn.Close

LogtoDatabase "Member Edited", Startup.CurrentUser & " Edited " & _
Me.Last.Text & "," & Me.First.Text

Unload Me


And heres the problem... When I use ".update" instead of ".updatebatch" I get an error with the selected record set...

But when I updatebatch I get about 3-4 seconds of program lag (it just freezes from any response...)


Also I'm triyng to store an image under a "blog" type... but I can't seem to pull the information back...

This worked under an access database, and yes i can create and view the information in my program with no problem....

Okay heres the code:


Private Sub Closesave_Click()
'On Error Resume Next
Dim intnum As Integer

' Open the picture file
intnum = FreeFile
Open strImageName For Binary As #intnum
' size the byte array to appropriate size
ReDim BytImage(FileLen(strImageName))
' Read data and close file
Get #intnum, , BytImage
Close #1

DBConnect
MyRecSet.Open "Members", MyConn, adOpenDynamic, adLockPessimistic, adCmdTable

Do Until MyRecSet.Fields.Item("lastname").Value & ", " & MyRecSet.Fields.Item("Firstname").Value = Username.Text

MyRecSet.MoveNext

If MyRecSet.EOF = True Then
MsgBox ("Member not found.")
MyConn.Close
Exit Sub
End If


Loop

With MyRecSet
.Fields("ID") = IDnum.Text
.Fields("Lastname") = Last.Text
.Fields("Firstname") = First.Text
.Fields("Middle") = Middle.Text
.Fields("Email") = Email.Text
.Fields("Areacode_1") = Areacode_1.Text
.Fields("Phone1_1") = Phone1_1.Text
.Fields("Phone2_1") = Phone2_1.Text
.Fields("Areacode_2") = Areacode_2.Text
.Fields("Phone1_2") = Phone1_2.Text
.Fields("Phone2_2") = Phone2_2.Text
.Fields("Address") = Address.Text
.Fields("City") = City.Text
.Fields("Health") = HealthStatus.Text
.Fields("Debit") = Debitammount.Text
Select Case Male.Value
Case Is = True
.Fields.Item("Gender").Value = "M"
Case Else
.Fields("Gender").Value = "F"
End Select
If strImageName <> "" Then
.Fields("Picture").AppendChunk BytImage
End If
.Fields("Birth_date").Value = BirthDate.Text
.Fields("Height").Value = MemHeight.Text
.Fields("Weight").Value = Weight.Text
.Fields("Health_Insurance").Value = Healthinsurance.Text
.Fields("Company").Value = Company.Text
.UpdateBatch
End With

MyConn.Close

LogtoDatabase "Member Edited", Startup.CurrentUser & " Edited " & _
Me.Last.Text & "," & Me.First.Text

Unload Me


And heres the problem... When I use ".update" instead of ".updatebatch" I get an error with the selected record set...

But when I updatebatch I get about 3-4 seconds of program lag (it just freezes from any response...)


Also I'm triyng to store an image under a "blog" type... but I can't seem to pull the information back...

This worked under an access database, and yes i can create and view the information in my program with no problem....

Look here
http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html

But I would personally recommend to save just the location of the image rather than the image itself.

Look here
http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html

But I would personally recommend to save just the location of the image rather than the image itself.

Well in Visual basic I save it into a OLEobject

Should I be saving this as a blob in the first place?

Well in Visual basic I save it into a OLEobject

Should I be saving this as a blob in the first place?

Save it as an image file and save its path to your table, but do not save the image itself in your database as a BLOB field.

Save it as an image file and save its path to your table, but do not save the image itself in your database as a BLOB field.


I just used longblob and it worked perfectly without me having the change any code...


Problem is, when I try to edit, and I use myrecset.update it gives me an error about the currently selected record set...

But If I use myrecset.updatebatch, it works, but I get about 4-5 seconds where my program freezes.... which is unacceptable...

I just used longblob and it worked perfectly without me having the change any code...


Problem is, when I try to edit, and I use myrecset.update it gives me an error about the currently selected record set...

But If I use myrecset.updatebatch, it works, but I get about 4-5 seconds where my program freezes.... which is unacceptable...

so, you really want to save the image in your table as blob field. Try using the stream object like in the example from the link, or try changing the adLockPessimistic to adLockOptimistic and call the update.

and btw, you should open your recordset using select with where clause and remove the do loop, this is perhaps the reason why it took 4-5 seconds to save the data.


Dim strSQL As String
strSQL = "SELECT * FROM Members WHERE LastName+FirstName ='" & Username.Text & "'"
MyRecSet.Open strSQL, MyConn, adOpenDynamic, adLockOptimistic, adCmdText

The problem seems to be that I'm using .updatebatch instead of .update

Run-Time error '-2147467259 880004005)':

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




I use the do loop to retieve the information to edit.... it has to be something else.... becuase I get no lag retreiving the information...

Did you try what i suggested? to use the where clause and select the record that you want to update and remove the Do loop?

and why are you updating the ID field? if the ID field is your primary key you don't need to update it

.Fields("ID") = IDnum.Text 'Remove this line

DBConnect
'MyRecSet.Open "Members", MyConn, adOpenDynamic, adLockPessimistic, adCmdTable

Dim strSQL As String
strSQL = "SELECT * FROM Members WHERE LastName+"", ""+FirstName ='" & Username.Text & "'"
MyRecSet.Open strSQL, MyConn, adOpenDynamic, adLockOptimistic, adCmdText


'Do Until MyRecSet.Fields.Item("lastname").Value & ", " & MyRecSet.Fields.Item("Firstname").Value = Username.Text

' MyRecSet.MoveNext

'If MyRecSet.EOF = True Then
' MsgBox ("Member not found.")
' MyConn.Close
' Exit Sub
'End If


'Loop


With MyRecSet
.Fields("ID") = IDnum.Text
.Fields("Lastname") = Last.Text
.Fields("Firstname") = First.Text
.Fields("Middle") = Middle.Text
.Fields("Email") = Email.Text
.Fields("Areacode_1") = Areacode_1.Text
.Fields("Phone1_1") = Phone1_1.Text
.Fields("Phone2_1") = Phone2_1.Text
.Fields("Areacode_2") = Areacode_2.Text
.Fields("Phone1_2") = Phone1_2.Text
.Fields("Phone2_2") = Phone2_2.Text
.Fields("Address") = Address.Text
.Fields("City") = City.Text
.Fields("Health") = HealthStatus.Text
.Fields("Debit") = Debitammount.Text
Select Case Male.Value
Case Is = True
.Fields.Item("Gender").Value = "M"
Case Else
.Fields("Gender").Value = "F"
End Select
If strImageName <> "" Then
.Fields("Picture").AppendChunk BytImage
End If
.Fields("Birth_date").Value = BirthDate.Text
.Fields("Height").Value = MemHeight.Text
.Fields("Weight").Value = Weight.Text
.Fields("Health_Insurance").Value = Healthinsurance.Text
.Fields("Company").Value = Company.Text
.Update
End With




Same error with the myrecset.update

:-\

ID number isn't actually the primary key though....

It can't even myrecset.updatebatch anymore....

You should first check the recordset whether it is EOF or not before conducting any updates to the table.

If Not MyRecSet.EOF And Not MyRecSet.BOF Then
With MyRecSet
.Fields("Lastname") = Last.Text
'etc....
End With
End If
'etc...

I already told you that if the ID field is a primary key you don't have to save it (see my last post) :rolleyes:

and you should first check the recordset whether it is EOF or not before conducting any updates to the table.

If Not MyRecSet.EOF And Not MyRecSet.BOF Then
With MyRecSet
.Fields("Lastname") = Last.Text
'etc....
End With
End If
'etc...


ID.value isn't the primary key Rec_No.vale (which isn't ever edited or seen)

I use the exact same method to select the user information.....

i'll go back through all my code and see if I messed up something...

I didn't change the code except for drivers and connection information from the transition form AccessDB to mySQL DB


and my code already checks for being at the EOF (before I put in your code)


'If MyRecSet.EOF = True Then
' MsgBox ("Member not found.")
' MyConn.Close
' Exit Sub
'End If


which was put inside the do loop statement....

ID.value isn't the primary key Rec_No.vale (which isn't ever edited or seen)

I use the exact same method to select the user information.....

i'll go back through all my code and see if I messed up something...

I didn't change the code except for drivers and connection information from the transition form AccessDB to mySQL DB


and my code already checks for being at the EOF (before I put in your code)


'If MyRecSet.EOF = True Then
' MsgBox ("Member not found.")
' MyConn.Close
' Exit Sub
'End If


which was put inside the do loop statement....


And that do-loop statement works great, I've used it alot before.... and have gotten "member not found" when I put in a non-existing name....

ID.value isn't the primary key Rec_No.vale (which isn't ever edited or seen)

I use the exact same method to select the user information.....

i'll go back through all my code and see if I messed up something...

I didn't change the code except for drivers and connection information from the transition form AccessDB to mySQL DB

Is ID field an autonumber field? if so, you should also remove the line rs.Fields("ID").Value = Blah...

and try checking the recordset for End of file.

forget about the do loop it's just a waste, you don't need that, you can do it in a single select.

Is ID field an autonumber field? if so, you should also remove the line rs.Fields("ID").Value = Blah...

and try checking the recordset for End of file.


Nope it's a regular ~10 digit number (made for id cards)

The program generates them.... but they must be editable so people with existing ID numbers can be put in manually...

My problem is that it seems to not select the recordset like it did in Access....

I can add, and view entries (haven't tried deleting yet) with no problem or lag whatsoever.... thats why I find this weird...

Nope it's a regular ~10 digit number (made for id cards)

The program generates them.... but they must be editable so people with existing ID numbers can be put in manually...

My problem is that it seems to not select the recordset like it did in Access....

I can add, and view entries (haven't tried deleting yet) with no problem or lag whatsoever.... thats why I find this weird...

what's the field define as primary key?

Rec_no is it's an autonumber so I don't worry about it, being as it's generated at the creation time of the record....

Rec_no is it's an autonumber so I don't worry about it, being as it's generated at the creation time of the record....

try checking the recordset for end of file as I already mentioned, using your last post code without the do loop, debug your program and set a break point to "If Not MyRecSet.EOF" and see if it will execute the next line.

try checking the recordset for end of file as I already mentioned, using your last post code without the do loop, debug your program and set a break point to "If Not MyRecSet.EOF" and see if it will execute the next line.


I did that and it isn't at the EOF....(goes through to update with no problem)

It says the row to update can't be found....

I've looked quite extensively over the internet, and this problem does seem to occur often....

Heres my connection string....


MyConn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=Wellness Center Database;" & _
"USER=root;" & _
"PASSWORD=WellnessDB;" & _
"PORT=3306;" & _
"Option=3;"


Noone seems to have a fix for this though...

try changing your connectionstring to

MyConn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=Wellness Center Database;" & _
"USER=root;" & _
"PASSWORD=WellnessDB;" & _
"PORT=3306;" & _
"OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

if still doesn't work, try using the Stream object. like in the example from the link I posted.