Determine Next Available Order ID

I am trying to determine the next available order id using the method below. It works provided the table has a record in it. If it doesn't I get the error "Input string was not in a correct format." I am certain that it is because the query is returning a value of NULL. How can get around that or check for the NULL value?

' Establish data connection...

Dim sqlConnAsNew SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))

'Determine order id number...

Dim order_idAsInteger

Dim strSQLAsString

strSQL = "Select MAX(order_id) from mkt_order"

Dim sqlCmdAsNew SqlCommand(strSQL, sqlConn)

Dim sqlDAAsNew SqlDataAdapter(sqlCmd)

Dim sqlDSAsNew DataSet

sqlDA.Fill(sqlDS, "item")

If sqlDS.Tables(0).Rows.Count <> 0Then

order_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1

Else

order_id = 1

EndIf

This is because a DBNull will be returned if you use MAX() function and there is no (qualified) record in the table. Let's try TOP...ORDER BY... to avoid using MAX():

strSQL = "Select TOP 1 order_id from mkt_order ORDER BY order_id DESC"

determine if temp table exists

I need to determine a way to search if a tempory table exist before atteptin
g
to read a record from it.
The only way I know of is to perform the following:
if exists(select * from tempdb..sysobjects where name like '#tablename%'
and type='U')
However, if there are multiple sessions on the same machine, this may pass
but the current session doesn't have an existing temporary table. Is there
a
way to determine that a temporary table listed in sysobjects is for the
current session?Try:
IF OBJECT_ID('tempdb..#tablename') IS NOT NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:12A99761-D2FD-4CE8-A6C5-DFA8673B5D9D@.microsoft.com...
> I need to determine a way to search if a tempory table exist before
attepting
> to read a record from it.
> The only way I know of is to perform the following:
> if exists(select * from tempdb..sysobjects where name like '#tablename%'
> and type='U')
> However, if there are multiple sessions on the same machine, this may pass
> but the current session doesn't have an existing temporary table. Is
there a
> way to determine that a temporary table listed in sysobjects is for the
> current session?|||if object_id('tempdb..#tmp','U') is not null
print('#tmp exists')
else
print('#tmp does not exist')
-oj
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:12A99761-D2FD-4CE8-A6C5-DFA8673B5D9D@.microsoft.com...
>I need to determine a way to search if a tempory table exist before
>attepting
> to read a record from it.
> The only way I know of is to perform the following:
> if exists(select * from tempdb..sysobjects where name like '#tablename%'
> and type='U')
> However, if there are multiple sessions on the same machine, this may pass
> but the current session doesn't have an existing temporary table. Is
> there a
> way to determine that a temporary table listed in sysobjects is for the
> current session?

detecting Update Conflict

When a user try to update the record I use timestamp field (upsize_ts) to
figure out if data was modified by other user.
However I found out @.@.DBTS may cause problem in Multi user senario and may
not return correct Tiemstamp value as it is not the timestamp for the curren
t
operation but for the database is there any inexpensive solutin to resolve
this.
Thanks
Tanweer
CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
@.rowversion timestamp OUTPUT,
@.people_phone_id int,
@.offsysid varchar(5),
@.people_id int=Null,
@.sequence_id smallint=Null,
@.code_type varchar(5)=Null,
@.code_value varchar(50)=Null,
@.modify_operator varchar(15)=Null,
@.upsize_ts timestamp=Null
AS
SET NOCOUNT OFF
DECLARE @.errormsg varchar(1000)
DECLARE @.errcode int
DECLARE @.rowcount int
DECLARE @.modify_date datetime
SET @.modify_date = GetDate()
UPDATE [dbo].[PEOPLE_PHONE] SET
[people_id] = case when @.people_id is null then people_id else @.people_id
end,
[sequence_id] = case when @.sequence_id is null then sequence_id else
@.sequence_id end,
[code_type] = case when @.code_type is null then code_type else @.code_type
end,
[code_value] = case when @.code_value is null then code_value else
@.code_value end,
[modify_date] = case when @.modify_date is null then modify_date else
@.modify_date end,
[modify_operator] = case when @.modify_operator is null then modify_operator
else @.modify_operator end
WHERE
[people_phone_id] = @.people_phone_id
AND [offsysid] = @.offsysid
AND [upsize_ts] = @.rowversion
SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
--Use it to check the value of Timestamp column
--print 'Timestamp' + convert(varchar(50),@.rowversion)
select @.rowversion
IF(@.errcode = 0)
BEGIN
IF(@.RowCount = 0)
BEGIN
IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
@.people_phone_id AND [offsysid] = @.offsysid)
BEGIN
RAISERROR('Row not updated because it was changed', 15, 1)
RETURN 50003
END
ELSE
BEGIN
RAISERROR('Row is gone', 15, 1)
RETURN 50004
END
END
ELSE
PRINT 'Row updated'
END
ELSE
PRINT 'Error'
RETURN @.errcode
GOeasist of all..
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
ALL OPERATIONS
COMMIT TRAN
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||A doubt, why do you bother in this scnario. There is only one update and no
select and no transaction. You will never get an erronous update in this cas
e.
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||Don't use @.@.dbts, but rather retrieve the actual value for the updated row.
If more than one row is updated at a time, then you'd have to store the
values in a temporary table (e.g. a table variable) or use the OUTPUT clause
in SQL 2005.
ML
http://milambda.blogspot.com/|||I guess I have to go with ML syggestion there is no easy way out.
May be a wish for SQL 2005 Service Pack2
Thanks
Tanweer
"ML" wrote:

> Don't use @.@.dbts, but rather retrieve the actual value for the updated row
.
> If more than one row is updated at a time, then you'd have to store the
> values in a temporary table (e.g. a table variable) or use the OUTPUT clau
se
> in SQL 2005.
>
> ML
> --
> http://milambda.blogspot.com/|||are you using sql server 2005'
--
"Tanweer" wrote:
> I guess I have to go with ML syggestion there is no easy way out.
> May be a wish for SQL 2005 Service Pack2
> Thanks
> Tanweer
> "ML" wrote:
>

Detecting of record does not work in if query

Edit: Newer mind. I tested this query more after writing this, and now it seems to work!

I hope it continues to work.

In following query, else is never executed.

CREATE PROCEDURE Put_into_basket
( @.Product_code varchar(20))
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@.Product_code)
BEGIN
INSERT dbo.t_shopping_basket (Product_code, Name,Price)
SELECT Product_code, Name,Price
FROM dbo.t_product
WHERE Product_code= @.Product_code
END
ELSE --this part is never executed
BEGIN
UPDATE dbo.t_shopping_basket
SET Quantity=Quantity+1
WHERE Product_code=@.Product_code
END
END
GO

The query should test if there is a record or row with Product_code=@.Product_code.

If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of.

If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.

But when I test query, it never runs the quantity+1 part.

This is the code that I am using to do the same function. I use your method in other queries but also could not get it working for the cart. In my mind it was like the if exist can only take one other arguement either the update or the insert into but not both. So I changed mine around to this I also have to track another variable as there are different files they can access.

1SELECT2 @.CountItems = Count(ProductID)3FROM4 ShoppingCart5WHERE6 ProductID = @.ProductID7 AND8 CartID = @.CartID9 AND10Filenumber = @.FileNumber1112IF @.CountItems > 01314 UPDATE15 ShoppingCart16 SET17 Quantity = (@.Quantity + ShoppingCart.Quantity)18 WHERE19 ProductID = @.ProductID20 AND21 CartID = @.CartID22 AND23FileNumber = @.FileNumber2425ELSE26 INSERT INTO ShoppingCart27 (28 CartID,29 Quantity,30 ProductID,31FileNumber32 )33 VALUES34 (35 @.CartID,36 @.Quantity,37 @.ProductID,38@.FileNumber39 )

|||

Thanks, I'll keep this in mind. Expesially because my version didn't work at first. Hopefully it was my error and not something which may happen again.

I'm quite new at SQL, your code has some interesting features. Count(ProductID) for instance. You have all carts in same table?

Out of curiosity, where do need FileNumber field. That is, is specific to your solution?

Regards

Leif

|||

Yea it is specitic to my cart. They can buy the same report but covering different info. Not wanting to put a couple thousand individual prices in and having to update every time they added to the site, I have one price for each different report and then tell which report they bought. So the cart could show report1 several times and each one would have a different file number. If they buy something that is not related to a filenumber like a subscription then I just put a 0 in that field.

I use one table for the cart and if they buy, then I move the contents over to the order tables (2 tables) and delete the items from the cart. So that table should stay fairly small.

sql

Detect Record Conflict:

