Invalid Use of Null

I am getting the "Invalid use of Null" message when I execute the following code under the following conditions.

fields in SQL Server table
db_date varchar(30) --> "December 29"
db_subject varchar(200) --> "Test Subject"
db_thought text --> "This is a test to see if this works"

I execute the following code from VB6

strText = "select * from db_table where db_date = 'December 29'"
ors.open strText, dbConnect
if not ors.eof then
if not isnull(ors("db_thought")) then
txtBox1 = ors("db_thought")
end if
end if

Here is my problem. Since there is a value in db_thought the code if not isnull(ors("db_thought")) evaluates to true -- this is what I expect. However when I try to assign the ors("db_thought") to the txBox1 field I get an Invalid use of Null. What am I missing?

ThanksWhat happens if you change the if statement to:

if isnull(...) then
txtField1 = ""
else
txtField1 = ...
end if

Also, put in a msgbox before the assignment to display the value or look at it in debug mode under locals.|||Originally posted by rnealejr
What happens if you change the if statement to:

if isnull(...) then
txtField1 = ""
else
txtField1 = ...
end if

Also, put in a msgbox before the assignment to display the value or look at it in debug mode under locals.

I tired your suggestion, and I get the same results. It appears that the test to see if there is information in the field is testing to be true, but the attempt to read from the field is saying that the information is null. I have rechecked the database to see if there is valid data in the field and I see data there.

INVALID LENGTH PARAMETER PASSED....

I have the follwoing stored procedure:

ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on

declare@.s varchar(500)
declare @.i int

set @.s = ''
select @.s = @.s + EventType + ','-- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...'-- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'

set nocount off
end --procedure
GO

When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:

Invalid length parameter passed to the SUBSTRING function.

There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.

Any suggestions would be greatly appreciated.

Thanks!!On Jun 4, 12:43 pm, ansonee <anso...@.yahoo.comwrote:

Quote:

Originally Posted by

I have the follwoing stored procedure:
>
ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on
>
declare @.s varchar(500)
declare @.i int
>
set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x
>
set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:
>
Invalid length parameter passed to the SUBSTRING function.
>
There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.
>
Any suggestions would be greatly appreciated.
>
Thanks!!

increase the value of @.s from 500 to 5000 maybe and test it ?|||ansonee (ansonee@.yahoo.com) writes:

Quote:

Originally Posted by

set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

I'm afraid that this relies on undefined behaviour. It may produce what
you want today. It might not tomorrow. If you are on SQL 2000, you
will need to run a cursor. On SQL 2005 there exists an option with
XML. See SQL Server MVP Antith Sen's article on
http://www.projectdmx.com/tsql/rowconcatenate.aspx for more information.

Quote:

Originally Posted by

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too
long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:
>
Invalid length parameter passed to the SUBSTRING function.
>
There is no SUBSTRING being used anywhere in the query

No, but there is LEFT, which is just a shortcut for SUBSTRING.

More to the point, you have failed to handle the case that the query
does not find any events, and @.i is the empty string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Invalid column name for the value of a column

I am having a SQl satement as
If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
Drop table ##TEMPDelete
SET @.Name = (Select replace(@.Name,' ','_'))
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+ @.Name + ' where eid = '+ @.eid
Exec (@.SQL)
Set @. Name = (select @.Name from ##TEMPDelete)
Print AddressTemp
this results back as an error
Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
samay
please adviceI don't see where @.eid is getting valued. Assuming that it is a char
variable - I would think that you want the select statement to be:
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '''+ @.eid +''''
So that it puts quotes around the value of @.eid
--TJTODD
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:EBA43F13-84EB-42A0-9635-3AD2840C78F8@.microsoft.com...
> I am having a SQl satement as
> If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> Drop table ##TEMPDelete
> SET @.Name = (Select replace(@.Name,' ','_'))
> Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '+ @.eid
> Exec (@.SQL)
> Set @. Name = (select @.Name from ##TEMPDelete)
> Print AddressTemp
>
> this results back as an error
> Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> samay
> please advice|||Well, where are your single quotes? eid is a string, right? Try
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '''+ @.eid +''''
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:EBA43F13-84EB-42A0-9635-3AD2840C78F8@.microsoft.com...
> I am having a SQl satement as
> If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> Drop table ##TEMPDelete
> SET @.Name = (Select replace(@.Name,' ','_'))
> Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '+ @.eid
> Exec (@.SQL)
> Set @. Name = (select @.Name from ##TEMPDelete)
> Print AddressTemp
>
> this results back as an error
> Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> samay
> please advice

Invalid attempt to read when no data is present using SQLdatareader

I'm trying to determine if the record is NULL/empty or is valid from the datareader.

objReader = strCMD.ExecuteReader
objReader.Read()
if objReader.IsDBNull(0) = true then...

If NULL/Empty, display no records found. If records are found, display "1 or more records have been found". I keep getting the error "Invalid attempt to read when no data is present". I'm not sure what I am doing wrong here.Do While objReader.Read()
' your code.
Loop|||Use the HasRows property of a DataReader:

objReader = strCMD.ExecuteReader
If objReader.HasRows() Then
' Display 1 or more rows found
Else
' Display no rows found
End If

The reason your code was failing was because the "objReader.Read()" line attempts to access the next element in the DataReader, and so if it's empty then it becomes an invalid access, like trying to access one more index past the end of an array. If all you need is whether or not the DataReader contains any information, then HasRows is your easiest bet.|||And if what you need to do is access the info datareader contains, and are getting this error, and have a do while loop, what's the next thing to try?|||Look at why the SQL isn't returning anything!

Intricate SQL Statement

Hi there at the forum,
I have a table with the following structure
CREATE TABLE [dbo].[Demand] (
[ArtNr] [varchar] (20) NOT NULL ,
[Plandate] [datetime] NOT NULL ,
[Dispo_element] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AmountReq] [decimal](18, 3) NULL ,
[AmountAvail] [decimal](18, 3) NULL ,
[PlannedDelivery] [decimal](18, 3) NULL ,
[Target_Inventory] [decimal](18, 3) NULL
) ON [PRIMARY]
GO
The table contains data pertaining to supply control.
[ArtNr] designates the article number
[Plandate] shows the date of a movment
[Dispo_element] contains a code that classifies the row in the tabel as bein
g:
- Inventory
- Demand
- Delivery
[AmountReq] is the amount of a demand
[AmountAvail] is the amount available after a demand or a delivery had been
accounted for
[PlannedDelivery] is the amount that is to be delivered
[Target_Inventory] displays the missing amount in order to fulfill the
demands of a given day. Should the available amout be larger than the
demand, this column displays 0.
It is possible to have more than one delivery and more than one demand for
an articel on a any given day. Target
Data Example is
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '1.1.2005', 'inventory', 0, 100, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '2.1.2005', 'demand', 50, 50, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, 0, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, -50, 0, 50)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'supply', 0, 150, 100, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '5.1.2005', 'demand', 200, -50, 0, 50)
At the moment I show this data in a grid which means that for a day with 10
demands and 10 deliveries, 20 rows are shown.
My Question is: Is it possible to show a row per day, displaying the
consolidated data
Date Req Avail Deliv Target Type
1.1.05 0 100 0 0 inventory
2.1.05 50 50 0 0 demand
4.1.05 0 200 150 0 supply
4.1.05 100 100 0 0 demand
5.1.05 200 -100 0 -100 demand
Thank you very much for any help you might provide. I thought at first
about doing this by the means of some cursor and a temp table but the result
were just too slow. I hope
that it is possible to do this using SELECT statements without having to use
a cursor.
Best regardsYour table doesn't have a primary key! Hopefully your intention is to
fix that. Try:
SELECT artnr, plandate,
SUM(amountreq),
SUM(amountavail),
SUM(planneddelivery),
SUM(target_inventory),
dispo_element
FROM Demand
GROUP BY plandate, artnr, dispo_element
David Portas
SQL Server MVP
--

Intranet, windows authentication, Sql Server Login Failed for user (null)

Hi--

