Can we insert NULL in place of foreign key.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now I
tried out turning "Allow Null" on for the column "DepartmentID" in the
"Student" table. As soon as I did this, I was able to insert a row with
"DepartmentID" as <NULL>.
Now isn't this wrong? Or is it that the database overrides the checking of
the foreign key against the table "Department" when I check the "Allow Null"
option ON?
It would be really helpful if someone could elaborate on this.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriHi
Yes, referencing table may accept NULL's. Like people that don't have
children.
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:C5A2D39D-5C28-4661-B68B-B2CA6BA48CDF@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow
> Null"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Aayush Puri wrote:
> I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow Nul
l"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
SQL's foreign key constraints work like check constraints.
Specifically, values are permitted as long as the constraint isn't
violated. A null comparison returns an UNKNOWN result rather than a
FALSE one and UNKNOWN isn't regarded as a violation of the constraint.
So the behaviour you mentioned is "right" according to the SQL
standard. Whether it makes good logical sense is a different matter.
I'd suggest that you either disallow nulls or that you create some
default value in the Depatrtments table to represent the case that you
would otherwise have used a null for - a "Not Applicable" department
for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

can we increment the column --autimaticallyis there any possiblity with tiggers

hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in prod
uction code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-cre
ating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satish
It is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegr oups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>
|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish
sql

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

Can this been done?

Hi all,
I have a table:
CREATE TABLE [dbo].[StockStatus] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[StockID] [int] NOT NULL ,
[CompanyID] [int] NULL ,
[ContactID] [int] NULL ,
[StockStatusID] [int] NOT NULL ,
[BY] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
I use this table for the current status and for history about the status
changes.
Everything so good so far.
BUT now the way of working has changed. There is a new status.
It is now also possible to go back in status.
I give an example:
Status 1 = create
Status2(new!) = reject
Status3 = assign.
Normaly status changed from (1)create to (3)assign.
But now after assign you can choose reject.
In an overview the user wants to see all the stock that have the status
assign (and now also reject)
but not stocks that are already assigned...:s
Enless, they are rejected ofcourse .. :s
The assigning and reject can occure multiple times in the process...
I am try to make a query this whole afternoon but without any luck :-(
Can this be done or do I need to split it into multiple tables or...'
Any advise is welcome...
TiaCan you give us some sample data (in the form of INSERT statements) and
sample output? I'm certain this is possible... Also, are you using SQL
Server 2000 or 2005? Some of the 2005 features may be helpful here.
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Goofy" <nomail@.goofy.com> wrote in message
news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I have a table:
> CREATE TABLE [dbo].[StockStatus] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [StockID] [int] NOT NULL ,
> [CompanyID] [int] NULL ,
> [ContactID] [int] NULL ,
> [StockStatusID] [int] NOT NULL ,
> [BY] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> I use this table for the current status and for history about the status
> changes.
> Everything so good so far.
> BUT now the way of working has changed. There is a new status.
> It is now also possible to go back in status.
> I give an example:
> Status 1 = create
> Status2(new!) = reject
> Status3 = assign.
> Normaly status changed from (1)create to (3)assign.
> But now after assign you can choose reject.
> In an overview the user wants to see all the stock that have the status
> assign (and now also reject)
> but not stocks that are already assigned...:s
> Enless, they are rejected ofcourse .. :s
> The assigning and reject can occure multiple times in the process...
> I am try to make a query this whole afternoon but without any luck :-(
> Can this be done or do I need to split it into multiple tables or...'
> Any advise is welcome...
> Tia
>
>|||I like to use binary encoding in cases where states need to be tracked.
Works like this:
status & 1 will be 1 if 1 bit is turned on, 0 otherwise
status & 2 will be 2 if 2 bit is turned on, 0 otherwise
status & 4 will be 4 if 4 bit is turned on, 0 otherwise
status & 8 will be 8 if 8 bit is turned on, 0 otherwise
...
So now you can have multiple states at one time: status 5 is the
combination of status 1 and status 4
You can turn on and off status bits by using bit wise operators, or simple
addition/subtraction
set status = 1 + 4 will give a status of 5
TheSQLGuru
President
Indicium Resources, Inc.
"Goofy" <nomail@.goofy.com> wrote in message
news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I have a table:
> CREATE TABLE [dbo].[StockStatus] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [StockID] [int] NOT NULL ,
> [CompanyID] [int] NULL ,
> [ContactID] [int] NULL ,
> [StockStatusID] [int] NOT NULL ,
> [BY] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> I use this table for the current status and for history about the status
> changes.
> Everything so good so far.
> BUT now the way of working has changed. There is a new status.
> It is now also possible to go back in status.
> I give an example:
> Status 1 = create
> Status2(new!) = reject
> Status3 = assign.
> Normaly status changed from (1)create to (3)assign.
> But now after assign you can choose reject.
> In an overview the user wants to see all the stock that have the status
> assign (and now also reject)
> but not stocks that are already assigned...:s
> Enless, they are rejected ofcourse .. :s
> The assigning and reject can occure multiple times in the process...
> I am try to make a query this whole afternoon but without any luck :-(
> Can this be done or do I need to split it into multiple tables or...'
> Any advise is welcome...
> Tia
>
>|||Yes, sure I can, thank you.
I am using SQL2000.
A simple version of the table:
CREATE TABLE [dbo].[StockStatus] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[StockID] [int] NOT NULL ,
[StockStatusID] [int] NOT NULL ,
[CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(22, 2, '27-8-2007 15:05:46')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(22, 4, '27-8-2007 15:10:11')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(23, 3, '27-8-2007 16:15:51')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(23, 4, '27-8-2007 15:10:11')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(23, 2, '27-8-2007 15:15:46')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(23, 4, '27-8-2007 16:35:46')
What I try to get back is
StockStatus of stockid 22 is 3 (Latest time)
StockStatus of stockid 23 is 4 (Latest time)
I want only back the statuses of 2 and 3.
So in this example only stockID 22.
The table is filled with lots more records of course and statuses...
But if a status (with higher date exists in a higher status(then 2 or 3)) I
dont want them in the result.
I hope you can help me.
thnx
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
news:6B5AC7C1-B1C3-46A9-AE19-4325945A44FC@.microsoft.com...
> Can you give us some sample data (in the form of INSERT statements) and
> sample output? I'm certain this is possible... Also, are you using SQL
> Server 2000 or 2005? Some of the 2005 features may be helpful here.
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Goofy" <nomail@.goofy.com> wrote in message
> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the status
>> changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the status
>> assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>|||Hi TheSQLGuru,
Thanks for your input.
But a stock can have only have 1 status at the same time.
It is created, assigned or reject or used, etc.
thanks anyway.
Grz,
"TheSQLGuru" <kgboles@.earthlink.net> schreef in bericht
news:ebQBCAM6HHA.980@.TK2MSFTNGP06.phx.gbl...
>I like to use binary encoding in cases where states need to be tracked.
>Works like this:
> status & 1 will be 1 if 1 bit is turned on, 0 otherwise
> status & 2 will be 2 if 2 bit is turned on, 0 otherwise
> status & 4 will be 4 if 4 bit is turned on, 0 otherwise
> status & 8 will be 8 if 8 bit is turned on, 0 otherwise
> ...
> So now you can have multiple states at one time: status 5 is the
> combination of status 1 and status 4
> You can turn on and off status bits by using bit wise operators, or simple
> addition/subtraction
> set status = 1 + 4 will give a status of 5
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Goofy" <nomail@.goofy.com> wrote in message
> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the status
>> changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the status
>> assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>|||How about:
SELECT *
FROM
(
SELECT *
FROM StockStatus ss
WHERE
ss.CreateDate = (
SELECT MAX(CreateDate)
FROM StockStatus ss1
WHERE ss1.StockId = ss.StockId
)
) x
WHERE
x.StockStatusId IN (2,3)
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Goofy" <nomail@.goofy.com> wrote in message
news:OqaY4FM6HHA.1208@.TK2MSFTNGP05.phx.gbl...
> Yes, sure I can, thank you.
> I am using SQL2000.
> A simple version of the table:
> CREATE TABLE [dbo].[StockStatus] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [StockID] [int] NOT NULL ,
> [StockStatusID] [int] NOT NULL ,
> [CreateDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(22, 2, '27-8-2007 15:05:46')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(22, 4, '27-8-2007 15:10:11')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(23, 3, '27-8-2007 16:15:51')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(23, 4, '27-8-2007 15:10:11')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(23, 2, '27-8-2007 15:15:46')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(23, 4, '27-8-2007 16:35:46')
> What I try to get back is
> StockStatus of stockid 22 is 3 (Latest time)
> StockStatus of stockid 23 is 4 (Latest time)
> I want only back the statuses of 2 and 3.
> So in this example only stockID 22.
> The table is filled with lots more records of course and statuses...
> But if a status (with higher date exists in a higher status(then 2 or 3))
> I dont want them in the result.
> I hope you can help me.
> thnx
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
> news:6B5AC7C1-B1C3-46A9-AE19-4325945A44FC@.microsoft.com...
>> Can you give us some sample data (in the form of INSERT statements) and
>> sample output? I'm certain this is possible... Also, are you using SQL
>> Server 2000 or 2005? Some of the 2005 features may be helpful here.
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Goofy" <nomail@.goofy.com> wrote in message
>> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the status
>> changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the status
>> assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>>
>|||That looks great...thanks alot
And performance wice?
Is did a good solution you think?
in this table there will be at least 100,000 records...
thank you
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
news:6CBB3FC9-3022-41EC-AFF3-3FCF421CB809@.microsoft.com...
> How about:
>
> SELECT *
> FROM
> (
> SELECT *
> FROM StockStatus ss
> WHERE
> ss.CreateDate => (
> SELECT MAX(CreateDate)
> FROM StockStatus ss1
> WHERE ss1.StockId = ss.StockId
> )
> ) x
> WHERE
> x.StockStatusId IN (2,3)
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Goofy" <nomail@.goofy.com> wrote in message
> news:OqaY4FM6HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Yes, sure I can, thank you.
>> I am using SQL2000.
>> A simple version of the table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(22, 2, '27-8-2007 15:05:46')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(22, 4, '27-8-2007 15:10:11')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 3, '27-8-2007 16:15:51')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 4, '27-8-2007 15:10:11')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 2, '27-8-2007 15:15:46')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 4, '27-8-2007 16:35:46')
>> What I try to get back is
>> StockStatus of stockid 22 is 3 (Latest time)
>> StockStatus of stockid 23 is 4 (Latest time)
>> I want only back the statuses of 2 and 3.
>> So in this example only stockID 22.
>> The table is filled with lots more records of course and statuses...
>> But if a status (with higher date exists in a higher status(then 2 or 3))
>> I dont want them in the result.
>> I hope you can help me.
>> thnx
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
>> news:6B5AC7C1-B1C3-46A9-AE19-4325945A44FC@.microsoft.com...
>> Can you give us some sample data (in the form of INSERT statements) and
>> sample output? I'm certain this is possible... Also, are you using SQL
>> Server 2000 or 2005? Some of the 2005 features may be helpful here.
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Goofy" <nomail@.goofy.com> wrote in message
>> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the
>> status changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the status
>> assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>>
>>
>|||Depending on the indexes in place, it should be pretty good. If you include
all of the columns necessary for the query (in order to cover it), the query
processor can use an index on (StockId, CreateDate DESC) to very efficiently
handle this kind of query.
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Goofy" <nomail@.goofy.com> wrote in message
news:efn3MNU6HHA.1148@.TK2MSFTNGP05.phx.gbl...
> That looks great...thanks alot
> And performance wice?
> Is did a good solution you think?
> in this table there will be at least 100,000 records...
>
> thank you
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
> news:6CBB3FC9-3022-41EC-AFF3-3FCF421CB809@.microsoft.com...
>> How about:
>>
>> SELECT *
>> FROM
>> (
>> SELECT *
>> FROM StockStatus ss
>> WHERE
>> ss.CreateDate =>> (
>> SELECT MAX(CreateDate)
>> FROM StockStatus ss1
>> WHERE ss1.StockId = ss.StockId
>> )
>> ) x
>> WHERE
>> x.StockStatusId IN (2,3)
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Goofy" <nomail@.goofy.com> wrote in message
>> news:OqaY4FM6HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Yes, sure I can, thank you.
>> I am using SQL2000.
>> A simple version of the table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(22, 2, '27-8-2007 15:05:46')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(22, 4, '27-8-2007 15:10:11')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 3, '27-8-2007 16:15:51')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 4, '27-8-2007 15:10:11')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 2, '27-8-2007 15:15:46')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 4, '27-8-2007 16:35:46')
>> What I try to get back is
>> StockStatus of stockid 22 is 3 (Latest time)
>> StockStatus of stockid 23 is 4 (Latest time)
>> I want only back the statuses of 2 and 3.
>> So in this example only stockID 22.
>> The table is filled with lots more records of course and statuses...
>> But if a status (with higher date exists in a higher status(then 2 or
>> 3)) I dont want them in the result.
>> I hope you can help me.
>> thnx
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
>> news:6B5AC7C1-B1C3-46A9-AE19-4325945A44FC@.microsoft.com...
>> Can you give us some sample data (in the form of INSERT statements) and
>> sample output? I'm certain this is possible... Also, are you using SQL
>> Server 2000 or 2005? Some of the 2005 features may be helpful here.
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Goofy" <nomail@.goofy.com> wrote in message
>> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the
>> status changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the
>> status assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>>
>>
>sql

