asp.net error retrieving from database when query string contains number

Question

i am using a dataset to retrieve some info from the database like this:

    protected DataSet getInfo() //getting the user info
{
    string id = Request.QueryString["User"];

    SqlCommand cmd = new SqlCommand("SELECT * FROM copy WHERE id=@id", conn());
    cmd.Parameters.AddWithValue("@id", id);

    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(ds);

    return ds;
}

   cvPage.InnerHtml = getInfo().Tables[0].Rows[0]["cv"].ToString();
   clPage.InnerHtml = getInfo().Tables[0].Rows[0]["cl"].ToString();
   bioPage.InnerHtml = getInfo().Tables[0].Rows[0]["bio"].ToString();

and it works fine until there is a number in the query string so if:

QueryString["User"] = "111111"

i will get an incorrect syntax error:

Incorrect syntax near '111111'.

but if there is no numbers it will work fine.

my database contains four columns:

id | varchar
cv | nvarchar
cl | nvarchar
bio | nvarchar

i am using asp.net 4.0.

what is the problem?

thanks

Use sql servedr profiler to see what's the exact query that is being executed behind the scene . — Beatles1692, Feb 22 '12 at 23:11
@AbeMiessler - yes it must be a varchar. but i already tried CAST as int and didn't work. — Wahtever, Feb 22 '12 at 23:12
I agree with @AbeMiessler. Otherwise you could convert the integer "1111" into a varchar in your select statement. — Christopher Rathermel, Feb 22 '12 at 23:12
@ChristopherRathermel - i already tried CAST and CONVERT but didn't work. — Wahtever, Feb 22 '12 at 23:14
@Beatles1692 - unfortunately i don't have access to the server profiler as i am using Express Edition — Wahtever, Feb 22 '12 at 23:21
`Incorrect syntax near '111111'` can't be coming from that code. — Martin Smith, Feb 22 '12 at 23:26
@MartinSmith - when i try the code in sql server it works fine, and this code will work fine with non-numeric query strings — Wahtever, Feb 22 '12 at 23:28
@Wahtever - I think you are looking at the wrong bit of your `C#` code. It must be sending a string to SQL Server containing the literal `11111` to get that error message but you are showing a parameterised query. Double check the line number in the error message. Also is your code **exactly** the same as what you posted or have you changed anything? — Martin Smith, Feb 22 '12 at 23:30

el ninho · Accepted Answer · 2012-02-22T23:32:43.147

4

 SqlCommand cmd = new SqlCommand("SELECT * FROM copy WHERE id= CONVERT(varchar(4000), @id) ", conn());

edited Feb 22 '12 at 23:32

answered Feb 22 '12 at 23:11

el ninho

4,183
15
56
77

yes same error "Incorrect syntax near '111111'" but when i try to convert to int i get this error instead `Conversion failed when converting the varchar value '.....Website.com/Shatha' to data type int` – Wahtever Feb 22 '12 at 23:30
OK, another approach, try dealing with it on db-side. Change 4000 with something other if you want, ideally with number on column definition. – el ninho Feb 22 '12 at 23:34
yes thank you, works great. just a quick question can a table name contain numbers like table 12eight. thank you – Wahtever Feb 22 '12 at 23:45
@Wahtever - I guess you were doing something like `"SELECT * FROM " + id + "copy WHERE id=@id"` then? – Martin Smith Feb 22 '12 at 23:49
@MartinSmith - its actually something iam trying to do, and i wanted to know if it's possible. – Wahtever Feb 22 '12 at 23:53
@Wahtever - The code you posted cannot cause the error you say you were getting. You must be concatenating the literal value into the query somewhere. – Martin Smith Feb 22 '12 at 23:54
@MartinSmith - i really which i could understand what happened, maybe it was a database problem at the time. i will try to revert back to the original code in a couple of days and see if it works. – Wahtever Feb 22 '12 at 23:59
1

@Wahtever - In answer to your other question, yes object names can contain numbers but must be delimited with `[]` if they start with a number. e.g. `[123foo]`. Watch out for SQL injection. – Martin Smith Feb 23 '12 at 00:01
@MartinSmith - thank you. and i will try to identify the problem, once i do i will post it here. thank you again. – Wahtever Feb 23 '12 at 00:09
1

@MartinSmith - you were right, it was the url rewriting system. it was going to another page executing a differnt sql without changing the url and by the time i tried the last code they got it fixed. – Wahtever Feb 23 '12 at 08:12

asp.net error retrieving from database when query string contains number

1 Answers1