Why is my query not executing properly in C#?

Question

Wrote this query in MSSQL:

SELECT Code, Description,LEAD(Code, 1) OVER (ORDER BY code) AS next_code FROM Liguanea_Lane WHERE code LIKE '%88%'

Wrote back the same query in my C# code only this time it is accepting input from a comboBox name "search" and executes on the button click. This is it below:

  private void button1_Click(object sender, EventArgs e)
    {
        try
        {

            string connectionString = "Data Source=JAVY26;Initial Catalog=Pharmacies;Integrated Security=True";
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string query = "SELECT Code, Description,LEAD(Code, 1) OVER (ORDER BY code) AS next_code FROM Liguanea_Lane WHERE code LIKE '%" + search.Text+"%'; ";
            SqlCommand cmd = new SqlCommand(query, con);

            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                string scode = dr.GetString(dr.GetOrdinal("next_code"));
                textBox2.Text = scode;
            }
        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.ToString());
        }
    }

My issue is that I keep getting: "Data is Null. This method or property cannot be called on Null Values." Is my query within my code wrong? It works fine in my SQL Server.

First - read up about parameterized queries. Second, `Code` may have a null value in the return set. The query executes fine, but `dr.GetString()` is refusing to operate on a null value. — Rob, Nov 02 '16 at 02:22
Will definitely look it up. Thanks Rob. Appreciate it. Although when I run the query in sql server there isn't any null values — Javy26, Nov 02 '16 at 02:32
have you confirmed via debugging that the value of the `query` variable exactly matches your original SQL query? — DeanOC, Nov 02 '16 at 02:35
For some reason it's reading a null value right at the "scode" variable — Javy26, Nov 02 '16 at 02:43
You should get rid of all `catch (Exception ex)` lines in your code. They hide errors and just make coding more difficult. Only catch specific exceptions that you can recover from. — Enigmativity, Nov 02 '16 at 03:02
Duplicate post gets rid of the error but nothing happens on button click @CodeFarmer — Javy26, Nov 02 '16 at 03:29
because it's null in DB, and you set `""` in textbox, so you should check the insert code why it's empty.@JevonDavis — Bucketcode, Nov 02 '16 at 04:27

score 0 · Answer 1 · answered Nov 02 '16 at 03:56

I would assume the problem is because of this:

while (dr.Read())
{
   string scode = dr.GetString(dr.GetOrdinal("next_code"));
   textBox2.Text = scode;
}

This is a loop without user intervention in it, suppose the query returns 3 rows:

Code   Description   Next_Code
-------------------------------
088    ABC           881
881    BCD           882
882    CDE           Null

On the first pass of your loop it assigns 881 to textbox2.Text; On the second pass, it assigns 882 to textbox2.Text On the third pass it errors because next_code is null.

I think it's case where the "next_code ' is seeing as a temporary column therefore it's seeing it as a column that doesn't exist thus null readings. — Javy26, Nov 02 '16 at 04:04

score 0 · Answer 2 · answered Nov 02 '16 at 04:31

I improvised. I just added a next_code field in my existing database and copy the values based on what precedes and proceeds. Just did all the work in the database instead and modified the code:

private void button1_Click(object sender, EventArgs e)
    {
        try
        {

            string connectionString = "Data Source=JAVY26;Initial Catalog=Pharmacies;Integrated Security=True";
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string query = "SELECT Code, Description, Next_Code FROM Liguanea_Lane WHERE code LIKE '%" + search.Text+"%'; ";
            SqlCommand cmd = new SqlCommand(query, con);

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
               string scode = dr.GetString(dr.GetOrdinal("next_code"));
                textBox2.Text = scode;

            }
        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.ToString());
        }
    }

It now returns the values I need. Thanks for the feedback everyone. Appreciate the time you took to try and assist.

Why is my query not executing properly in C#?

2 Answers2