question about Replication

Hello, i am try to build a Pocket PC database program. Using replication, i can sync the data. I want to build a program to record some data and some picture attach to it. Can those picture sync to SQL server by replication? And how to do that on the application? ThanksYes, they can. Just store the data in an image column and replicate that column. It will plug into the replication engine just like any other data.|||question about that image column. how to input an image as a value to that datarow? will it just adding an description showing the path of the file?|||you can search the web for examples.

question about Replication

Hello, i am try to build a Pocket PC database program. Using replication, i can sync the data. I want to build a program to record some data and some picture attach to it. Can those picture sync to SQL server by replication? And how to do that on the application? ThanksYes, they can. Just store the data in an image column and replicate that column. It will plug into the replication engine just like any other data.|||question about that image column. how to input an image as a value to that datarow? will it just adding an description showing the path of the file?|||you can search the web for examples.

Question about paging?

Good Day!

i have a problem regarding on paging.

in my Report i want to view the record 1 record per page.

Example:

I have a textboxes where i view my data the

- Firstname

- organization

-Department

I want it to view it 1 page per records.

How to do that? Sorry coz im a new bie in the reporting services

can anybody help me?

I got the already the paging per records but the problem is there is a blank page in next page. Example page 1 has a records then page 2 is a blank page then page 3 has a record?

Why is that? what is the cause?

Canany body help me? Your help is greatly appreciated.

|||

Hey,

Is it ok if you view like

First Name Organization Department

Then use table, to see the records and create a group and give your field like first name , department. and check page break at the end so it gives you in 1 record per page.

Any doubts let me know.

Amarnath

|||Make sure that there are no report items after the group that you have set to break.

Question about OUTPUT clause

In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY.

Question: will this sql 2005 approach also provide the newly added identity value?

insert into TestTable -- ID column of testtable is an Identity field

output inserted.id, inserted.col1 into @.insertedRecords

values('row 20')

select ID from @.insertedRecords

TIA,

Barkingdog

.

Sure can. Did your attempts not work? Here is a script to demo:

create table testTable
(
testTableId int identity primary key,
value varchar(10) unique
)
go

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
values('row 20')

select scope_identity()
select * from @.insertedRows

go

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
select 'row 21'
union all
select 'row 22'

select * from @.insertedRows

|||

I haven't encoutnered any problems but I was just wondering if my original thought was correct.

Thanks.

Barkingdog

sql

question about looping through a record set

I am wondering if someone can help me write a stored procedure to solve
this problem without using a cursor, which I gather should be avoided
if at all possible from what I've read here.
My stored proc needs to return a list of statuses from a table for a
given id number. 98% of the time, this will return one record, but the
other 2% it will be more than one record. I need to loop through these
statuses and make sure they are one of two possibilities, and then if
they are great, make sure they are part of my record set that I return.
If not, do not include them.
Here is at least the first part,
CREATE PROCEDURE prcCheckOrderComplete (@.OrderNum int, @.shipped int,
@.cancelled int) AS
select iItemStatusCodeID from tblShoppingCart where iOrderID =
@.OrderNum
Now, the pseudocode --
/*pseudocode*/
foreach iItemStatusCodeID
if ((iItemStatusCode == @.shipped) || (iItemStatusCode ==
@.cancelled))
great! add it to an array to be returned.
Thanks for any assistance.You are too focused on row-by-row processing. Why not just say
WHERE iOrderID = @.OrderNum
AND (iItemStatusCode IN (@.shipped, @.cancelled));
<danielle.m.manning@.gmail.com> wrote in message
news:1140795611.590886.314190@.e56g2000cwe.googlegroups.com...
>I am wondering if someone can help me write a stored procedure to solve
> this problem without using a cursor, which I gather should be avoided
> if at all possible from what I've read here.
> My stored proc needs to return a list of statuses from a table for a
> given id number. 98% of the time, this will return one record, but the
> other 2% it will be more than one record. I need to loop through these
> statuses and make sure they are one of two possibilities, and then if
> they are great, make sure they are part of my record set that I return.
> If not, do not include them.
> Here is at least the first part,
> CREATE PROCEDURE prcCheckOrderComplete (@.OrderNum int, @.shipped int,
> @.cancelled int) AS
> select iItemStatusCodeID from tblShoppingCart where iOrderID =
> @.OrderNum
> Now, the pseudocode --
> /*pseudocode*/
> foreach iItemStatusCodeID
> if ((iItemStatusCode == @.shipped) || (iItemStatusCode ==
> @.cancelled))
> great! add it to an array to be returned.
>
> Thanks for any assistance.
>|||You are right! I am. Thanks for the tip, i think that will work. I'm
working with perl 99% of the time, which is probably why I am thinking
that way.