In a table or 'shifts' for employees i'm trying to test a proposed new shift
for conflicts, the employee's shifts must have 12 hours between them,
This makes sense to me but, is it going to be efficiant considering the
table could have ten's of thousands of shifts.
IF EXISTS
(
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid = @.NEW_OPID AND
(R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
R.StartTime < DATEADD(hh, 12, @.NEW_END))
)
My basic concern is the use of the criteria on two seperate fields: START &
END is it possible to use indexes to maintain performance?
Kind regards,
Graeme.You can definitely increase performance by having, clustered index on
operatorid = and non clustered indedx on EndTime and Start Time
USE BETWEEN -AND so that the = is also taken into consideration
Regards
R.D
IF EXISTS
(
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid = @.NEW_OPID AND
(R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
R.StartTime < DATEADD(hh, 12, @.NEW_END))
)
--
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> In a table or 'shifts' for employees i'm trying to test a proposed new shi
ft
> for conflicts, the employee's shifts must have 12 hours between them,
> This makes sense to me but, is it going to be efficiant considering the
> table could have ten's of thousands of shifts.
> IF EXISTS
> (
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid = @.NEW_OPID AND
> (R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
> R.StartTime < DATEADD(hh, 12, @.NEW_END))
> )
> My basic concern is the use of the criteria on two seperate fields: START
&
> END is it possible to use indexes to maintain performance?
>
> Kind regards,
> Graeme.
>
>|||Hi R.D.
thanks for your response.. does this make sense?
Assumeing I calculate 12h before the proposed shifts start as 'A' and
12 after the proposed shifts end as 'B'
Method 1:
if the DB looks for shifts with a START < B
and END > A [this is simple criteria but would it cause massive table
scaning to determine results, it woiuld also locate very long shifts (months
even) that conflicted]
it would have to check every shift that starts before B to see if the shift
ends after A would this be done using two (seperatoe) index ss [on two
differnt DB Fields, (START & END) and then find matchese.
Method 2:
I''m going to have to assume the shift can't be more than 12h (or say 24 to
be safe) and then use BETWEEN on START AND BETWEEN on END.
for instance:
... WHERE ... ( R.EndTime BETWEEN A and B+24 AND
R.StartTime BETWEEN A-24 and B)....
Notice the 24 hours beeing added/subtracted to limit the window of matched
records. as I'm restricting the results by OperatorID i'm only going to
expec that 90% of the time the DB will find no CONFLICTS and 10% on 1
CONFLICT, more than 1 conflicts should be very rare!
Is this sort of thing necessary or does the Query Optimizer do this
automaticly? somehow?
Thanks for you help so far,
Kind regards,
Graeme.
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:0A5159C7-A1D2-4469-9417-84A9DDEDA3ED@.microsoft.com...
> You can definitely increase performance by having, clustered index on
> operatorid = and non clustered indedx on EndTime and Start Time
> USE BETWEEN -AND so that the = is also taken into consideration
> Regards
> R.D
> IF EXISTS
> (
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid = @.NEW_OPID AND
> (R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
> R.StartTime < DATEADD(hh, 12, @.NEW_END))
> )
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Graeme Stow" wrote:
>|||Graeme Stow
Query optimiser generally does everything if you have indexes. However the
final selection can be made only by analysing the execution plans. When you
have index on that, It would not choose Table scan.
Try different combination and select the best one using execution plan.
Thumb rule is thst execution plan should avoid( unless you are selecting
everything) table scan, book marks, filters and should use Index s or
clustered s.
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> Hi R.D.
> thanks for your response.. does this make sense?
> Assumeing I calculate 12h before the proposed shifts start as 'A' and
> 12 after the proposed shifts end as 'B'
> Method 1:
> if the DB looks for shifts with a START < B
> and END > A [this is simple criteria but would it cause massive table
> scaning to determine results, it woiuld also locate very long shifts (mont
hs
> even) that conflicted]
>
> it would have to check every shift that starts before B to see if the shif
t
> ends after A would this be done using two (seperatoe) index ss [on two
> differnt DB Fields, (START & END) and then find matchese.
> Method 2:
> I''m going to have to assume the shift can't be more than 12h (or say 24 t
o
> be safe) and then use BETWEEN on START AND BETWEEN on END.
> for instance:
> .... WHERE ... ( R.EndTime BETWEEN A and B+24 AND
> R.StartTime BETWEEN A-24 and B)....
> Notice the 24 hours beeing added/subtracted to limit the window of matched
> records. as I'm restricting the results by OperatorID i'm only going to
> expec that 90% of the time the DB will find no CONFLICTS and 10% on 1
> CONFLICT, more than 1 conflicts should be very rare!
>
> Is this sort of thing necessary or does the Query Optimizer do this
> automaticly? somehow?
> Thanks for you help so far,
> Kind regards,
> Graeme.
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:0A5159C7-A1D2-4469-9417-84A9DDEDA3ED@.microsoft.com...
>
>|||Graeme Stow
I missed something in my first response. I think R.operatorid is not
unique. And If combination of ShiftID and operatorid is unique(should be)
try creating clustered primary index on this. And Non clustered index on
start and end dates.
Regards
R.D
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Graeme Stow
> Query optimiser generally does everything if you have indexes. However the
> final selection can be made only by analysing the execution plans. When yo
u
> have index on that, It would not choose Table scan.
> Try different combination and select the best one using execution plan.
> Thumb rule is thst execution plan should avoid( unless you are selecting
> everything) table scan, book marks, filters and should use Index s or
> clustered s.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Graeme Stow" wrote:
>|||Hi R,
After creating the following indexs:
create index tblShiftRecords_IX1
on tblShiftRecords (OperatorID, StartTime)
create index tblShiftRecords_IX2
on tblShiftRecords (EndTime)
Note: i've already got a clustered index on ShiftID (It's IDENTITY(1,1)
would it be better to have that as NON-CLUSTERED and have a more usefull
index as clustered like StartTime?
The folowing [testing] query
SELECT *
FROM tblshiftrecords
WHERE operatorid = 546 AND
StartTime BETWEEN DATEADD(hh, -24, '20050516 08:02:00') AND DATEADD(hh,
12, '20050516 17:00:00') AND
EndTime BETWEEN DATEADD(hh, -12, '20050516 08:02:00') AND DATEADD(hh,
36, '20050516 17:00:00')
Produces -
1)Index S - cost 0.003327 (98%)
2)Bookmark Lookup - 0.000070 (2%)
3)Filter - 0.000001 (0%)
It's looking better,
Cheers for your help.
Kind regards,
p.s. the query plan is ignoring IX2 on EndTime.
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:28FFAED1-9F90-420E-900D-A5654AE5DCD4@.microsoft.com...
> Graeme Stow
> I missed something in my first response. I think R.operatorid is not
> unique. And If combination of ShiftID and operatorid is unique(should be)
> try creating clustered primary index on this. And Non clustered index on
> start and end dates.
> Regards
> R.D
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>|||Graeme
Thats great. You can last do one thing.
have covering index. create individual non clustered indexes on operators
id, start time and end time and see you can eliminate book mark.
--
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> Hi R,
> After creating the following indexs:
> create index tblShiftRecords_IX1
> on tblShiftRecords (OperatorID, StartTime)
> create index tblShiftRecords_IX2
> on tblShiftRecords (EndTime)
>
> Note: i've already got a clustered index on ShiftID (It's IDENTITY(1,1)
> would it be better to have that as NON-CLUSTERED and have a more usefull
> index as clustered like StartTime?
> The folowing [testing] query
> SELECT *
> FROM tblshiftrecords
> WHERE operatorid = 546 AND
> StartTime BETWEEN DATEADD(hh, -24, '20050516 08:02:00') AND DATEADD(hh
,
> 12, '20050516 17:00:00') AND
> EndTime BETWEEN DATEADD(hh, -12, '20050516 08:02:00') AND DATEADD(hh,
> 36, '20050516 17:00:00')
> Produces -
> 1)Index S - cost 0.003327 (98%)
> 2)Bookmark Lookup - 0.000070 (2%)
> 3)Filter - 0.000001 (0%)
> It's looking better,
> Cheers for your help.
> Kind regards,
> p.s. the query plan is ignoring IX2 on EndTime.
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:28FFAED1-9F90-420E-900D-A5654AE5DCD4@.microsoft.com...
>
>

