ExecuteScalar() Not Returning Value?

Okay so here's a wierd one. I use SQLYog to peek into/administrate my databases.

I noticed that this chunk of code is not producing a value...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%?CurrentPage%') AND Enabled=1", Conn)With Cmd.Parameters.Add(New MySqlParameter("?CurrentPage",thisPage))End WithConn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog.

SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1

This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself.

Does anybody have any ideas?

Try

WHERE (PermissionFiles LIKE'%' + ?CurrentPage + '%')

Jos

|||

That didn't give me the desired result, either. It started returning "every" row that met all criteria but theCurrentPage.

But, thanks to your suggestion, what I ended up with was...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%" & thisPage & "%') AND Enabled=1 AND Everybody=0", Conn)Conn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Which works "as intended". Thanks!|||

execute scalar returns firts column from firts row of returned data so use this:

SELECT (SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

you can also use:

ifexists(SELECT *FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

select 1

else

select 0

which can work faster if you have more than one record whcih meet your criteria

I hope that it will work

sql