Question about join?

Hi there,
I would like to know what is missing in the select instruction below because
is repeating 3 times each record. I tried to use
DDepen CROSS JOIN HT_DBAU001_DCliente and that was worst because brought me
much more rows repeated!!!
Thanks,
VILMAR
BRAZIL
SELECT distinct DDepen.DPCliente, DDepen.DPNome, DDepen.DPEmail,
DDepen.DPSenha, HT_DBAU001_DCliente.CLSITU
FROM DDepen,HT_DBAU001_DCliente
WHERE (DDepen.DPCliente BETWEEN 1 AND 4) AND (HT_DBAU001_DCliente.CLSITU
BETWEEN 8 AND 10)
ORDER BY DDepen.DPCliente, DDepen.DPNome
Brings me this:
DPCliente DPNome DPEmail
DPSenha CLSITU
-- --- --
-- -- --
--
1 José jr@.XXX.com.br
00000 8.0
1 José jr@.XXX.com.br
00000 9.0
1 José jr@.XXX.com.br
00000 10.0
3 Marinho
lider@.XXX.com.br 00000 8.0
3 Marinho
lider@.XXX.com.br 00000 9.0
3 Marinho
lider@.XXX.com.br 00000 10.0
(6 row(s) affected)Difficult to explain in a short email but do a google on 'cartesian product'
or 'cross join'. Also, review this in BOL:
Using Cross Joins
A cross join that does not have a WHERE clause produces the Cartesian
product of the tables involved in the join. The size of a Cartesian product
result set is the number of rows in the first table multiplied by the number
of rows in the second table. This is an example of a Transact-SQL cross
join:
Your query joins two tables together but the join does not describe how
those tables are related.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"news.microsoft.com" <suporte@.hitecnet.com.br> wrote in message
news:ORnEdJ$vDHA.2508@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I would like to know what is missing in the select instruction below
because
> is repeating 3 times each record. I tried to use
> DDepen CROSS JOIN HT_DBAU001_DCliente and that was worst because brought
me
> much more rows repeated!!!
> Thanks,
> VILMAR
> BRAZIL
> SELECT distinct DDepen.DPCliente, DDepen.DPNome, DDepen.DPEmail,
> DDepen.DPSenha, HT_DBAU001_DCliente.CLSITU
> FROM DDepen,HT_DBAU001_DCliente
> WHERE (DDepen.DPCliente BETWEEN 1 AND 4) AND
(HT_DBAU001_DCliente.CLSITU
> BETWEEN 8 AND 10)
> ORDER BY DDepen.DPCliente, DDepen.DPNome
> Brings me this:
> DPCliente DPNome DPEmail
> DPSenha CLSITU
> -- --- --
--
> -- -- --
--
> --
> 1 José
jr@.XXX.com.br
> 00000 8.0
> 1 José
jr@.XXX.com.br
> 00000 9.0
> 1 José
jr@.XXX.com.br
> 00000 10.0
> 3 Marinho
> lider@.XXX.com.br 00000 8.0
> 3 Marinho
> lider@.XXX.com.br 00000 9.0
> 3 Marinho
> lider@.XXX.com.br 00000 10.0
> (6 row(s) affected)
>

