CODEWITHSUNDEEP
c#sql-server
Mehdi Sedaqat
Mehdi Sedaqat

Reputation: 13

C#(loading search results in textbox)

I am trying to search the database and set the results in textboxes. I am getting error, which says "invalid cast exception". I need your guide please.

private void btn_search_Click(object sender, EventArgs e)
{
    con.Open();
    string STR="select * from TICKETSALES where REFERENCE="+txtSearch.Text;
    cmd = new SqlCommand(STR,con );
    dr = cmd.ExecuteReader();
    if(dr.Read())
    {
        txtTrans.Text = dr.GetInt32("TRANSACTIONNUMBER").ToString();
        txtPax.Text = dr.GetString("PASSENGERNAME");
    }
    else
    {
        MessageBox.Show("Ticket Number not Found");
    }
}

Upvotes: 0

Views: 390

Answers (3)

Prasad Telkikar
Prasad Telkikar

Reputation: 16049

  1. Modify your select statement to get required column details.
  2. While assigning values to test box, use column index to get value from dr
  3. Convert value to string assign it to respective textbox

Here is sample implementation

con.Open();
//Use of camelCasing. transactionNumber instead of TRANSACTIONNUMBER
string STR="select transactionNumber,passengerNumber from TICKETSALES where REFERENCE=@search";
cmd = new SqlCommand(STR,con );
cmd.Parameters.Add("@search", txtSearch.Text);
dr = cmd.ExecuteReader();
if(dr.Read())
    {
        txtTrans.Text = Convert.ToString(dr[0]);
        txtPax.Text = Convert.ToString(dr[1]);
    }

Few tips for best coding practices (Credits: @tsahi-asher)

  • Don't pass values inside query, use parameters in query and use Paramere.Add() function to replace parameter with its value
  • Don't put your sql statements in presentation layer. Have some dedicated layer of SQL.
  • Don't use select *, use specific column name.
  • Don't use all-caps for identifier names, use camelCase.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37347

There are few issue already mentioned in comments and posts. I will chip in my remarks - you don't dispose of unmanaged resources, one answer covers that, but it violates your code. So here is alternative solution:

SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;

//some methods, fields

private void btn_search_Click(object sender, EventArgs e)
{
    con.Open();
    // as it has benn already said, you have to prevent yourself from SQL injection!
    cmd = (new SqlCommand("select * from TICKETSALES where REFERENCE=@ref", con)).Parameters.AddWithValue("@res", txtSearch.Text.Trim());
    dr = cmd.ExecuteReader();

    if (dr.Read())
    {
        txtTrans.Text = dr.GetInt32("TRANSACTIONNUMBER").ToString();
        txtPax.Text = dr.GetString("PASSENGERNAME");
    }
    else
    {
        MessageBox.Show("Ticket Number not Found");
    }
}
// it looks like you have unamanaged resources held by fields in your form,
// so to release them you have to call their Dispose() method!
// normally you should use using keyword if they were used locally in a method, as other answer states
public void Dispose()
{
    base.Dispose();
    if (con != null) con.Dispose();
    if (cmd != null) cmd.Dispose();
    if (dr != null) dr.Dispose();
}

Upvotes: 0

B.Hawkins
B.Hawkins

Reputation: 363

How about something like this:

  • Note the sql injection protection by paramtising the sql query. 

    private void btn_search_Click(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        string query = "select top 1 TRANSACTIONNUMBER, PASSENGERNAME from ticketsales where reference=@ref";
        using (SqlDataAdapter adap = new SqlDataAdapter(query, con))
        {
            con.Open();
            DataTable dt = new DataTable();
            adap.SelectCommand.Parameters.AddWithValue("@ref", txtSearch.Text.Trim());
            adap.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                txtTrans.Text = dt.Rows[0]["TRANSACTIONNUMBER"].ToString().Trim();
                txtPax.Text = dt.Rows[0]["PASSENGERNAME"].ToString().Trim();
            }
            else
            {
                MessageBox.Show("Ticket Number not Found");
            }
        }
    }
}

Upvotes: 0

Related Questions