Find Next Record in Database in C#

Question

I want to show next record by clicking a button. Here's my code

private DataTable GetData()
        {
            DataTable dt = new DataTable();

            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                SqlCommand sqlCmd = new SqlCommand("Select * From Data", connection);
                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

                sqlDa.Fill(dt);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {

            }
            finally
            {
                connection.Close();
            }
            return dt;
        }


        public Form1()
        {

            DataTable dt = GetData();
            if (dt.Rows.Count > 0)
            {
                // Populate the TextBox with the first entry on page load
                txtName.Text = dt.Rows[0]["Name"].ToString();
            }
        }

but i am getting an exception on txtName.Text = dt.Rows[0]["Name"].ToString(); Object Reference is not set of an object.

Please help me

Your result set must have `null` for the value of `Name`. Just add a null check. Or, `txtName` is null. — gilly3, Dec 05 '11 at 20:32
Made an edit to remove your db connection info as you had the username and pwd. — Maess, Dec 05 '11 at 20:34
on side note.It seems that you are a java developer.use "using" block instead of try catch finally — Sleiman Jneidi, Dec 05 '11 at 20:37
@sleimanjneidi - using try/catch does not mean you're a java developer. That was a funny remark. — JonH, Dec 05 '11 at 20:39
If you are using Windows Forms, consider using a BindingNavigator control - http://msdn.microsoft.com/en-us/library/2wcswths.aspx - this wraps up the functionality you may be trying to achieve. — dash, Dec 05 '11 at 20:43
@JonH-c# developers never use try catch finally for disposable objects — Sleiman Jneidi, Dec 05 '11 at 20:51
What...the using statement is good practice but whoever told you this is completly wrong. Using statement is cleaner but if you know what you are doing and know about closing connections and disposing of objects of course you can use try catch finally. — JonH, Dec 06 '11 at 01:05

JonH · Answer 1 · 2011-12-05T20:41:42.683

2

First thing to check on the db side, is there really a column as Name, it could be that this column was aliased by a sql developer or by yourself:

SELECT Name AS NotAnotherName, ID FROM...

Second thing to do is before reading the contents of it check if it is null or if the column contains DBNull.value which is not the same as null so:

If (dt.Rows[0]["Name"] != DBNull.Value)
  //proceed

(You can add the if after you check if the number of rows > 0).

edited Dec 05 '11 at 20:41

answered Dec 05 '11 at 20:34

JonH

32,732
12
87
145

In the example he showed he is actually checking if there is a row! – ChristiaanV Dec 05 '11 at 20:36
@ChristiaanV - Just because you have a row doesn't mean the column doesn't contain a null value. – JonH Dec 05 '11 at 20:37
The column must exist. If the column does not exist, the Exception would be an `ArgumentException`, not a `NullReferenceException`. – gilly3 Dec 05 '11 at 20:39
@gilly3 - the column must exist, but the contents may not. – JonH Dec 05 '11 at 20:39
@gilly3 - Maybe he needs to check if the contents of Name != `DBNull.Value` – JonH Dec 05 '11 at 20:42
@JonH - `DBNull.Value` is not null, and would not throw a `NullReferenceException`. The value of `DBNull.Value.ToString()` is an empty string. – gilly3 Dec 05 '11 at 20:51
@gilly3 - My response indicates that the two namely `DBNull` and `null` are not equivalent, please read my post. – JonH Dec 05 '11 at 20:52
Also `String.Empty` != `DBNull.Value` as you have indicated. Its not an empty string, it refers to a `NULL` value coming from a database column. – JonH Dec 05 '11 at 20:55
@JonH - My mistake. My debugger is misleading me: http://i.imgur.com/MM0WC.png Then later: http://i.imgur.com/Zfwbs.png No joke! What's up with that? But then, it also says `DBNull.Value == true`: http://i.imgur.com/UyqPb.png But, now I'm not sure if I should trust it. :) – gilly3 Dec 05 '11 at 21:05
How else would the debugger interpret such blasphemy? – JonH Dec 05 '11 at 21:10
Here's a good example: http://codebetter.com/petervanooijen/2004/04/12/system-dbnull-value-null/ also from MSDN `The engine will convert a DBNull value to null for comparison and will convert null to a DBNull value for insertion into the database.` – JonH Dec 05 '11 at 21:10
See here also `DBNull.Value is what the database returns to represent a null entry in the database. DBNull.Value is not null and comparissons to null for column values retrieved from a database row will not work, you should always compare to DBNull.Value` Here's a good talk on it via stackoverflow http://stackoverflow.com/questions/4958379/what-is-the-difference-between-null-and-system-dbnull-value – JonH Dec 05 '11 at 21:13

