Odtnhj

I am trying to get the user id from the user's table called userDataTbl(in my MySQL database) by a user name. However, when I run the code below it keeps returning -1 and the result parameter keeps being -1 as if it didn't find the user.

I have also used the method below in order to check if a username already exists in the table-and then I just checked if the result is greater than 0 to return that it exists and otherwise that it doesn't exists, but it didn't work there either and the result kept being -1 even though I put names that did exist in the table. I have tried debugging but it didn't tell me much because it doesn't really show me what the computer does in the query so that I'll know what's wrong with it.
this is the code :

//returns a user id according to a name
public int theId(string name)

 SqlCommand command2 = new SqlCommand("Select userId from dbo.userDataTbl where userName=@username", this.connection);
 command2.Parameters.AddWithValue("@username", name);
 int result = command2.ExecuteNonQuery();
 if (result > 0)
 
 SqlDataReader reader = command2.ExecuteReader();
 reader.Read(); // we have only 1 row
 try
 
 string foundId = String.Format("0", reader["userId"]);
 int id = Convert.ToInt32(foundId);
 return id;
 
 catch
 
 return 0;
 
 
 else
 
 return -1;
 

I will be very grateful for any help, it's really important for me.

Based on MSDN:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

If you want to get the number of rows affected by the SELECT command and save it to an int variable you can use Count and ExecuteScalar:

"SELECT COUNT(userId)..."
var result = (int)command2.ExecuteScalar();

I think int result = command2.ExecuteNonQuery(); line causes problem for you remove that line, change the code as below and run it

public int theId(string name)

 SqlCommand command2 = new SqlCommand("Select userId from dbo.userDataTbl where userName=@username", this.connection);
 command2.Parameters.AddWithValue("@username", name);
 this.connection.open();
 try
 
 SqlDataReader reader = command2.ExecuteReader();
 if(reader.HasRows)
 
 while(reader.Read())
 
 string foundId = String.Format("0", reader["userId"]);
 int id = Convert.ToInt32(foundId);
 return id; 
 
 
 else
 
 return -1;
 
 
 catch(Exception ex)
 

 

Hope this helps you - Happy coding !!