Inlcude Null Values In Result Set

I'm working with an Access DB, and two tables. This is going to be ported to MS SQL shortly. The tables are category and merchantcategory, and the link is on category.id and merchantcategory.category_id.

*** Category Table ***
id, category
1, apples
2, oranges
3, lemons

*** Merchantcategory Table ***
id, category_id, merchant_id
1, 1, 1
2, 3, 4
3, 3, 19

What I need to do is something like
SELECT category, count(merchant_id) AS merchants
FROM category, merchantcategory
WHERE category.id = merchantcategory.category_id
GROUP BY category
ORDER BY category

But, I need the merchants column to either return 0 or NULL for those categories that do not have an associated merchant. I would like to see something like:

1, 1
2, 0
3, 2

A. Is this possible?

B. If it is, what do I need to do to get the result? I would prefer not to have a bunch of record sets and loops.

View Replies


ADVERTISEMENT

RecordSet (and GetRows) Is Retrieving Null Values When They Aren't Null

I have an interesting situation that needs to be addressed ASAP.

I am running a standard SQL Query in ASP to retrieve some rows/columns from a table that's on a SQL Server 2000 database. I put the results of that query into a RecordSet, and then use the GetRows function to push the results that are in the recordset into an array variable. In the query that I am running, 39 columns and 2 rows are returned.

The GetRows function appears to be working - the array variable is being created with the proper dimensions. However, the values of the different "cells" of the array are not showing up correctly. The first and the last column values for each row are correct, yet everything else in between is blank, empty, NULL, or whatever.

In other words: ArrayVariable(0,0) shows up correctly, ArrayVariable(38,0) shows up correctly, but everything in between is null.

I did a test on the RecordSet and discovered that it was the culprit - it wasn't being filled in properly. This is strange, though, because the select query that I run in the ASP code to create the recordset returns perfect results when I run it on the SQL Server 2K database.

View Replies View Related

Combine The Two Variable Values And Show The Result

I have two variables:

UserLastName = request.cookies("UserLastName")
UserFirstName = request.cookies("UserFirstName")

and I'm trying to create a third variable that would strip the first letter of each of the above variables and combine them to create initials. Eg. if FirstName is John and LastName is Doe, the UserInitial variable would be "JD".

View Replies View Related

NULL Values In SQL

I have 2 questions about SQL server. First, what should i do about allowing NULL values when designing the database? What difference does it make? And if I disallow NULL values, should i enter default values?

Second, when adding new records to database with recordset.addnew, what SELECT statment, cursor, and lock type should i use?

I've always used "SELECT * FROM Table" and adOpenStatic, and adLockOptimistic. This SELECT statement looks very inefficient to me, but I don't know what else to use.

View Replies View Related

Inserting NULL Values

How do I tell my SQL Server database to insert a NULL value for an empty string. Like if I set a value to null in ASP like this: x = null then it inserts as empty in to the database, how do I get it to insert a reall null value?

View Replies View Related

Checking For Null Values

i have a null value in one of my session variables.

So i tried using this:

if Session.Contents("Department") = "" then
response.write "DEPARTMENT IS NULL"
end if

it doesn't work though.

Session.Contents("Daepartment") = null doesn't work too.
how should i do the checking to check for null values?

View Replies View Related

Empty/Null Values In SPs

My typical code to call an Stored Proc in ASP is as follows ...

View Replies View Related

Null Values From Recordset

I'm reading records from a recordset and placing them in a table. One of these fields is a date value and I format it using the following:

Code:

<%=FormatDateTime(oRs.Fields.item("DateRecd"), vbShortDate)%>

If this field contains no data, how can I display it without getting an error?

View Replies View Related

Problem With Len() Function & Null Values

i've come across a real head-hurter. I'm looping through a recordset and
response.writing it's rows out with no problem except 1 field. The field
type is varchar and contains words like meeting, holiday, etc.

Problem is, I'm trying to render a "n/a" when the field is null as in
LISTING 1 below. My code isn't catching the null values. How can I test for
null values? I could swear I've successfully used the Len() test like below
successfully on similiar null varchar fields, but perhaps not.

Any ideas?

LISTING 1:

If Len(objRS(7)) < 1 Then
xTeamName= "n/a" ' this is problem line
Else
xTeamName = objRS(7)
End If

View Replies View Related

Why Paramater Values Are NULL When Page Is In EXCEL Format?

I have 3 asp pages. The first page (login.asp) takes the parameter values(userid, groupid, locationid and deptid) based on the login (validate using a table). On submit the page params.asp is executed. Here the user has to select the filtering.

Then the page results.asp is executed. When the result is in excel format only the parameter value p_attdate (p_attdate is the value on page params.asp) contains a value.

The value of p_gid(p_gid is a session variable) remain null. BUT when the output is in html format, the parameter value p_gid return the correct value. I want the output to be in excel format but the sql statement is returning the wrong result. Only p_attdate is returning the correct value. I think because p_attdate is a form variable.

Can anyone tell me why value of p_gid is always null when the output is in excel format???

View Replies View Related

Filter The Result And Display The Final Result

it is possible to compare the result of 2 recordset and
filter those similiar results,and display only the remaining results

View Replies View Related

Sum Result

I have a database setup and the results come out as

Product 1: Shorts
Size: M
Price: £2.99

Product 2: T-Shirt
Size: XL
Price: £3.99

Total: "TOTAL HERE"

What I need to do is total these up

The Cells are called:
Product
Size
Price

View Replies View Related

Result Set

I want to do some checking for my table for eg:

I want to check if the table for which year then if same year then do something.....

View Replies View Related

Calculation Result

Does anyone know how i can make

[code]
Dim excl

excl = rs("price") / 1.175
[code]

return only a 2 decimal place number

View Replies View Related

Top 10 Search Result

i want to have a search application wherein it returns a results page and arranged based on the most relevant word. can you please give me an idea with this or point me to any info.

View Replies View Related

Assigning The Result

How can I assign the result of a SQL-query to an ASP variable? This is what I have so far but it doesn't seem to work. Code:

dim strSQL
strSQL="select TestID FROM TestID_Description WHERE CL1='" &Request.Form("CL1")& "' AND CL2='" &Request.Form("CL2")& "'AND CL3='" &Request.Form("CL3")& "'AND CL4='" &Request.Form("CL4")& "'AND CL5='" &Request.Form("CL5")& "'AND CL6='" &Request.Form("CL6")& "'"
objRS("TC_Number")=objConn.execute(strSQL)

Maybe it's interesting to know that TC_Number is the primary key of the table.

View Replies View Related

Detect Result

The delete method of the FileSystemObject.FileObject does not return a result. If permissions disallow deletion, it will not raise an error. Conversely, if the delete method does succeed, a call to FileExists directly afterward may return true because the system hasn't updated yet (or whatever actually goes on). Is there any way to accurately check the result of a deletion?

View Replies View Related

ASP Result Page

why my results page is not coming back with anything?? I have set up a simple form in dreamweaver, it connects to a access database and IIS web server. When i test the recordset it works ok (returning the desired affect).

It uses a post method therefore the recordset uses form variable. But when i preview in browser(either IE or Firefox or Netscape) and enter text the browser comes back with something like the page does not exist or can't be found? It's not the recordset and result page so is there something else i'm missing?

View Replies View Related

Joining 2 Result Sets

i want to left join result sets together coming from two different databases based on a common field storenumber. They are separate databases as per management design.

Basically it should match rows based on the store number, those who don't have matches should display only the storenumber and no other info like: Code:

Store Q1 Q2 Q3 Q4
1 x x x x
2 x x x x
3 <--No data in 3, just display storenumber
4 x x x x

View Replies View Related

Keep Track Of The Query Result

I have a search form that display result based on the user search criteria.
the result displayed through paging when the user select a specific product description to look at then go back
to the search result the user will lose it's place.

my question is:
How can I let the user search the database for all the products that I have then select a specific product to read the description of it
then be able to go back to the original search. I have the first part working fine but to be able to go back to the original search that I need your help. Code:

View Replies View Related

Refreshing Page With Result Set

