How to Access Specific Fields of Data from Database

Question

I am working on a inventory software in which I want to access the ProductName and Product Price by Comparing it With the ProductCode, the Data I've Already Stored in Database table named ProductLog, the Data in Product Log is:

ItemNO       Productode       ProductName      ProductPrice
     1             123              lux              58
     2             321              soap             68

now I want that I only enter productCode in my textbook named txtProductCode, and press tab then ProductPrice(txtProductPrice) and ProductName(txtProductName) boxes fills automatically.

The code I tried to compare the Productcode and access values is:

  private void txtProdcutCode_Leave(object sender, EventArgs e)
        {
            ///////////////////////////////////////////////////////////////////////

            InitializeComponent();
            string sql;

            int productCode = 0;

            productCode = Convert.ToInt32(txtProdcutCode.Text);

            sql = "";
        sql = "SELECT dbo.ProductLog.ProductName, dbo.ProductLog.ProductName";
        sql = " WHERE ProductLog.ProductCode = " + txtProdcutCode.Text + "";


            SqlConnection cn = new SqlConnection();
            SqlCommand rs = new SqlCommand();
            SqlDataReader sdr = null;
            clsConnection clsCon = new clsConnection();


            clsCon.fnc_ConnectToDB(ref cn);


            rs.Connection = cn;
            rs.CommandText = sql;
            sdr = rs.ExecuteReader();


            while (sdr.Read())
            {

                txtProductPrice.Text = sdr["ProductPrice"].ToString();
                txtProductName.Text = sdr["ProductName"].ToString();
            }


            //lblTotalQuestion.Text = intQNo.ToString();

            sdr.Close();
            rs = null;
            cn.Close();




            /////////////////////////////////////////////////////////////////////////         
        }

but in line productCode = Convert.ToInt32(txtProdcutCode.Text); it says Input string was not in a correct format.

Please help me out with this problem.

EDIT:

I've also tried this code :

   private void txtProdcutCode_Leave(object sender, EventArgs e)
        {
            ///////////////////////////////////////////////////////////////////////

            string sql;

          //  int productCode = 0;

            //productCode = Convert.ToInt32(txtProdcutCode.Text);

            sql = "";
            sql = "SELECT dbo.ProductLog.ProductName, AND dbo.ProductLog.ProductName";
            sql = " WHERE dbo.ProductLog.ProductCode = " + txtProdcutCode.Text + "";


            SqlConnection cn = new SqlConnection();
            SqlCommand rs = new SqlCommand();
            SqlDataReader sdr = null;
            clsConnection clsCon = new clsConnection();


            clsCon.fnc_ConnectToDB(ref cn);


            rs.Connection = cn;
            rs.CommandText = sql;
            sdr = rs.ExecuteReader();


            while (sdr.Read())
            {

                txtProductPrice.Text = sdr["ProductPrice"].ToString();
                txtProductName.Text = sdr["ProductName"].ToString();
            }


            //lblTotalQuestion.Text = intQNo.ToString();

            sdr.Close();
            rs = null;
            cn.Close();




            /////////////////////////////////////////////////////////////////////////         
        }

but it says Incorrect syntax near the keyword 'WHERE'. means I am making mistake in calling database table in my query, but I am not able to find out the mistake ...

Answer 1

There are some issues with your SQL.

1. You were originally overwriting the sql variable and only ended up with a WHERE clause;
2. You don't have a FROM statement so the database doesn't know where you're trying to retrieve records from.
3. The use of AND in a SELECT statement is incorrect; you just need commas to separate the fields.
4. You're never selecting ProductPrice from the DB, but selecting ProductName twice!
5. You're not using parameterized SQL for your query, leaving your app open to SQL injection attacks.

To address this (points 1-4, I will leave point 5 for your own research),

  sql = "";
  sql = "SELECT dbo.ProductLog.ProductName, AND dbo.ProductLog.ProductName";
  sql = " WHERE dbo.ProductLog.ProductCode = " + txtProdcutCode.Text + "";

Should be

  sql += "SELECT ProductName, ProductPrice";
  sql += "  FROM dbo.ProductLog";
  sql += " WHERE ProductCode = '" + txtProdcutCode.Text + "'";

Note: This answer assumes that the value of txtProductCode.Text is an integer!

EDIT: It turns out that the column, ProductCode, was a VarChar. For OP and others reading this question, when you get SQL conversion errors check your column datatype in SQL server and make sure it matches what you're submitting.

That's the basics. There are many other improvements that can be made but this will get you going. Brush up on basic SQL syntax, and once you get that down, look into making this query use a parameter instead of directly placing txtProductCode.Text into your query. Good luck!

Answer 2

Never call InitializeComponent method twice.It's creating your form and controls and it's calling in your form's constructor.Probably when you leave your textBox it's creating again and textBox will be blank.therefore you getting that error.Delete InitializeComponent from your code and try again.

Update: your command text is wrong.here you should use +=

sql += " WHERE dbo.ProductLog.ProductCode = " + txtProdcutCode.Text + "";

But this is not elegant and safe.Instead use paramatirezed queries like this:

SqlCommand cmd = new SqlCommand();
cmd.Connection =  cn;
cmd.CommandText = "SELECT dbo.ProductLog.ProductName,dbo.ProductLog.ProductName WHERE dbo.ProductLog.ProductCode = @pCode";
cmd.Parameters.AddWithValue("@pCode", txtProdcutCode.Text );