2

This is my query: SELECT fullName FROM userData WHERE userName=?

And my C# Code:

var con = new SqlConnection();
  var cmd = new SqlCommand();
  var dt = new DataTable();
  con.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\hugow\Documents\Visual Studio 2015\Projects\OSGS_v2\dataBase.mdf;Integrated Security=True;Connect Timeout=30";
  cmd.Connection = con;
  string cmdText = null;
  string usrName = Request.Cookies["usrData"]["usrName"];
  cmdText = "SELECT fullName FROM userData WHERE userName=" + usrName;
  cmd.CommandText = cmdText;
  con.Open();
  string fullName = (string)cmd.ExecuteScalar();
  nameLabel.Text = fullName;

Nothing bad right? Well, I get the error: Can't convert System.Int32 to System.String. Convert it to String right? Well, the main problem is: the data ("fullName") is a String from it's origin.

So why is this error occurring?

Thanks in advance.

EDIT

I forgot to include cmd.CommandText = cmdText; to include in my code snippet, but it was already there in my real code.

Hugo Woesthuis
  • 193
  • 2
  • 15
  • 6
    Crying for SqlInjection... Don´t use un-checked input for sql-operations. *Allways* use parameterized queries instead. Anyway: what does `ExecuteScalar` return in your case (without the cast)? – MakePeaceGreatAgain Oct 05 '16 at 14:01
  • 5
    You never assign to `cmd.CommandText`. What command are you *actually* executing? :) – Luaan Oct 05 '16 at 14:07
  • Also make sure you add quotation marks surrounding usrName before concatenating "SELECT fullName FROM userData WHERE userName=" + usrName; – derloopkat Oct 05 '16 at 14:14

5 Answers5

3

You are missing assigning commandText to SqlCommand Object

cmd.CommandText = cmdText; 
string fullName = (string)cmd.ExecuteScalar();
Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
2

First assign your cmdText to cmd.CommandText

 cmd.CommandText = cmdText;

Then ExecuteScalar() and then convert it using ToString() method

 var temp = cmd.ExecuteScalar();
 string fullName = temp.ToString();
Mostafiz
  • 7,243
  • 3
  • 28
  • 42
1

Answers to circumvent your problem have already been given. To answer the question why this is occurring: Casting to string can be done with (string) and ToString(), but (string) applied to an object can only convert a string object to string. ExecuteScalar is an Int32 object, so this won't work.

See also here:

Casting to string versus calling ToString

Community
  • 1
  • 1
Thern
  • 1,017
  • 8
  • 17
1

This, 

"SELECT fullName FROM userData WHERE userName=" + usrName;

should be 

"SELECT fullName FROM userData WHERE userName='" + usrName + "'";

SQL Server thinks your usrName is an int because you did not enclose it in quotes.

Also, be aware of single quotes in the actual usrName. Say, for example, you have O'Keefe - you woul need to escape the single '. You do this by doubling them up.

So something like:

"SELECT fullName FROM userData WHERE userName='" + usrName.replace("'", "''") + "'";
Darren Wainwright
  • 30,247
  • 21
  • 76
  • 127
  • Sorry, didn't see your comment, though yes, it would for sure see it as an int. If he debugs his code and examines the resulting SQL he would see it is missing single quotes. Its only declared as a string in c# - SQL knows nothing about this and just executes the command as-is. – Darren Wainwright Oct 05 '16 at 14:39
  • Nope, the casting error was in line "string fullName = (string)cmd.ExecuteScalar();" because ExecuteScalar() returns a number. In C# usrName is declared as a string so when you concatenate you get "SELECT fullName FROM userData WHERE userName=SOME NAME OR EMPTY" which produces a syntax error in SQL Server. This is not considered a number and not the cause of the problem reported by OP. – derloopkat Oct 05 '16 at 14:51
0

use the following code:

var result= cmd.ExecuteScalar();

if (result!= null) {
    fullName = result.ToString();
}
Shaikh Farooque
  • 2,620
  • 1
  • 19
  • 33