我该如何解决这个问题。我真的很感激帮助。 [英] How do I solve this problem. I would really appreciate the help.

问题描述

我想通过ClinicName在普通桌子上检索一个诊所ID，并在他们注册时将clinID发送给用户。



我尝试了什么：

I want to retrieve a clinicID on the general table by the ClinicName and send the clinicID to the user when they register.

What I have tried:

int clinicId;
            string sqlClinicID = "SELECT ClinicID FROM GENERAL WHERE ClinicName ='" + txtClinicName.Text + "'";
             cmd = new SqlCommand(sqlClinicID, con);
             SqlDataReader rd = cmd.ExecuteReader();

            while (rd.Read())
            {
                clinicId = Convert.ToInt32(rd[0].ToString());
            }
            con.Close();
            
            sendMsg(clinicId, username, txtPassword.Text, email);

推荐答案

对于初学者，不要'这样做！永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。



其次，如果您的数据库中只有一个匹配值，为什么循环？



第三，如果它是数据库中的整数，为什么要将它转换为字符串，以便将其转换回整数？



第四，你需要完成后配置SqlCommands，SqlReaders，SqlConnections。



试试这个：

For starters, don't do it like that! Never 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.

Secondly, if there is only one matching value in your DB why loop?

Thirdly, if it's an integer in your DB, why convert it to a string, in order to convert it back to an integer?

Fourthly, you need to Dispose SqlCommands, SqlReaders, SqlConnections when you are finished with them.

Try this:

int clinicId;
string sqlClinicID = "SELECT ClinicID FROM GENERAL WHERE ClinicName =@CN";
using (SqlCommand cmd = new SqlCommand(sqlClinicID, con))
   {
   cmd.Parameters.AddWithVaue("@CN", txtClinicName.Text); 
   clinicId = (int) cmd.ExecuteScalar();
   sendMsg(clinicId, username, txtPassword.Text, email);
   }

如果这不能解决你的问题，你需要更详细地解释究竟出了什么问题。





感谢它帮助了我，但我在这一行收到错误。

If that doesn't solve your problem, you need to explain in better detail what exactly is going wrong.


Thank it helped me but im getting an error on this line.

System.NullReferenceException: Object reference not set to an instance of an object.

clinicId = (int)cmd.ExecuteScalar();

这是因为没有匹配的项目 - 而且您的代码不会检查用户输入。当你不允许用户犯错误时，你的代码就会失败 - 而且用户总是会犯错误：我在输入错误时必须纠正错别字！

当没有错误时匹配，SQL不返回任何行。



所以总是允许你的用户犯错误，并优雅地处理它们：

That's because there is no matching item - and your code doesn't check user input. When you don't allow the user to make mistakes, your code will fail - and users make mistakes all the time: heck I had to correct typos while I was typing this!
And when there is no match, SQL doesn't return any rows.

So always allow your users to make mistakes, and handle them gracefully:

int clinicId = -1;
string sqlClinicID = "SELECT ClinicID FROM GENERAL WHERE ClinicName = @CN";
using (SqlCommand cmd = new SqlCommand(sqlClinicID, con))
    {
    cmd.Parameters.AddWithValue("@CN", txtClinicName.Text);
    object o = cmd.ExecuteScalar();
    if (o == null)
        {
        ... Report problem to user...
        return;
        }
    clinicId = (int)o;
    sendMsg(clinicId, username, txtPassword.Text, email);
    }

我认为这样更好

I think this is better

<pre>int clinicId = -1;
string sqlClinicID = "SELECT ClinicID FROM GENERAL WHERE ClinicName = @CN";
using (SqlCommand cmd = new SqlCommand(sqlClinicID, con))
    {
    cmd.Parameters.AddWithValue("@CN", txtClinicName.Text);
    object result= cmd.ExecuteScalar();
    if (result.Length>0)
        {
        clinicId = (int)result;
        }else
{
// An error Occurs 
return
}
   
    sendMsg(clinicId, username, txtPassword.Text, email);
    }

这篇关于我该如何解决这个问题。我真的很感激帮助。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！