ADO & SQL Mystery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ADO & SQL Mystery

Author

Topic

payntm
Starting Member

18 Posts

Posted - 2006-02-26 : 14:49:18

Hello all,

I've run into a problem while developing an ASP application and I am absolutely stumped. I can attach some code later but think it's probably better to just explain the situation first.

I build a form dynamically, fill out the form and then submit. After I submit (and post back to the same page), I start a transaction and then use ADO to call 1 stored procedure to insert 1 record into a table. Then I loop through all the form fields posted back and for each form field that has data in it, I dynamically crate the parameters and call a 2nd stored procedure to insert that record into a 2nd table. So, there could be a varied number of inserts for the 2nd table. Let's assume that the sql statements are correct (they are) and my table primary keys are setup right (they are). I am getting a primary key violation on both tables when I try to insert, even when there are NO RECORDS in the 2 tables. Also, there are no constraints (right now) on these 2 tables.

My question(s) are: A) How can that happen? B) Even when I get that error, the records ARE INSERTED into both tables correctly? C) When I do get the error, even though I shouldn't..it still tries to rollback the transaction like it should but it is not rolling back? Just to add a little more confusion, these inserts work perfectly in Mozilla/Netscape, but do not work with IE. Actually, I'd say 1 out of 3 or 4 times IE will give me this error?

For the record, I used ad-hoc sql queries before using stored procedure and received the same results. I also got rid of the transaction and still nothing.

Does anyone have a clue as to what might be going on here?

Thanks in advance,
Mike

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-26 : 20:27:11

Dammit, I posted a bit of an answer here and the forum ate it

Anyway, run SQL Profiler while you try to save your form data. That will show you exactly what is being sent to SQL Server and might give you some clues.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

payntm
Starting Member

18 Posts

Posted - 2006-02-26 : 21:07:43