Can this be Done?

Hi, just wondering if something like this can be done

procedure [dbo].[UpdateStringColumn]
@.KeyValue int,
@.Column varchar(50),
@.value float,
@.TableName varchar(30),
@.KeyName varchar(30)
as
begin
exec('UPDATE '+@.TableName+'
Set '+@.column+ ' = '+@.value+'
where '+@.TableName+'.'+@.KeyName+' = '+@.KeyValue+'' )
end

If I can get this to work I only need a few stored procedures to update any table column combination, one for each @.Value datatype

Well, it can be done. But it is a bad idea. Using dynamic SQL like this has lot of implications - maintainability, security risks, performance problems, manageability etc. See below link for some discussion:

http://www.sommarskog.se/dynamic_sql.html

Other things to ponder: Why do you want to have single SP to update data? And what happens if you want to update multiple columns in a table? What happens if the data type of each column in different? What happens if there is some function in the client that need to only update one column and another function that updates entire row? What happens if you need to perform additional validations for updates on some tables? What happens if there are some users who can only update some tables? How will you manage the permissions since with dynamic SQL you need to grant all the required permissions to users directly?

can somone help me tell what is wrong with this coding?

GLOBAL STRING Line1_BatchID
GLOBAL STRING Line1_CompletedDate
GLOBAL INT Line1_CompletedQty
GLOBAL STRING Line1_CompletedTime
GLOBAL STRING Line1_ExpiryDate
GLOBAL INT Line1_Qty
GLOBAL STRING Line1_OrderID

GLOBAL INT Line1_BatchID_ID
GLOBAL INT Line1_CompletedDate_ID
GLOBAL INT Line1_CompletedQty_ID
GLOBAL INT Line1_CompletedTime_ID
GLOBAL INT Line1_ExpiryDate_ID
GLOBAL INT Line1_OrderStatus_ID

GLOBAL STRING Line2_BatchID
GLOBAL STRING Line2_CompletedDate
GLOBAL INT Line2_CompletedQty
GLOBAL STRING Line2_CompletedTime
GLOBAL STRING Line2_ExpiryDate
GLOBAL STRING Line2_OrderStatus
GLOBAL INT Line2_Qty
GLOBAL STRING Line2_OrderID

GLOBAL INT Line2_BatchID_ID
GLOBAL INT Line2_CompletedDate_ID
GLOBAL INT Line2_CompletedQty_ID
GLOBAL INT Line2_CompletedTime_ID
GLOBAL INT Line2_ExpiryDate_ID
GLOBAL INT Line2_OrderStatus_ID

GLOBAL STRING Line3_BatchID
GLOBAL STRING Line3_CompletedDate
GLOBAL INT Line3_CompletedQty
GLOBAL STRING Line3_CompletedTime
GLOBAL STRING Line3_ExpiryDate
GLOBAL STRING Line3_OrderStatus
GLOBAL INT Line3_Qty
GLOBAL STRING Line3_OrderID

GLOBAL INT Line3_BatchID_ID
GLOBAL INT Line3_CompletedDate_ID
GLOBAL INT Line3_CompletedQty_ID
GLOBAL INT Line3_CompletedTime_ID
GLOBAL INT Line3_ExpiryDate_ID
GLOBAL INT Line3_OrderStatus_ID

GLOBAL STRING result
GLOBAL STRING result2

FUNCTION getOrderDetail(STRING Product_ID)
INT counter = 0
INT statusSQL, sqlResult;
STRING Sql1
STRING Sql2
STRING Sql3
result = ""

statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END

Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
//Select first SetId
result = SQLGetField(statusSQL, "SetId")
END
END

Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter < 9 THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END
END

IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END

counter = counter + 1
END
END

END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateBatchID(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID)
END

sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateCompletedQty(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID)
END

sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateCompletedOrder(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedTime + "' WHERE [Id] = " + IntToStr(Line1_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line1_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line1_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END

IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedDate + "' WHERE [Id] = " + IntToStr(Line2_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedTime + "' WHERE [Id] = " + IntToStr(Line2_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line2_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line2_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);

END

IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedDate + "' WHERE [Id] = " + IntToStr(Line3_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedTime + "' WHERE [Id] = " + IntToStr(Line3_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line3_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

INT FUNCTION getLine1_Qty()
RETURN Line1_Qty
END

INT FUNCTION getLine1_CompletedQty()
RETURN Line1_CompletedQty
END

STRING FUNCTION getLine1_OrderID()
RETURN Line1_OrderID
END

STRING FUNCTION getLine1_BatchID()
RETURN Line1_BatchID
END

INT FUNCTION getLine2_Qty()
RETURN Line2_Qty
END

INT FUNCTION getLine2_CompletedQty()
RETURN Line2_CompletedQty
END

STRING FUNCTION getLine2_OrderID()
RETURN Line2_OrderID
END

STRING FUNCTION getLine2_BatchID()
RETURN Line2_BatchID
END

INT FUNCTION getLine3_Qty()
RETURN Line3_Qty
END

INT FUNCTION getLine3_CompletedQty()
RETURN Line3_CompletedQty
END

STRING FUNCTION getLine3_OrderID()
RETURN Line3_OrderID
END

STRING FUNCTION getLine3_BatchID()
RETURN Line3_BatchID
END

FUNCTION dbTest()

INT statusSQL, sqlResult;
INT counter = 0
STRING Sql1
STRING Sql2
STRING Sql3
STRING Product_ID = "Biscuit"
result = ""

statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END

Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
result = SQLGetField(statusSQL, "SetId")
END
END

Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
Line1_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
Line2_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
Line3_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END

counter = counter + 1
END
END

END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

STRING FUNCTION getresult()
RETURN result
END

STRING FUNCTION getresult2()
RETURN result2
END

Give us some clues. What do you think may be the problem?

Most of us are not going to spend much time looking through your code without a hint about what we are looking for.

And it appears to be VB5 (or earlier) code -GLOBAL variables have long fallen by the wayside.

|||i have some field in the SQL table, when information is passed in, the data goes into the wrong field. for eg, i have the field date and quantity, when information is passed into the SQL, the data got mixed up, date data is stored into the quantity field and quantity is stored in to the date field....

Can someone Help me?

Hi peoples Im newbie in Sql server 2005 and learning Here is my problem:

I have:

Table1 Complaint_Types-

TypeID int PK
Type VarChar

Table2 -Members-

MemberID Int Pk

MemberName VarChar

Roomnumber VarChar

Table3 -Computers-

ComputerID Int Pk

ComputerDescription VarChar

MemberID Int Fk

Table4 -Techs-

TechID Int Pk

TechName VarChar

HireDate DateTime

Table5 -Complaints-

TypeID Int Fk

MemberID Int Fk

ComputerID Int Fk

TechID Int Fk

Description VarChar

PostDate DateTime

ResolvedDate DateTime

I Need:

1- Idea to Get data from related Tables

2- Insert / Update all Tables

3- Calculate Date Time Feilds

eg: Complaints in current month

Complaints in last year

Time Consumed on a Complaint (Return date Postdate) in Days, hours,minutes

if any body have a working example in vb.net in form of windows Forms or Asp.net please Let me Know

any type of help welcomed

regards

Rashed Nadeem

Moving to Getting Started forum|||

Hi Rashed,

I would suggest to refer SQL Server Books Online for Insert/Update/Delete Statements

select Columnlists from Table -- Will fetch records from table, adding where columnname would fetch records based on condition given in where

insert into Table values('values',0) -- will add values to table

Refer http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16621

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18550

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=13581

Refer http://www.connectionstrings.com/ for Various Connection Strings

and http://www.planet-source-code.com/ for VB, .Net source code examples

HTH

Hemantgiri S. Goswami

|||

Hi Rashed:

It seems like you are seeking a way to access SQL data using managed code.

Getting familiar with ADO.net may be a great start.

Following are the links to some MSDN references and sample code that may help to get you started:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

Hope these information helps. :-)

Thanks

Tommy