LIKE operator in SQL for my Access Database returns no values in ASP CLASSIC while it does if the query gets copied directly in Access

So as the title says, I'm trying to run a query in my ASP Classic page but for some reason it doesn't return a record set while it does returns a record set it if the query is copied directly in Access.

There is one thing where it probably goes wrong, namely:

The query uses an LIKE operator on a NUMERIC value.

Obviously the like operator can only be used on STRING values so I tried to cast the numeric value to a string using CStr, this had no effect. Then I tried to just hard code a value in my query and in Access this does seem to work (even though I am using the LIKE operator on a string to find numeric values - it works in Access).

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

You have mistake here from tblUsers u,a.Login_Name try to move this piece of code a.Login_Name to select Select u.User_Id, a.Login_Name from tblUsers u inner join (SELECT s.Login_Name Login_Name, COUNT(s.s1CIDNumber)as abc FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name) a on u.Login_Name=a.Login_Name ...

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

When creating a foreign key constraint, you can also decide what should happen with the constraints. For instance, if you want to delete all articles when a category is deleted (as I guess is your question): Schema::table('articulos', function($table) { $table->foreign('categoria_id')->references('id')->on('categorias')->onDelete('cascade'); $table->foreign('creador_id')->references('id')->on('users'); }); Also see documentation. The cascade identifies to the...

Your server has magic quotes enabled and your local server not. Remove it with the following sentence set_magic_quotes_runtime(0) As this function is deprecated and it will be deleted in PHP 7.0, I recommend you to change your php.ini with the following sentencies: magic_quotes_gpc = Off magic_quotes_runtime = Off If you...

Have a separate table with one row that contains the mainOfferName of the row that you want. Then query as: select yt.*, (nt.mainOfferName is not null) as valueFlag from yourtable yt left join newtable nt on yt.mainOfferName = nt.mainOfferName; When you want to change the value, change the value in...

this will work. you have to provide separate case statement to each condition SQLFIDDLE for the same SQLFIDDLE SELECT EMP_NO, sum(CASE WHEN Emp_Shift = 'AL' THEN 1 ELSE 0 END) AS COUNT_AL, sum(CASE WHEN Emp_Shift = 'S' THEN 1 ELSE 0 END) AS COUNT_S, sum(CASE WHEN Emp_Shift = 'H' THEN...

This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...

Edit In hindsight, this problem is a running partitioned maximum over Column1 * 2. It can be done as simply as SELECT Id, Column1, Model, Product, MAX(Column1 * 2) OVER (Partition BY Model, Product Order BY ID ASC) AS Column2 FROM Table1; Fiddle Original Answer Here's a way to do...

Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ...

SQLite is an embedded database, i.e., it is designed to be used together with a 'real' programming language. It might be possible to import that log file into a database file, but the whole point of having a database is to store the data, which is neither a direct goal...

You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some...

I have tested your code, I do not see any issues except for the fact, your For statement is a bit off and that you needed to set the db object. Try this code. Sub toto() Dim db As Database, i As Integer Set db = CurrentDb For i =...

Yes, You can use two different view in SELECT query. You have to JOIN them, if them have matched column in each other. Just treat two different views as like two different tables when using in SELECT Clause. SELECT vw1.a, vw2.b FROM View1 vw1 INNER JOIN View2 vw2 ON vw1.id...

The issue is that you are using the alias C where you should not, with the count function. This: C.Count(C.column6) should be: Count(C.column6) and the same change applies in the order by clause (which might be counting the wrong column - should it not be column6?): order by C.count(column5) desc...

The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...

you can achieve it like this DECLARE @AccountId int; @AccountID=20; DELETE FROM Table_A WHERE FunctionId IN (Select FunctionId FROM Table_B WHERE [email protected]); DELETE FROM Table_B WHERE [email protected]; ...

Query The query is not as simple as it looks at first. The shortest query string does not necessarily yield best performance. This should be as fast as it gets, being as short as possible for that: SELECT p.username, COALESCE(w.ct, 0) AS won, COALESCE(l.ct, 0) AS lost FROM ( SELECT...

The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...

It's not possible in Entity Framework to have Entities without primary key. Try to get a possible unique key from the views, combining columns, ... to create a unique primary key. If is not possible there is a workaround, if is only a queryable view, with out need to do...

So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...

The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....

select @variable will just return you a value of variable. You need to use some dynamic SQL I believe. Maybe smth like exec('select ' + @colname + ' from ' etc) will work for you (at least it will work in MS SQL server).

Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...

If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...