Detect Missing Records in Flat File

I am importing records from a flat file to a database table. If a record is in the table but NOT in the flat file, I need to update a date column in the table.

Any ideas?

First page of this forum, "Checking to see if a record exists, if so update, else insert"

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Phil, I read that post before asking my question.

Since the row exists in the table but NOT in the flat file, a lookup doesn't work. Unless you know of a way to connect a Lookup to a flat file. As I mentioned, I need to know when a row in the database doesn't exist in a flat file.

|||A lookup does too work, you just have to load a staging table first with the contents of the flat file.

You can have more than one data flow task in a given package, so in the first data flow, load up the staging table with the file, and then in the second data flow, use an OLE DB source against your table and then use the lookup transformation against your staging table.|||

Thanks again Phil.

I seemed to have accomplished the same thing by using a Merge Join setup as a left outer join.

Do you see any performance advantages to using the staging table with a lookup instead?

|||

skaszyk wrote:

Thanks again Phil.

I seemed to have accomplished the same thing by using a Merge Join setup as a left outer join.

Do you see any performance advantages to using the staging table with a lookup instead?

If the file is hugh, something on the order of +1,000,000 rows, I'd say yes, but flat files are very fast to process, and I would not think you'd gain anything by using your method versus mine. For most things I do, though, I always push data into staging tables first, before working with the data. Having the data in a table offers the ability to perform SQL upfront while leveraging the database engine, among other things.|||

