iterating through a multi-value parameter

is there a way to loop through a multi-value parameter and retrieve contents
of all selected values to dislpay on a report. currently, i can reference
individual parameter values by using Parameters!PARAMETER.Value(0...).
However with dynamic parameter values generated from a query, this becomes a
pain
regardsnitz
Create a text box and put in the following:
=Join(Parameters!MyParam.Label, ", ")
This should grab all of the parameters selected and display them in your
textbox. You also have to add the parameter to the query as well; but it
sounds like you have already done that. For example:
and (m.company_num IN (@.MyParam))
Hope this helps.
Rob Cuscaden
"nitz" wrote:
> is there a way to loop through a multi-value parameter and retrieve contents
> of all selected values to dislpay on a report. currently, i can reference
> individual parameter values by using Parameters!PARAMETER.Value(0...).
> However with dynamic parameter values generated from a query, this becomes a
> pain
> regards
>|||duh...thank you very much..i really need to review what text functions are
available..
since i have you on the line ;)..is there a way to loop through the
parameters collections grab paramaters and their values and output to a text
box...it would be nice to have reusable bit of code for every report.
instead of this
= "DIVISION: " & Parameters!DIVISION.Value & " BOUTIQUES: " &
JOIN(Parameters!REPS.Label, ", ") & VbCrLf & etc
thanks again
"Rob" wrote:
> nitz
> Create a text box and put in the following:
> =Join(Parameters!MyParam.Label, ", ")
> This should grab all of the parameters selected and display them in your
> textbox. You also have to add the parameter to the query as well; but it
> sounds like you have already done that. For example:
> and (m.company_num IN (@.MyParam))
> Hope this helps.
> Rob Cuscaden
> "nitz" wrote:
> > is there a way to loop through a multi-value parameter and retrieve contents
> > of all selected values to dislpay on a report. currently, i can reference
> > individual parameter values by using Parameters!PARAMETER.Value(0...).
> > However with dynamic parameter values generated from a query, this becomes a
> > pain
> > regards
> >sql

IT WORKED!

Hello everyone,

i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.

Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?

thanks a lot.

Have you tried using Data Conversion transform...

you could use an expression like

IsNull(Column) ? ValueifNull : ValueifNotNull

See some examples here:

http://msdn2.microsoft.com/en-us/library/ms141184.aspx

|||Thanks a lot, the code and the article helped a lot.

I used an Derived Column Transformation to parse null values to zeros with the expression. The good thing is that i don't loose the mapping to the old columns. that's great.

Additionally this article helps to understand Derived Column Transformation:
http://msdn2.microsoft.com/en-us/library/ms141069.aspx

regards amigo|||

So, you got it!

That's great.

Issue with getting values from child controls in a gridview, to use for the update using a

Hi all,

I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:

<

asp:GridViewID="GridEditSettlement"runat="server"AutoGenerateColumns="False"BackColor="Navy"BorderColor="IndianRed"BorderStyle="Solid"Font-Names="Verdana"Font-Size="X-Small"DataSourceID="SqlDataSource_grid"AllowPaging="True"AllowSorting="True"ForeColor="White"DataKeyNames="legid"><Columns><asp:CommandFieldShowEditButton="True"CancelImageUrl="~/App_Graphics/quit.gif"CancelText=""EditImageUrl="~/App_Graphics/EditGrid.GIF"EditText=""UpdateImageUrl="~/App_Graphics/save.gif"UpdateText=""ButtonType="Image"/><asp:BoundFieldDataField="StartDate"HeaderText="Start Date"ReadOnly="True"/><asp:BoundFieldDataField="EndDate"HeaderText="End Date"ReadOnly="True"/><asp:BoundFieldDataField="CounterpartDealRef"HeaderText="CP Deal Ref"ReadOnly="True"/>

<asp:TemplateFieldHeaderText="Preliminary Settlement Price"><ItemTemplate><asp:LabelID=lblPreliminaryrunat=serverText='<%# Bind("PrimarySettlementPrice") %>'/></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtPrimaryPriceText='<%# Bind("PrimarySettlementPrice") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

<asp:TemplateFieldHeaderText="Agreed Settlement Price"><ItemTemplate><asp:LabelID=lblAgreedrunat=serverText='<%# Bind("AgreedSettlementPrice") %>'/></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtAgreedPriceText='<%# Bind("AgreedSettlementPrice") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