Question about IDENTITY

Just a quick question.

I have a table with an IDENTITY field. I inserted a record using the is 1024 so as to distinguish the record. There are only about 40 records in the table, including id 1024.

What I need is for the IDENTITY to go back to 39 and start it's count from there to fill the gap up to 1024. How is this done?

Delete the record with id 1024 then edit the table and set the "Identity Seed" to 39. You could probably write an alter table statement to handle this or go into enterprise manager and design the table.

Why can't the ID's continue after 1024? Other than looks it shouldn't matter as long as the ID's are unique.

-- LZ

|||

True. It doesn't matter, the IDs are unique, and 1024 serves it purpose...
For the most part, I am just trying to get a better handle on IDENTITY and the various ways to control it.

I only have access to the database through code (and a form that I hid on the server that allows me to insert SQL statements), and I am just trying to make sure I have as much control over the data as I would if I had direct access to it.

Thanks for your input...

Question about How Data can be rendered on Report

I have a dataset that is comprised of several records of employees leave
types and hours. Each leave type record is unique(sicks on record and
vacation another). 2 fields of each record,I group on (Employee and
TimePeriod) and the Leave type hours I want to display in separate columns.
What I am trying to do is group by Employee with a page break after each
employee(this was easy), then group by period(same page, this was done too),
but I also need to put the hours in different columns depending on the Leave
type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
CREATED.
i.e)
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 8
1/8/2005-1/15/2005 4 0
BUT INSTEAD I AM GETTING THIS
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 0
1/1/2005-1/8/2005 0 8
1/8/2005-1/15/2005 4 0
1/8/2005-1/15/2005 0 4
I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?Create the two text boxes for Sick and Vacation... For the values use an
expression like
for Sick
=Iif(Fields!LeaveType.Value="Sick",Hours,0)
for Leave textbox
=Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
too),
> but I also need to put the hours in different columns depending on the
Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||I used the Iif statement you wrote. It works fine, but what I don't want it
to do is to put the next record in the dataset onto the next row. I want the
next record to go into the next column as in previous post example. I only
want the next dataset record to go into the next row if the group changes.
See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values use an
> expression like
> for Sick
> =Iif(Fields!LeaveType.Value="Sick",Hours,0)
> for Leave textbox
> =Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees leave
> > types and hours. Each leave type record is unique(sicks on record and
> > vacation another). 2 fields of each record,I group on (Employee and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break after each
> > employee(this was easy), then group by period(same page, this was done
> too),
> > but I also need to put the hours in different columns depending on the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Create a grouping on Employee and DateRange and drop the Employee name, =date range (week, whatever it's called), and the Sum() for Vacation and =Sick in the Group Footer. Make sure you use the group name as the scope =for the Sum (IOW, =3DSum(Fields!SickTime.Value, "GroupName") where ="GroupName" is the name fo the group).
You won't have any values dropping into the detail rows and you can =place the Employee Name in the group header row if you don't want it to =repeat for each row. Make sure you also sort the data set by Employee =and Time.
HTH.
-- TIM ELLISON
"dillig" <dillig@.discussions.microsoft.com> wrote in message =news:1C382976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...
I used the Iif statement you wrote. It works fine, but what I don't =want it to do is to put the next record in the dataset onto the next row. I =want the next record to go into the next column as in previous post example. I =only want the next dataset record to go into the next row if the group =changes. See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values =use an
> expression like
> for Sick
> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)
> for Leave textbox
> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)
> > Hope this helps...
> > -- > Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> > I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> > "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees =leave
> > types and hours. Each leave type record is unique(sicks on record =and
> > vacation another). 2 fields of each record,I group on (Employee =and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break =after each
> > employee(this was easy), then group by period(same page, this was =done
> too),
> > but I also need to put the hours in different columns depending on =the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT =RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I =WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE =HELP'?
> >
> > >
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Create a grouping on Employee and =DateRange and drop the Employee name, date range (week, whatever it's called), and the =Sum() for Vacation and Sick in the Group Footer. Make sure you use the =group name as the scope for the Sum (IOW, ==3DSum(Fields!SickTime.Value, "GroupName") where "GroupName" is the name fo the =group).
You won't have any values dropping into =the detail rows and you can place the Employee Name in the group header row if you =don't want it to repeat for each row. Make sure you also sort the data set by =Employee and Time.
HTH.
-- TIM ELLISON
"dillig" wrote in message news:1C3=82976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...I used the Iif statement you wrote. It works fine, but what I don't want =it to do is to put the next record in the dataset onto the next row. =I want the next record to go into the next column as in previous post =example. I only want the next dataset record to go into the next row if the =group changes. See the example for clarification."Wayne Snyder" wrote:> Create the two text boxes for Sick and Vacation... =For the values use an> expression like> for Sick> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)> for Leave =textbox> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)> > =Hope this helps...> > -- > Wayne Snyder, MCDBA, SQL Server MVP> Mariner, Charlotte, NC>">http://www.mariner-usa.com">www.mariner-usa.com> =(Please respond only to the newsgroups.)> > I support the =Professional Association of SQL Server (PASS) and it's> community of SQL =Server professionals.>">http://www.sqlpass.org">www.sqlpass.org> > ="dillig" wrote in message> news:889=F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...> > I have a dataset that is comprised of several records of =employees leave> > types and hours. Each leave type record is =unique(sicks on record and> > vacation another). 2 fields of each record,I =group on (Employee and> > TimePeriod) and the Leave type hours I want =to display in separate> columns.> > What I am trying to =do is group by Employee with a page break after each> > =employee(this was easy), then group by period(same page, this was done> =too),> > but I also need to put the hours in different columns depending =on the> Leave> > type. BUT I DONT WANT IT TO CREATE A =NEW ROW. I WANT THE NEXT RECORD TO GO> IN> > THE NEXT COLUMN =UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW> ROW> => CREATED.> > i.e)> = > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> >> >> > BUT INSTEAD I AM GETTING =THIS> > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 0> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =0 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =0 = 4> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. =CAN ANYONE HELP'?> >> > =>

---

--=_NextPart_000_01AB_01C4F4BE.37F23400--|||Have you tried using a Matrix instead of a table? Put time in the rows and
leave type in the columns.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
>I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
> columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
> too),
> but I also need to put the hours in different columns depending on the
> Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||Thanks to all who replied to this post. Creating a second grouping from
timeperiod and putting the totals for the inside group in group2's footer
worked great.
Your guys are awesome.
"dillig" wrote:
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done too),
> but I also need to put the hours in different columns depending on the Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>

