CODEWITHSUNDEEP
c#asp.net
kunalranjan08
kunalranjan08

Reputation: 21

asp.net database connectivity

Please take a look at the following code.

protected void Button2_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(constring);

    SqlCommand cmd = new SqlCommand();

    if (DropDownList3.SelectedItem.Text == "Economy")
    {
        seats = Convert.ToInt32(DropDownList1.SelectedItem.Text);

        cmd.Connection = con;
        con.Open();
        cmd.CommandText = "select easeats from flight where fno='" + fn + "'";
       int eds = Convert.ToInt32(cmd.ExecuteScalar());

        if (eds > seats)
        {
            Panel2.Visible = true;                //seats available
            cl = DropDownList3.SelectedItem.Text;  
            seat = seats.ToString();
            seats = eds;
        }
        else
        {
            Panel3.Visible = true;         // seats not available 
        }
        con.Close();
    }
}

I am getting error in the line:int eds = Convert.ToInt32(cmd.ExecuteScalar());

And the error is

error in converting varchar value to datatype int

What is wrong with this code?

Upvotes: 1

Views: 277

Answers (2)

Davide Piras
Davide Piras

Reputation: 44595

try to replace:

int eds = Convert.ToInt32(cmd.ExecuteScalar());

with:

int eds = 0;

int.TryParse(cmd.ExecuteScalar(), out eds);

in this way if the Convert fails you have no issues and continue with eds = 0...

side note, your handling of exceptions and connection lifetime is very poor, think about replacing the whole block in this way:

protected void Button2_Click(object sender, EventArgs e)
{
    using(var con = new SqlConnection(constring))
    using(var cmd = con.CreateCommand())
    {
        if (DropDownList3.SelectedItem.Text == "Economy")
        {
            seats = Convert.ToInt32(DropDownList1.SelectedItem.Text);

            con.Open();
            cmd.CommandText = "select easeats from flight where fno='" + fn + "'";

            int eds = 0;
            object result = cmd.ExecuteScalar(); 
            int.TryParse(result, out eds);

            if (eds > seats)
            {
                Panel2.Visible = true;                //seats available
                cl = DropDownList3.SelectedItem.Text;  
                seat = seats.ToString();
                seats = eds;
            }
            else
            {
                Panel3.Visible = true;         // seats not available 
            }
        }
    }
}

of course, anyway, you should also consider to refactor the whole thing and separate clearly database and business logic from UI logic, in general you should never deal directly with connections and queries inside the UI and even less in a ButtonClick event handler.

Upvotes: 2

walther
walther

Reputation: 13598

Firstly, NEVER construct your query like this. Use parameterized queries instead.

Secondly, the error message is quite clear - you're trying to convert to int some varchar column, that probably contains some literal characters as well and not only numbers...

Thirdly, prefer "using" statements over explicitly closing the connection. It's more safe that way.

Upvotes: 4

Related Questions