<asp:BoundFieldDataField="Volume"HeaderText="Volume"ReadOnly="True"/><asp:BoundFieldDataField="Price"HeaderText="Price"ReadOnly="True"/><asp:BoundFieldDataField="TotalVolume"HeaderText="Total Volume"ReadOnly="True"/><asp:BoundFieldDataField="InstrumentName"HeaderText="Instrument"ReadOnly="True"/><asp:BoundFieldDataField="NominalValue"HeaderText="Nominal Value"ReadOnly="True"/><asp:BoundFieldDataField="Strike"HeaderText="Strike"ReadOnly="True"/><asp:BoundFieldDataField="DeliveryDate"HeaderText="Delivery Date"ReadOnly="True"/><asp:TemplateFieldHeaderText="LegId"SortExpression="LegId"><ItemTemplate><asp:LabelID="lblLegID"runat="server"Text='<%# Bind("LegId") %>'></asp:Label></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtLegIDText='<%# Bind("LegId") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

</Columns><RowStyleBackColor="#FFFF66"ForeColor="#333333"/><EditRowStyleBackColor="#FFFF66"Font-Names="Verdana"Font-Size="X-Small"ForeColor="#333333"/><PagerStyleForeColor="White"/><AlternatingRowStyleBackColor="White"ForeColor="#333333"/></asp:GridView> <br/>

<asp:SqlDataSourceID="SqlDataSource_grid"runat="server"ConnectionString="<%$ ConnectionStrings:DealCaptureDev %>"SelectCommand="sp_get_single_deal"SelectCommandType="StoredProcedure"UpdateCommand="Update trDealLeg Set PrimarySettlementPrice=@.primarysettlement, AgreedSettlementprice=@.agreedsettlement, LastUpdate=GetDate(), LastUpdateBy=Session('userid') Where LegID=@.legid"EnableCaching="True"ConflictDetection="CompareAllValues"ProviderName="System.Data.SqlClient"><SelectParameters><asp:QueryStringParameterDefaultValue=""Name="dealnum"QueryStringField="deal"Type="String"/></SelectParameters><UpdateParameters><asp:ControlParameterControlID="txtLegId"PropertyName="Text"Name="legId"/><asp:ControlParameterControlID="txtPrimarySettlement"Name="primarysettlement"PropertyName="Text"/><asp:ControlParameterControlID="txtAgreedSettlement"Name="agreedsettlement"PropertyName="Text"/><asp:SessionParameterDefaultValue=""Name="userid"SessionField="userid"/></UpdateParameters></asp:SqlDataSource>

As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work).

So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event.

What is the best approach? Anyone come up against this issue before?

Thanks,

KB

You could try this way:

code in gridview: <asp:TemplateField HeaderText="company name" SortExpression="camcompany"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("camcompany")%>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("camcompany")%>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("camcompany")%>'></asp:Label> </ItemTemplate> </asp:TemplateField>code in sqlDataSource: <UpdateParameters> <asp:Parameter Name="camcompany" Type="String" /> ......... </UpdateParameters>

You can specify a row and access the controls within that row:

Dim lastNameAsString = selectRow.Cells(1).Text

You can access the individual cells of theGridViewRow object by using theCells property. If a cell contains other controls, you can retrieve a control from the cell by using theControls collection of the cell. You can also use theFindControl method of the cell to find the control, if the control has anID specified.

seehttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridviewrow.aspx for details.

Hope it helps.

Issue when not storing null db values.

