Syntax error near Source when trying to access a database C# asp.net

Question

string databaseLocation = "|DataDirectory|\\Users.mdf";
string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + databaseLocation + ";Integrated Security=True;User Instance=True";    
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.CommandText = String.Format("SELECT * FROM Users WHERE Username = {0}", username);
command.CommandType = CommandType.Text;
command.Connection = sqlConnection;
sqlConnection.Open();
int numberOfRows = command.ExecuteNonQuery();
sqlConnection.Close();
return numberOfRows;

This should check the Users.mdf database for the number of occorances of the username. but im getting a "syntax error near Source" runtime error when it hits the ExecuteNonQuery. I cant find anything wrong... Please help :)

It should? The query returns all rows with a username that matches - you want SELECT COUNT(*) AS numInstances ... so you can refer to the column alias to get the value... — OMG Ponies
– OMG Ponies, Commented Nov 17, 2011 at 2:03
but i thought executenonquery counts the number of results so i dont need the COUNT(*)? — vbman11
– vbman11, Commented Nov 17, 2011 at 2:08
You are also using String.Format to substitute the user name. Apart from this being a bad idea (you should use command parameters, it will most likely generate a syntax error as the user name in the WHERE clause should be surrounded by single quotes as the username is probably a string column. — Strelok
– Strelok, Commented Nov 17, 2011 at 2:10

Jeff Ogata · Accepted Answer · 2011-11-17 02:17:30Z

4

Your formatted sql statement is not including delimiters for the username:

command.CommandText = String.Format("SELECT * FROM Users WHERE Username = {0}", username);

sets the command text to something like:

SELECT * FROM Users WHERE Username = foo

This is easily corrected, but it would be better to use a SqlParameter:

command.CommandText = "SELECT * FROM Users WHERE Username = @username");
command.Parameters.AddWithValue("@username", username);

Also, ExecuteNonQuery will return -1 for the number of rows affected, since the select doesn't affect rows. Instead do:

command.CommandText = "SELECT COUNT(*) FROM Users WHERE Username = @username");
command.Parameters.AddWithValue("@username", username);
...
int numberOfRows = (int)command.ExecuteScalar();

edited Nov 17, 2011 at 2:17

answered Nov 17, 2011 at 2:08

Jeff Ogata

58k20 gold badges117 silver badges128 bronze badges

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

4 Comments

vbman11 Over a year ago

when I make these changes I get this error: "The data types text and nvarchar are incompatible in the equal to operator."

Jeff Ogata Over a year ago

@vbman11 Ok, your Username column is Text. Change it to an nvarchar column of appropriate size.

vbman11 Over a year ago

@adrift hey nvarchar works! thanks! but what if I want to use a text type?

Jeff Ogata Over a year ago

@vbman11, Text wouldn't be appropriate for a column like this - it was intended to be used for storing text data of up to 2 billion characters. It is also deprecated (see msdn.microsoft.com/en-us/library/ms187993.aspx). You could instead use nvarchar(max) where it makes sense - not for this column.

Strelok · Accepted Answer · 2011-11-17 02:27:13Z

0

Your code should be:

string databaseLocation = "|DataDirectory|\\Users.mdf";
string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + databaseLocation + ";Integrated Security=True;User Instance=True";    
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT COUNT(*) FROM Users WHERE Username = @User";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@User",username);
command.Connection = sqlConnection;
sqlConnection.Open();
int numberOfRows = command.ExecuteScalar();
sqlConnection.Close();
return numberOfRows;

edited Nov 17, 2011 at 2:27

answered Nov 17, 2011 at 2:16

Strelok

51.7k9 gold badges106 silver badges118 bronze badges

Collectives™ on Stack Overflow

Syntax error near Source when trying to access a database C# asp.net

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related