weird sql data reader thing...

[Guest]

Hiya

Right, I'm trying to make an A-Z page for my site. It starts on A, with a
row of 26 letters along the top that you can click on.

Fine so far.

Now I've come to pull the data in from the SQL database, and something very
bizarre is happening. Heres the statement...

SELECT * FROM Artists WHERE (AZArtistName LIKE '" + curLetter + "%')

I was using field names, but tried * to see if it worked. No luck.

If I'm on A, I get no results (i should get 2), on B i get 2 which is right
I think. On M I don't get any where I should get at least a couple.

The statement works perfectly in Enterprise Manager query window... using
WHERE (AZArtistName LIKE 'A%') - works fine, but nothing from code.

Any ideas?!?!

Cheers


Dan

 

[Fabio]

SELECT * FROM Artists WHERE (AZArtistName LIKE '" + curLetter + "%')

1. what datatype is curLetter?
2. try to use SQL params, ie WHERE (AZArtistName LIKE @curLetter)

 

[Jeff Sheldon]

Dan,

Try breaking on the line that gets the value of "curLetter" (or
response.writing) verify that it has only that letter in it and no
leading or trailing spaces.

-Jeff

 

[Guest]

Hi guys

curLetter is a string, and it's pulled from QueryString. I've got the code
displaying the SQL statement, and for A I'm getting...

SELECT ArtistID, AZArtistName FROM Artists WHERE (AZArtistName LIKE 'A%')
ORDER BY AZArtistName ASC

Can't see any reason why this isn't working...

What does the @ do ? as in Fabio's suggestion?

Cheers


Dan

 

[Fabio]

Hi guys

curLetter is a string,

Then you should not need the single quotes around curLetter in your SELECT
statement: add the % symbol to curLetter and add it like that to the SQL
Statement. That takes me to the next point...

What does the @ do ? as in Fabio's suggestion?

That's a SQL parameter:

// SQL command - notice the parameter in the SELECT statement
SqlCommand c = new SqlCommand();
c.CommandText = "SELECT * FROM Artists WHERE (AZArtistName LIKE @curLetter)";

// SQL parameter: define type, assign a value and add it to the command
SqlParameter p = new SqlParameter("@curLetter",SqlDbType.Char,1);
p.Value = curLetter;
c.Parameters.Add(p);

// exec datareader here as usual...


Have fun,
Fabio

 

[Fabio]

Then you should not need the single quotes around curLetter in your
SELECT statement: add the % symbol to curLetter and add it like that to
the SQL Statement. That takes me to the next point...

On second thoughts, maybe that's not correct - but please feel free to verify
and let me know...