weird sql data reader thing...

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
dhnriverside said:
SELECT * FROM Artists WHERE (AZArtistName LIKE '" + curLetter + "%')
Click to expand...

1. what datatype is curLetter?
2. try to use SQL params, ie WHERE (AZArtistName LIKE @curLetter)
 
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
 
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
 
dhnriverside said:
Hi guys

curLetter is a string,
Click to expand...

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?
Click to expand...

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 said:
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...
Click to expand...

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

Nope, didn't work. I get no results at all using that method. It's very
bizarre this. I don't get this at all. Very weird.

Any more suggestions anyone?!

Cheers


Dan
 
Hi again

Having looked into it some more it turns out to be a problem with the way I
was setting up my table in the code, using TableRow, TableCell(), etc.

I apologise for not noticing this, but thanks anyway cos I now know how to
use SqlParameter, should I ever need to!

Cheers


Dan
 
Back
Top