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

CodePudding user response：

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";
}

CodePudding user response：

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!