用于获取产品ID的参数化查询 [英] Parameterized queries for getting product id

问题描述

运行此命令后，出现错误:无效的列名.
我想要的是:显示组合框所选项目的产品ID.

您如何改善呢?

 字符串 ProductNo = lblrawmatno.Text;
字符串 ProductName = cboproductname.SelectedItem.ToString();
          
           
字符串 sql = "  + ProductName；

SqlCommand cmd = 新 SqlCommand(sql，cn);

SqlDataReader dr = cmd.ExecuteReader();
dr.Close(); 

解决方案

 字符串 ProductNo = lblrawmatno.Text;
字符串 ProductName = cboproductname.SelectedItem.ToString();
          
           
字符串 sql = " ;
 
SqlCommand cmd = 新 SqlCommand(sql，cn);
cmd.Parameters.AddWithValue(" ，ProductName)；
 
SqlDataReader dr = cmd.ExecuteReader();
// 在此处读取结果
dr.Close(); 

您可以改善更多...

 字符串 sqlQuery = " ;
使用( var  dataConnection = 新 SqlConnection( connectionString))
{
    使用( var  dataCommand = 新 SqlCommand( sqlQuery，dataConnection))
    {
        dataCommand.Parameters.AddWithValue(" ，ProductName)；
        dataConnection.Open();
         var  reader = dataCommand.ExecuteReader();
        同时(reader.Read()){
                // 做某事
        }
    }
} 

用作

 字符串 sql = " ;
 
SqlCommand cmd = 新 SqlCommand(sql，cn);
cmd.Parameters.AddWithValue(" ，ProductName);
 
SqlDataReader dr = cmd.ExecuteReader(); 

我猜ProductName是一个基于字符的列，因此您必须在文字上加上引号.否则，SQL会认为您正在按名称引用列:

 字符串 sql = "  + ProductName +  '";  



但是请注意以下几点:请不要那样构造您的SQL.使用SqlParameter可以防止SQL注入攻击.



此致



曼弗雷德(Manfred)

After I run this, I get an error: Invalid column name.
What I want: is to display the Product id of the combobox selected item.

How do you improve this?

string ProductNo = lblrawmatno.Text;
string ProductName = cboproductname.SelectedItem.ToString();
          
           
string sql = "SELECT ProductNo FROM RawMat WHERE ProductName=" + ProductName;

SqlCommand cmd = new SqlCommand(sql, cn);

SqlDataReader dr = cmd.ExecuteReader();
dr.Close();

解决方案

string ProductNo = lblrawmatno.Text;
string ProductName = cboproductname.SelectedItem.ToString();
          
           
string sql = "SELECT ProductNo FROM RawMat WHERE ProductName=@ProductName";
 
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.AddWithValue("@ProductName", ProductName); 
 
SqlDataReader dr = cmd.ExecuteReader();
// reading the results here 
dr.Close();   

you can improve more...

string sqlQuery = "SELECT ProductNo FROM RawMat WHERE ProductName=@ProductName";
using (var dataConnection = new SqlConnection(connectionString))
{
    using (var dataCommand = new SqlCommand(sqlQuery, dataConnection))
    {
        dataCommand.Parameters.AddWithValue("@ProductName", ProductName);
        dataConnection.Open();
        var reader = dataCommand.ExecuteReader();
        while (reader.Read()) {
                //do something
        }
    }
}

Use As

string sql = "SELECT ProductNo FROM RawMat WHERE ProductName=@PName";
 
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.AddWithValue("@PName",ProductName);
 
SqlDataReader dr = cmd.ExecuteReader();

I''d guess that ProductName is a character based column, so you''ll have to put quotes around the literal. Otherwise SQL will think you are referencing a column by name:

string sql = "SELECT ProductNo FROM RawMat WHERE ProductName='" + ProductName+"'";

One note of caution though: Please don''t construct your SQL like that. Use SqlParameter as it will prevent SQL injection attacks.

Regards,

Manfred

这篇关于用于获取产品ID的参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！