Get data from SQL table throwing null exception even when try block is present

Question

In my WinForms app I have this below code which fetches the lot number from a SQL Server table when the button is clicked.

private void getlotnumbers()
{
    try
    {
        SqlConnection con = new SqlConnection(cs);
        SqlCommand cmd;
        con.Open();
        string s = "select LotNumber from Lot_Numbers where CoilNumber = @p1";
        cmd = new SqlCommand(s, con);
        cmd.Parameters.AddWithValue("@p1", coilNoTextBox.Text);
        cmd.CommandType = CommandType.Text;
        int i = cmd.ExecuteNonQuery();
        lotNoTextBox.Text = cmd.ExecuteScalar().ToString();
        con.Close();
    }
    catch(Exception ex)
    {
        MessageBox.Show("Lot Number Not Found:" + ex.Message.ToString(), "Lot Number",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }        
}

The function was working fine till yesterday, but from today it is throwing below exception and stopping the application even though try block is present.

System.NullReferenceException: 'Object reference not set to an instance of an object.'

I don't know why is this happening, but I was adding another form called "formedit" to edit the existing record, which is very similar to "FornewEntry", but with prefilled values of selected datagridview row, maybe I have messed up something while doing the same as this is first application as I am learning C# below is the link to my solution kindly help me.

So, the code you posted isn't even the code that's throwing the exception? I recommend you read the canonical [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) question — MindSwipe, Dec 21 '20 at 07:17
hi ..the code i posted is the code that's throwing the exception , at `lotNoTextBox.Text = cmd.ExecuteScalar().ToString();` — RaviKumar, Dec 21 '20 at 07:18
so the lotNoTextbox is null ?? can you put breakpooint there and check if your label has a value ? — puko, Dec 21 '20 at 07:20
yes it will be empty at form load ..that's i am trying to fil it with above code by getting the lotnumber from sql table , also even when there is try block why it crashing my application , can you please download my solution and click `LotNolinklabel` in "fornewentry" and suggest me what is wrong? — RaviKumar, Dec 21 '20 at 07:22
"can you please download my solution and click" no way mate, sorry but no one will download here source code and compile it on their own machine. This is not how SO works. You need to provide all necessary information here in your post. Because your post also serves as a land mark for future readers with a similar problem. Please debug your application and tell us which value is `null`. Is it the result of `cmd.ExecuteScalar()` ? or event your `lotNoTextbox`? — Mong Zhu, Dec 21 '20 at 07:53
"click LotNolinklabel" even if we would do this. Do you think we would get access to your data base? are there credentials in your connection string in your solution that you provide? are you sure you want those credentials beeing freely available on the internet for everyone to download? — Mong Zhu, Dec 21 '20 at 07:56
hi @MongZhu thank you for your suggestion ..i found out that the error is coming at the line: ```lotNoTextBox.Text = cmd.ExecuteScalar().ToString();``` as the "coilnotext" box and lot number was not matched , but again i have used try block right? ..it must show the exception in messagebox and let me use my application , but why it stepping out of the application itself? — RaviKumar, Dec 21 '20 at 07:58
would you please answer our questions, because we are trying to gather information to be able to answer yours. — Mong Zhu, Dec 21 '20 at 08:00
h @MongZhu `Please debug your application and tell us which value is null ` the `lotNoTextBox.Text` value is null as the matching value for the value in `coilnotextbox ` is not present in my table .but why the exception is not getting captured in my try catch block? — RaviKumar, Dec 21 '20 at 08:13
"but why the exception is not getting captured in my try catch block?" this is difficult to answer, are you running this in VS debug mode? — Mong Zhu, Dec 21 '20 at 09:17
Yes i was running it in the same ...and answer of @Jameel Nazir helped me correct it — RaviKumar, Dec 21 '20 at 09:18
Related - don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) — SMor, Dec 21 '20 at 12:42

score 2 · Accepted Answer · answered Dec 21 '20 at 07:43

2

you are trying to execute this statement string s = "select LotNumber from Lot_Numbers where CoilNumber = @p1"; I think your lotNumber column value is null and when you execute lotNoTextBox.Text = cmd.ExecuteScalar().ToString(); it will return null so if you can handle it

var lotNumber= cmd.ExecuteScalar()==null?"Handle empty case or throw exception with custom message":cmd.ExecuteScalar().ToString();
lotNoTextBox.Text =lotNumber.ToString();

answered Dec 21 '20 at 07:43

Jameel Nazir

86
7

thank you for your answer , i have already handled the exception using try block ,,but still it stepping out of the whole application , why is it so? – RaviKumar Dec 21 '20 at 07:59
because your expression returns null value and you are performing null.ToString() in case of null c# returns error 'Object reference not set to an instance of an object.' – Jameel Nazir Dec 21 '20 at 08:50

score 2 · Answer 2 · answered Dec 21 '20 at 10:08

2

Why not handling null values within your SQL query?

string s = "select ISNULL(LotNumber, -1) from Lot_Numbers where CoilNumber = @p1";
// returns -1 when LotNumber is not found

answered Dec 21 '20 at 10:08

Usama Aziz

131
10

hi ..thank you for the suggestion ..will try the same. – RaviKumar Dec 21 '20 at 10:32

Get data from SQL table throwing null exception even when try block is present

2 Answers2