ListBox and Database Update SQL Server

I have a listbox that is populated with data from a MS SQL Server database… then I use a ContextualMenuAction event to add a record to the listbox and update the database… everything works just fine… the listbox is populated with the new record and the database insert works just fine…

If I have an empty listbox and try the same thing… I get no errors from Xojo or the database but the record is not added to the database but it is added to the listbox… don’t know what I am doing wrong… I have single stepped thru the code and it is the same for when the listbox is populated or when it is empty… no errors are shown anywhere…

What am I doing wrong…

Post the project to find out

Sorry Tim… I am new here… how do I do that…

I am not certain that I understand your issue. Let’s see if I do:

  • you are feeding data from a SQL Server DB into a listbox.
  • When the table contains data, you add the correct records to the listbox
  • When the table is empty, you still add a blank record to the listbox
  • returning to the database is OK.

+1 on @Tim Parnell comment

Here is a shot in the dark:

  • You have variables for each of the DB field. The variables are string and default to “”.
  • You have a listbox.AddRow statement somewhere in your code before you check whether there really is data to feed.
    That would add a blank line in the listbox.

Of course, posting some relevant code would help narrow down the possibilities.

Here is the code that is running in the ContextualMenuAction event:

Select Case hitItem.Text Case "Add Customer" Me.AddRow("Acct ID","New Customer") CustomersDataList.ColumnType(0) = ListBox.TypeEditableTextField CustomersDataList.ColumnType(1) = ListBox.TypeEditableTextField sql = "insert tblCustomers (AcctID, Company) values ('AcctID','New Customer')" mDB.SQLExecute(sql) sql = "select @@Identity" data = mDB.SQLSelect(sql) CustomersDataList.RowTag(CustomersDataList.LastIndex) = data.IdxField(1).StringValue If mDB.Error Then MsgBox("DB Error: " + mDB.ErrorMessage) End If Case "Delete Customer" If Me.ListIndex >= 0 Then sql = "select count(*) from tblContainers where CustomerID = " + CustomersDataList.RowTag(Me.ListIndex) data = mDB.SQLSelect(sql) If data.IdxField(1).StringValue = "0" Then sql = "delete tblCustomers where ID = " + CustomersDataList.RowTag(Me.ListIndex) mDB.SQLExecute(sql) CustomersDataList.RemoveRow(Me.ListIndex) Else MsgBox("You can not delete this customer!") End If End If End Select

This runs without error but only works if there are already rows in the ListBox… if the ListBox is empty… it still runs without error and the row is added to the ListBox but not added to the database…

Your code doesn’t check for the error when it actually does the insert. Only after the call to @@identity, so you won’t know whether you have an error or not.

Yes Tim… thanks… I was aware that this version of my code doesn’t check in the correct place but I have had it in the correct place and it still doesn’t return an error… got in a hurry… but thanks for the call out on this…

[quote=397520:@Steve Baker]Here is the code that is running in the ContextualMenuAction event:

... sql = "insert tblCustomers (AcctID, Company) values ('AcctID','New Customer')" ...
[/quote]

Shouldn’t the insert statement be

sql = “insert INTO tblCustomers (AcctID, Company) values (‘AcctID’,‘New Customer’)”

The “into” is optional… the query has been tested against the SQL Server database and it runs just fine…

Do you need an mDb.Commit in there? Grasping at straws.

Where are the values for AcctID and Customer coming from? Are you actually using the literal values “Acct ID” and “New Customer”? What is the datatype of AcctID? Are you sure you are checking for an error right after the execution of the insert query? Are you still getting a value for @@Identity? What is this value? Which record does it point to?

Please post the contents of the cellaction event handler for this listbox.

First… thanks to everyone for your help… the SQL as shown are the correct values to be inserted… have placed the database error checking in the correct location… it still doesn’t return an error… it doesn’t show any @@Identity because it hasn’t updated the database… I have looked into the cellaction event and it doesn’t get fired… but here is the code anyway…

ContextualMenuAction event:

Select Case hitItem.Text Case "Add Customer" Me.AddRow("Acct ID","New Customer") CustomersDataList.ColumnType(0) = ListBox.TypeEditableTextField CustomersDataList.ColumnType(1) = ListBox.TypeEditableTextField sql = "insert tblCustomers (AcctID, Company) values ('AcctID','New Customer')" mDB.SQLExecute(sql) If mDB.Error Then MsgBox("DB Error: " + mDB.ErrorMessage) End If sql = "select @@Identity" data = mDB.SQLSelect(sql) CustomersDataList.RowTag(CustomersDataList.LastIndex) = data.IdxField(1).StringValue 'If mDB.Error Then 'MsgBox("DB Error: " + mDB.ErrorMessage) 'End If Case "Delete Customer" If Me.ListIndex >= 0 Then sql = "select count(*) from tblContainers where CustomerID = " + CustomersDataList.RowTag(Me.ListIndex) data = mDB.SQLSelect(sql) If data.IdxField(1).StringValue = "0" Then sql = "delete tblCustomers where ID = " + CustomersDataList.RowTag(Me.ListIndex) mDB.SQLExecute(sql) CustomersDataList.RemoveRow(Me.ListIndex) Else MsgBox("You can not delete this customer!") End If End If End Select

CellAction event:

Select Case column Case 0 // Account column sql = "update tblCustomers set AcctID = '" + Me.Cell(row,column) + "' where ID = " + Me.RowTag(Me.ListIndex) mDB.SQLExecute(sql) Case 1 // Customer column sql = "update tblCustomers set Company = '" + Me.Cell(row,column) + "' where ID = " + Me.RowTag(Me.ListIndex) mDB.SQLExecute(sql) End Select

Thanks again for everyone’s help…

I took your code from the ContextualMenuAction event and created a test project but used Sqlite instead. I made the appropriate changes for the identity sql statement and added INTO in the insert statement. Adding a new record on an empty listbox worked properly with those minor changes. I’m assuming that the CustomersDataList Listbox is the same one you are referencing when you use the me reference or is it an entirely seperate Listbox?

[quote=397510:@Steve Baker]
What am I doing wrong…[/quote]

Start by not touching the database until the user has provided all information necessary to create a new record. Do not create dummy records in a database, especially a multiuser one, it is just asking for trouble…

Your add method should simply add a blank row to the listbox. When the user has entered all the required information then and only create the record and update the interface.

The best solution for something like this is some kind of Model-View-Controller pattern.