My web page is calling a result set that is displayed on the page. If I click refresh, it pops a warning saying, "The page cannot be refreshed without resending the information." I then have to click RETRY in order to refresh the page.

Is there anyway to automatically perform that action so that my users will not have to be prompted?

View Replies View Related

Search Result By Relevancy

i am trying to make a search/result page(using asp, dreamweaver,iis,winnt,miscrosoft access database) wherein the results page brings up the most relevant words or phrases and not in alphabetical order.

View Replies View Related

How To Deal With Big Query Result?

In my search application, user can type a number to search. I use LIKE in my
query.

If a query result generates over 10,000 recordsets, it may several minutes
to run.

Is there a way to only query certain recordset at a time?

I found in SQL you can use "TOP". Like select TOP 100.... it will return
only top 100 records.

Is there a way to query may be from 101 to 200 then from 201 to 300...

My query is like: Select * from Product where PartNumber like '%" PartNumber
"%'

Any idea is welcome.

View Replies View Related

Execute Asp From Database Result?

I'm working on a application where I've created a couple of Sub routines I call them from my ASP page.

But I would love to put the hole HTML including my "call showOffers" in my database and when I get the result set do I want to execute the "call showOffer". Is this possible?

View Replies View Related

Asp Script For Forms Result

I am struggling to come up with a way to save a forms' results, to a text file, but the name of the text file comes from the input of a user name on another forms...

ex. john doe enters pg1 & enters his name in a space on the page (form) . this links him to the next page, pge_form. I need the results of page_form to be named johndoe.txt

View Replies View Related

Optimizing - Large Db Result Set

when creating a webpage that must display a large result set, the page load time is unbearable. Unfortunately, there are a lot of records and they all need to be displayed, at once, on this one page.

To reduce file size, I have minimized the amount of HTML tags used to dislay these records. My only other idea is to write javascript matrix to hold the data, and then to use dom to create the page on the client side. Are there any tried and true ways of speeding up page loads?

View Replies View Related

Database Result Error

We have completed a clean install of Microsoft Server 2003
on our Web Server and are using frontpage 2002 for our web
site. On our asp page (www.dgf.k12.mn.us/contactus.asp) we
are getting the following error:

Database Results Error
Description: Syntax error in query. Incomplete query
clause.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine
www.dgf.k12.mn.us/contactus.asp

We cannot find any kind of documentation on this error.
Has anyone else seen this kind of error, and know how to
fix it?

View Replies View Related

Result To Multiple Pages

This is my first post to this newsgroup, I have question regarding showing resultset to html. Let's say I have in resultset 200 rows, and I'd like to show on each page 10 of them, so it could look like for example google search results:

Results 1 - 10 of about 17,700 for "asp search engine" [results]
Result Page: [1] 2 3 4 5 6 7 8 9 10 Next

Is there some helpful script/guide?

View Replies View Related

Count The Result In A Record Set.

I have a question about looping in a recordset. I'm actually showing some actualities in a page, based on a start and end date. All is working well. My problem is that I put a <hr> between each news, but I don't want a <hr> at the end of the last news. I would like to count how many results I have in my recordset so I would be able not to show the last <hr>.

View Replies View Related

Percentage On Search Result

i'm sure there're no post about this in asp forum.Any idea how do I show the percentage number for the matching result.

example :

Your search for xxxx :

1. item 1 98%
2. item 2 86%
3. item 3 75%

View Replies View Related

Realtime Result Checking

i have a command object that executes an insert for a billing system.i also have a recordset object that creates a cursor to check. the credits status before. Another to check the credits status after.however, both appear to be the same when i do a response.write .How could i check the credits before and the credits after online and realtime ?

View Replies View Related

Return A Cgi Script Result

How can I have ASP call a cgi script, via Form-GET, and then display the results in the same ASP page?

View Replies View Related

SQL Result To Excel File.

I have a SQL query to generate the report for me in SQL Query Analyzer. I would like to create an ASP page that has a link, and when people click on that link, it will prompt a download message (open or save) in Excel format. Is there any codes can do this?

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved