Replacing value from Listbox

Arief_Sarjono · October 16, 2017, 10:45am

Hi All,

I wanna update the Stock value with the update one drom Listbox,
I have put this code, run but the result is null.

[code] drop_column
add_column
dim totalrow as integer
totalrow = stocklist.listcount-1
for x as integer = 0 to totalrow
dim a,b,c,d as string
a=stocklist.cell(x,0) 'ID column
d=stocklist.cell(x,3) 'Stock Column

If AddTeamRow11(a,d) then
  dim lox as Integer
  for lox = 0 to stocklist.ListCount -1
    stocklist.Cell(lox,0)=str(val(nomor.text)+1)
    nomor.Text=str(val(nomor.text)+1)
  next
  nomor.text="0"
  
  l_connection.Text = "3 rows added to Team."
Else
  'AddDataStatusLabel.Text = "There was an error adding the data."
End If

next[/code]

Addteamrow11 code,

[code] If Not IsConnected Then
MsgBox(“Connect to the database and create the table first.”)
Return False
End If

Dim row As New DatabaseRecord

// ID will be added automatically
row.Column(“ID”) = ID
row.Column(“stock”) = stok

mDB.InsertRecord(“master”, row)

If mDB.Error Then
l_connection.Text = "DB Error: " + mDB.ErrorMessage
Return False
End If

Return True[/code]

Drop_column Code

[code] Dim sql As String
sql = “ALTER TABLE master DROP COLUMN stock”

Dim data As RecordSet
data = mDB.SQLSelect(sql)
[/code]

Add Column Code,

[code] Dim sql As String
sql = “ALTER TABLE master ADD COLUMN stock TEXT”

Dim data As RecordSet
data = mDB.SQLSelect(sql)
[/code]

I was trying to use an UPDATE command, but no luck,

any helps…?

Thanks
Arief

Eli_Ott · October 16, 2017, 10:51am

What type of database?

Arief_Sarjono · October 16, 2017, 10:55am

Msyql

Eli_Ott · October 16, 2017, 11:35am

Your code above does not show your UPDATE command.

Secondly, master is a reserved word in MySQL you cannot use it for as a table name.

Arief_Sarjono · October 17, 2017, 4:09am

yes, its not The UPDATE command, I was trying to said, that I ever trying to use UPDATE command also not working.

if the problem coming from Table Name (master), why its work on local machine (XAMPP), I can do Inserting and Delete. but, what I need to do it now is inserting/replacing the value only. Because the data contains 5000 rows, which is very slow, when do Inserting after Table Dropping.

I was looking for other option to do it.

Thanks
Arief

Jeff_Tullin · October 17, 2017, 4:58am

AFter your drop column, or append column,

data = mDB.SQLSelect(sql)

does not give you a recordset that contains all the rows in the data variable.
When you issue a COMMAND to the database like this, shouldnt you be using .SQLExecute ?

If you want a recordset you can walk through,

data = mDB.SQLExecute(sql) data = mDB.SQLSelect("Select * from "master"))

might do it.

Try

mDB.SQLExecute("update "master" set stock = 5 where ID = '6'")

Arief_Sarjono · October 17, 2017, 6:56am

I have tried both, but still no luck.

this code is works, but its only change one row only ID no. 6.

mDB.SQLExecute("update "master" set stock = 5 where ID = '6'")

I found somewhere on the net,

sql="UPDATE master  SET stock='x' WHERE ID!=0;"

its do changing the value, but all the column data replaced with ‘x’

how to change the ‘x’ into stocklist.cell(x,3) 'Stock Column

Thanks
Arief

Jeff_Tullin · October 17, 2017, 8:21am

Good grief. Of course it does.
Thats exactly what it says. set stock = 5 where ID = ‘6’

SET stock='x'

… again that’s exactly what you told it to do… set the value to be ‘x’
Why does this surprise you?

If you want to update all the rows, you omit the where clause. You dont need to say ‘where the ID is not 0’… (there might actually be a row that has an ID of 0 !)

mDB.SQLExecute("update "master" set stock = 5")

or for your specific case:

mDB.SQLExecute("update "master" set stock = '" +   stocklist.cell(x,3)  +"'")

The truth is, these are not really ‘Xojo’ questions.
This is basic SQL syntax, and I think you probably need to look at some SQL guides before you try to use SQL in your app.
Dropping the column and creating it again from new was a very strange idea, for example.

Arief_Sarjono · October 17, 2017, 11:38am

I have do the test, but still no luck. No error shown, but the value wont change.

mDB.SQLExecute("update "master" set stock = '" +   stocklist.cell(x,3)  +"'")

I have modified into this code also no luck, no error, but won’t change the value

[code] dim x as Integer
for x=0 to stocklist.Listcount -1
dim d,e as string
d=stocklist.cell(x,3) ’ stock value
e=stocklist.cell(x,0) 'Row ID

Dim sql As String
sql = "UPDATE master SET stock='"+d +"' WHERE ID='"+e+"'"
    
Dim data As RecordSet
data = mDB.SQLSelect(sql)
mDB.SQLExecute(sql)

it is, but I can’t find any simple method to do it.

Would be great If I can have it…

Thanks
Arief

Jeff_Tullin · October 17, 2017, 12:16pm

Dim data As RecordSet //what is this for??
data = mDB.SQLSelect(sql) //what is this for??

should be enough to do it.
What does it actually contain at the point you run it?
Does it work if the table is NOT called master?

If the table is called mytable, the ID is called ID, the stock column is called stock,
the ID is a number and the stock column is a number, then

update mytable set stock =  5 where ID = 6

is the syntax you need.

So you know the syntax is capable of updating where it is right.

update mytable set stock =  5 where ID = 6

is the syntax you need.
Check what is actually in the SQL you generate. Are there spaces around the numbers for example?

Arief_Sarjono · October 18, 2017, 4:39pm

I am forgot to mention,
the listbox was populated from local database not mysql database, its sqlite, but the db name, table and columns are using the same name.

Will it be the problem?
Using two kind of database from local and online.?

What the goals actually is, i want to make a copy of database, when the Internet online, then write the database both local and online, if not just write to local only.

Thanks
Regards
Arief

Jeff_Tullin · October 18, 2017, 5:01pm

[quote]What the goals actually is, i want to make a copy of database, when the Internet online, then write the database both local and online, if not just write to local only.
[/quote]

Walk first.
Does the update code work yet?

Tim_Hare · October 18, 2017, 10:43pm

[quote=354914:@Arief Sch.]Will it be the problem?
Using two kind of database from local and online.?[/quote]
No. You are simply creating an invalid sql string. Examine it in the debugger (or use MsgBox). If you can’t see the error, copy the string from the debugger and paste it into a query browser.

Arief_Sarjono · October 20, 2017, 10:29am

Hi All,

Now is the code working proper, I dunno why, maybe just because I remove this line,

Dim data As RecordSet data = mDB.SQLSelect(sql)

anyway, thanks for the helps…

Regards,
Arief