dear friend,

In my opinion, you can do that using a MERGE JOIN.

Add 2 sources, one OLEDB Datasource in order to get all the IDs, and a second to get the records from flatfile.

Add a merge join and configure it as right or left join depend on the sources order.

Helped?

Regards!

|||

PedroCGD wrote:

dear friend,

In my opinion, you can do that using a MERGE JOIN.

Add 2 sources, one OLEDB Datasource in order to get all the IDs, and a second to get the records from flatfile.

Add a merge join and configure it as right or left join depend on the sources order.

Helped?

Regards!

Pedro, he's already done that. Please read the entire thread first.|||

Sorry phil, but my page wasn't refreshed and didn't see some posts...

Regards!

detailsview control - how to edit record in Sql Server Express

I have null values in some of the fields & when I use EDIT to make changes these changes are not recorded + I receive no error.

Check the condition in the update statement. If it is correct..pls provide the complete code...so that we can give you some suggestion accordingly

Details section does not show all details

I have a data entry application where the user is allowed to enter multiple funding, goals and objective selections for each Project ID record. I have a database that lists each Project by ID and a relational table for each selection and its corresponding project id. For example:

projid type descr

1 Funding Capital

1 Goal PI-1 To develop a project that will come in on schedule

1 Objective PI-1.1 Schedule objective 1

1 Goal T-3 To develop a project that will come in on budget

1 Objective T-3.1 Budget objective 1

1 Objective T-3.2 Budget objective 2

1 Objective T-3.2 Budget objective 3

The report only prints the following in the details section:

projid type descr

1 Funding Capital

1 Goal PI-1 To develop a project that will come in on schedule

1 Objective PI-1.1 Schedule objective 1

Any help is greatly appreciated!

I started a brand new report starting with the details and then adding the two groups and now all records and data shows and sort correctly.

|||

I started a new report and now all related data is showing in the details for each record. But there is another problem...the report sorts by Project Owner then Project Name. If the project name is the same in 2 different records (i.e. Record id = 89 with project name = "Project" and Record id=90 and project name = "Project" the report show record 89 but in the details it shows related detail for record id's 89 and 90 and then doesn't show the individual record id = 90 at all. I don't understand why the details for each record are dependent on the project name. I am new to SQL Reporting and appreciate any help.

detail record header

Hi,

I'm new to SSRS. I was just wondering how do I make the header for a detail record appear once per grouping rather than once per detail record?

Thanks.

Yeah, I'd just not got my grouping setup correctly. Sorted now.

Design to accomodate entity based schema versioning

in simple words it's about versioning at record level.

Example

TableEmployee - EmployeeId, EmployeeName,
EmployeeAddress, DepartmentId,
TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,
validity
TableDepartment - DepartmentId, Department
TableDesignation - DesignationId, designation

Via Modify-Employee-Details screen following are editable
EmoyeeName
EmployeeAddress
Department
Designation

this screen should allow user to navigate through changes history.
Example :

Version -1
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Accounts
Designation Accountant

Version -2
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Accounts
Designation Chief Accountant - changed

Version -3
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Sales - changed
Designation Marketing Manager - changed

Question :
What is the best proposed database design for maintaining history
records bound with version and retrieval technique

Best Regards
SasankaSasanka Pinidiya (pinidiya@.yahoo.com) writes:
> in simple words it's about versioning at record level.
> Example
> TableEmployee - EmployeeId, EmployeeName,
> EmployeeAddress, DepartmentId,
> TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,
> validity
> TableDepartment - DepartmentId, Department
> TableDesignation - DesignationId, designation
> Via Modify-Employee-Details screen following are editable
> EmoyeeName
> EmployeeAddress
> Department
> Designation
>...
> Question :
> What is the best proposed database design for maintaining history
> records bound with version and retrieval technique

There are several techniques, and which is the best depends on the actual
business case.

One techniques which makes it very simple to retrieve the data at a
certain date, is store one version each day. Clearly, this technique
is not appropriate here. This technique is good for things that changes
about daily as for instance currency prices.

Another technique is store key, date and then what changed. But this
makes is difficult to retrieve the complete information at a given point
in time.

So the best technique seems to be to have a table with the same columns
as the base table, and with a datetime column added to hold the time
of the change.

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

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

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
David
With ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.co m...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David
|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.co m...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
DavidWith ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
DavidWith ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David