Hello:
Because of the possible issues associated with storing null values in the
db, the development team of which I am a part opted to avoid using them
when possible. This has resulted in an interesting issue -
Because we are using surrogate GUID values as primary keys, if a foreign key
value does not point to a related record, an "empty" or "not selected" value
must be stored. To accomplish this requirement we are representing empty
records with the GUID value '{00000000-0000-0000-0000-000000000000}'. So
far so good.
But here's the catch. Because we allow the database to enforce ref
integerity, a record must be created in the referenced primary key table
that corresponds with the empty record. Any any foreign key references in
our "empty" record must also contain empty guid values, which means that
empty records must be created in those related tables, etc. etc. etc.
Now, all of this seems to be working but it can be rather tedious to
maintain all of this additional data - almost all of our tables must now
have contain an "empty" record. Has me wondering if we may have made a
wrong turn at Albaquerke.
Any thoughts? Have others encountered a similar issue? Just want to make
sure we are on the right track . . .
Thanks,
Chris> Has me wondering if we may have made a wrong turn at Albaquerke.
Yea - turn back before you get to the border.
Albeit NULLs can be a pain, NULLs are certainly appropriate for unknown or
unspecified values. Too many nulls can be an indicator of a db design
issue. You should never need to create a 'dummy' row because you are using
a non-null value as a FK. That's an easy but seriously flawed kludge.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:OaWrwqz$FHA.360@.TK2MSFTNGP09.phx.gbl...
> Hello:
> Because of the possible issues associated with storing null values in the
> db, the development team of which I am a part opted to avoid using them
> when possible. This has resulted in an interesting issue -
> Because we are using surrogate GUID values as primary keys, if a foreign
> key value does not point to a related record, an "empty" or "not selected"
> value must be stored. To accomplish this requirement we are representing
> empty records with the GUID value
> '{00000000-0000-0000-0000-000000000000}'. So far so good.
> But here's the catch. Because we allow the database to enforce ref
> integerity, a record must be created in the referenced primary key table
> that corresponds with the empty record. Any any foreign key references in
> our "empty" record must also contain empty guid values, which means that
> empty records must be created in those related tables, etc. etc. etc.
> Now, all of this seems to be working but it can be rather tedious to
> maintain all of this additional data - almost all of our tables must now
> have contain an "empty" record. Has me wondering if we may have made a
> wrong turn at Albaquerke.
> Any thoughts? Have others encountered a similar issue? Just want to make
> sure we are on the right track . . .
> Thanks,
> Chris
>
>
>|||Wow...GUID's for primary key values!!!
You better hope that you never have to match those up by hand when debugging
an application.|||Thanks for your input, Dan. Will have to consider other options.
This brings up a related issue. Generally, are "1 to 0-1" relationships
better handled with a nullable foreign key or should the "zero/one" data be
stored in a separate table so that data will only be stored if applicable?
Chris
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Ovz$G1z$FHA.3928@.tk2msftngp13.phx.gbl...
> Yea - turn back before you get to the border.
> Albeit NULLs can be a pain, NULLs are certainly appropriate for unknown or
> unspecified values. Too many nulls can be an indicator of a db design
> issue. You should never need to create a 'dummy' row because you are
> using a non-null value as a FK. That's an easy but seriously flawed
> kludge.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
> news:OaWrwqz$FHA.360@.TK2MSFTNGP09.phx.gbl...
>|||> This brings up a related issue. Generally, are "1 to 0-1" relationships
> better handled with a nullable foreign key or should the "zero/one" data
> be stored in a separate table so that data will only be stored if
> applicable?
Perhaps I don't understand your question correctly so you may want to post a
DDL example. It seems to me that one has a nullable FK then the
non-mandatory data are already in a separate table.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:ObNCj$2$FHA.356@.TK2MSFTNGP12.phx.gbl...
> Thanks for your input, Dan. Will have to consider other options.
> This brings up a related issue. Generally, are "1 to 0-1" relationships
> better handled with a nullable foreign key or should the "zero/one" data
> be stored in a separate table so that data will only be stored if
> applicable?
> Chris
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Ovz$G1z$FHA.3928@.tk2msftngp13.phx.gbl...
>|||diagram 1:
Parents
--
ParentID
ParentDescription
ChildID (nullable)
Children
--
ChildID
ChildDescription
diagram 2:
Parents
--
ParentID
ParentDescription
ParentChildren
--
ParentID
ChildID
Children
--
ChildID
ChildDescription
Dan, I guess diagram 2 would illustrate what I was trying to describe.
The first diagram contains the nullable field we were initially discussing,
but in the second diagram the parent/child relationship is stored in a
separate table - ParentChildren. An entry in this table would only be made
if necessary (therefore eliminating the need to use a null value).
Was curious as to whether this would be considered the correct way to model
a "1 to 0/1" relationship. All of the database books, I have discuss the
common relationships (1-1, 1 - many, many - many), but don't touch upon this
scenario.
Thanks!
Chris
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eSMsI73$FHA.1676@.TK2MSFTNGP09.phx.gbl...
> Perhaps I don't understand your question correctly so you may want to post
> a DDL example. It seems to me that one has a nullable FK then the
> non-mandatory data are already in a separate table.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
> news:ObNCj$2$FHA.356@.TK2MSFTNGP12.phx.gbl...
>|||"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:uQv0ys$$FHA.3864@.TK2MSFTNGP12.phx.gbl...
> diagram 1:
> Parents
> --
> ParentID
> ParentDescription
> ChildID (nullable)
>
> Children
> --
> ChildID
> ChildDescription
>
> diagram 2:
> Parents
> --
> ParentID
> ParentDescription
>
> ParentChildren
> --
> ParentID
> ChildID
>
> Children
> --
> ChildID
> ChildDescription
> Dan, I guess diagram 2 would illustrate what I was trying to describe.
> The first diagram contains the nullable field we were initially
> discussing, but in the second diagram the parent/child relationship is
> stored in a separate table - ParentChildren. An entry in this table would
> only be made if necessary (therefore eliminating the need to use a null
> value).
> Was curious as to whether this would be considered the correct way to
> model a "1 to 0/1" relationship. All of the database books, I have
> discuss the common relationships (1-1, 1 - many, many - many), but don't
> touch upon this scenario.
> Thanks!
> Chris
I've built a database for managing daycares.
I use the table structure in diagram2 to map parent/child although in my
case it's more of a contactChild table.
In the contactChild table, there is a column which identifies the
relationship between the child and the contact
(mother/father/grandfather/aunt).
So a contact can be the father of one child and the uncle of another.
In your case, if you are entering only parents, I guess it's better to keep
that information in the parent table (still using diagram2)..
I don't think that a person can be one child's father and the mother of
another.
Although in today's world, I wouldn't be surprised.|||I'll assume that these aren't real entity names and are named for your
parent/child relationship example.
Let's take a closer look at the first model:
--diagram 1:
CREATE TABLE Parents
(
ParentID int NOT NULL,
ParentDescription varchar(50) NOT NULL,
ChildID int NULL
)
CREATE TABLE Children
(
ChildID int NOT NULL,
ChildDescription varchar(50) NOT NULL
)
ALTER TABLE Parents
ADD CONSTRAINT PK_Parents
PRIMARY KEY (ParentID)
ALTER TABLE Children
ADD CONSTRAINT PK_Children
PRIMARY KEY (ChildID)
ALTER TABLE Parents
ADD CONSTRAINT FK_Parents_Children
FOREIGN KEY (ChildID)
REFERENCES Children(ChildID)
This will permit orphaned children (child with no parent). If you need
referential integrity, you could remove ChildID from the Parents entity and
add ParentID to the Children entity with as a FK. This would implement your
1-0/1 cardinality, provide referential integrity and provide effective space
physical space utilization.
--diagram 1a:
CREATE TABLE Parents
(
ParentID int NOT NULL,
ParentDescription varchar(50) NOT NULL
)
CREATE TABLE Children
(
ChildID int NOT NULL,
ChildDescription varchar(50) NOT NULL,
ParentID int NOT NULL
)
ALTER TABLE Parents
ADD CONSTRAINT PK_Parents
PRIMARY KEY (ParentID)
ALTER TABLE Children
ADD CONSTRAINT PK_Children
PRIMARY KEY (ChildID)
ALTER TABLE Children
ADD CONSTRAINT FK_Children_Parents
FOREIGN KEY (ParentID)
REFERENCES Parents(ParentID)
The second model as I understand it:
--Diagram 2:
CREATE TABLE Parents
(
ParentID int NOT NULL,
ParentDescription varchar(50) NOT NULL
)
CREATE TABLE Children
(
ChildID int NOT NULL,
ChildDescription varchar(50) NOT NULL
)
CREATE TABLE ParentChildren
(
ParentID int NOT NULL,
ChildID int NOT NULL,
)
ALTER TABLE Parents
ADD CONSTRAINT PK_Parents
PRIMARY KEY (ParentID)
ALTER TABLE Children
ADD CONSTRAINT PK_Children
PRIMARY KEY (ChildID)
ALTER TABLE ParentChildren
ADD CONSTRAINT PK_ParentChildren
PRIMARY KEY (ParentID)
ALTER TABLE ParentChildren
ADD CONSTRAINT FK_ParentChildren_Parents
FOREIGN KEY (ParentID)
REFERENCES Parents(ParentID)
ALTER TABLE ParentChildren
ADD CONSTRAINT FK_ParentChildren_Children
FOREIGN KEY (ChildID)
REFERENCES Children(ChildID)
This also permits orphaned children. The only value over diagram 1a that I
see is that you could more easily modify this schema to support a many
relationship. Normally, an associative table like ParentChildren is used to
normalize a many-to-many or when additional attributes describe the
relationship.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:uQv0ys$$FHA.3864@.TK2MSFTNGP12.phx.gbl...
> diagram 1:
> Parents
> --
> ParentID
> ParentDescription
> ChildID (nullable)
>
> Children
> --
> ChildID
> ChildDescription
>
> diagram 2:
> Parents
> --
> ParentID
> ParentDescription
>
> ParentChildren
> --
> ParentID
> ChildID
>
> Children
> --
> ChildID
> ChildDescription
> Dan, I guess diagram 2 would illustrate what I was trying to describe.
> The first diagram contains the nullable field we were initially
> discussing, but in the second diagram the parent/child relationship is
> stored in a separate table - ParentChildren. An entry in this table would
> only be made if necessary (therefore eliminating the need to use a null
> value).
> Was curious as to whether this would be considered the correct way to
> model a "1 to 0/1" relationship. All of the database books, I have
> discuss the common relationships (1-1, 1 - many, many - many), but don't
> touch upon this scenario.
> Thanks!
> Chris
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eSMsI73$FHA.1676@.TK2MSFTNGP09.phx.gbl...
>|||On Mon, 12 Dec 2005 11:39:53 -0500, ChrisB wrote:

