连接SQL Server数据库并使用C#检索数据(我在编辑中) [英] connecing SQL server database and retrieving data using C#, (i am editin)

问题描述

非常感谢您，我尝试了另一种方法，它奏效了..非常感谢您的帮助.但现在我被困在另一个地方，如果你能帮忙的话

Thank u so much i tried another thing and it worked.. thanks a lot for your help. but now i am stuck at another place if u could please help

namespace employee_info
{
public partial class _Default : System.Web.UI.Page
{
protected string SHQAPRDConn = System.Configuration.ConfigurationManager.AppSettings["SHQAPRDConn"];
 
protected void Page_Load(object sender, EventArgs e)
{
string login_user;
login_user = User.Identity.Name;
int int_substring = login_user.IndexOf("\\");
login_user = login_user.Substring(int_substring + 1);
login_user = "";
lblMsg.Text = login_user;
 
{
string strSql;
SqlConnection con = new SqlConnection(Conn);

SqlCommand objCmd;
SqlDataAdapter daEmployee;
SqlDataAdapter daVisa;
DataTable dtEmployee = new DataTable();
DataTable dtVisa = new DataTable();
strSql = "SELECT A.EMPLID, A.FIRST_NAME, A.LAST_NAME";
strSql = strSql + " FROM PS_EMPLOYEES A where EMPLID = ''" + login_user + "''";

con.Open();
objCmd = new SqlCommand(strSql, con);
objCmd.CommandType = CommandType.Text;
daEmployee = new SqlDataAdapter(objCmd);
daEmployee.Fill(dtEmployee);
 
foreach (DataRow myRow in dtEmployee.Rows)
{
txtFirstName.Text = myRow[1].ToString().Trim();
txtLastName.Text = myRow[2].ToString().Trim();

}
 
 
 
strSql = "SELECT max(EFFDT) , max(EXPIRATN_DT)";
strSql = strSql + " FROM PS_VISA_PMT_DATA where EMPLID = ''" + login_user + "''";
con.Close();
objCmd.Dispose();
con = new SqlConnection(SHQAPRDConn);
con.Open();
objCmd = new SqlCommand(strSql, con);
objCmd.CommandType = CommandType.Text;
daVisa = new SqlDataAdapter(objCmd);
daVisa.Fill(dtVisa);

foreach (DataRow myRow in dtVisa.Rows)
{
txtEffdt.Text = DateTime.Parse(myRow[0].ToString()).ToString("dd/MMM/yyyy");
txtExp.Text = DateTime.Parse(myRow[1].ToString()).ToString("dd/MMM/yyyy");
}
 
 
 
objCmd.Dispose();
daEmployee.Dispose();
daVisa.Dispose();
dtEmployee.Dispose();
dtVisa.Dispose();
con.Close();

现在，我想给用户提供一个条件，如果用户的签证在sysdate到期一个月后过期，则将被提醒.

now i jus wanted to give a condition lik to be alerted if the user s visa expires one month from the sysdate.

推荐答案

您还可以输入

"SELECT EMPLID, NAME from PS_EMPLOYEES where EMPLID = @login_user "

并使用
SqlParameter param = new SqlParameter("@login_user", ....

You can also put

"SELECT EMPLID, NAME from PS_EMPLOYEES where EMPLID = @login_user "

and use
SqlParameter param = new SqlParameter("@login_user", ....

It will eliminate the use of SQL Injection.

"EMPLID"是标识列吗?
如果是这样，则可能是int而不是string .

如果我没错，你应该改变.

is "EMPLID" the identity column?
If so it is probably an int and not string .

if i got this right you should change.

"SELECT EMPLID, NAME from PS_EMPLOYEES where EMPLID = login_user ";

到

to

"SELECT EMPLID, NAME from PS_EMPLOYEES where EMPLID =" + login_user;

然后使用变量login_user = "60050163";
而不是字符串"login_user".
如果那是您要获取的内容.

then you use the variable login_user = "60050163";
instead of the string "login_user".
If that is what you are trying to get.

如果您有一个带有到期日期列的表，该表的数据类型为DateTime，则可以在查询中执行此操作.

if you got a table with expiration date column which is of DateTime datatype, you can do this in your query.

"SELECT EXPIRATN_DT,DATEDIFF(MM,Getdate(),EXPIRATN_DT) from Whatever_table_i_am_using"

这将返回2列col0 = EXPIRATND_DT和col1 = Integer值，表示EXPIRATN_DT与今天的日期之间有多少个月.

那么您可以轻松编写if语句.

This, will return 2 columns col0 = EXPIRATND_DT and col1 = Integer value on how many months there is between EXPIRATN_DT and todays date.

then you can write your if statement pretty easy.

//Second column of MyRow<br />
if((int)MyRow[1] == 1)<br />
{<br />
//My alert message code goes here<br />
}

这篇关于连接SQL Server数据库并使用C#检索数据(我在编辑中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！