为什么结果返回0？

问题描述：

我是新来的c＃ASP.NET，我想检查数据库值与登录部分的值插入，我做了一切正常，但为什么我仍然继续得到的值是不正确的，而我输入相同的值在我的数据库中的一个......任何想法？我的行数为dt继续得到0 ...是否有什么错误，当我添加参数？为什么结果返回0？

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Ng\Documents\Visual Studio 2015\Projects\Assignment6\Assignment6\App_Data\photoCompetition.mdf;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"); 

protected void Button1_Click(object sender, EventArgs e) 
     { 
       con.Open(); 
       SqlCommand cmd = new SqlCommand("SELECT * FROM [User] WHERE email='@useremail' and password='@password'", con); 
       cmd.Parameters.Add("@useremail", SqlDbType.Text).Value = emailtext.Text; 
       cmd.Parameters.Add("@password", SqlDbType.Text).Value = passwordtext.Text; 
       SqlDataAdapter sda = new SqlDataAdapter(cmd); 
       DataTable dt = new DataTable(); 
       sda.Fill(dt); 
       int i = cmd.ExecuteNonQuery(); 
       con.Close(); 

       if (dt.Rows.Count > 0) 
       { 
        Response.Redirect("Membermenu.aspx"); 
       } 
       else 
       { 
        lblMsg.Text = "Your username and password is incorrect"; 
        lblMsg.ForeColor = System.Drawing.Color.Red; 
        emailtext.Text = ""; 
        passwordtext.Text = ""; 
       } 
     }

答

当您使用参数，你不需要设置单引号字符串，在查询中删除引号

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Ng\Documents\Visual Studio 2015\Projects\Assignment6\Assignment6\App_Data\photoCompetition.mdf;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"); 

protected void Button1_Click(object sender, EventArgs e) 
     { 
       con.Open(); 
       SqlCommand cmd = new SqlCommand("SELECT * FROM [User] WHERE [email protected] and [email protected]", con); 
       cmd.Parameters.Add("@useremail", SqlDbType.Varchar).Value = emailtext.Text; 
       cmd.Parameters.Add("@password", SqlDbType.Varchar).Value = passwordtext.Text; 
       SqlDataAdapter sda = new SqlDataAdapter(cmd); 
       DataTable dt = new DataTable(); 
       sda.Fill(dt); 
       int i = cmd.ExecuteNonQuery(); 
       con.Close(); 

       if (dt.Rows.Count > 0) 
       { 
        Response.Redirect("Membermenu.aspx"); 
       } 
       else 
       { 
        lblMsg.Text = "Your username and password is incorrect"; 
        lblMsg.ForeColor = System.Drawing.Color.Red; 
        emailtext.Text = ""; 
        passwordtext.Text = ""; 
       } 
     }

我已经尝试，但我得到这个错误数据类型varchar和文本在相等的操作符不兼容。 –

什么是电子邮件和密码列的数据类型？这是什么数据库？ MS SQLServer？ – Krishna

是varchar和数据库是MSSQL服务器 –

答

我看起来像是与你的连接字符串，你的代码工作，而只改变连接串

public class TestSQLConnection 
{ 
     static string sqlConn = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString; 
    SqlConnection con = new SqlConnection(sqlConn); 

    public void TestConnection() 
    { 
     con.Open(); 
     SqlCommand cmd = new SqlCommand("SELECT * FROM [Users] WHERE [email protected] and [email protected]", con); 
     cmd.Parameters.Add("@useremail", SqlDbType.VarChar).Value = "David"; 
     cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = "Fawzy"; 
     SqlDataAdapter sda = new SqlDataAdapter(cmd); 
     DataTable dt = new DataTable(); 
     sda.Fill(dt); 
     con.Close(); 
     if (dt.Rows.Count > 0) 
     { 
      Console.WriteLine("Exist"); 
     } 
     else 
     { 
      Console.WriteLine("Not Exist"); 
     } 
    } 
}

<connectionStrings> 
<add name="TestDB" connectionString="Data Source=localhost;Initial Catalog=TestDB;User ID=sa;Password=xxxx;Integrated Security=False;" 
    providerName="System.Data.SqlClient" />

为什么结果返回0？

相关推荐