Click here to Skip to main content
15,849,785 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a SQLite database on a Windows CE device. It has a column called product_code. The values stored in this column contain both letters and characters. The first character is dependent on the department in which the product is, for example for Draught it would be D, for Spirits S. A sample product_code column entry would be S124 or S09.

On the device, I have a form to add new products to the database. I want the form to be able to generate the next available product code. In order to do that, I need to select the max value of a product_code for a specific department (selected from a combo box).

I have tried using this:

stm = "SELECT MAX(CAST(product_code AS Int)) AS maxPCode FROM product WHERE department_description = '" + cmbDeps.Text.ToString() + "';";


I have also tried:
stm = "SELECT MAX(CAST(product_code AS Int)) AS maxPCode FROM product WHERE product_code NOT LIKE '%[a-z]%' AND ISNUMERIC(product_code) = 1 AND department_description = '" + cmbDeps.Text.ToString() + "';";


But seems like ISNUMERIC() is not a function in SQLite? Is there any other way to get around this problem? I was thinking about possibly using replace(), but not sure if there is a way to strip any letter or if I have to have a condition for each possible department?

What I have tried:

string maxPcode = "";
           using (SQLiteConnection con = new SQLiteConnection(cs1))
           {
               string stm;
               stm = "SELECT MAX(CAST(product_code AS Int)) AS maxPCode FROM product WHERE product_code NOT LIKE '%[a-z]%' AND ISNUMERIC(product_code) = 1 AND department_description = '" + cmbDeps.Text.ToString() + "';";
               con.Open();
               using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
               {

                   using (SQLiteDataReader rdr = cmd.ExecuteReader())
                   {
                       while (rdr.Read())
                       {
                           if (!rdr.IsDBNull(rdr.GetOrdinal("maxPCode")))
                           {
                               rdr.GetOrdinal("product_code");
                               maxPcode = rdr.GetString(0);
                           }

                       }


                   }

               }

               con.Close();

           }
Posted
Updated 23-Feb-16 23:32pm

1 solution

Hello,

The result of a mathematical operation with non numeric value will turn into null so you can give a try to replace isnumeric with the following:

SQL
select max(cast(your_column as integer)) from your_table where ifnull(your_column + 1, null) is not null


Note 1: Why do you not use ExecuteScalar()?

Note 2: Try to use as much as possible parameterized queries instead of concatenations

Edit:

SQL
select 'A' || substr('00000000' || cast(max(cast(substr(product_code, 2) as integer)) + 1 as text), -4) from products where substr(product_code, 1, 1) = 'A'


"substr(product_code, 2)" gives you numeric part
"cast(max(cast(substr(product_code, 2) as integer)) + 1" gives you next id
the last substr gives you a formatted numerical id
the last concatenate gives you what you want
 
Share this answer
 
v3
Comments
Artur Leonowicz 24-Feb-16 4:43am    
That would not work as it would return nulls for all my values, right? D0001 is not a numeric value and since it contains a letter, it can't be casted to an integer?
pjaar89 24-Feb-16 5:04am    
Check the edited solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900