Set empty string in TextBox if Database value is null in ASP.Net not working

Question

i have code to read empty row in database, if no row in database then textbox = "0"

my code :

protected void CheckNota()
        {
            
            string vNota;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd1 = new SqlCommand("select ISNULL ((KdNota), 0) as vKdNota from tProdukBeliHead where  KdNota = '" + txtKdBeli.Text.Trim() + "'", con))
                //using (SqlCommand cmd1 = new SqlCommand("select KdNota from tProdukBeliHead where  KdNota = '" + txtKdBeli.Text.Trim() + "'", con))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
                    {
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        if (dt.Rows[0]["vKdNota"] == DBNull.Value)
                        {
                            vNota = "0";
                        }
                        else
                        {
                            vNota = dt.Rows[0]["KdNota"].ToString();
                        }
                    }
                }
            }
}

but textbox not showing value 0, only report this : There is no row at position 0.

thank you

Your code assumes that there is a row. If your query returns no results then dt.Rows[0] won't exist. You should check that the datatable actually has rows. Have a look at this: stackoverflow.com/questions/6264554/… — squillman
– squillman, Commented Oct 12, 2021 at 19:11
Use proper parameterization, don't inject data into your query — Charlieface
– Charlieface, Commented Oct 12, 2021 at 19:20
You aren't selecting KdNota in your query. KdNota <> vKdNota — LarsTech
– LarsTech, Commented Oct 12, 2021 at 19:30

TheBatman · Accepted Answer · 2021-10-12 19:29:02Z

1

dt.Rows[0] doesn't exist. That would be the first entry in the collection, but the collection is empty. So you are trying to access a row entry to see if it's value is null. Instead you should check if the collection itself is empty. It should look like this

if (dt.Rows.Count == 0)
{
     vNota = "0";
}

answered Oct 12, 2021 at 19:29

TheBatman

7704 silver badges10 bronze badges

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

Comments

Albert D. Kallal · Accepted Answer · 2021-10-12 22:19:14Z

try this - you don't need a adaptor unless you going to update the results.

Hence:

void CheckNota()
{
    string vNota;
    using (SqlConnection con = new SqlConnection("your connect string"))
    {
        using (SqlCommand cmd1 = 
                new SqlCommand("select ISNULL(KdNota), 0) as vKdNota from tProdukBeliHead where KdNota = @kdNota", con))
        {
        DataTable dt = new DataTable();
        cmd1.Parameters.Add("@kdNota", SqlDbType.NVarChar).Value = txtKdBeli.Text;
        con.Open();
        dt.Load(cmd1.ExecuteReader());
        if (dt.Rows.Count > 0)
            vNota = dt.Rows[0]["vKdNota"].ToString();
        else
            vNota = "0";
        }
    }
}

as a FYI? We saved some lines of code, so we traded that savings by adding a parameter. This gives us sql injection safe code AND ALSO saved some lines of code. And we also did not have to mess with single quotes in the sql along with concatenation which actually is HARDER to write anyway!

Collectives™ on Stack Overflow

Set empty string in TextBox if Database value is null in ASP.Net not working

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related