如何从数据库中访问特定的数据字段

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:

我正在开发一个库存软件，在这个软件中，我希望通过将产品名称和产品价格与产品代码进行比较，我已经存储在名为ProductLog的数据库表中的数据，产品日志中的数据是:

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.

现在，我只想在名为txtProductCode的课本中输入productCode，然后按tab，然后是ProductPrice(txtProductPrice)和ProductName(txtProductName)框自动填充。

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.

但是，在line productCode = Convert.ToInt32(txtProdcutCode.Text)中;它说输入字符串的格式不正确。

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 ...

但是它在关键字“WHERE”附近有不正确的语法。这意味着我在查询中调用数据库表时犯了错误，但是我无法发现错误……

2 个解决方案

#1

There are some issues with your SQL.

您的SQL存在一些问题。

You were originally overwriting the sql variable and only ended up with a WHERE clause;
您最初覆盖sql变量，最后只得到WHERE子句;
You don't have a FROM statement so the database doesn't know where you're trying to retrieve records from.
您没有FROM语句，因此数据库不知道您试图从何处检索记录。
The use of AND in a SELECT statement is incorrect; you just need commas to separate the fields.
在SELECT语句中使用和是不正确的;您只需要逗号来分隔字段。
You're never selecting ProductPrice from the DB, but selecting ProductName twice!
您不会从DB中选择ProductPrice，而是选择两次ProductName !
You're not using parameterized SQL for your query, leaving your app open to SQL injection attacks.
您没有为查询使用参数化SQL，这使得您的应用程序可以接受SQL注入攻击。

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

为了解决这个问题(第1-4点，我将把第5点留给您自己的研究)，

  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!

注意:这个答案假设txtProductCode的值。文本是一个整数!

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.

编辑:原来，列ProductCode是VarChar。对于正在阅读这个问题的OP和其他人，当您遇到SQL转换错误时，请检查SQL server中的列数据类型，并确保它与您提交的内容匹配。

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!

这是最基本的。还有许多其他的改进，但这将使你前进。修改一下基本的SQL语法，一旦理解了这些语法，就可以考虑使用参数而不是直接放置txtProductCode。文本到您的查询。好运！

#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.

不要两次调用InitializeComponent方法。它正在创建窗体和控件，并在窗体的构造函数中调用。可能当你离开你的文本框时，它又在创建，文本框将是空的。因此你会得到这个错误。从代码中删除InitializeComponent并再次尝试。

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 );

#1