Ad:

ASP.NET Discussion Thread
Unsolved
Views: 2617

Apr 30th, 2009

Incorrect syntax near '='.

Expand Post »

Hi there,

Im getting the error above, when i run my code.

         string myCommand = "SELECT * FROM Manager WHERE UserName=" + ID;

        SqlDataAdapter da = new SqlDataAdapter(myCommand, con);

        DataSet ds = new DataSet();

        try
        {
            con.Open();
            da.Fill(ds); // <- highlighting this part of the code..
        }
        finally
        {
            con.Close();
        }
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            txtUserName.Text = dr[1].ToString();

        }

    }
}

All i want to do is query the MS SQL database for data and extract row by row into an HTML table. This has proven to be a real pain in the neck. This simple function is sooo easy with PHP.

Please let me know if im on the right track, or if you can help with my error, i appreciate your help!

Thanks!

Last edited by RobertKramers; Apr 30th, 2009 at 10:37 am.

Similar Threads

Syntax error! Urgent. in JSP
System.Data.Sqlclient.Sqlexception: Line1 incorrect syntax at ']'. in ASP.NET
Incprrect Syntax Problem in ASP.NET
Syntax error - confused in ASP
Incorrect syntax near '=' in MS SQL

RobertKramers

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Apr 2009

Permalink

Apr 30th, 2009

Re: Incorrect syntax near '='.

try to change your code to :

string myCommand = "SELECT * FROM Manager WHERE UserName= ID";

it should work if it doesn't try more alternatives.

[/I]

mrGee

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Apr 2009

Permalink

Apr 30th, 2009

Re: Incorrect syntax near '='.

Try enclosing your ID value in single quotes.

ASP.NET Syntax (Toggle Plain Text)
string myCommand = "SELECT * FROM Manager WHERE UserName='" + ID + "'";
string myCommand = "SELECT * FROM Manager WHERE UserName='" + ID + "'";

Ezzaral

Moderator

Featured Poster

Reputation Points: 3239

Solved Threads: 838

Posting Genius

Offline

6,756 posts
since May 2007

Permalink

Apr 30th, 2009

Re: Incorrect syntax near '='.

you need a space before and after the "=" sign. That may help.

Only enlcose your ID value in single quotes if is a text value.

ninjaimp

Reputation Points: 22

Solved Threads: 9

Junior Poster

Offline

129 posts
since Apr 2008

Permalink

May 2nd, 2009

Re: Incorrect syntax near '='.

I would highly recommend against building your queries dynamically like this. You should use parameterized SQL for security and performance reasons, please see:
http://www.daniweb.com/forums/thread176306.html

Here is sample code for your situation:

ASP.NET Syntax (Toggle Plain Text)
private void simpleButton1_Click(object sender, EventArgs e)
{
	const string connStr = @"Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
	const string query = "Select * From Invoice Where InvNumber = @InvNumber";
	const int invNumber = 1100;
 
	DataSet ds = new DataSet();
 
	using (SqlConnection conn = new SqlConnection(connStr))
	{
		conn.Open();
		using (SqlCommand cmd = new SqlCommand(query, conn))
		{
			cmd.Parameters.Add("@InvNumber", SqlDbType.Int).Value = invNumber;
			using (SqlDataAdapter da = new SqlDataAdapter(cmd))
			{
				da.Fill(ds);
			}
		}
		conn.Close();
	}
}
private void simpleButton1_Click(object sender, EventArgs e)
{
	const string connStr = @"Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
	const string query = "Select * From Invoice Where InvNumber = @InvNumber";
	const int invNumber = 1100;

	DataSet ds = new DataSet();

	using (SqlConnection conn = new SqlConnection(connStr))
	{
		conn.Open();
		using (SqlCommand cmd = new SqlCommand(query, conn))
		{
			cmd.Parameters.Add("@InvNumber", SqlDbType.Int).Value = invNumber;
			using (SqlDataAdapter da = new SqlDataAdapter(cmd))
			{
				da.Fill(ds);
			}
		}
		conn.Close();
	}
}

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

May 2nd, 2009

Re: Incorrect syntax near '='.

CHECK THAT YOUR ID CONTAINS A VALUE OR NOT. I THINK YOUR ID DOES NOT CONTAIN VALUE... So THAT YOUR QUERY WILL BE LIKE WHERE ...= SO WHEN YOU EXECUTE THE QUERY GETS THIS ERROR.

mail2saion

Reputation Points: 26

Solved Threads: 44

Posting Whiz in Training

Offline

247 posts
since Apr 2009

Permalink

May 2nd, 2009

Re: Incorrect syntax near '='.

Click to Expand / Collapse Quote originally posted by mail2saion ...

CHECK THAT YOUR ID CONTAINS A VALUE OR NOT. I THINK YOUR ID DOES NOT CONTAIN VALUE... So THAT YOUR QUERY WILL BE LIKE WHERE ...= SO WHEN YOU EXECUTE THE QUERY GETS THIS ERROR.

This also gives another reason why parameterized queries should be used, because in the case of a blank ID it would run the query looking for null.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

May 3rd, 2009

Re: Incorrect syntax near '='.

Totally agree with SKnake on this, any kind of attempt to use dynamic sql should include thorough checking for SQL injection attempts.

The solution is as stated that the parameter is text and should be enclosed in single quotes spaces around the equals will make no difference at all, if the parameter potentially could contain Unicode text like Japanese or Chinese characters it should also be prefixed with an N to let SQL server know that the contents could be of the NVARCHAR type.

sedgey

Reputation Points: 68

Solved Threads: 9

Junior Poster

Offline

130 posts
since Jan 2007

Permalink

Mar 27th, 2011

Re: Incorrect syntax near '='.

Click to Expand / Collapse Quote originally posted by ninjaimp ...

you need a space before and after the "=" sign. That may help.

Only enlcose your ID value in single quotes if is a text value.

THANKYOU very much for this, it fixed my issue!!!

Kwad_Kore

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jan 2011

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP.NET Forum Timeline: How to publish asp.net website in iis?

Next Thread in ASP.NET Forum Timeline: identification of row in table

DaniWeb Message

Cancel Changes

User Name
Password