SQLDataReader does not identify the empty columns

Question

So, I have a function which reads the result of a query that is used later on the program as it follows:

connection.Open();
int combination;
using (SqlCommand com1 = new SqlCommand())
{
    com1.Connection = connection; 
    com1.CommandText = "select FinalComboId from relationTable where sourceCombo=@source and destinationCombo=@destination";
    com1.Parameters.Add(new SqlParameter("@source",combo.ToString() ?? ""));
    com1.Parameters.Add(new SqlParameter("@destination", destination ?? ""));
    SqlDataReader comboLinkReader = com1.ExecuteReader();
    if (!comboLinkReader.Read() || comboLinkReader.FieldCount==0)
    {
        ScriptManager.RegisterClientScriptBlock(this, GetType(),
                   "alertMessage", @"alert('Combination does not exists,please contact admin!')", true);
    }
    else 
    {
        combination = Convert.ToInt32(comboLinkReader["FinalComboId"]);

    }
}

What I would like to achieve is: if the result is empty, than execute the alert script, else save the result as an integer, that will be used for further calculations. I have followed several tutorials and examples regarding that issue, and when I executed the function the other day, it worked just fine. Now, 2 hours from launching it to production, it does not calculate the first condition:

if (!comboLinkReader.Read() || comboLinkReader.FieldCount==0)
       {//calculations  here}

I have also tried with

if (!comboLinkReader.Read() || comboLinkReader.IsDbNull(0))
   {//calculations}

and I have the same problem. The query should return one single value. Is there something that I am doing wrong?

IsDbNull is a method that needs an argument for the column index. — Tim Schmelter
– Tim Schmelter, Commented Oct 7, 2015 at 9:41
@TimSchmelter Yes, I am editing the question, forgot the index — Ange1
– Ange1, Commented Oct 7, 2015 at 9:42
How do you know there are no results? Did you debug the code? If Read returns true when you didn't expect it, what are the contents of the row it loaded? There's no reason to count fields or check for nulls (unless you have stored nulls in this field) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 7, 2015 at 9:47
In theory, it should fire the alert script if the result of the query is null or it has no value, else it should return the value. What actually happens, is that sometimes, the first condition is not executed, event though the query has no results — Ange1
– Ange1, Commented Oct 7, 2015 at 9:47

Tim Schmelter · Accepted Answer · 2015-10-07 09:45:12Z

3

IsDbNull is a method that needs an argument for the column index.

int? finalComboId = null;
if(comboLinkReader.Read() && !comboLinkReader.IsDbNull(0))
    finalComboId = comboLinkReader.GetInt32(0);
if(!finalComboId.HasValue)
    ScriptManager.RegisterClientScriptBlock(this, GetType(), "alertMessage", @"alert('Combination does not exists,please contact admin!')", true);
else 
    combination = finalComboId.Value;

answered Oct 7, 2015 at 9:45

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Ange1 Over a year ago

With this piece of code, comboLinkReader.Read() shows empty results (as it should). However, even the script is hit (after putting a breakpoint) , it does not appear in the screen. Maybe there is some condition I am missing?

Tim Schmelter Over a year ago

What means "comboLinkReader.Read() shows empty results"? You know that you have to show the page to the client to see the alert? You are currently at the server and the alert is a javascript function that is executed when the document loads.

Ange1 Over a year ago

If I put a breakpoint on comboLinkReader and open the "properties" , it shows at the Result View properties : Empty "Enumeration generated no results"

Tim Schmelter Over a year ago

@Ange1: which property shows that? It's not clear what's wrong. Where have you set the breakpoint exactly?

Ange1 Over a year ago

Do you think that I should put the alert on Client Side instead of server side? I have other pages where scripts are generated on server side, and they all worked fine. The breakpoint is at ` finalComboId = comboLinkReader.GetInt32(0);` , in order to see the comboLinkReader values

|

Collectives™ on Stack Overflow

SQLDataReader does not identify the empty columns

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related