Question 87992 in ASP.NET Question posted in
The official documentation can be found here.

Assign query result to a variable and return variable – Asp.net

private int getuserid(String username){
        
            SqlConnection con = new SqlConnection(_conString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;

            cmd.CommandText = "SELECT USER_ID from tblUser where USERNAME ='" +username+"'";
            int locid = Convert.ToInt32(cmd.CommandText);

            return locid;
        }

Hi everyone, do you have an idea on how to assign value of user_id in a variable and return it. I tried but I am not sure its good.

Tags:

2

Answers


  1. 0 
    try

using (var conn = new SqlConnection(SomeConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();

    cmd.CommandText = "SELECT USER_ID from tblUser where USERNAME =@username";
    cmd.Parameters.Add("@username", SqlDbType.NVarChar, 30).Value = username;
    using (var reader = cmd.ExecuteReader())
    {
        if (reader.Read())
        {
            int locaid = Convert.ToInt32(reader.GetOrdinal("USER_ID"));
        }
    }
}

    OR

        int locaid = (int) cmd.ExecuteScalar();
    Login or Signup to reply.
  2. 0

    You need to actually open the connection and run the command.

    You should also take measures to protect against sql injection and leaving the connection open in the event of an exception. This code solves all three concerns:

    private int getuserid(String username)
{
    using (var con = new SqlConnection(_conString))
    using (var cmd = new SqlCommand("SELECT user_id FROM tblUser WHERE username = @username", con))
    {
        // Use actual column type and length from the database
        cmd.Parameters.Add("@username", SqlDbType.NVarChar, 30).Value = username;
        con.Open();
        return (int)cmd.ExecuteScalar();
    }
}

    Finally, you need to define what you want to happen if there’s no match for your WHERE clause. Right now there will be an exception, but that’s also true for the original code in the question, so as far as I know at this time that’s what you actually want to do.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search