I am building an intRAnet website using windows authentication for website access and SQL Server access ( Trusted_Connection = true ).

In IIS I have these settings:

Allow Anonymous = unchecked (false)Windows Authentication = Checked (true)Digest windows = checked (true)

In my Web.Config file:

authentication="windows"impersonate="true"allowusers="*"

When I pull up the page these are my credentials:

Security.Principal.Windows: mydomain\myuserid (this is correct what it shows on my page)

Me.User.Identity: mydomain\myuserid (this is correct what it shows on my page)

Threading.currentThread.currentUser mydomain\myuserid (this is correct what it shows on my page)

So the ASP.NET page recognizes it is me and my domain. However, when i click a button to pull some data from a database I get the error message: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

My data access on SQL Server works fine. The weird thing is when I debug on my machine it pulls data fine. but when I copy the files to the windows 2003 server it doesn't work.

Do i have to do something with delegation?

some guys and i did some research and we think that kerberos delegation is not enabled on our domain in active directory.

When i take off digest, and select basic authentication the credentials pass...but i don't want that.

Intersection of N sets

Imagine a table that enumerates membership of items to some set:
create table sets
(
setId int not null,
itemId int not null
);
some data:
set 1 = {1,3,5,7,9}
set 2 = {1,2,3,4,5}
set 3 = {4,5,6,7}
translated to this model:
setId, itemId
1,1
1,3
1,5
1,7
1,9
2,1
2,2
2,3
2,4
2,5
3,4
3,5
3,6
3,7
Consider another table containing an enumeration of sets to calculate the
intersection of:
create table setIntersection
(
setId int not null
);
If the setIntersection table contains the records {1,2,3}, I'd like to calcu
late
the intersection of the items contained in the sets 1, 2 and 3.
In the example above, this would be the {5}.
While this is trivial to do in a typical imperative fashion (looping inside
an SP), I'm wondering if it's possible to perform this operation in a single
query, perhaps using an CTE.Essentially, it would be a join on the the tables, each representing a set.
SELECT t1.itemid -- or t2.itemid or t3.itemid
FROM tbl t1,
tbl t2,
tbl t3
WHERE t1.itemid = t2.itemid
AND t2.itemid = t3.itemid
AND t1.setid = 1
AND t2.setid = 2
AND t3.setid = 3 ;
Anith|||Taras Tielkes (taras.tielkes@.gmail.com) writes:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
>...
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to
> calculate the intersection of the items contained in the sets 1, 2 and
> 3. In the example above, this would be the {5}.
SELECT itemId
FROM sets s
WHERE EXISTS (SELECT *
FROM setIntersection sI
WHERE sI.setId = s.setId)
GROUP BY itemId
HAVING COUNT(*) = (SELECT COUNT(*) FROM setIntersection)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ah, but I meant to ask the question in a generic way: "how can I writer a
query that will perform the required operation for an arbitrary number of
sets in the example model. Say 100 defined sets :-)

> Essentially, it would be a join on the the tables, each representing a
> set.
> SELECT t1.itemid -- or t2.itemid or t3.itemid
> FROM tbl t1,
> tbl t2,
> tbl t3
> WHERE t1.itemid = t2.itemid
> AND t2.itemid = t3.itemid
> AND t1.setid = 1
> AND t2.setid = 2
> AND t3.setid = 3 ;|||In that case, Erland's response should help. You may also want to search the
archives for "relational division" to find some related examples.
Anith|||Taras Tielkes wrote:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
> translated to this model:
> setId, itemId
> 1,1
> 1,3
> 1,5
> 1,7
> 1,9
> 2,1
> 2,2
> 2,3
> 2,4
> 2,5
> 3,4
> 3,5
> 3,6
> 3,7
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to cal
culate
> the intersection of the items contained in the sets 1, 2 and 3.
> In the example above, this would be the {5}.
> While this is trivial to do in a typical imperative fashion (looping insid
e
> an SP), I'm wondering if it's possible to perform this operation in a sing
le
> query, perhaps using an CTE.
It's relational division:
sets/setIntersection