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();
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);
}