How do i convert an int retrieved from a database to string

Question

I have a login form that I wanna select the userID (which is in the form of an int) from the database, and store it as a string.

string insertQuery = 
  "SELECT UserID FROM Customers WHERE Email = @Email AND Password = @Password";

SqlCommand com = new SqlCommand(insertQuery, conn);

com.Parameters.AddWithValue("@Email", tbEmail.Text);
com.Parameters.AddWithValue("@Password", tbPassword.Text);

string result = (string)com.ExecuteScalar();

But after I login, I get this error:

System.InvalidCastException: 'Unable to cast object of type 'System.Int32' to type 'System.String'.

Like this: string insertQuery = "SELECT CONVERT(NVARCHAR(20),UserID) FROM Customers WHERE Email = @Email AND Password = @Password"; — Prashant Pimpale
– Prashant Pimpale, Commented Dec 28, 2018 at 12:48
Instead of (string) in front if it, add .ToString() behind it. — Nyerguds
– Nyerguds, Commented Dec 28, 2018 at 12:52
Why do you want to do that? What's wrong with keeping it an int? — Klaus Gütter
– Klaus Gütter, Commented Dec 28, 2018 at 12:52
If the value is an integer then use an int to read it. This seems like an X-Y Problem. — David
– David, Commented Dec 28, 2018 at 12:58

Dmitrii Bychenko · Accepted Answer · 2018-12-28 13:04:04Z

7

What if the record doesn't exist (i.e. the cursor is empty)? Let's read and check if we have at least one record:

// Keep Sql being readable
string insertQuery = 
  @"SELECT UserID 
      FROM Customers 
     WHERE Email = @Email 
       AND Password = @Password";

// Do not forget to dispose IDisposable
using (SqlCommand com = new SqlCommand(insertQuery, conn)) {
  com.Parameters.AddWithValue("@Email", tbEmail.Text);
  com.Parameters.AddWithValue("@Password", tbPassword.Text);

  using (var reader = com.ExecuteReader()) {
    string result = reader.Read()
      ? Convert.ToString(reader[0]) // record exists
      : null;                       // cursor is empty

    //TODO: put relevant code which works with result here
  }
}

answered Dec 28, 2018 at 13:04

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sri harsha · Accepted Answer · 2018-12-28 12:58:06Z

3

You can try using like below

string result = Convert.ToString(com.ExecuteScalar());

answered Dec 28, 2018 at 12:58

sri harsha

6806 silver badges16 bronze badges

Comments

uzay95 · Accepted Answer · 2018-12-28 12:59:49Z

1

ExecuteScalar returns Object type and you can convert it into which ever type you like

public override object ExecuteScalar ();

You can call it's ToString() method and it returns string form of it's value.

answered Dec 28, 2018 at 12:59

uzay95

16.8k31 gold badges123 silver badges189 bronze badges

Comments

Berkay Yaylacı · Accepted Answer · 2018-12-28 13:03:14Z

1

Try this,

string result = string.Empty;

SqlCommand com = new SqlCommand(..);
..
object executedResult = com.ExecuteScalar();

if(executedResult != null) {
   result = executedResult.ToString();
}

Hope helps,

answered Dec 28, 2018 at 13:03

Berkay Yaylacı

4,5132 gold badges22 silver badges39 bronze badges

Comments

Pawel Czapski · Accepted Answer · 2018-12-28 13:06:44Z

1

Probably the simplest solution would be (assuming query always return result):

string result = com.ExecuteScalar().ToString();

You can cast as nvarchar in your query also:

string insertQuery = "SELECT cast(UserID as nvarchar) FROM Customers WHERE Email = @Email AND Password = @Password";

edited Dec 28, 2018 at 13:06

answered Dec 28, 2018 at 12:57

Pawel Czapski

1,8742 gold badges17 silver badges29 bronze badges

Comments

Sravan Kumar · Accepted Answer · 2018-12-28 13:12:18Z

0

Below are my findings

    string userID;

using(SqlConnection conn = new SqlConnection(connectionString))
{
            string insertQuery = "SELECT UserID FROM Customers WHERE Email = @Email AND 
            Password = @Password";
            SqlCommand com = new SqlCommand(insertQuery, conn);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Email", tbEmail.Text.ToString().Trim());
            com.Parameters.AddWithValue("@Password", tbPassword.Text.ToString().Trim());
            SqlDataReader reader = com.ExecuteReader();
            while(reader.Read())
            {
              userID = reader["UserID"].ToString();
            }
}

answered Dec 28, 2018 at 13:12

Sravan Kumar

12 bronze badges

Collectives™ on Stack Overflow

How do i convert an int retrieved from a database to string

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related