Write content of listbox to a DB table

Targets Windows
1

Hello all the experts here.

I am trying to do something very simple and can’t get it right, blame it to my non technical background, but i am keen to learn.

So

  1. I am reading a table
  2. writing to a listbox

I am using MBS sql plugin and using the graffiti listbox.

the third step
as I read the table and populate the list, I want to also write to another DB table.
and the code looks like below - now io keep getting command out of sync error.

I am just trying to populate a table as i read from one table, very basic, not sure what i am doing is wrong here, but this should be starightforward in any programming.

// fetch results row by row and print results*********************
while cmd.FetchNext

dim field1 as string = cmd.Field(“fld1”).asStringValue
dim field2 as string = cmd.Field(“fld2”).asStringValue
dim field3 as string = cmd.Field(“fld3”).asStringValue

//Load the listbox*********************************
me.AddRow( Str(field1), str(field2), str(field3))

cmdi = new SQLCommandMBS

// Insert row*********************************************
cmdi = new SQLCommandMBS(app.con, “Insert into tbl1 (xfield1, xfield2, xfeidl3) values(‘1’, ‘2’, ‘3’)”)

// Insert row
cmdi.Execute

// commit changes on success
app.con.Commit

'MsgBox “Input parameters bound, rows inserted!”

wend

Thanks
Wahed

2

Hello @Wahed_Qadri ,

I will show you how to post code next time. The code from your initial post would the look like this:

/ fetch results row by row and print results*********************
while cmd.FetchNext

dim field1 as string = cmd.Field(“fld1”).asStringValue
dim field2 as string = cmd.Field(“fld2”).asStringValue
dim field3 as string = cmd.Field(“fld3”).asStringValue

//Load the listbox*********************************
me.AddRow( Str(field1), str(field2), str(field3))

cmdi = new SQLCommandMBS

// Insert row*********************************************
cmdi = new SQLCommandMBS(app.con, “Insert into tbl1 (xfield1, xfield2, xfeidl3) values(‘1’, ‘2’, ‘3’)”)

// Insert row
cmdi.Execute

// commit changes on success
app.con.Commit

'MsgBox “Input parameters bound, rows inserted!”

wend

Pretty neet !

This is how you can you can do this:

  1. Copy the code in the post you are editing
  2. Add a few Enter at the bottom
  3. Select only the code
  4. Click on this icon:
    image

Very simple. One word of caution:

Thes quotes in the code you posted are not straight like this ", they are curly ”. See the difference ? That means if one copy your code in Xojo editor to test it, it won’t compile. THe same is true for single quote '.

Now in you code, you never check for error codes. That’s important to catch the issues.

I don’t get what you mean. Could you tell us on which line it fails at what error is displayed if any ?

This will help us help you !

3

Command out of sync typically means you’re trying to execute a SQL command on a connection while another command is already running on that same connection. This is usually trying to write to a database table while still reading from the database table, which is what you’re code appears to be doing. You should retrieve the results, cache any changes you need to make and allow the previous operation to complete, then do any writing.

You don’t specify what type of database you’re using, but here’s the docs for MySQL.

1 Like
4

Thanks Gilles, will paste the way you suggested. Yes, i am aware of the quotes need t be straight, thanks for pointing that out.

On the issue, Anthony replied and that has helped resolved my issues. thanks again.

5

Thanks Anthony, I was able to fix this by introducing a new connection. I wasn’t aware the connections were held by previous commands. Thanks for you inputs. Appreciate it.
Regards,
Wahed