gilly3 · Accepted Answer · 2011-12-05T21:30:05.990

1

Add in null checks for txtName and dt.Rows[0]["Name"]:

if (dt.Rows.Count > 0 && txtName != null && dt.Rows[0]["Name"] != null) 
{
    txtName.Text = dt.Rows[0]["Name"].ToString(); 
}

If dt.Rows[0]["Name"] is null, it is null in the Database. If that is not expected, debug your database insertion code.

If txtName is null, it is because you are executing this code before txtName has been initialized. Make sure your code does not execute until after txtName has been initialized.

edited Dec 05 '11 at 21:30

answered Dec 05 '11 at 20:36

gilly3

87,962
25
144
176

this code is working but there is no data in the txtName.Text. – Snake Dec 05 '11 at 20:38
@BilalAsghar - Move your code out of the constructor and into a `Load` event handler. See the edit to my answer. – gilly3 Dec 05 '11 at 20:43
@gilly3 why are you checking if `txtName !=Null` it appears the OP wants to overwrite that value. Try `if (dt.Rows.Count > 0) { if(dt.Rows[0]["Name"] != null)...` – JonH Dec 05 '11 at 20:44
@JonH - The OP wants to set the value of the `Text` property of `txtName`, not overwrite `txtName`. – gilly3 Dec 05 '11 at 20:46
@gilly3 - which it does when you enter the if, there is no need to check if `txtName!= null`, that doesn't make sense. – JonH Dec 05 '11 at 20:47
@JonH - According to Bilal's comment above `txtName` is in fact null. You can't set any properties on a null object. – gilly3 Dec 05 '11 at 20:53
gilly3 - i think you are misreading this. He is saying through the debugger the value of txtName is nothing, but your code is checking if it is `!=` null. That is why its not working! You don't need to check if `txtName != null`, get rid of that portion and that will allow him to set the value to `txtName` – JonH Dec 05 '11 at 20:57
@JonH - If `txtName` is null and you attempt to write to `txtName.Text`, you will get a `NullReferenceException`. If `txtName` is *not* null, then the if statement passes, and the code get's executed. Bilal said in his question that he *is* getting an exception: 'but i am getting an exception on txtName.Text = dt.Rows[0]["Name"].ToString(); Object Reference is not set of an object.' And in his comment above he said that `txtName` *is* null: 'txtName = null. How can i resolve it?' – gilly3 Dec 05 '11 at 21:13
@gilly3 - but txtName is not dynamically generated according to his code, why would it be `null` ??? If its a web form or a windows form and he has dragged and dropped a text box and called it txtName what state is it in? Definitely not a null state. – JonH Dec 05 '11 at 21:14
@JonH - I can't say *why* it is null, but Bilal did not post his complete code - for example, there is no class definition. Who's to say that `txtName` is not created dynamically? – gilly3 Dec 05 '11 at 21:19
I think you are missing the point. Look at his bilals comment. Your code would work if you removed txtName!=null. – JonH Dec 06 '11 at 01:02
@JonH - Are you just messing with me now? – gilly3 Dec 06 '11 at 01:13
@gilly3 absolutely not I am being serious. But I am done with this as it seems like the OP doesn't really care - it's as if we care more. – JonH Dec 06 '11 at 13:13

Find Next Record in Database in C#

2 Answers2