>Hello:
>Because of the possible issues associated with storing null values in the
>db, the development team of which I am a part opted to avoid using them
>when possible. This has resulted in an interesting issue -
(snip)
Hi Chris,
In fact, there are not so many issues with null values.
There are issues with handling missing data, though. But since missing
data is a fact of life, we'll have to make do as good as we can.
There are three ways to handle missing data.
1. Use nulls. This is the special value that is defined in SQL for
exactly this reason: to represent missing data. Most of the behaviour of
null is geared towards giving you the "best" behaviour wrt missing data.
2. Use a self-chosen symbol to represent missing data. This doesn't
solve any of the issues with missing data. Instead, it makes life worse
becuase the SQL engine is NOT taught to handle whatever you use to
represent missing data as missing. You'll have to write lots of special
code to make sure that the special values is excluded whenever they need
to be excluded.
3. Use seperate tables for all values that might be missing. If a
persons name, age and sex are always known but shoe size and hair colour
might be missing, use one table to store name, ageand sex, a second
table to store shoe size and a third for haid colour. This rids you of
the nulls AND of the need to represent missing data by another special
value. It also rids you of some of the idiosyncrasies of missing data.
But you'll also find that you often need to have all the values
together, so you use outer joins to combine the tables - and then the
NULLS will be back too.
Option 2 is not a serious option. Since the problem is not the null
symbol but the missing data that this symbol represents, choosing a
different symbol to represent missing data solves nothing.
Option 1 or 3 is more a matter of taste - and of course, if performance
matters you'll want to test them both.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Dan:
Thanks very much for your detailed examples. After giving your posts some
thought, I've decided to go with the 1a design you suggested.
Chris
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23vwv40AAGHA.3928@.tk2msftngp13.phx.gbl...
> I'll assume that these aren't real entity names and are named for your
> parent/child relationship example.
> Let's take a closer look at the first model:
> --diagram 1:
> CREATE TABLE Parents
> (
> ParentID int NOT NULL,
> ParentDescription varchar(50) NOT NULL,
> ChildID int NULL
> )
> CREATE TABLE Children
> (
> ChildID int NOT NULL,
> ChildDescription varchar(50) NOT NULL
> )
> ALTER TABLE Parents
> ADD CONSTRAINT PK_Parents
> PRIMARY KEY (ParentID)
> ALTER TABLE Children
> ADD CONSTRAINT PK_Children
> PRIMARY KEY (ChildID)
> ALTER TABLE Parents
> ADD CONSTRAINT FK_Parents_Children
> FOREIGN KEY (ChildID)
> REFERENCES Children(ChildID)
> This will permit orphaned children (child with no parent). If you need
> referential integrity, you could remove ChildID from the Parents entity
> and add ParentID to the Children entity with as a FK. This would
> implement your 1-0/1 cardinality, provide referential integrity and
> provide effective space physical space utilization.
> --diagram 1a:
> CREATE TABLE Parents
> (
> ParentID int NOT NULL,
> ParentDescription varchar(50) NOT NULL
> )
> CREATE TABLE Children
> (
> ChildID int NOT NULL,
> ChildDescription varchar(50) NOT NULL,
> ParentID int NOT NULL
> )
> ALTER TABLE Parents
> ADD CONSTRAINT PK_Parents
> PRIMARY KEY (ParentID)
> ALTER TABLE Children
> ADD CONSTRAINT PK_Children
> PRIMARY KEY (ChildID)
> ALTER TABLE Children
> ADD CONSTRAINT FK_Children_Parents
> FOREIGN KEY (ParentID)
> REFERENCES Parents(ParentID)
> The second model as I understand it:
> --Diagram 2:
> CREATE TABLE Parents
> (
> ParentID int NOT NULL,
> ParentDescription varchar(50) NOT NULL
> )
> CREATE TABLE Children
> (
> ChildID int NOT NULL,
> ChildDescription varchar(50) NOT NULL
> )
> CREATE TABLE ParentChildren
> (
> ParentID int NOT NULL,
> ChildID int NOT NULL,
> )
> ALTER TABLE Parents
> ADD CONSTRAINT PK_Parents
> PRIMARY KEY (ParentID)
> ALTER TABLE Children
> ADD CONSTRAINT PK_Children
> PRIMARY KEY (ChildID)
> ALTER TABLE ParentChildren
> ADD CONSTRAINT PK_ParentChildren
> PRIMARY KEY (ParentID)
> ALTER TABLE ParentChildren
> ADD CONSTRAINT FK_ParentChildren_Parents
> FOREIGN KEY (ParentID)
> REFERENCES Parents(ParentID)
> ALTER TABLE ParentChildren
> ADD CONSTRAINT FK_ParentChildren_Children
> FOREIGN KEY (ChildID)
> REFERENCES Children(ChildID)
> This also permits orphaned children. The only value over diagram 1a that
> I see is that you could more easily modify this schema to support a many
> relationship. Normally, an associative table like ParentChildren is used
> to normalize a many-to-many or when additional attributes describe the
> relationship.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
> news:uQv0ys$$FHA.3864@.TK2MSFTNGP12.phx.gbl...
>