Thanks for the reply: I did just run Profiler and it did everything it should...but I got the error (when I shouldn't have)...but it did call the rollback transaction method, but didn't roll back and it did insert the records. All info being passed in seems to be correct and it isn't duplicating records to justify the primary key violation error. I've attached the profiler data from an insert where I get the error. The 1st stored proc call (exec InsertFESheet) is where it says its a duplicate key. I promise you it is not a duplicate. The primary key is the 1st 3 fields shown ('C3', '1', '02/26/2006')

======================================================

SQL:BatchCompleted set implicit_transactions on Microsoft® Windows® Operating System Mike Paynter 0 0 0 0 2400 54 2006-02-26 19:53:56.403

RPC:Completed exec InsertFESheet 'C3', '1', '02/26/2006', 1, NULL, 'Feb 26 2006 7:53PM', 'Feb 26 2006 7:53PM', 'TTT'

Microsoft® Windows® Operating System Mike Paynter 0 2 0 0 2400 54 2006-02-26 19:53:56.403

RPC:Completed exec InsertLostTime 'C3', '1', '02/26/2006', 1, 150, 100

Microsoft® Windows® Operating System Mike Paynter 0 2 0 0 2400 54 2006-02-26 19:53:56.403

RPC:Completed exec InsertLostTime 'C3', '1', '02/26/2006', 1, 153, 1
Microsoft® Windows® Operating System Mike Paynter 0 2 0 0 2400 54 2006-02-26 19:53:56.403

RPC:Completed exec InsertLostTime 'C3', '1', '02/26/2006', 1, 151, 99

Microsoft® Windows® Operating System Mike Paynter 0 2 0 16 2400 54 2006-02-26 19:53:56.403

RPC:Completed exec InsertLostTime 'C3', '1', '02/26/2006', 1, 152, 98 Microsoft® Windows® Operating System Mike Paynter 0 2 0 0 2400 54 2006-02-26 19:53:56.420

SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRAN Microsoft® Windows® Operating System Mike Paynter 0 0 0 0 2400 54 2006-02-26 19:53:56.420

SQL:BatchCompleted set implicit_transactions off

Microsoft® Windows® Operating System Mike Paynter 0 0 0 0 2400 54 2006-02-26 19:53:56.420

RPC:Completed exec SelectTimeFrames '1'

Microsoft® Windows® Operating System Mike Paynter 0 10 0 0 2400 54 2006-02-26 19:53:56.420

SQL:BatchCompleted SelectCategoryGroups

Microsoft® Windows® Operating System Mike Paynter 0 2 0 0 2400 54 2006-02-26 19:53:56.420

RPC:Completed exec SelectCategories 1

Microsoft® Windows® Operating System Mike Paynter 0 2 0 16 2400 54 2006-02-26 19:53:56.420

Audit Login -- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
M

icrosoft® Windows® Operating System Mike Paynter 2400 55 2006-02-26 19:53:56.437

RPC:Completed exec SelectOOSCode 1

Microsoft® Windows® Operating System Mike Paynter 0 4 0 0 2400 55 2006-02-26 19:53:56.437

RPC:Completed exec SelectOOSCode 2

Microsoft® Windows® Operating System Mike Paynter 0 14 0 0 2400 55 2006-02-26 19:53:56.437

RPC:Completed exec SelectOOSCode 3

Microsoft® Windows® Operating System Mike Paynter 0 8 0 0 2400 55 2006-02-26 19:53:56.437

RPC:Completed exec SelectOOSCode 4

Microsoft® Windows® Operating System Mike Paynter 0 14 0 0 2400 55 2006-02-26 19:53:56.437

RPC:Completed exec SelectOOSCode 5

Microsoft® Windows® Operating System Mike Paynter 0 14 0 13 2400 55 2006-02-26 19:53:56.437

RPC:Completed exec SelectOOSCode 6

Microsoft® Windows® Operating System Mike Paynter 0 38 0 0 2400 55 2006-02-26 19:53:56.450

Audit Logout

Microsoft® Windows® Operating System

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-27 : 08:53:21

Well, I have bad news for you: You can promise everything is working perfectly all you want, but we can't help you unless we can see the relevant code. Even better, if you can recreate this scenerio on a smaller scale and send us some statements so that we can also recreate this on our end, we can help troubleshoot.

Kristen
Test

22859 Posts

Posted - 2006-02-27 : 09:06:03

"but didn't roll back and it did insert the records"

BEGIN TRANSACTION
GO
... (1)OK Statements ...
GO
--- (2)DUFF statement ---
GO
... (3)OK Statements ...
GO
COMMIT
GO

is liable to cause a ROLLBACK between (2) and (3), and the all statements in (3) will be running outside the transaction block - maybe that's what's happening to you? Probably wise to double check that you catch, and handle, and errors in the individual calls you make to SQL Server.

"The primary key is the 1st 3 fields shown ('C3', '1', '02/26/2006')"

What's different between these 4 statements then? Of have I misunderstood?


RPC:Completed exec InsertLostTime 'C3', '1', '02/26/2006', 1, 150, 100 

RPC:Completed exec InsertLostTime 'C3', '1', '02/26/2006', 1, 153, 1 

RPC:Completed exec InsertLostTime 'C3', '1', '02/26/2006', 1, 151, 99 

RPC:Completed exec InsertLostTime 'C3', '1', '02/26/2006', 1, 152, 98

"You can promise everything is working perfectly all you want"

Always a dangerous assertion to make on here - as I have discovered to me cost more than once

Kristen

payntm
Starting Member

18 Posts

Posted - 2006-02-27 : 11:57:08

Okay...you're right. Clearly it is not working perfectly. I just meant that the insert statements were being dynamically created correctly, and when I run them in query analyzer they insert with no error. Also, my tables appear to be built correctly. So something else is happening within the scope of the application.

Secondly, let me clarify somethings I think I didn't explain as well before. I will post some relevant code to back up my situation.

I begin a transaction within my asp app, then I used 1 stored procedure called InsertFESheet to insert data into 1 table. Here is an example:

exec InsertFESheet 'C3', '1', '02/26/2006', 1, NULL, 'Feb 26 2006 7:53PM', 'Feb 26 2006 7:53PM', 'TTT'
(The 1st 3 parameters are the primary key to the table)

Then I loop through all the form fields posted back and for each form field that has data in it, I call a 2nd stored procedure called InsertLostTime. Here is an example from before:

exec InsertLostTime 'C3', '1', '02/26/2006', 1, 150, 100
(1st 5 parameters are the primary key to the table)

So there is always going to be 1 insert from calling the 'InsertFESheet' stored proc and 0 to many inserts from calling the 'InsertLostTime' stored proc. Then I check for errors and either commit or rollback the transaction....I need to do all these inserts as 1 transaction. Also to mention, I added transactions within the stored proc's themselves so I have nested transactions. Not sure what other way to handle that situation. I could see how if more than 1 of the InsertLostTime functions fail that it would rollback the entire outer transaction...but the inserts are still being performed? Anyways, here's some of my code (minus some of the obvious connection stuff, etc). I also have some update functionality, but that seems to always work so just ignore that:

Sub updateDB()
Dim message, arrayCounter, SplitID

On Error Resume Next

'Start a transaction
objConn.BeginTrans

'Determine if we're inserting or updating FE_SHEET table based on querystring 'action' parameter
If (Request.QueryString("action") = "Create New") Then
InsertFESheet()
message = "Record created"
Else
UpdateFESheet()
message = "Record updated"
End If

'Loop through all form items to get data textboxes we want to store
For Each Item in Request.Form
For iCount = 1 to Request.Form(Item).Count
SplitID = Split(Item, "_") 'Getting each form's id (we only want the one's with NUMBER '_' NUMBER. (i.e. 1_150)
If (UBound(SplitID) = 1) Then 'Only store data for the input id's that contain NUMBER_NUMBER (i.e. 1_355 -- TimeframeID_OOSCode)
If IsNumeric(SplitID(0)) Then 'valid TimeFrameID
If IsNumeric(SplitID(1)) Then 'valid OOSCode
If Not Request.Form(Item)(iCount) = "" Then 'only store actual data...not any blank fields
If (Request.QueryString("action") = "Create New") Then 'Check if we're in 'create' mode
call InsertLostTime(SplitID(0), SplitID(1), Request.Form(Item)(iCount)) 'insert data into LOSTTIME table
Else 'We're in 'retrieve' mode
call SelectLostTimeDataByTimeFrameIDAndOOSCode(SplitID(0), SplitID(1))
If objRS.eof then 'No entry in db for this TimeframeID and OOSCode, we have to insert it
objRS.close
call InsertLostTime(SplitID(0), SplitID(1), Request.Form(Item)(iCount)) 'insert data into LOSTTIME
Else 'This is in the db...update the record objRS.close
call UpdateLostTime(Request.Form(Item)(iCount), SplitID(0), SplitID(1)) 'update data in LOSTTIME table
End If
End If
End If
End If
End If
End If
Next
Next

'If there were errors rollback transaction, otherwise commit transaction. Display success/failure message
If ErrorsFound(objConn) = True Then objConn.RollbackTrans
Response.Write("<td class='messageCell' width='50%'>Error updating record!</td><td width='25%'> </td></tr>")
Else
objConn.CommitTrans
Response.Write("<td class='messageCell' width='50%'>" & message & "</td><td width='25%'> </td></tr></table>")
End If
End Sub

Sub InsertFESheet()
clearParameters(objCommand) 'Clear old command parameters and setup command object for stored procedure call
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "InsertFESheet"
objCommand.Parameters.Append objCommand.CreateParameter("@LineNum", adVarChar, adParamInput, 3, Request.Querystring("selLineNum"))
objCommand.Parameters.Append objCommand.CreateParameter("@Shift", adVarChar, adParamInput, 1, Request.Querystring("selShift"))
objCommand.Parameters.Append objCommand.CreateParameter("@Date", adVarChar, adParamInput, 10, Request.Querystring("txtDate"))
objCommand.Parameters.Append objCommand.CreateParameter("@CategoryGrp", adTinyInt, adParamInput, 1, 1)
objCommand.Parameters.Append objCommand.CreateParameter("@UpdateUser", adVarChar, adParamInput, 3, NULL)
objCommand.Parameters.Append objCommand.CreateParameter("@LastUpdate", adDBTimeStamp, adParamInput, 1, Now)
objCommand.Parameters.Append objCommand.CreateParameter("@CreateDate", adDBTimeStamp, adParamInput, 1, Now)
objCommand.Parameters.Append objCommand.CreateParameter("@CreateUser", adVarChar, adParamInput, 3, Request.Form("txtOperatorInitials"))
objCommand.execute
For Each errorObject In objCommand.ActiveConnection.Errors
displayError(errorObject)
Next
End Sub

Sub InsertLostTime(TFID, Code, Data)
clearParameters(objCommand) 'Clear old command parameters and setup command object for stored procedure call
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "InsertLostTime"
objCommand.Parameters.Append objCommand.CreateParameter("@LineNum", adVarChar, adParamInput, 3, Request.Querystring("selLineNum"))
objCommand.Parameters.Append objCommand.CreateParameter("@Shift", adVarChar, adParamInput, 1, Request.Querystring("selShift"))
objCommand.Parameters.Append objCommand.CreateParameter("@Date", adVarChar, adParamInput, 10, Request.QueryString("txtDate"))
objCommand.Parameters.Append objCommand.CreateParameter("@TimeFrameID", adTinyInt, adParamInput, 2, cInt(TFID))
objCommand.Parameters.Append objCommand.CreateParameter("@OOSCode", adSmallInt, adParamInput, 3, cInt(Code))
objCommand.Parameters.Append objCommand.CreateParameter("@LostTime", adInteger, adParamInput, 4, cInt(Data))
objCommand.execute
For Each errorObject In objCommand.ActiveConnection.Errors
displayError(errorObject)
Next
End Sub

***** AND FINALLY *** THE 2 STORED PROCEDURES

CREATE PROCEDURE dbo.InsertFESheet
(
@LineNum as varchar(3),
@Shift as varchar(1),
@Date as varchar(10),
@CategoryGrp as tinyint,
@UpdateUser as varchar(3),
@LastUpdate as datetime,
@CreateDate as datetime,
@OperatorInitials as varchar(3)
)
AS

SET NOCOUNT ON
BEGIN TRANSACTION
IF EXISTS (SELECT LineNum, Shift, [Date] FROM FE_SHEET WHERE
LineNum = @LineNum And Shift = @Shift And [Date] = @Date)
BEGIN
ROLLBACK TRANSACTION
RAISERROR(' A filler sheet with these parameters already exists in the database',10,1)
RETURN
END

IF @UpdateUser = NULL
BEGIN
Set @UpdateUser = ''
INSERT INTO FE_SHEET VALUES(@LineNum, @Shift, @Date, @CategoryGrp, @UpdateUser , @LastUpdate, @CreateDate, @OperatorInitials)
END
ELSE
BEGIN
INSERT INTO FE_SHEET VALUES(@LineNum, @Shift, @Date, @CategoryGrp, @UpdateUser , @LastUpdate, @CreateDate, @OperatorInitials)
END
COMMIT TRANSACTION
GO

CREATE PROCEDURE dbo.InsertLostTime
(
@LineNum as varchar(3),
@Shift as varchar(1),
@Date as varchar(10),
@TimeframeID as tinyint,
@OOSCode as smallint,
@LostTime as int
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION
IF EXISTS
(SELECT LineNum, Shift, [Date], TimeframeID, OOSCode FROM LOSTTIME
WHERE LineNum = @LineNum And Shift = @Shift And [Date] = @Date AND OOSCode = @OOSCode AND LostTime = @LostTime)
BEGIN
ROLLBACK TRANSACTION
RAISERROR(' There is already lost time data with these parameters in the database',10,1)
RETURN
END

INSERT INTO LOSTTIME VALUES(@LineNum, @Shift, @Date, @TimeframeID, @OOSCode, @LostTime)
COMMIT TRANSACTION
END
GO

Kristen
Test

22859 Posts

Posted - 2006-02-27 : 12:22:23

" I could see how if more than 1 of the InsertLostTime functions fail that it would rollback the entire outer transaction...but the inserts are still being performed?"

Depends if you have referential integrity to prevent it, but otherwise once the outer transaction is rolled back then any subsequent inserts will be outside the scope of a TRANSACTION block - so when you finally do the COMMIT (or the ROLLBACK

) you will get an error that there is NO current transaction block!

Having said that it sounds that your method is fine - you do need to do the looping and send each transaction to the SProcs as you have described (there may be other ways to do this, but I don't think they are likely to be any "better")

"On Error Resume Next"

Not a good plan - ANY error (including syntax error!) is going to be ignored, a s will any errors in any called SUB/FUNCTIONs

"If ErrorsFound(objConn) = True Then objConn.RollbackTrans"

I expect you need to check ErrorsFound(objConn) after EVERY call to the Database, not just at the end (I don't for sure, but I expect ADO will throw away its errors when you call it to process the next command)

Kristen

payntm
Starting Member

18 Posts

Posted - 2006-02-27 : 12:46:33

I may not be handling the errors the right way, but after each insert/update stored procedure call I do call:

For Each errorObject In objCommand.ActiveConnection.Errors
displayError(errorObject)
Next

And this does catch the RAISEERROR messages that I return from my stored procedures.

As far as the transactions. I agree about it rolling back the entire transaction if more than 1 of the inserts fails, however, it doesn't rollback and the records are still inserted and there are no transaction errors...just the primary key violation error, which is puzzling. I noticed when I stripped things way down and just called 1 insert into the 1st table and 1 insert into the 2nd table, it did not complain...even if I forced it to insert duplicate records...it would fail and rollback properly. Once I got involved with this looping and calling multiple inserts into table 2 is where it started acting up. I'm not sure how else to do it or why that would cause an issue though....even without transactions being involved????

Kristen
Test

22859 Posts

Posted - 2006-02-27 : 13:29:11

"after each insert/update stored procedure call I do call"

Sorry, missed that. Get rid of the ON ERROR RESUME NEXT (at least during your debugging) and see if it raises an error for any other reason.

"it doesn't rollback"

How do you know? ... ->

"it would fail and rollback properly"

Was your test a single multi-statement block, or multiple separate statements?

There's a difference between

BEGIN TRANSACTION
GO
... (1)OK Statements ...
GO
--- (2)DUFF statement ---
GO
... (3)OK Statements ...
GO
ROLLBACK
GO

and

BEGIN TRANSACTION
GO
... (1)OK Statements ...
--- (2)DUFF statement ---
... (3)OK Statements ...
GO
ROLLBACK
GO

In the second one the whole of the remaining block will be aborted, and then the ROLLBACK will fire.

If you are calling SQL Server in a loop from your application you are most likely emulating the first example, and not the second one!

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-27 : 13:47:08

It looks like you are doing a lot more work in your stored procs than you need to. Why are you testing for RI and then raising errors manually? If a failed call to the proc is going to produce an error, why not just add the data and if it violates RI, let SQL Server return the error?

Also, we need CREATE TABLE statement for you two tables. What is the PK of each table?

payntm
Starting Member

18 Posts

Posted - 2006-02-27 : 14:30:08

I guess I added the extra work in the stored proc because I wanted it to return a specific error message. Just to clarify. Currently there is a foreign key constraint. But that isn't the issue because I didn't have it initially and it was still having this issue. I added it because it should be there...(user should not be able to insert into table 2 if there is no record in table 1)

Here is the Create Table scripts:

1st table (Parent)
===================
CREATE TABLE [dbo].[FE_SHEET] (
[LineNum] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Shift] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CategoryGrp] [tinyint] NOT NULL ,
[UpdateUser] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [datetime] NULL ,
[CreateDate] [datetime] NOT NULL ,
[CreateUser] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FE_SHEET] WITH NOCHECK ADD
CONSTRAINT [DF_FE_SHEET_CategoryGrp] DEFAULT (1) FOR [CategoryGrp],
CONSTRAINT [PK_FE_SHEET] PRIMARY KEY CLUSTERED
(
[LineNum],
[Shift],
[Date]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FE_SHEET] ADD
CONSTRAINT [FK_FE_SHEET_CATEGORYGRP_REF] FOREIGN KEY
(
[CategoryGrp]
) REFERENCES [dbo].[CATEGORYGRP_REF] (
[CategoryGrp]
)
GO

2nd table (Child)
===================

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LOSTTIME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LOSTTIME]
GO

CREATE TABLE [dbo].[LOSTTIME] (
[LineNum] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Shift] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TimeframeID] [tinyint] NOT NULL ,
[OOSCode] [smallint] NOT NULL ,
[LostTime] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LOSTTIME] WITH NOCHECK ADD
CONSTRAINT [PK_LOSTTIME] PRIMARY KEY CLUSTERED
(
[LineNum],
[Shift],
[Date],
[TimeframeID],
[OOSCode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LOSTTIME] ADD
CONSTRAINT [FK_LOSTTIME_FE_SHEET] FOREIGN KEY
(
[LineNum],
[Shift],
[Date]
) REFERENCES [dbo].[FE_SHEET] (
[LineNum],
[Shift],
[Date]
),
CONSTRAINT [FK_LOSTTIME_OOSCODES_REF] FOREIGN KEY
(
[OOSCode]
) REFERENCES [dbo].[OOSCODES_REF] (
[OOSCode]
),
CONSTRAINT [FK_LOSTTIME_SHIFT] FOREIGN KEY
(
[TimeframeID]
) REFERENCES [dbo].[SHIFT] (
[TimeframeID]
)
GO

payntm
Starting Member

18 Posts

Posted - 2006-02-27 : 14:42:59

FYI. Here's the error I get when I comment out the On Error Resume Next statement:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_FE_SHEET'. Cannot insert duplicate key in object 'FE_SHEET'.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-27 : 15:15:51

TimeFrameID is part of your PK for your LOSTTIME table. Are you passing in inique values for that column? It looks like you are just using

Request.QueryString("selLineNum")

which is a constant for the entire page. So each call to the proc will use the same value, thus violating the PK constraint.

payntm
Starting Member

18 Posts

Posted - 2006-02-27 : 16:10:40

Actually,

Table LOSTTIME
===========================
LineNum (Primary Key)
Shift (Primary Key)
[Date] (Primary Key)
TimeframeID (Primary Key)
OOSCode (Primary Key)
LostTime

Table FE_SHEET
=========================
LineNum (Primary Key)
Shift (Primary Key)
[Date] (Primary Key)
CategoryGrp
UpdateUser
LastUpdate
CreateDate
CreateUser

*** Each record in the LOSTTIME table will have values for LineNum, Shift, and [Date] that will correspond to 1 entry in the FE_SHEET table. The TimeframeID can be the same for multiple records in LOSTTIME, the uniqueness comes from the OOSCode field. So there will be multiple records that have the exact same LineNum, Shift, [Date], and TimeframeID....but with the addition of the OOSCode, that key is unique.

Hope that explains it better.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-27 : 16:40:34

My bad, I missed the last column in the constraint.

Unrelated advice, but you really should use proper datatypes in your database if possible. Try not to store dates in a varchar() column.

payntm
Starting Member

18 Posts

Posted - 2006-02-27 : 16:43:48

Yes I know that some of the data types really should be something other than what they are....but I was developing as I went along. I can always change them pretty easily later when everything else works.

Kristen
Test

22859 Posts

Posted - 2006-02-27 : 17:38:32

"I can always change them pretty easily later when everything else works"

So long as you have a completely automated testing system!

Kristen

payntm
Starting Member

18 Posts

Posted - 2006-02-27 : 18:08:36

We're getting away from the actual problem I'm trying to solve.

Kristen
Test

22859 Posts

Posted - 2006-02-27 : 19:04:28

Actually I think its a valid point. If you do all your development and testing such that everything works, and then change the datatype to Date, then you will have to retest everything - and if something doesn't work you may have a lot of rebuilding to do to accommodate the datatype change retrospectively

Kristen

payntm
Starting Member

18 Posts

Posted - 2006-02-27 : 19:17:02

Point taken...but I'd just have to change the database type along with the stored proc input variable...and then modify the parameter passed in to the stored proc to match the new data type. ..It's really not that big a deal.....This isn't a huge project...

But either way. If we can get the existing code working, I'll worry about that change later.

payntm
Starting Member

18 Posts

Posted - 2006-03-01 : 12:44:44

Any other ideas? I'm still struggling with this issue.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources