Selecting max value from text field in sqlite

Question

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 23-Feb-16 22:07pm

Artur Leonowicz

Updated 23-Feb-16 22:32pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

pjaar89 · Accepted Answer · 2016-02-23T22:31:00

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