wherein MRNID and VesselCode are primary keys of the table.
What do you mean by "horozontally"
<MRNID=10002><VesselCode=VSL1>
This is horozontally.
<MRNID=10002>
<VesselCode=VSL1>
Would be vertical. Can you be a bit more clear?
|||do you mean pivoting the table
well sql 2k5 has a pivot keyword
int Key = (int)command.ExecuteScalar();
where command is SqlCommand object.
It doesn't work, maybe I've misunderstood the usage of ExecuteScalar.ExecuteScalar() returns the first row/first column of the resulet set. This should work IF part of the command contains something like SELECT Scope_IDentity() or SELECT @.@.IDENTITY after the insert, and the table has an IDENTITY column.sql
I have one table nameMagazine_Details,it has a composite primary key
Create table Magazine_Details(MagazineTitlevarchar(60),MagazineTypevarchar(20),IssueTypevarchar(25),IssueNumbervarchar(10),Qntyint,Qnty_in_Handint,[Date]datetimeconstraint PK_Magazine_Details_Compositeprimary key(MagazineTitle,IssueNumber))
Another table isMagazine_Issued and i want reference from Magazine_Details table. but i am auable to create foreign key constaraint inMagazine_Issued table.
create table Magazine_Issued(Member_IDvarchar(100),MagazineTitlevarchar(60)references Magazine_Details(MagazineTitle),IssueNumbervarchar(10)references Magazine_Details(IssueNumber),IssueDatedatetime,Magazine_Statusvarchar(10))
Hi,
If you need to create the foreign key constraint, you also need to add the CONSTRAINT clause and constraint name.
create table Magazine_Issued
(
Member_ID varchar(100),
MagazineTitle varchar(60) CONSTRAINT FK_MagazineTitle_Details_Issued FOREIGN KEY (MagazineTitle) references Magazine_Details(MagazineTitle),
IssueNumber varchar(10) CONSTRAINT FK_IssueNumber_Details_Issued FOREIGN KEY (IssueNumber) references Magazine_Details(IssueNumber),
IssueDate datetime,
Magazine_Status varchar(10)
)
after trying to create Magazine_Issued Table following error comes.
primary or candidate keys in the referenced table 'Magazine_Details' that match the referencing column list in the foreign key 'FK_MagazineTitle_Details_Issued'.
Could not create constraint. See previous errors.
miazaidi:
after trying to create Magazine_Issued Table with above script following error comes.
primary or candidate keys in the referenced table 'Magazine_Details' that match the referencing column list in the foreign key 'FK_MagazineTitle_Details_Issued'.
Could not create constraint. See previous errors.
I want to get the Primary Key Columns in Arrays by sending a table
name. I am using SQL Server 2000 and I want to make a find utility in VB.net which
will work for all the forms; I have tables with one Primary key and some tables with composite Primary keys.
I used to do this in VB 6 by making a function which fills the Primary Keys in
List Box (I require to fill in list box), now I need to get in array.
Can some one tell me the migration of the following VB 6 Code?
This was written for the MS Access, I need same for SQL Server, I can
not find Table Def and Index Object in VB.net 2003.
Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName As
String)
Dim lttabDef As TableDef
Dim liCounter As Integer
Dim liLoop As Integer
Dim idxLoop As Index
Dim fldLoop As Field
With ldbDatabase
For Each lttabDef In .TableDefs
If lttabDef.Name = lsTableName Then
liCounter = lttabDef.Fields.Count
For liLoop = 0 To liCounter - 1
cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name
Next liLoop
For Each idxLoop In lttabDef.Indexes
With idxLoop
lblIndexName = .Name
If .Primary Then
liCounter = 0
For Each fldLoop In .Fields
cboPrimaryKeys.List(liCounter) = fldLoop.Name
liCounter = liCounter + 1
Next fldLoop
End If
End With
Next
cboFieldLists.ListIndex = 0
If cboPrimaryKeys.ListCount > 0 Then
cboPrimaryKeys.ListIndex = 0
End If
Exit For
End If
Next
End With
End Sub
USE master|||Thanks Tmorton
EXEC sp_primarykeys @.table_server = N'LONDON1',
@.table_name = N'Customers',
@.table_catalog = N'Northwind',
@.table_schema = N'dbo'
![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_uSjcx2gn18E__T6EHF41lOZLSl7A1wmvgdVOgGA-wwiVgZxwmi2dnB2gu3ZaRrGiMIyteHX8uBMSTw4ZeoZX-642-pi3D7KXwB_B6PRtdLcG_rIQ_LUQ=s0-d)
xxx 61 maths 45
xxx 61 science 50
another table primary key ( id,language)
id language write
61 english yes
61 Hindi no
Output:
Xxx 61 maths 45 english yes
Xxx 61 maths 45 Hindi no
Xxx 61 science 50 english yes
Xxx 61 science 50 hindi no
how to join these tables to get
every information in 2 rows will it possible
xxx 61 maths 45 English yes
xxx 61 science 50 hindi no
please suggest me to right path
thanking u
please mail to me:nallisalmon@.yahoo.co.in
I don′t see the logic which one has to be excluded from the output results ? Is it just randomly or is there a deeper logic implemented ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
I try to make something or set an option, which would generate next values of the id of some table (id is primary key) while inserting a row to the table. I used once a sequence for Oracle database and wonder if there is something similar in .Net for "standard" database (Add new item -> SQL Database). I'm using SqlDataSources.
Is Repeater a component that would be useful here? Or shall i write a function that would generate ids manually?
Thanks in advance
I think what you want is an IDENTITY column. Here's a simple article with exampleshttp://www.databasejournal.com/features/mssql/article.php/3307541 and here's the more detailed stuff from MSDN http://msdn2.microsoft.com/en-us/library/aa933196(SQL.80).aspx
|||
Yes, it works now! I didn't noticed 'Column properties' in table definition ^_^
That was exactly what i was looking for - thanks!
My question is how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails.
Using TransactionScope with single connection in DataObject. I am trying to insert row in two dataTable using its own tableAdapter (two tableAdapter).
I have Product table with ProductID primary key with incriment identity. and that ProductID is used to insert row in ProductHistory Table. Lets say Product table has the last ProductID=8 (8 rows) and the next ProductID will be 9.
When I insert row in both table and if the second table insert fails both gets roll back (which is good). but when I insert again another time the Product ID=10 not 9. Is there any way to roll back the ProductID in Product table so when i insert next time it has incriment number instead of gap.
That is the nature of IDENTITY column. The number once assigned to a row is gone whether the transaction succeeds or not. Even if the transaction succeeds, if you delete the row, the number is not re-assigned. Hopefully your application is based only on the PK-FK relationship and not on the serial order of the data.
|||Thats what I thought but thought there might be a way I guess not, My table relation is based on PK-FK . It looks odd when some one is viewing Product table and has a gap in Product ID in between product (further someone might have question or confuse). I guess I have to manually create Product ID Primary Key data.
Thank for your answer
|||Just out of curiosity, why is the ProductId important? ProductId should just be a PK to identify the product, it should not matter if there are gaps.
|||For Developer standpoint it shouldnt matter at all, For other users, they would like to see incriment by 1. The product page displays Product ID, Product Name, Product Insert Date. So if they will see gap in Product ID inbetween they will have question and trust me they dont want that way, I have dealt in similar situation before.
|||So why is productId even shown? Do users care?
|||Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID
|||
rumax96:
Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID
Then a gap won't matter - unless your users remember that the product they want appeared 4 products further down a page than product 26, and therefore assume they want product 30. I know users can be extremely odd, but that seems to me to be pushing it a bit.
|||
Thanks Mike, I understand , From a developing perspective it doenst make sense wether there is a gap or not in Primary key field. The maing thing is user dont like gap in their ProductID and If that is the way the users wants then We have no choice. Like I said I have deal before in similar situation.
ID - primary key and identity field (seed 1 increment 1)
GroupID - int
Upon INSERT I want groupID to be the value of ID
The only way I can figure out how to do this (being inexperienced with sql) is to INSERT with GroupID = 0 and then do a SELECT @.iGroupID=SCOPE_IDENTITY() and then UPDATE the record. But even inside a transaction this seems error prone and not the right way. Can anyone tell me the correct way to do this. Thank you in advance.You can try using a trigger.
|||I hope you are using SQL Server 2000 ... If that being the case we can use UDFs to accomblish this task ... A typical code snippet for the same looks like below:
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[GroupID] [int] NOT NULL ,
[AnotherColumn] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]CREATE TRIGGER [InsertTrigger] ON [dbo].[Table1]
AFTER INSERT
AS
declare theCursor cursor for SELECT ID FROM inserted
open theCursor
declare @.ThisID int
select @.ThisID = -1
while 1 = 1
begin
fetch next from theCursor into @.ThisID
if @.@.fetch_status <> 0
break
Update Table1
Set GroupID = @.ThisID
WHERE ID=@.ThisID
end
close theCursor
deallocate theCursor
CREATE FUNCTION dbo.GetIdentity() RETURNS INT AS
BEGIN
RETURN (IDENT_CURRENT('vin_test'))
END
CREATE TABLE vin_test
(
colAINT IDENTITY(1,1) NOT NULL,
colBINT DEFAULT dbo.GetIdentity(),
colCVARCHAR(10)
)
INSERT INTO vin_test (colC) VALUES ('Test')
SELECT * FROM vin_test|||Yes the ISP is server 2000 and my MSDE seems to support functions but prompts me to select from Inline, Table-values, Scalar-valued, can you please tell me which to select. Also I have about 20 tables I need to do this with. Do I need to write a function for each table?
THANKS!|||Yes, we need to create one for each table. I am sure it is better than creating 10 triggers ... :)
Select the scalar-valued function ...|||My final question:
Is the UDF better/safer/more stable/reliable then what I am doing in the stored procedure where inside a transaction I insert, get the scope_identity and then update the just inserted record. I understand my (hack/kludge?) method may take more server processing but I besides that I am wondering if it is any less reliable then the UDF method. (last question :) again, thanks.|||I can say it with certain that UDFs are quite stable ... There is no second thoughts on that statement. We have used this in our production code and have tested the same with concurrent users ... Works like a gem ... :) ...
Hmm ... the second part of the question has a couple of interesting points to backup:
1. Keep the transaction short. Which means if you have a big routine to do the same it is quite not advisible.
2. CPU cycles are not cheaper and so is memory. If you were to use SPs in such a fashion then we are using both ... :) ... And I dont recommend the same strongly ...
I know this is a delicate topic to answer and can raise a couple of eye-brows who are reading this ... But these are my views on the same. I am open to hear from others if they think otherwise ...|||stored procedures return only integers...UDF can return other datatypes like float...etc
other than that i guess both are similar...in terms of security/performance...etc|||Hmm ... Stored Procedures can also return more than one value using the OUTPUT parameter types ... And I feel that we can do things in UDF that are quite not possible with Stored Procedures ... Read my article on UDF at :http://www.extremeexperts.com/sql/articles/UDFFunctions.aspx ...
And for the present problem description ... I feel UDF is a cleaner and neater way rather than the conventional UDF approach ...
