如何从数据库中搜索两个匹配的项目? [英] How to search from database with two items matching?
问题描述
我有一个带有两个复合主键的数据库表(例如HTML,Q1 / HTML,Q2 / HTML,Q3)我可以使用这两个数据搜索特定信息。
模块问题 - 没问题
HTML Q1 abc
HTML Q2 def
HTML Q3 hhh
PHP Q1 bbb
PHP Q2 lll
PHP Q3 eee
i想要搜索HTML Q2(def)
如何执行匹配两个复合主键的操作?
我的尝试:
public MOduleQuestion Search(string mcode,string qno)
{
MOduleQuestion MQOB = new MOduleQuestion();
try
{
string sql =select * from Module_Question MQ_Module_Code ='+ mcode +'&& MQ_Question_No ='+ qno +';
SqlCommand cmd = new SqlCommand(sql,m_con);
m_con.Open();
SqlDataReader dreader = cmd.ExecuteReader();
if(dreader.Read())
{
MQOB.setModuleCode(dreader [0] .ToString());
MQOB.setQuestionNo(Convert.ToInt16(dreader [1] .ToString())); // Int
MQOB.setQuestion(dreader [2] .ToString());
MQOB.setOP1(dreader [3] .ToString());
MQOB.setOP2(dreader [4] .ToString());
MQOB.setOP3(dreader [5] .ToString());
MQOB.setOP4(dreader [6] .ToString());
MQOB.setAnswer(dreader [7] .ToString());
}
else
{
MQOB.setModuleCode(null);
}
dreader.Close();
}
catch(例外)
{
MessageBox.Show(找不到问题记录!!);
}
最后
{
m_con.Close();
}
返回MQOB;
}
试试
string sql = 从Module_Question中选择*,其中MQ_Module_Code = @ mcode和MQ_Question_No = @qno;
SqlCommand cmd = new SqlCommand(sql,m_con);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add( @ mcode,mcode);
cmd.Parameters.Add( @ qno,qno);
注意:格式化sql查询字符串易受攻击到 SQL注入 [ ^ ]攻击
总是使用参数化查询以防止SQL Server中的SQL注入攻击 [ ^ ]
请参考 CPallini 解决您遇到的错误在您的代码和修正中完成。
您必须使用AND
运算符,而不是&&&
(据我所知,SQL
非法)查询中的一个。请参阅:
SQL AND和OR运算符 [ ^ ]。
SQL操作员 [ ^ ]。
I have a database table with two composite primary key , (e.g HTML , Q1 / HTML, Q2 / HTML, Q3) i can to search a particular information by using both of the data.
Module Question-No question
HTML Q1 abc
HTML Q2 def
HTML Q3 hhh
PHP Q1 bbb
PHP Q2 lll
PHP Q3 eee
i want to search for HTML Q2 which is ("def")
How can i perform it that matches for both composite primary keys?
What I have tried:
public MOduleQuestion Search(string mcode, string qno) { MOduleQuestion MQOB = new MOduleQuestion(); try { string sql = "select * from Module_Question where MQ_Module_Code = '" + mcode + "' && MQ_Question_No ='" + qno +"' "; SqlCommand cmd = new SqlCommand(sql, m_con); m_con.Open(); SqlDataReader dreader = cmd.ExecuteReader(); if (dreader.Read()) { MQOB.setModuleCode(dreader[0].ToString()); MQOB.setQuestionNo(Convert.ToInt16(dreader[1].ToString())); //Int MQOB.setQuestion(dreader[2].ToString()); MQOB.setOP1(dreader[3].ToString()); MQOB.setOP2(dreader[4].ToString()); MQOB.setOP3(dreader[5].ToString()); MQOB.setOP4(dreader[6].ToString()); MQOB.setAnswer(dreader[7].ToString()); } else { MQOB.setModuleCode(null); } dreader.Close(); } catch (Exception) { MessageBox.Show(" No Question Record Were Found!!"); } finally { m_con.Close(); } return MQOB; }
try
string sql = "select * from Module_Question where MQ_Module_Code = @mcode and MQ_Question_No = @qno"; SqlCommand cmd = new SqlCommand(sql, m_con); cmd.CommandType= System.Data.CommandType.Text; cmd.Parameters.Add("@mcode",mcode); cmd.Parameters.Add("@qno",qno);
Note: Formatting the sql Query string is vulnerable to SQL Injection[^] attacks
always use Parameterized queries to prevent SQL Injection Attacks in SQL Server[^]
Please refer CPallini solution for what mistake you have done in your code and the correction.
You have to use theAND
operator, instead of the&&
(illegal inSQL
, as far as I know) one in your query. See:
SQL AND and OR Operators[^].
SQL Operators[^].
这篇关于如何从数据库中搜索两个匹配的项目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!