Help on Sqlite Update Command

[jeterry]

Attached is a program, that I am having a problem in using the Sqlite update command. The update command starts at line 241. The code for line 241 is below.

i = Msgbox ("Do You want to Save the Data?","Save Data",cMsgboxYesNo,cMsgBoxExclamation)
If i = cYes Then
text = "update Customer set ccontact = '" &TextBoxc8.Text&"' where ROWID = "&recnum
cmd.CommandText = text
cmd.ExecuteNonQuery
End If

As you can see, I am using rowid to save the data. At the present time I am only trying to save the Customer contact data.

The program sometime saves the data the first time, however any record that is saved thereafter does not get saved. The data appears to be saved if you go back into the record you just saved. However if you exit the program and then re-run it and look at that record again the data is not there.

So could someone please take a look and see what I am doing wrong.

One other item, if you try to click on the same record again that you just saved the event does not fire. You have to click another record and display it before you can go back and click the record you saved.

Thanks

 

[dennishea]

jeterry, this is what I use to update a colume.

cmd.CommandText = "update WorkData set Mach = '" & txtMach.Text & "' where (Internal = '" & txtInternal.Text & "')"
cmd.ExecuteNonQuery

I hope this helps.

 

[jeterry]

Sorry Everyone Left out Data file

Sorry but I left out the data file. Attached is the data file and program again.

I will look what you suggested dennishea, but using rowid should have worked.


Thanks

 

[Erel]

Can you upload the image files as well?

 

[jeterry]

Image Files?

There shouldn't be any image files needed. That is if your are requesting a bitmap file.

Are you requesting the dll, I am using???

 

[dennishea]

jeterry, the db you put in your zip is either corrupted or is not sqlite 3, I think. When I tried to run your prg I get a can't find error and when I look at it with a sqlite viewer it says it is not sqlite 3. I am very interested in seeing how this turns out because my program is working but could have a whole lot less code following your example. Hang in there, some one on this forum will put us straight at the problem.

 

[Erel]

The DB works fine here, and there are no missing files.
But I don't know how to reproduce the problem.
Can you prove the steps required to see it?

 

[dennishea]

Ok, when I downloaded the zip file I moved the data base into the folder I had already created and that setup didn't want to run. I decided to move the dll's into the folder I unzipped and now the program runs fine and the save updates the data base fine also. I'm running xp pro edtion with sp2 installed running b4ppc 6.05. The program runs fine for me also now that I have the most current stuff in the same folder.

 

[jeterry]

Additional Information

First of all, thanks to all you for look at this.

Here are the step I am doing on the desktop.

1. Fire the app up, click application, click customer menu.

2. click any customer.

3. add or change the data in the contact field on the first tab.

4. click the x, to exit the screen. msgbox will ask to save data. click yes.

5. at the customer list screen select another customer and do steps 2, 3 & 4 again.

6. exit the application and then go back in and look at the customer that you changed. Some of the time the first customer that you changed or add data too in the contact field will have been saved and sometimes not. But the data that you add to the second is not there.

7. You should note that sometimes when you go back and click one of the customers that you changed it will show that the data is still there, yet when you exit the program and come back in and look at the same customer the data will be missing.

8. One addtional item as noted in the orginal post. You can't click the same customer again after you save it. The click event will not fire. You have to select another customer and then go back and select that customer to view the data.

I placed a msgbox to look at the update text below the text vaiable to display it.

I have tried the following code to save the customer data-

text = "update Customer set ccontact = '" &TextBoxc8.Text&"' where (ccode = '"&custsel& "')"
text = "update Customer set ccontact = '" &TextBoxc8.Text&"' where (ROWID = "&recnum& ")"
text = "update Customer set ccontact = '" &TextBoxc8.Text&"' where (ROWID = '"&recnum& "')"
text = "update Customer set ccontact = '" &TextBoxc8.Text&"' where ROWID = '"&recnum& "'"
text = "update Customer set ccontact = '" &TextBoxc8.Text&"' where ROWID = "&recnum

I have checked the net framework version I have installed and it show that version 1.1, 1.1 hot fix and version 2 sp 1, installed.

BTW running xp pro. sp2

 

[dennishea]

jeterry, after following your directions I get the same flakey results. Like you I have tried a couple of different things but can't seem to find the problem. Haven't given up but need a break, will try again later. Hope fully erel will show us the way or point us in right direction. :sign0148:

 

[Erel]

I've fixed two things in your code and it seems to work properly:
- You are calling Con.BeginTransaction several times without first closing the transaction with con.EndTransaction.
Begin/EndTransaction should be used when you are executing several queries one after the other. I recommend you to just delete those lines.
- You should only call Reader.Close after using Reader.Value = Cmd.ExecuteReader.

 

[jeterry]

Could you post

Erel,

Could you post the changes.

Thanks

 

[Erel]

The updated code is attached.

 

[jeterry]

Works great

Erel,

Thanks works great nowhttp://www.b4x.com/forum/images/smilies/sign0060.gif
:sign0060: