none
Use SqlDataAdapter problem RRS feed

  • Question

  • Following is my code

     

    string commandString = "select acNo, chiName, engName, brno, add1, add2, tel1, tel2, fax1, fax2, contact, status, term, [close], openDate, closeDate, credit, ltrandate, ltinvno, remark, mSales, ySales, balance, sales, discount from dbo.Customer";

                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, conn);

                DataSet ds = new DataSet();

                dataAdapter.Fill(ds, "prog");

                DataTable dataTable = ds.Tables["prog"];

                int currRec = 0;

                int totalRec = dataTable.Rows.Count;

                companyName.Text = dataTable.Rows[currRec]["engName"].ToString();

     

     

    I want to make textbox supporting user to input to value for searching.
    I used to try it.
    string commandString = "select acNo, chiName, engName, brno, add1, add2, tel1, tel2, fax1, fax2, contact, status, term, [close], openDate, closeDate, credit, ltrandate, ltinvno, remark, mSales, ySales, balance, sales, discount from dbo.Customer" + id.Text();
    But it dose not success.

     

    Please teach me how to di it.

    Thank you

    Wednesday, August 15, 2007 3:45 AM

All replies

  •  

    What doesnt work? Any errors? or does it not bring up the results?

    Your SQL query looks incorrect, in addition for performance and security reasons, you should use Stored Procedures as they are safer, faster and you can update the database end of any changes in procedures than the code itself. It's better practice.

     

    Going back to the original problem, the query should be like this:

     

    string commandString = "select acNo, chiName, engName, brno, add1, add2, tel1, tel2, fax1, fax2, contact, status, term, [close], openDate, closeDate, credit, ltrandate, ltinvno, remark, mSales, ySales, balance, sales, discount from dbo.Customer WHERE [FieldName] LIKE '%" + id.Text + "%'";

     

    this will search, on a given field name, the value entered in the id textbox. having the LIKE %value% clause does a match which contains the text in between the % operator. Otherwise, it will be looking for the exact input you entered.

     

    Does this help?

    Wednesday, August 15, 2007 3:52 AM
  • Reasonable people can disagree on whether or not using SP's is a best practice.  But there's no disagreement on constructing a WHERE clause with string concatenation. 

     

    Don't.  Ever.

     

    Use parameters.  It's actually easier to use parameter queries than it is to construct them programmatically, once you learn how, because then you can test the queries in Management Studio or through the Database Explorer in VS without wondering whether or not your concatenation logic is doing the right thing.

     

    Assuming that you're searching on an "id" column in the table, that the user's entered a value into a TextBox named idTextBox, and that the "id" column is a string:

     

    string commandString = "SELECT acNo, chiName, engName, brno, add1, add2, tel1, tel2, fax1, fax2, contact, status, term, [close], openDate, closeDate, credit, ltrandate, ltinvno, remark, mSales, ySales, balance, sales, discount FROM dbo.Customer WHERE id = @id";

    SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, conn);

    dataAdapter.SelectCommand.Parameters.AddWithValue("@id", idTextBox.Text);

     

    Wednesday, August 15, 2007 8:19 AM
  • Dear,

     

    Add the id.Text asa parameter/ Then it will work. hope this will solve your problem.

     

    Regards,

    Wasif Ahmad

     

    Wednesday, August 15, 2007 8:58 PM