Question about getting the latest identity field in a specific table

Dear All,

Suppose in the program a record is added to a table whose
primary key is a identity field. If I really want to get the lastest
value for that field after the insertion, is it the best way to use
IDENT_CURRENT() to obtain this value?

Thanks for your kind attention

Yours faithfully,
BennyI would rather use SCOPE_IDENTITY() or @.@.IDENTITY depending on the
requirements. SQL Server Books Online states that, IDENT_CURRENT is similar
to the 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three
functions return last-generated identity values. However, the scope and
session on which 'last' is defined in each of these functions differ.

- IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
- @.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.

--
- Anith
( Please reply to newsgroups only )|||Anith Sen (anith@.bizdatasolutions.com) writes:
> - IDENT_CURRENT returns the last identity value generated for a specific
> table in any session and any scope.

One important thing to clarify here is that IDENT_CURRENT() can be affected
by insertions by other processes, where as scope_identity and @.@.identity
cannot.

Thus, ident_current() is rarely the function you should call in application
code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

question about database design

I have a table, named car. It normally have 10000 records and will growth in
the furture.
Administrator can delete the car record through an web based administrator
platform
And we not like delete the car record physically.
My question is that, what is the best method to keep the database
performance.
1. add additional field, which called is_deleted, in the car table, and mark
it as 'T' if the record is deleted, and 'F' when the record is active.
2. create new table, which have the same as schema as car table, and named
deleted_car, when administrator delete a car record, the program will remove
a record from car table and insert it into the deleted_car table.
Thanks~I prefer the first one as there is no need for additional table
Madhivanan|||> My question is that, what is the best method to keep the database
> performance.
Make sure that you have properly designed database.
Make sure that you have properly defined indexes on the tables.
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
> I have a table, named car. It normally have 10000 records and will growth
in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||Either method will work. For a table that small either method will work
well.
If the table starts to get into the hundreds of thousands of records you
will see a performance improvment (small) by using two tables. If you get
into the millions of records it will start to actuall make a difference...
This is assuming that you are using indexes correctly. If you are not, then
having 2 tables will have a large speed improvement even with the smaller
record sets.
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I have a table, named car. It normally have 10000 records and will growth
>in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
> mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
> remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||The second option might cause you some maintenance headaches down the
road... If you update your application you have to make sure you apply
updates to both sets of tables, your application will need to be modified to
work with the changes in both sets of tables, etc. What if someone
accidentally "deletes" a record and has to bring it back/make it active
again? What if, somehow, a single item gets copied to both tables?
For 10,000 -- or even 100,000 -- records I don't think it would be worth the
hassle to split it up.
Thx
Mike C
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I have a table, named car. It normally have 10000 records and will growth
>in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
> mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
> remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||right~~|||hehe~~
right...|||thanks~
^.^|||thanks~
you made my mind clear again~|||>> 1. add additional field [sic], which called is_deleted, in the car
table, and mark it as 'T' if the record [sic] is deleted, and 'F' when
the record [sic] is active. <<
Rows are not records; columns are not fields; an RDBMS is not a
sequential file system. The basic idea is right, but make this
attribute a general status code that can take other values or make it a
timestamp so you know when the event occurred.