isql problem with null values

I am having a problem running a stored procedure using isql to launch the procedure.

My stored procedure contains a statement like:

select @.myvar = count(*) from mytable where mycolumn is null

when I run the procedure from query analyser a value is set in @.myvar showing the correct number of null values from mytable which is output to another table where I can see it.

Without making any changes, when I run the identical procedure using isql to execute the procedure @.myvar gets set to zero. The procedure cannot find the null values in mytable

I have tried setting ANSI_NULLS on and off inside the procedure but still get the same result.

Can someone please tell me what is going on, and how I might get isql to show me how many null values exist in the table?I don't seems to have anyproblem with this

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT null UNION ALL
SELECT null UNION ALL
SELECT null
GO

CREATE PROC mySproc99 @.Rows int OUTPUT AS
SELECT @.Rows = COUNT(*) FROM myTable99 WHERE Col1 IS NULL
GO

DECLARE @.Rows int
EXEC mySproc99 @.Rows OUTPUT
SELECT @.Rows

DECLARE @.cmd varchar(8000), @.sql varchar(8000)
SELECT @.sql = 'DECLARE @.Rows int EXEC mySproc99 @.Rows OUTPUT SELECT @.Rows'
SELECT @.cmd = 'osql -U <username> -P <password> -S <servername> -d Northwind -Q "' + @.sql + '"'
EXEC master..xp_cmdshell @.cmd
GO

DROP PROC mySproc99
DROP TABLE myTable99
GO|||Which SQL Server are you running? If SQL 2000, which campatibility level?|||Brett,

Thanks for your reply, I'll try out your script tomorrow morning when I get into the office. And let you know the result.

The reason I'm confused, is I set up my script using osql and it worked fine on our test server, It didn't run at all on production, then I found that it did run if I changed osql to isql, but with the quirk noted. I'm sure it is something to do with the OLE DB config, but I am not sure where to start looking for differences. Also the osql version might work if I could get ODBC set up on production, but I'm not sure how to go about doing that.

Anyway, thanks again for all your help and I'd be grateful for any further suggestions.|||I don't have tons of experience with xp_cmdshell but my results from the example script above look pretty strange:

www.sullivanstreet.us/query.jpg