Question update command doesn't update

[mcbulan]

Hello all,

I am trying to develop a window which does following procedure ;
1 . there are 2 listboxes (active students and passive students) and several names in each listbox.
2 . when i double-click any student name at any listbox, this student moves to the other group (for example, if i db-click on a student item in passive students grup, this item will go to the active listbox.
3 . finally, when i click "OK" button (or update button) , i want it to update the database with active names and passive names as 1 or 0.
I could complete 1st and 2nd procedures but at 3rd one, it doesnt update the database. It works successfully and doesnt send any exception but when i look at the database (related table), i see that the data is not updated.

Here is my code below :

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim con As New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db\pesi2000.mdf;Integrated Security=True;User Instance=True")
Dim i As Integer
Try
con.Open()
For i = 0 To actives.Items.Count - 1
Dim updatestr As String = "update aplist set ap='1' where ieid='" + actives.Items(i) + "' "
Dim updateit As New Data.SqlClient.SqlCommand(updatestr, con)
updateit.ExecuteNonQuery()
Next
For i = 0 To passives.Items.Count - 1
Dim updatestr As String = "update aplist set ap='0' where ieid='" + passives.Items(i) + "' "
Dim updateit As New Data.SqlClient.SqlCommand(upadtestr, con)
updateit.ExecuteNonQuery()
Next
Catch ex As Exception
errorstr.Text = ex.ToString 'this is a label
End Try
con.Close()
End Sub

I urgently need help, please. all help is appreciated.

 

[rcombs4]

When you access the items of both the active and passive listboxes you get a ListItem object. You cannot just pass a list item object into your line of SQL. You either have to return the .Value or .Text properties of the object. Which one you use is dependant upon how your data is bound to the list box.

try this

actives.Items(i).Value 
passives.Items(i).Value

 

[mcbulan]

SQL string (update,insert etc.) are clear

In fact, it's normal that everyone will think some possible causes like this. Bu i quickWatched my update string and it is completely normal. i copied to query analyser and it worked succesfully.
Look at these :

insert into students (name,surname,age,gender) values('a','b','20','M')
update aplist set ap='1' where ieid = '1000'

these are what my program produces and applies to the database. But it doesnt update or insert. Also it returns a message "1 rows affected", but i cannot see any inserted or updated data over database. It makes me feel that i need something like "commit;" or something like it.
There must be something wrong with the configuration i think. I am using visual studio .net 2005 (developing at vb.net win32 application) and sql express mdf file under <myprogrampath>/db ...
I could not find any clue thorugh internet and it really drives me insane!! (

 

[rcombs4]

I'd make sure your SQL text is correct. Id stop it at

Dim updateit As New Data.SqlClient.SqlCommand(updatestr, con)

and make sure that the updatestr value is exactly what you want it to be.

 

[mcbulan]

1 more thing! just hit

but i realized a very important point :
I added a part to my program code like this :
1 - Update changes in the listboxes
2 - Bind the data to the lists again <<<< this part
When i close the program, all changes disposes and maybe database rollbacks the canges maybe.
In the middle of the program, i stopped it and looked at the database, still the changes were not applied.
Maybe all changes are keept in a buffer until a "commit" like command performed. This is really wierd
this may help

yeah! the update string is completely correct. i copied it and paste to the "New Query" window and then executed, it worked!

but it doesnt update when it is sent from my code

 

[mcbulan]

thanks for replies

finally, i gave up to develop my program at vb.net, . delphi seems help more.

 

[cjard]

1) read the DNU link in my signature
2) read the DW2 link in my signature, section Creating a Simple Data App

Delphi really isn't easier