从数据库中选择值 [英] Selecting Values from Database

问题描述

您好。我想从2个表中选择值。

我的第一个表（Student_Data）如下：

 USN |名称| FName |部门|分支| Sem 
 001 abc ABC BE CS 3 
 002 xyz XYZ BE CS 3 
 003 def DEF BE CS 4 
 004 pqr PQR BE CS 5 

我的第二个表（Course_Data）如下：

 CC | C_Title | Dept | Branch | Sem | Crdits 
 sub1 C BE CS 3 5 
 sub2 C ++ BE CS 3 5 
 sub1数学BE CS 4 5 
 sub1 Java BE CS 5 5 

我想要的结果如下：当用户输入USN为001时，Dept为BE，Branch为CS，Sem为3）

 USN |名称| FName | CC | C_Title | Crdits 
 001 abc ABC sub1 C 5 
 001 abc ABC sub2 C ++ 5 

我试过并且我能够构建以下查询：

  SELECT  *  FROM （ SELECT  Student_Data.USN，Student_Data .Name，Student_Data.FName，Course_Data.CC，Course_Data.Credits  FROM  Student_Data  INNER   JOIN  Course_Data  ON  Student_Data.Dept = Course_Data.Dept  AND  Student_Data。 Branch = Course_Data.Branch） WHERE  USN = ' + TextBox1.Text +'  ORDER   BY  CC 

我在WHERE Clause中收到错误的语法错误。

请帮帮我。

谢谢。

解决方案

首先，不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



然后尝试这样的事情：

  SELECT  s.USN，s.Name，s.FName，c.CC，c.C_Ttile，c.Credits 
  FROM  Student_Data s 
  INNER   JOIN  Course_Data c 
  ON  s.Dept = c.Dept  AND  s.Branch = c.Branch 
  WHERE  s.USN=@USN 
  ORDER   BY  c.CC 

不要忘记将@USN参数添加到SqlCommand对象并将其值设置为文本框内容！

Hello. I am trying to Select values from 2 tables.
My 1st table(Student_Data) is as follows:

USN       |Name    |FName    |Dept    |Branch    |Sem
001        abc      ABC       BE       CS         3
002        xyz      XYZ       BE       CS         3
003        def      DEF       BE       CS         4
004        pqr      PQR       BE       CS         5

My 2nd table(Course_Data) is as follows:

CC       |C_Title  |Dept    |Branch    |Sem  |Crdits
sub1       C        BE       CS         3      5
sub2       C++      BE       CS         3      5
sub1       Math     BE       CS         4      5
sub1       Java     BE       CS         5      5

I want my result as follows : (When user enters USN as 001,Dept as BE, Branch as CS and Sem as 3)

USN       |Name    |FName     |CC       |C_Title    |Crdits
001        abc      ABC        sub1      C           5
001        abc      ABC        sub2      C++         5

I tried and I was able to built the following Query :

SELECT * FROM (SELECT Student_Data.USN, Student_Data.Name, Student_Data.FName, Course_Data.CC, Course_Data.Credits FROM Student_Data INNER JOIN Course_Data ON Student_Data.Dept=Course_Data.Dept AND Student_Data.Branch=Course_Data.Branch) WHERE USN = '" + TextBox1.Text + "' ORDER BY CC 

I am getting error at WHERE Clause as Incorrect Syntax.
Please Help me out.
Thank you.

解决方案

First, Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Then try something like this:

SELECT s.USN, s.Name, s.FName, c.CC, c.C_Ttile, c.Credits 
FROM Student_Data s
INNER JOIN Course_Data c
ON s.Dept=c.Dept AND s.Branch=c.Branch
WHERE s.USN=@USN 
ORDER BY c.CC

Don't forget to add the @USN parameter to your SqlCommand object and set it's value to the textbox content!

这篇关于从数据库中选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！