Question posted in
The official documentation can be found here.

How to store multiple SQL data columns into different variables C# – Asp.net

I am trying to store sql data that I have for a voucher id and voucher amount into a variable and display it into a label on a click of a button.

protected void Button1_Click(object sender, EventArgs e)
{
    string voucherId = String.Empty;
    string voucherAmount = String.Empty;

    string queryVoucherId = "select voucherid from ReturnForm where email = '" + Session["username"] + "';";
    string queryVoucherAmount = "select voucheramount from ReturnForm where email = '" + Session["username"] + "';";

    int index = 0;

    using (SqlConnection con = new SqlConnection(str))
    {
        SqlCommand cmd = new SqlCommand(queryVoucherId, con);

        con.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            voucherId = reader[index].ToString();
            index++;
        }
    }

    using (SqlConnection con = new SqlConnection(str))
    {
        SqlCommand cmd = new SqlCommand(queryVoucherAmount, con);
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            voucherAmount = reader[index].ToString();
            index++;
        }
    }

    if (txtVoucher.Text == voucherId)
    {
        Label3.Visible = true;
        Label3.Text = voucherAmount;
    }
}

When I click the button its giving me an error saying that the index is out of bounds.

Tags:

3

Answers


  1. 0

    I strongly recommend combining your sql queries into a single one, write it into a datatable and continue your logic from there. IMHO it is much cleaner code:

    string email = Session['username'];
string query = $"SELECT voucherid, voucheramount FROM ReturnFrom where Email = '{email}'";

DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = conn.CreateCommand())
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
    cmd.CommandText = query;
    cmd.CommandType = CommandType.Text;
    conn.Open();
    sda.Fill(dt);
    conn.Close();
}

// Work with DataTable dt from here on
...
    Login or Signup to reply.
  2. 0

    Building on @JSGarcia’s answer – but using parameters as one ALWAYS should – you’d get this code:

    string email = Session['username'];
string query = $"SELECT voucherid, voucheramount FROM ReturnFrom WHERE Email = @email";

DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(query, conn))
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
    // set the parameter before opening connection
    // this also defines the type and length of parameter - just a guess here, might need to change this
    cmd.Parameters.Add("@email", SqlDbType.VarChar, 100).Value = email;

    conn.Open();
    sda.Fill(dt);
    conn.Close();
}

    Personally, I’d rather use a data class like

    public class VoucherData
{
    public int Id { get; set; }
    public Decimal Amount { get; set; }
}

    and then get back a List<VoucherData> from your SQL query (using e.g. Dapper):

    string query = $"SELECT Id, Amount FROM ReturnFrom WHERE Email = @email";

List<VoucherData> vouchers = conn.Query<VoucherData>(query).ToList();

    I’d try to avoid the rather clunky and not very easy to use DataTable construct…

    Login or Signup to reply.
  3. 0

    Well, one more big tip?
    You ONLY as a general rule need a dataadaptor if you going to update the data table.

    And you ONLY need a new connection object if you say not using the sql command object.

    The sqlcommand object has:

    a connection object - no need to create a separate one
a reader - no need to create a separate one.

    Note how I did NOT create a seperate connection object, but used the one built into the command object.

    And since the parameter is the SAME in both cases? Then why not re-use that too!!

    So, we get this:

        void TestFun2()
    {
        String str = "some conneciton???";

        DataTable rstVouch = new DataTable();
        using (SqlCommand cmdSQL =
              new SqlCommand("select voucherid from ReturnForm where email = @email",
              new SqlConnection(str)))
        {
            cmdSQL.Parameters.Add("@email", SqlDbType.NVarChar).Value = Session["username"];
            cmdSQL.Connection.Open();

            rstVouch.Load(cmdSQL.ExecuteReader());

            // now get vouch amount
            cmdSQL.CommandText = "select voucheramount from ReturnForm where email = @email";

            DataTable rstVouchAmount = new DataTable();
            rstVouchAmount.Load(cmdSQL.ExecuteReader());

            if (rstVouch.Rows[0]["vourcherid"].ToString() == txtVoucher.Text)
            {
                Label3.Visible = true;
                Label3.Text = rstVouchAmount.Rows[0]["voucheramount"].ToString();
            }
        }
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search