|
 |
asp_databases thread: How do you handle the situation when not all fields are returned
Message #1 by Gregory_Griffiths@c... on Mon, 29 Jan 2001 11:55:07 +0000
|
|
Dear All,
I currently have the following code snippet :
' create the SQLString
SQLString="SELECT table1.field1 PRODNAME,table2.field2 FROM table1,
table2 WHERE table1.key=table2.key AND table1.key="&myvar
' execute the SQL
set results = dbConn.execute(SQLString)
' assign the results to variables
if (isNull(results("PRODNAME"))) or (results("PRODNAME")=" ") then
' do nothing
else
PRODNAME=results("PRODNAME")
end if
Believing that this would cope with the fact that there may not be a
value in every field returned in results. However, this gives me the
following error message :
%FDF-1.2 1 0 obj << /FDF << /Fields [
ADODB.Field error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted; the
operation requested by the application requires a current record.
But this only occurs if one or more of the fields is not returned by
the query.
Any ideas about how to get around this ?
Message #2 by Imar Spaanjaars <Imar@S...> on Mon, 29 Jan 2001 14:04:14 +0100
|
|
Yes, first check if your recordset contains a record at all. If it does,
only then check for the fields. You can' t check for valid field values, if
there is no record at all:
set results = dbConn.execute(SQLString)
' assign the results to variables
if not results.EOF then ' there is at least one record
if (isNull(results("PRODNAME"))) or (results("PRODNAME")=" ") then
' do nothing
else
PRODNAME=results("PRODNAME")
end if
else
' No records at all in your recordset
end if
HtH
Imar
At 11:55 AM 1/29/2001 +0000, you wrote:
>Dear All,
> I currently have the following code snippet :
>
>' create the SQLString
>SQLString="SELECT table1.field1 PRODNAME,table2.field2 FROM table1,
>table2 WHERE table1.key=table2.key AND table1.key="&myvar
>
>' execute the SQL
>set results = dbConn.execute(SQLString)
>
>' assign the results to variables
>if (isNull(results("PRODNAME"))) or (results("PRODNAME")=" ") then
> ' do nothing
>else
> PRODNAME=results("PRODNAME")
>end if
>
>Believing that this would cope with the fact that there may not be a
>value in every field returned in results. However, this gives me the
>following error message :
>
>%FDF-1.2 1 0 obj << /FDF << /Fields [
>ADODB.Field error '800a0bcd'
>
>Either BOF or EOF is True, or the current record has been deleted; the
>operation requested by the application requires a current record.
>
>But this only occurs if one or more of the fields is not returned by
>the query.
>
>Any ideas about how to get around this ?
Message #3 by "Dallas Martin" <dmartin@z...> on Mon, 29 Jan 2001 09:08:22 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0024_01C089D3.07504620
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You can use an OUTER JOIN
"SELECT t1.field1 PRODNAME,
t2.field2
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.key =3D t2.key
WHERE t1.key =3D " & myvar
Change the word LEFT to either FULL or RIGHT
to determine the rows that you want.
LEFT returns all rows in t1 with NULLS in t2
RIGHT returns NULLS in t1 with all rows in t2
FULL returns all rows in t1 and t2 with either
NULLS in t1 or t2.
Now don't forget to use the VBScript IsNull() function
to test the columns before displaying. ie.
If IsNull(rs("field2")) then response.write("None") else .....
By the way is myvar string or numeric?
Cheers,
Dallas Martin
----- Original Message -----
From: <Gregory_Griffiths@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, January 29, 2001 6:55 AM
Subject: [asp_databases] How do you handle the situation when not all
fields are returned
> Dear All,
> I currently have the following code snippet :
>
> ' create the SQLString
> SQLString=3D"SELECT table1.field1 PRODNAME,table2.field2 FROM table1,
> table2 WHERE table1.key=3Dtable2.key AND table1.key=3D"&myvar
>
> ' execute the SQL
> set results =3D dbConn.execute(SQLString)
>
> ' assign the results to variables
> if (isNull(results("PRODNAME"))) or (results("PRODNAME")=3D" ") then
> ' do nothing
> else
> PRODNAME=3Dresults("PRODNAME")
> end if
>
> Believing that this would cope with the fact that there may not be a
> value in every field returned in results. However, this gives me the
> following error message :
>
> %FDF-1.2 1 0 obj << /FDF << /Fields [
> ADODB.Field error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been deleted; the
> operation requested by the application requires a current record.
>
> But this only occurs if one or more of the fields is not returned by
> the query.
>
> Any ideas about how to get around this ?
>
>
> ---
> FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
> IN YOUR INBOX!
> Get the latest and best HTML, XML, and JavaScript tips, tools, and
> developments from the experts. Sign up for one or more of EarthWeb's
> FREE IT newsletters at http://www.earthweb.com today!
$subst('Email.Unsub')
>
Message #4 by "Wally Burfine" <oopconsultant@h...> on Mon, 29 Jan 2001 15:37:33 -0000
|
|
check for results.eof first
>From: Gregory_Griffiths@c...
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] How do you handle the situation when not all
>fields are returned
>Date: Mon, 29 Jan 2001 11:55:07 +0000
>
>Dear All,
> I currently have the following code snippet :
>
>' create the SQLString
>SQLString="SELECT table1.field1 PRODNAME,table2.field2 FROM table1,
>table2 WHERE table1.key=table2.key AND table1.key="&myvar
>
>' execute the SQL
>set results = dbConn.execute(SQLString)
>
>' assign the results to variables
>if (isNull(results("PRODNAME"))) or (results("PRODNAME")=" ") then
> ' do nothing
>else
> PRODNAME=results("PRODNAME")
>end if
>
>Believing that this would cope with the fact that there may not be a
>value in every field returned in results. However, this gives me the
>following error message :
>
>%FDF-1.2 1 0 obj << /FDF << /Fields [
>ADODB.Field error '800a0bcd'
>
>Either BOF or EOF is True, or the current record has been deleted; the
>operation requested by the application requires a current record.
>
>But this only occurs if one or more of the fields is not returned by
>the query.
>
>Any ideas about how to get around this ?
>
>
>---
>FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
>IN YOUR INBOX!
>Get the latest and best HTML, XML, and JavaScript tips, tools, and
>developments from the experts. Sign up for one or more of EarthWeb's
>FREE IT newsletters at http://www.earthweb.com today!
>$subst('Email.Unsub')
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
Message #5 by Gregory_Griffiths@c... on Mon, 29 Jan 2001 16:08:51 +0000
|
|
Problem solved, I just needed to do an outer join on some of the tables
so that I got the data that was there returned, thanks for the other
suggestions, many of them have already been included.
> -----Original Message-----
> From: griffiths, gregory d. /here
> Sent: 29 January 2001 11:55
> To: asp_databases@p...
> Cc: griffiths, gregory d. /here
> Subject: [asp_databases] How do you handle the situation when not all
> fields are returned
>
>
> Dear All,
> I currently have the following code snippet :
>
> ' create the SQLString
> SQLString="SELECT table1.field1 PRODNAME,table2.field2 FROM table1,
> table2 WHERE table1.key=table2.key AND table1.key="&myvar
>
> ' execute the SQL
> set results = dbConn.execute(SQLString)
>
> ' assign the results to variables
> if (isNull(results("PRODNAME"))) or (results("PRODNAME")=" ") then
> ' do nothing
> else
> PRODNAME=results("PRODNAME")
> end if
>
> Believing that this would cope with the fact that there may not be a
> value in every field returned in results. However, this gives me the
> following error message :
>
> %FDF-1.2 1 0 obj << /FDF << /Fields [
> ADODB.Field error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been
> deleted; the
> operation requested by the application requires a current record.
>
> But this only occurs if one or more of the fields is not returned by
> the query.
>
> Any ideas about how to get around this ?
>
>
>
>
Message #6 by "Naseem, Kashif (Birmingham)" <kashif.naseem@c...> on Mon, 29 Jan 2001 15:35:24 -0000
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C08A09.1966B8B8
Content-Type: text/plain;
charset="iso-8859-1"
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: 29 January 2001 14:08
To: ASP Databases
Subject: [asp_databases] Re: How do you handle the situation when not all
fields are returned
You can use an OUTER JOIN
"SELECT t1.field1 PRODNAME,
t2.field2
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.key = t2.key
WHERE t1.key = " & myvar
Change the word LEFT to either FULL or RIGHT
to determine the rows that you want.
LEFT returns all rows in t1 with NULLS in t2
RIGHT returns NULLS in t1 with all rows in t2
FULL returns all rows in t1 and t2 with either
NULLS in t1 or t2.
Now don't forget to use the VBScript IsNull() function
to test the columns before displaying. ie.
If IsNull(rs("field2")) then response.write("None") else .....
By the way is myvar string or numeric?
Cheers,
Dallas Martin
----- Original Message -----
From: < <mailto:Gregory_Griffiths@c...>
Gregory_Griffiths@c...>
To: "ASP Databases" < <mailto:asp_databases@p...>
asp_databases@p...>
Sent: Monday, January 29, 2001 6:55 AM
Subject: [asp_databases] How do you handle the situation when not all fields
are returned
> Dear All,
> I currently have the following code snippet :
>
> ' create the SQLString
> SQLString="SELECT table1.field1 PRODNAME,table2.field2 FROM table1,
> table2 WHERE table1.key=table2.key AND table1.key="&myvar
>
> ' execute the SQL
> set results = dbConn.execute(SQLString)
>
> ' assign the results to variables
> if (isNull(results("PRODNAME"))) or (results("PRODNAME")=" ") then
> ' do nothing
> else
> PRODNAME=results("PRODNAME")
> end if
>
> Believing that this would cope with the fact that there may not be a
> value in every field returned in results. However, this gives me the
> following error message :
>
> %FDF-1.2 1 0 obj << /FDF << /Fields [
> ADODB.Field error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been deleted; the
> operation requested by the application requires a current record.
>
> But this only occurs if one or more of the fields is not returned by
> the query.
>
> Any ideas about how to get around this ?
>
>
> ---
> FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
> IN YOUR INBOX!
> Get the latest and best HTML, XML, and JavaScript tips, tools, and
> developments from the experts. Sign up for one or more of EarthWeb's
> FREE IT newsletters at <http://www.earthweb.com> http://www.earthweb.com
today!
<mailto:dmartin@z...> dmartin@z...
<mailto:$subst('Email.Unsub')>
$subst('Email.Unsub')
> ---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips,
tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
Message #7 by Robert Callahan <doghead@e...> on Mon, 29 Jan 2001 21:45:51 -0500
|
|
I tried submitting this question to the JS group and got no love.
How can I substitute data passed via a hidden form field from another
page into an array? I can't seem to make it happen.
I'm taking a dynamic SQL string composed on one page and substituting
the SQL string into an array that sets up a recordset connection on
another page. So I have a hidden form with a <% =strSQL %> value given
the an action that names
the result page.
On the result page, which needs to be scripted in JS, I declare the var
strSQL = Request.Form("searchSQL") or string(Request.Form("searchSQL"))
or some variation on the general theme, using querystring or whatever.
Then I try substituting this variable into the array, which goes
something like this: new Recordset("RSsearch", "dsn", "userID",
"password", strSQL, etc.).
The SQL string appears to be okay, but it may not be passing properly
into the array. In some formulations I get no-SQL results (with an
error saying there was no SELECT statement, because the query didn't
pass), but otherwise I just get an error message referring to the
support scripts, and the support script cited is the one that opens the
recordset. It's one of those "arguments are out of bounds or in
conflict with one another" things, when I do seem to get the SQL string
to pass. The SQL string will sometimes have a bunch of LIKE
'%pidgeons%' OR LIKE '%sparrows%' kinds of things, because the whole
point of this is to search each word of query strings across database
fields, in Access, which has no CONTAINS facility. But the SQL looks
okay taken on its own. But I can't make this connection array work in a
normal way.
Help? Ideas? Experience with stuff like this?
|
|
|
View Cart