question about best way to store an up or down value

I'm creating a table for maintenance records.

In each record many of the values are simply checkboxes.

In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?

-DavidWithout getting into design issues, the best way would be to use a BIT datatype, with 0 used to indicate FALSE or OFF, and 1 to indicate TRUE or ON.

Question about "Foreign key constraint"

I have two tables "Courses" ( with primary key "courseID) and "Prerequisties"
(with the foreign key "courseID"), when I want Insert new record into
"Prerequisties" table. It always complains :
"Insert statement conflicited with column FOREIGN KEY CONSTRAINT
FK_Prerequisties_Courses. The conflicit occured in Database table 'Courses',
colume 'courseID' ".
I used similar tables and foreign key, they worked well. But just not for
above tables. Can anyone please tell me from where should I start to fix the
issue. Thanks.
The tables should be set up as follows:
Courses
CourseID PrimaryKey
Prerequisites
PreReqID PrimaryKey
CourseID ForeignKey REFERENCES Courses(CourseID)
If you have already set up your ForeignKey in the Prerequisites table, then
you are attempting to insert rows with a CourseID that does not exist in the
Courses table.
If you have your ForeignKey in the Courses table, then you have it backwards
and should create it as shown above.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> I have two tables "Courses" ( with primary key "courseID) and
"Prerequisties"
> (with the foreign key "courseID"), when I want Insert new record into
> "Prerequisties" table. It always complains :
> "Insert statement conflicited with column FOREIGN KEY CONSTRAINT
> FK_Prerequisties_Courses. The conflicit occured in Database table
'Courses',
> colume 'courseID' ".
> I used similar tables and foreign key, they worked well. But just not for
> above tables. Can anyone please tell me from where should I start to fix
the
> issue. Thanks.
>
|||Actually, a prerequisite is usually a relationship between two courses.
There will be two CourseID columns (one for the required course and one for
the requiring course) and therefore two FKs.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uTSMTT6iEHA.2660@.TK2MSFTNGP15.phx.gbl...
> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table,
then
> you are attempting to insert rows with a CourseID that does not exist in
the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it
backwards[vbcol=seagreen]
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
for
> the
>
|||Thanks, Rick,
Actually for thr "Prerequisites" table
" PreReqID" is also a foregin key, there is no PrimaryKey for
"Prerequisites" table.
I used SQL server "Enterprise Manager" diagram to setup the relationship
between two tables. So I don't know how to add " CourseID ForeignKey
REFERENCES Courses(CourseID)", could you please point out.
"Rick Sawtell" wrote:

> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table, then
> you are attempting to insert rows with a CourseID that does not exist in the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it backwards
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
> the
>
>
|||Open up the Query Analyzer.
Find the first table by using the drill-downs on the left side.
Right-click the table and choose Script Object to Clipboard as CREATE.
Paste that script here.
Right-click the next table and choose the same thing and paste it here as
well.
By having your CREATE TABLE commands, we will be better able to help you out
on this one.
Rick
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:6ED8C986-EF42-4E2F-A4A6-710DDC666ABC@.microsoft.com...[vbcol=seagreen]
> Thanks, Rick,
> Actually for thr "Prerequisites" table
> " PreReqID" is also a foregin key, there is no PrimaryKey for
> "Prerequisites" table.
> I used SQL server "Enterprise Manager" diagram to setup the relationship
> between two tables. So I don't know how to add " CourseID ForeignKey
> REFERENCES Courses(CourseID)", could you please point out.
>
> "Rick Sawtell" wrote:
then[vbcol=seagreen]
the[vbcol=seagreen]
backwards[vbcol=seagreen]
for[vbcol=seagreen]
fix[vbcol=seagreen]
|||"Rick Sawtell" wrote:
..> By having your CREATE TABLE commands, we will be better able to help you
out
> on this one.
My database has over 40 tables that all generated by using "Enterprise
Manager" , so there are no related Create table commands, all others working
fine except these two. Anyway thanks for help.
|||Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_CoursePreRequisites_PreRequisites] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT (1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
"LaoJ" wrote:

>
> "Rick Sawtell" wrote:
> .> By having your CREATE TABLE commands, we will be better able to help you
> out
> My database has over 40 tables that all generated by using "Enterprise
> Manager" , so there are no related Create table commands, all others working
> fine except these two. Anyway thanks for help.
>
|||Up
Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_CoursePreRequisites_PreRequisites] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT (1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
..
|||I'm not getting the same errors that you are. (I'm getting something else
weird going on in my system.)
When I run your script (I moved the courseprerequisites table to the end),
the tables generated fine. I added rows to the courses table and identicle
rows to the prerequisites table. The CoursePreRequisites however would
allow me to add rows that violated the first FK constraint, but not the
second. Strange. I'll have to delve a little deeper and see what is going
on.
The only thing I could suggest to you is the following:
1. Get rid of the PreRequisites table. Store all of your courses in the
Courses table and then just use two references from the PreRequisites table
back to the Courses table.
2. Put a Primary Key constraint on the CoursePreRequisites table that
encompasses both FK columns within it. Example:
ALTER TABLE CoursePreRequisites
ADD CONSTRAINT PK_CoursePreRequisites PRIMARY KEY (CourseID, PreReqID)
This will keep you from creating duplicate pre-reqs for a course.
Sorry I was not more helpful.
Rick

Question about "Foreign key constraint"

I have two tables "Courses" ( with primary key "courseID) and "Prerequisties
"
(with the foreign key "courseID"), when I want Insert new record into
"Prerequisties" table. It always complains :
"Insert statement conflicited with column FOREIGN KEY CONSTRAINT
FK_Prerequisties_Courses. The conflicit occured in Database table 'Courses',
colume 'courseID' ".
I used similar tables and foreign key, they worked well. But just not for
above tables. Can anyone please tell me from where should I start to fix the
issue. Thanks.The tables should be set up as follows:
Courses
CourseID PrimaryKey
Prerequisites
PreReqID PrimaryKey
CourseID ForeignKey REFERENCES Courses(CourseID)
If you have already set up your ForeignKey in the Prerequisites table, then
you are attempting to insert rows with a CourseID that does not exist in the
Courses table.
If you have your ForeignKey in the Courses table, then you have it backwards
and should create it as shown above.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> I have two tables "Courses" ( with primary key "courseID) and
"Prerequisties"
> (with the foreign key "courseID"), when I want Insert new record into
> "Prerequisties" table. It always complains :
> "Insert statement conflicited with column FOREIGN KEY CONSTRAINT
> FK_Prerequisties_Courses. The conflicit occured in Database table
'Courses',
> colume 'courseID' ".
> I used similar tables and foreign key, they worked well. But just not for
> above tables. Can anyone please tell me from where should I start to fix
the
> issue. Thanks.
>|||Actually, a prerequisite is usually a relationship between two courses.
There will be two CourseID columns (one for the required course and one for
the requiring course) and therefore two FKs.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uTSMTT6iEHA.2660@.TK2MSFTNGP15.phx.gbl...
> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table,
then
> you are attempting to insert rows with a CourseID that does not exist in
the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it
backwards
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
for[vbcol=seagreen]
> the
>|||Thanks, Rick,
Actually for thr "Prerequisites" table
" PreReqID" is also a foregin key, there is no PrimaryKey for
"Prerequisites" table.
I used SQL server "Enterprise Manager" diagram to setup the relationship
between two tables. So I don't know how to add " CourseID ForeignKey
REFERENCES Courses(CourseID)", could you please point out.
"Rick Sawtell" wrote:

> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table, the
n
> you are attempting to insert rows with a CourseID that does not exist in t
he
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it backwar
ds
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
> the
>
>|||Open up the Query Analyzer.
Find the first table by using the drill-downs on the left side.
Right-click the table and choose Script Object to Clipboard as CREATE.
Paste that script here.
Right-click the next table and choose the same thing and paste it here as
well.
By having your CREATE TABLE commands, we will be better able to help you out
on this one.
Rick
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:6ED8C986-EF42-4E2F-A4A6-710DDC666ABC@.microsoft.com...[vbcol=seagreen]
> Thanks, Rick,
> Actually for thr "Prerequisites" table
> " PreReqID" is also a foregin key, there is no PrimaryKey for
> "Prerequisites" table.
> I used SQL server "Enterprise Manager" diagram to setup the relationship
> between two tables. So I don't know how to add " CourseID ForeignKey
> REFERENCES Courses(CourseID)", could you please point out.
>
> "Rick Sawtell" wrote:
>
then[vbcol=seagreen]
the[vbcol=seagreen]
backwards[vbcol=seagreen]
for[vbcol=seagreen]
fix[vbcol=seagreen]|||"Rick Sawtell" wrote:
.> By having your CREATE TABLE commands, we will be better able to help you
out
> on this one.
My database has over 40 tables that all generated by using "Enterprise
Manager" , so there are no related Create table commands, all others working
fine except these two. Anyway thanks for help.|||Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT & #91;FK_CoursePreRequisites_PreRequisites
] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT
(1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
"LaoJ" wrote:

>
> "Rick Sawtell" wrote:
> .> By having your CREATE TABLE commands, we will be better able to help yo
u
> out
> My database has over 40 tables that all generated by using "Enterprise
> Manager" , so there are no related Create table commands, all others worki
ng
> fine except these two. Anyway thanks for help.
>|||Up
Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT & #91;FK_CoursePreRequisites_PreRequisites
] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT
(1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
.|||I'm not getting the same errors that you are. (I'm getting something else
weird going on in my system.)
When I run your script (I moved the courseprerequisites table to the end),
the tables generated fine. I added rows to the courses table and identicle
rows to the prerequisites table. The CoursePreRequisites however would
allow me to add rows that violated the first FK constraint, but not the
second. Strange. I'll have to delve a little deeper and see what is going
on.
The only thing I could suggest to you is the following:
1. Get rid of the PreRequisites table. Store all of your courses in the
Courses table and then just use two references from the PreRequisites table
back to the Courses table.
2. Put a Primary Key constraint on the CoursePreRequisites table that
encompasses both FK columns within it. Example:
ALTER TABLE CoursePreRequisites
ADD CONSTRAINT PK_CoursePreRequisites PRIMARY KEY (CourseID, PreReqID)
This will keep you from creating duplicate pre-reqs for a course.
Sorry I was not more helpful.
Rick

question - inserting with formview control

Using formview control, I'm trying to insert a record. In the process of inserting, I want to save the categoryid from a shared class. The code runs fine but categoryid gets saved as null...Any pointers?? [When I display, it shows the value]

thanks

protected void savebutton_click(object sender, EventArgs e)
{
//this statement runs fine under debug...but values do not get saved?
SqlDataSource1.InsertParameters.Add("@.category", SharedValues.category.ToString());
SqlDataSource1.Insert();
}

<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="itemid" DefaultMode="edit" OnDataBound="FormView1_DataBound">
<EditItemTemplate>

Title:
<asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>'>
</asp:TextBox><br/>
Description:
<asp:TextBox ID="descriptionTextBox" runat="server" Text='<%# Bind("description") %>' Rows="10" TextMode="MultiLine" Width="500px" Height="166px">
</asp:TextBox><br/>


<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
Text="Update">
</asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>

</EditItemTemplate>
<InsertItemTemplate>
Title:
<asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>'>
</asp:TextBox><br/>
Description:
<asp:TextBox ID="descriptionTextBox" runat="server" Text='<%# Bind("description") %>' Rows="10" TextMode="MultiLine" Width="500px" Height="166px">
</asp:TextBox><br/>

<asp:label ID="categoryLbl" runat="server" Text='<%# SharedValues.category %>'></asp:label>
<br/>

<div class="actionbuttons">
<Club:RolloverButton ID="GreenRolloverButton3" CommandName="Insert" Text="Save"
runat="server" OnClick="savebutton_click" />
<Club:RolloverLink ID="GreenRolloverLink2" Text="Cancel" runat="server" NavigateURL="Classifieds.aspx" />
</div>
</InsertItemTemplate>

</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"
InsertCommand="insert into tads (title,[description], categoryid) values (@.title,@.description, @.category)"
SelectCommand="Select title, [description], categoryid from tads whereitemid=@.itemid"
UpdateCommand="update tads set title = @.title, [description] = @.description where itemid = @.itemid" >
<SelectParameters>
<asp:QueryStringParameter Name="itemid" QueryStringField="itemid" Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="title" Type="String" />
<asp:Parameter Name="description" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="title" Type="String" />
<asp:Parameter Name="description" Type="String" />
<asp:Parameter Name="category" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>

You shouldn't need to handle the Button's Click event at all. The FormView automatically knows how to insert items with a data source control. The command name of the button should be enough to get the insert to happen. Also, rather than adding the parameter to the data source's InsertParameters collection, you should handle the data source's Inserting event and add a SqlParameter to the command:
void SqlDataSource1_Inserting(...) {
SqlParameter parameter = new SqlParameter("@.param_name", <param value>);
e.Command.Parameters.Add(parameter);
}
Thanks,
Eilon