How to handle sqlcommand parameter that could be null?

I have this sql sqlcommand:

string sql = "INSERT Zones (ZName, TypeID, Description) VALUES (@dotnet.itags.org.ZName, @dotnet.itags.org.typeID, @dotnet.itags.org.description)";

command.Parameters.Add("@dotnet.itags.org.description", SqlDbType.NVarChar, 100).Value = description;

The column Description is nullable. But if the variable description is null, it seems the paramter @dotnet.itags.org.description can't be passed correctly.

How can I pass a null parameter to sql server?

Thanks!

You can send DBNull.Value as a value, that is it.

how to handle sql with selecting attributes from multiple tables

a sql like: "select t1.a, t2.b from t1, t2 ...",
how does ado.net handle the query result from this kind of sql?
thx.
hongyuyour question is unclear. but, for ado.net doesn't look any difference that
result is coming from one table or 10 tables as far as retrieveal is the
only aim.
say, it gives the same result as query analyzer/sql * plus gives, if you
write this query in that tools.
Rajesh Patel
"Jeff Lu" <hl_107@.yahoo.com> wrote in message
news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
> a sql like: "select t1.a, t2.b from t1, t2 ...",
> how does ado.net handle the query result from this kind of sql?
> thx.
> hongyu
>
One ResultSet, one DataTable
"Jeff Lu" <hl_107@.yahoo.com> wrote in message
news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
> a sql like: "select t1.a, t2.b from t1, t2 ...",
> how does ado.net handle the query result from this kind of sql?
> thx.
> hongyu
>
if i use DataReader to read the query results, how to get the meta data
of the columns from different tables?
thx.
Greg wrote:
> One ResultSet, one DataTable
>
> "Jeff Lu" <hl_107@.yahoo.com> wrote in message
> news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
>>a sql like: "select t1.a, t2.b from t1, t2 ...",
>>how does ado.net handle the query result from this kind of sql?
>>thx.
>>hongyu
>>
>
>
suppose there are 2 tables with 1-m relationship,
Order and Transaction(with orderId as the foreign key).
i want to get some Tx columns as well as some Order columns within
a single sql, which can be "select tx.attr1, tx.attr2, o.attr1 from
Transaction tx, Order o where tx.seq=? and tx.orderId=o.orderId".
with the query result from such a query, i can build a entity(let's
say a ReportingEntity) through some O-R mapping mechanism. The O-R
mapping layer needs meta data of the query result to build/bind
the entity. so, for each column/value of the DataReader, i
need to know its DB table name, DB column name and maybe its type.
DataReader.GetSchemaTable returns a DataTable, i didn't see a API
for getting the meta data across multiple tables mentioned above.
i guess i must have missed sth.
thx for your help.
William Ryan wrote:
> If you are using tables like that, you may want to consider using a
> DataRelation and pulling the tables individually, connecting them after
> you've grabbed them from the DB. If you are joining tables, you are pulling
> over redundant data that eats up bandwidth and depending on size, can really
> hinder performance. That's not to say that you should never join tables
> server side...but make sure that's really the way you want to do it. From
> your later post, you mention the meta data. I'm not sure what platform you
> are referring to, or what specific meta data you are referring to, but
> DataReader.GetSchemaTable will give you basic information about the fields
> pulled over.
> What specifically in the MD are you looking for? Let me know and I can
> probably be of more help.
> Bill
> "Jeff Lu" <hl_107@.yahoo.com> wrote in message
> news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
>>a sql like: "select t1.a, t2.b from t1, t2 ...",
>>how does ado.net handle the query result from this kind of sql?
>>thx.
>>hongyu
>>
>
>

how to handle nulls in a RadioButtonList

My webform RadioButtonList is bound to a column from a Sql Server dataSource. The radioButtonList is in a formView which starts up in edit mode. My question is how to handle null values because the only way I could get the page to come up when the columnn value is null is to have an item in the radio list which is setup for nulls such as, "Unknown" in the Text property and -1 in the value property. I understand that the RadioButtonList control is supposed to default to nulls which means that nothing in the radio list is selected - this is the behavior that I want, but I get the error below complaining that null (-1) is not valid because it does not exist as a valid value in the radio list: I have tried -1, '', etc. with no success.

'RadioButtonList2' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

How should I handle nulls at page startup?

Thanks.

While setting SelectedValue = null will normally allow no default selection for the RadioButtonList control, trying to set it to null in binding (using either Eval or Bind) doesn't work and throws an exception. Going through a method, however, seems to work as follows:

[ASPX]

<ItemTemplate>

<asp:RadioButtonListID="RadioButtonList1"runat="server"SelectedValue='<%# myFunc(Eval("NumValue").ToString()) %>'>

<asp:ListItemValue="1"Text="Item 001"></asp:ListItem>

<asp:ListItemValue="2"Text="Item 002"></asp:ListItem>

<asp:ListItemValue="3"Text="Item 003"></asp:ListItem>

<asp:ListItemValue="4"Text="Item 004"></asp:ListItem>

<asp:ListItemValue="5"Text="Item 005"></asp:ListItem>

</asp:RadioButtonList>

</ItemTemplate>

[code-behind: C#]

publicstring myFunc(string val)

{

if (val =="")

returnnull;

else

return val;

}

Thank you, the myFunc worked great for handling nulls, But it caused another problem with the update statement. My update statement no longer works because it has lost the identity of the bound variable somehow.

The default SelectedValue works fine:

aspx:

SelectedValue='<%# Bind("myField") %>'

UpdateCommand="update myTable setmyField=@.myField

But with your custom SelectedValue code I get the error at the bottom:

SelectedValue='<%# myFunc(Eval("r1").ToString()) %>'

UpdateCommand="update myTable setmyField=@.myField

here is the error:

Must declare the variable'@.myField'.

[SqlException (0x80131904): Must declare the variable'@.myField'.]

The customization to SelectedValue caused this, any idea why?

Thanks.

I can see why this would be an issue when trying to update since it deviates from the binding expected by the framework. Interesting situation you have here. I'll have to look into this before giving you an answer. I wouldn't be surprised if someone already faced such situation and resolved it.
Thanks. I am appreciative of your help.
I tried to see if there's a workaround for this issue, but I'm afraid there isn't as far as I can see. I thought that there might be a way to bind a NULL value into SelectedValue property (which can be done if a direct null value is assigned), but when binding to a table column, it rejects it for some reason. This problem/behavior may be related to the fact that RadioButtonList isn't meant to provide an option for "no selection" (i.e. once a selection is made, you cannot go back to a no-selection state), although the starting point allows no selection state. Perhaps the DropDownList control serves your purpose better since you can have a default value that represents no selection. Another option might be to add another item for your RadioButtonList that represents no selection and default what would have been null value to that choice. Sorry I don't have a better news for you.

Here's another way to handle this. Create a user control with your RadioButtonList in it, and create a property in it called SelectedValue such as following:

public string SelectedValue
{
get
{
return RadioButtonList1.SelectedValue;
}
set
{
if (value != "")
RadioButtonList1.SelectedVAlue = value;
}
}

This way you conditionally avoid setting the RadioButtonList value when it's null. Then you can happily use the SelectedValue='<%# Bind("FieldName") %>' syntax in your user control tag declaration.

How to handle NULL values in SQL Server

I'm building an ASP.NET application using VB.NET and SQL Sever 2000.

I'm returning data from a table to my datagrid that contains a datetime field. The date values for some of the records are null in the database table. My strategy (be it good or bad) is to update every field in a record during the datagrid's UpdateCommand event, regardless of whether each field's data has changed.

When I update a record whose date was null and has not been modified, SQL Server places a 1/1/1900 value in the date field. I was expecting the datetime field to remain null. Would someone be able to enlighten me on how best to handle this?

My update code looks like this:

Private Sub dgrdEaTask_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgrdEaTask.UpdateCommand
Dim Success As Boolean
Dim intEaId As Integer
Dim txtEaTask As TextBox
Dim txtDueDate As TextBox

txtEaTask = e.Item.Cells(2).Controls(0)
txtDueDate = e.Item.Cells(11).Controls(0)
intEaId = dgrdEaTask.DataKeys(e.Item.ItemIndex)

'Call the update method in the oEaTasks class
Success = oEaTasks.Update(txtEaId.Text, txtEaTask.Text, txtDueDate.Text)

If Success Then
dgrdEaTask.EditItemIndex = -1
BindGrid()
Else
'There was a problem updating the data
End If

End Sub

Thanks!use dbnull.value incase of null...

hth
Thanks!

I'll research it and give it try.

How to handle null value mapping in SqlDataSource

Hi, guys

If I pass null value to a parameter of SQL automatically generated by SqlDataSource (as WHERE field1=@dotnet.itags.org.field1, where field1 is of type ntext or nvarchar) at design-time, it seems SqlDataSource can not update appricately, throwing an exception: [SqlException (0x80131904): The data types ntext and nvarchar are incompatible in the equal to operator.],

even though the field1 in the db table is null-allowable.

How do I handle the situation where I must pass null value to update any field permitting null value.

Thanks,

Ricky.

What's the type of @.field1, and what are you setting it to? Post the relevant section of your code.

How to handle concurrency issue with better performance?

I am now thinking a method to handle the concurrency issue in my
program.
In .Net, the Command Builder Class will generate SQL automatically in
which it compares all the fields' original version with the version in
database as the update criteria and thus avoid overwriting others'
data.
In my case, I have a dataset retrieving data from joining several
tables and thus encounter error when use the DataSet.Update() method
of adapter directly.(cannot generate sql coz more 1 table in the
select command).
Therefore, I create stored procedure for the update command and assign
them to the dataset so that it can do the update. However, using the
concept of commandbuilder to handle concurrency issue, I have to
passing all the fields' original version to the stored procedure as
the where conditiion, the number of parameters might rise to about 30
to 40.
Will the preformance (esp. client) downgrade very much in using such
update method?
I was suggested by colleague to use 2 datasets, one is that I am using
right now and another will act as a image, storing the only table
which will be updated. So that when saving data to the database, I can
use the image DataSet's Update Command directly and needn't pass so
many parameters. But it seems that the synchonization between these 2
dataset is quite troublesome!
How you guys think? Which is better? or there is some better solution
which I may not know?
I am using VS.Net 2003 Enterprise to do the development, language is
VB .Net and the Database is Oracle 10g.
People using the application ~10-20, not a big number but the
performance of application must be as fast as possible due to the
business requirement.
Thanks in advanced.
Regards,
CherryI use .Net 2.0 and VS2005, but I'm going to take a stab at answering your
question.
When you do the update on the dataset, it will only update the records that
have been modified since they were pulled from the database. So you don't
need to keep a separate table of those.
Instead of checking all of the fields for changes, what many people do is
store a timestamp in the database for the last update date/time. When doing
your update, you check to see if that value is the same, and if it not,
somebody else has updated the record since you pulled it. In this way, you
can have the WHERE clause on your update use the time stamp and the primary
key fields.
Hope this helps.
Robin S.
--
"cherry" <cherryparadise001@.gmail.com> wrote in message
news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
>I am now thinking a method to handle the concurrency issue in my
> program.
> In .Net, the Command Builder Class will generate SQL automatically in
> which it compares all the fields' original version with the version in
> database as the update criteria and thus avoid overwriting others'
> data.
> In my case, I have a dataset retrieving data from joining several
> tables and thus encounter error when use the DataSet.Update() method
> of adapter directly.(cannot generate sql coz more 1 table in the
> select command).
> Therefore, I create stored procedure for the update command and assign
> them to the dataset so that it can do the update. However, using the
> concept of commandbuilder to handle concurrency issue, I have to
> passing all the fields' original version to the stored procedure as
> the where conditiion, the number of parameters might rise to about 30
> to 40.
> Will the preformance (esp. client) downgrade very much in using such
> update method?
> I was suggested by colleague to use 2 datasets, one is that I am using
> right now and another will act as a image, storing the only table
> which will be updated. So that when saving data to the database, I can
> use the image DataSet's Update Command directly and needn't pass so
> many parameters. But it seems that the synchonization between these 2
> dataset is quite troublesome!
> How you guys think? Which is better? or there is some better solution
> which I may not know?
> I am using VS.Net 2003 Enterprise to do the development, language is
> VB .Net and the Database is Oracle 10g.
> People using the application ~10-20, not a big number but the
> performance of application must be as fast as possible due to the
> business requirement.
> Thanks in advanced.
> Regards,
> Cherry
>
Would you put the timestamp check in the stored procedure on the server
side, or in the client side c# code?
"RobinS" <RobinS@.NoSpam.yah.none> wrote in message
news:gvCdnT3xivA-g5_bnZ2dnUVZ_qupnZ2d@.comcast.com...
>I use .Net 2.0 and VS2005, but I'm going to take a stab at answering your
>question.
> When you do the update on the dataset, it will only update the records
> that have been modified since they were pulled from the database. So you
> don't need to keep a separate table of those.
> Instead of checking all of the fields for changes, what many people do is
> store a timestamp in the database for the last update date/time. When
> doing your update, you check to see if that value is the same, and if it
> not, somebody else has updated the record since you pulled it. In this
> way, you can have the WHERE clause on your update use the time stamp and
> the primary key fields.
> Hope this helps.
> Robin S.
> --
> "cherry" <cherryparadise001@.gmail.com> wrote in message
> news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
>>I am now thinking a method to handle the concurrency issue in my
>> program.
>> In .Net, the Command Builder Class will generate SQL automatically in
>> which it compares all the fields' original version with the version in
>> database as the update criteria and thus avoid overwriting others'
>> data.
>> In my case, I have a dataset retrieving data from joining several
>> tables and thus encounter error when use the DataSet.Update() method
>> of adapter directly.(cannot generate sql coz more 1 table in the
>> select command).
>> Therefore, I create stored procedure for the update command and assign
>> them to the dataset so that it can do the update. However, using the
>> concept of commandbuilder to handle concurrency issue, I have to
>> passing all the fields' original version to the stored procedure as
>> the where conditiion, the number of parameters might rise to about 30
>> to 40.
>> Will the preformance (esp. client) downgrade very much in using such
>> update method?
>> I was suggested by colleague to use 2 datasets, one is that I am using
>> right now and another will act as a image, storing the only table
>> which will be updated. So that when saving data to the database, I can
>> use the image DataSet's Update Command directly and needn't pass so
>> many parameters. But it seems that the synchonization between these 2
>> dataset is quite troublesome!
>> How you guys think? Which is better? or there is some better solution
>> which I may not know?
>> I am using VS.Net 2003 Enterprise to do the development, language is
>> VB .Net and the Database is Oracle 10g.
>> People using the application ~10-20, not a big number but the
>> performance of application must be as fast as possible due to the
>> business requirement.
>> Thanks in advanced.
>> Regards,
>> Cherry
>
>
This question has been asked (and answered) many, many times here and in my
(and other's) books over the years.
The CommandBuilder (CB) is a mechanism with many limitations. As you have
found, it fails to deal with more sophisticated designs--all too often, even
those as simple as yours. Yes, many developers have migrated to stored
procedures which use TimeStamps to monitor concurrency--this approach is far
faster and easier to code. The newest CB also uses this approach as well
(ADO.NET 2.0 and VS2005).
As I describe in my latest book (if I may be so bold to mention it again),
the real solution is often in the design of the application and the way it
accesses data. Collisions occur because your design permits more than one
application (even the same application) to access the same row at the same
time--where both applications try to change the row. This is like designing
traffic intersections without signals and worrying about where to put the
ambulances and fire trucks to deal with the collisions. We all work with
relational database designs and more importantly DBAs that don't expose the
base table for a litany of reasons. Creating applications that party down on
these base tables is a plan for disaster. One morning you'll wake up with a
terrible security headache that can't be cured with a glass of tomato juice
and a raw egg.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"cherry" <cherryparadise001@.gmail.com> wrote in message
news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
>I am now thinking a method to handle the concurrency issue in my
> program.
> In .Net, the Command Builder Class will generate SQL automatically in
> which it compares all the fields' original version with the version in
> database as the update criteria and thus avoid overwriting others'
> data.
> In my case, I have a dataset retrieving data from joining several
> tables and thus encounter error when use the DataSet.Update() method
> of adapter directly.(cannot generate sql coz more 1 table in the
> select command).
> Therefore, I create stored procedure for the update command and assign
> them to the dataset so that it can do the update. However, using the
> concept of commandbuilder to handle concurrency issue, I have to
> passing all the fields' original version to the stored procedure as
> the where conditiion, the number of parameters might rise to about 30
> to 40.
> Will the preformance (esp. client) downgrade very much in using such
> update method?
> I was suggested by colleague to use 2 datasets, one is that I am using
> right now and another will act as a image, storing the only table
> which will be updated. So that when saving data to the database, I can
> use the image DataSet's Update Command directly and needn't pass so
> many parameters. But it seems that the synchonization between these 2
> dataset is quite troublesome!
> How you guys think? Which is better? or there is some better solution
> which I may not know?
> I am using VS.Net 2003 Enterprise to do the development, language is
> VB .Net and the Database is Oracle 10g.
> People using the application ~10-20, not a big number but the
> performance of application must be as fast as possible due to the
> business requirement.
> Thanks in advanced.
> Regards,
> Cherry
>
In the SP that handles the updates.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Brooke" <tbrooked@.hotmail.com> wrote in message
news:eHVQGGIbHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Would you put the timestamp check in the stored procedure on the server
> side, or in the client side c# code?
>
> "RobinS" <RobinS@.NoSpam.yah.none> wrote in message
> news:gvCdnT3xivA-g5_bnZ2dnUVZ_qupnZ2d@.comcast.com...
>>I use .Net 2.0 and VS2005, but I'm going to take a stab at answering your
>>question.
>> When you do the update on the dataset, it will only update the records
>> that have been modified since they were pulled from the database. So you
>> don't need to keep a separate table of those.
>> Instead of checking all of the fields for changes, what many people do is
>> store a timestamp in the database for the last update date/time. When
>> doing your update, you check to see if that value is the same, and if it
>> not, somebody else has updated the record since you pulled it. In this
>> way, you can have the WHERE clause on your update use the time stamp and
>> the primary key fields.
>> Hope this helps.
>> Robin S.
>> --
>> "cherry" <cherryparadise001@.gmail.com> wrote in message
>> news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
>>I am now thinking a method to handle the concurrency issue in my
>> program.
>> In .Net, the Command Builder Class will generate SQL automatically in
>> which it compares all the fields' original version with the version in
>> database as the update criteria and thus avoid overwriting others'
>> data.
>> In my case, I have a dataset retrieving data from joining several
>> tables and thus encounter error when use the DataSet.Update() method
>> of adapter directly.(cannot generate sql coz more 1 table in the
>> select command).
>> Therefore, I create stored procedure for the update command and assign
>> them to the dataset so that it can do the update. However, using the
>> concept of commandbuilder to handle concurrency issue, I have to
>> passing all the fields' original version to the stored procedure as
>> the where conditiion, the number of parameters might rise to about 30
>> to 40.
>> Will the preformance (esp. client) downgrade very much in using such
>> update method?
>> I was suggested by colleague to use 2 datasets, one is that I am using
>> right now and another will act as a image, storing the only table
>> which will be updated. So that when saving data to the database, I can
>> use the image DataSet's Update Command directly and needn't pass so
>> many parameters. But it seems that the synchonization between these 2
>> dataset is quite troublesome!
>> How you guys think? Which is better? or there is some better solution
>> which I may not know?
>> I am using VS.Net 2003 Enterprise to do the development, language is
>> VB .Net and the Database is Oracle 10g.
>> People using the application ~10-20, not a big number but the
>> performance of application must be as fast as possible due to the
>> business requirement.
>> Thanks in advanced.
>> Regards,
>> Cherry
>>
>>
>
> Creating applications that party down on
> these base tables is a plan for disaster. One morning you'll wake up with
> a terrible security headache that can't be cured with a glass of tomato
> juice and a raw egg.
LOL. Great turn of phrase.
Robin S.
---
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:eEzxCdKbHHA.4656@.TK2MSFTNGP03.phx.gbl...
> This question has been asked (and answered) many, many times here and in
> my (and other's) books over the years.
> The CommandBuilder (CB) is a mechanism with many limitations. As you have
> found, it fails to deal with more sophisticated designs--all too often,
> even those as simple as yours. Yes, many developers have migrated to
> stored procedures which use TimeStamps to monitor concurrency--this
> approach is far faster and easier to code. The newest CB also uses this
> approach as well (ADO.NET 2.0 and VS2005).
> As I describe in my latest book (if I may be so bold to mention it
> again), the real solution is often in the design of the application and
> the way it accesses data. Collisions occur because your design permits
> more than one application (even the same application) to access the same
> row at the same time--where both applications try to change the row. This
> is like designing traffic intersections without signals and worrying
> about where to put the ambulances and fire trucks to deal with the
> collisions. We all work with relational database designs and more
> importantly DBAs that don't expose the base table for a litany of
> reasons. Creating applications that party down on these base tables is a
> plan for disaster. One morning you'll wake up with a terrible security
> headache that can't be cured with a glass of tomato juice and a raw egg.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "cherry" <cherryparadise001@.gmail.com> wrote in message
> news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
>>I am now thinking a method to handle the concurrency issue in my
>> program.
>> In .Net, the Command Builder Class will generate SQL automatically in
>> which it compares all the fields' original version with the version in
>> database as the update criteria and thus avoid overwriting others'
>> data.
>> In my case, I have a dataset retrieving data from joining several
>> tables and thus encounter error when use the DataSet.Update() method
>> of adapter directly.(cannot generate sql coz more 1 table in the
>> select command).
>> Therefore, I create stored procedure for the update command and assign
>> them to the dataset so that it can do the update. However, using the
>> concept of commandbuilder to handle concurrency issue, I have to
>> passing all the fields' original version to the stored procedure as
>> the where conditiion, the number of parameters might rise to about 30
>> to 40.
>> Will the preformance (esp. client) downgrade very much in using such
>> update method?
>> I was suggested by colleague to use 2 datasets, one is that I am using
>> right now and another will act as a image, storing the only table
>> which will be updated. So that when saving data to the database, I can
>> use the image DataSet's Update Command directly and needn't pass so
>> many parameters. But it seems that the synchonization between these 2
>> dataset is quite troublesome!
>> How you guys think? Which is better? or there is some better solution
>> which I may not know?
>> I am using VS.Net 2003 Enterprise to do the development, language is
>> VB .Net and the Database is Oracle 10g.
>> People using the application ~10-20, not a big number but the
>> performance of application must be as fast as possible due to the
>> business requirement.
>> Thanks in advanced.
>> Regards,
>> Cherry
>
Thanks all.
Right now I am thinking whether I can use a existing DateTime column
(ie last_update_date) as the control instead of creating a new
TimeStamp.
In this way, I can save the storage (there are quite many records) and
the time in creating the column (and also quite many tables), right?
The result of using datetime or timestamp should be the same, right?
will there be any potential risk?
Thanks and Regards,
Cherry
On 3=A4=EB23=A4=E9, =A4U=A4=C81=AE=C927=A4=C0, "RobinS" <Rob...@.NoSpam.yah.=none> wrote:
> > Creating applications that party down on
> > these base tables is a plan for disaster. One morning you'll wake up wi=th
> > a terrible security headache that can't be cured with a glass of tomato
> > juice and a raw egg.
> LOL. Great turn of phrase.
> Robin S.
> ---
> "William (Bill) Vaughn" <billvaRemoveT...@.betav.com> wrote in messagenews=:eEzxCdKbHHA.4656@.TK2MSFTNGP03.phx.gbl...
>
> > This question has been asked (and answered) many, many times here and in
> > my (and other's) books over the years.
> > The CommandBuilder (CB) is a mechanism with many limitations. As you ha=ve
> > found, it fails to deal with more sophisticated designs--all too often,
> > even those as simple as yours. Yes, many developers have migrated to
> > stored procedures which use TimeStamps to monitorconcurrency--this
> > approach is far faster and easier to code. The newest CB also uses this
> > approach as well (ADO.NET 2.0 and VS2005).
> > As I describe in my latest book (if I may be so bold to mention it
> > again), the real solution is often in the design of the application and
> > the way it accesses data. Collisions occur because your design permits
> > more than one application (even the same application) to access the same
> > row at the same time--where both applications try to change the row. Th=is
> > is like designing traffic intersections without signals and worrying
> > about where to put the ambulances and fire trucks to deal with the
> > collisions. We all work with relational database designs and more
> > importantly DBAs that don't expose the base table for a litany of
> > reasons. Creating applications that party down on these base tables is a
> > plan for disaster. One morning you'll wake up with a terrible security
> > headache that can't be cured with a glass of tomato juice and a raw egg.
> > --
> > ____________________________________
> > William (Bill) Vaughn
> > Author, Mentor, Consultant
> > Microsoft MVP
> > INETA Speaker
> >www.betav.com/blog/billva
> >www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > __________________________________
> > Visitwww.hitchhikerguides.netto get more information on my latest book:
> > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> > ----=---
> > "cherry" <cherryparadise...@.gmail.com> wrote in message
> >news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
> >>I am now thinking a method to handle theconcurrencyissuein my
> >> program.
> >> In .Net, the Command Builder Class will generate SQL automatically in
> >> which it compares all the fields' original version with the version in
> >> database as the update criteria and thus avoid overwriting others'
> >> data.
> >> In my case, I have a dataset retrieving data from joining several
> >> tables and thus encounter error when use the DataSet.Update() method
> >> of adapter directly.(cannot generate sql coz more 1 table in the
> >> select command).
> >> Therefore, I create stored procedure for the update command and assign
> >> them to the dataset so that it can do the update. However, using the
> >> concept of commandbuilder to handleconcurrencyissue, I have to
> >> passing all the fields' original version to the stored procedure as
> >> the where conditiion, the number of parameters might rise to about 30
> >> to 40.
> >> Will the preformance (esp. client) downgrade very much in using such
> >> update method?
> >> I was suggested by colleague to use 2 datasets, one is that I am using
> >> right now and another will act as a image, storing the only table
> >> which will be updated. So that when saving data to the database, I can
> >> use the image DataSet's Update Command directly and needn't pass so
> >> many parameters. But it seems that the synchonization between these 2
> >> dataset is quite troublesome!
> >> How you guys think? Which isbetter? or there is somebettersolution
> >> which I may not know?
> >> I am using VS.Net 2003 Enterprise to do the development, language is
> >> VB .Net and the Database is Oracle 10g.
> >> People using the application ~10-20, not a big number but the
> >>performanceof application must be as fast as possible due to the
> >> business requirement.
> >> Thanks in advanced.
> >> Regards,
> >> Cherry- =C1=F4=C2=C3=B3Q=A4=DE=A5=CE=A4=E5=A6r -
> - =C5=E3=A5=DC=B3Q=A4=DE=A5=CE=A4=E5=A6r -
Keep in mind that a timestamp column does not store a date/time value. When
the INSERT ads a new row, two rows can be assigned the same datetime value
from GetDate().
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"cherry" <cherryparadise001@.gmail.com> wrote in message
news:1175246213.455878.159160@.r56g2000hsd.googlegroups.com...
Thanks all.
Right now I am thinking whether I can use a existing DateTime column
(ie last_update_date) as the control instead of creating a new
TimeStamp.
In this way, I can save the storage (there are quite many records) and
the time in creating the column (and also quite many tables), right?
The result of using datetime or timestamp should be the same, right?
will there be any potential risk?
Thanks and Regards,
Cherry
On 3¤ë23¤é, ¤U¤È1®É27¤À, "RobinS" <Rob...@.NoSpam.yah.none> wrote:
> > Creating applications that party down on
> > these base tables is a plan for disaster. One morning you'll wake up
> > with
> > a terrible security headache that can't be cured with a glass of tomato
> > juice and a raw egg.
> LOL. Great turn of phrase.
> Robin S.
> ---
> "William (Bill) Vaughn" <billvaRemoveT...@.betav.com> wrote in
> messagenews:eEzxCdKbHHA.4656@.TK2MSFTNGP03.phx.gbl...
>
> > This question has been asked (and answered) many, many times here and in
> > my (and other's) books over the years.
> > The CommandBuilder (CB) is a mechanism with many limitations. As you
> > have
> > found, it fails to deal with more sophisticated designs--all too often,
> > even those as simple as yours. Yes, many developers have migrated to
> > stored procedures which use TimeStamps to monitorconcurrency--this
> > approach is far faster and easier to code. The newest CB also uses this
> > approach as well (ADO.NET 2.0 and VS2005).
> > As I describe in my latest book (if I may be so bold to mention it
> > again), the real solution is often in the design of the application and
> > the way it accesses data. Collisions occur because your design permits
> > more than one application (even the same application) to access the same
> > row at the same time--where both applications try to change the row.
> > This
> > is like designing traffic intersections without signals and worrying
> > about where to put the ambulances and fire trucks to deal with the
> > collisions. We all work with relational database designs and more
> > importantly DBAs that don't expose the base table for a litany of
> > reasons. Creating applications that party down on these base tables is a
> > plan for disaster. One morning you'll wake up with a terrible security
> > headache that can't be cured with a glass of tomato juice and a raw egg.
> > --
> > ____________________________________
> > William (Bill) Vaughn
> > Author, Mentor, Consultant
> > Microsoft MVP
> > INETA Speaker
> >www.betav.com/blog/billva
> >www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > __________________________________
> > Visitwww.hitchhikerguides.netto get more information on my latest book:
> > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> > ------
> > "cherry" <cherryparadise...@.gmail.com> wrote in message
> >news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
> >>I am now thinking a method to handle theconcurrencyissuein my
> >> program.
> >> In .Net, the Command Builder Class will generate SQL automatically in
> >> which it compares all the fields' original version with the version in
> >> database as the update criteria and thus avoid overwriting others'
> >> data.
> >> In my case, I have a dataset retrieving data from joining several
> >> tables and thus encounter error when use the DataSet.Update() method
> >> of adapter directly.(cannot generate sql coz more 1 table in the
> >> select command).
> >> Therefore, I create stored procedure for the update command and assign
> >> them to the dataset so that it can do the update. However, using the
> >> concept of commandbuilder to handleconcurrencyissue, I have to
> >> passing all the fields' original version to the stored procedure as
> >> the where conditiion, the number of parameters might rise to about 30
> >> to 40.
> >> Will the preformance (esp. client) downgrade very much in using such
> >> update method?
> >> I was suggested by colleague to use 2 datasets, one is that I am using
> >> right now and another will act as a image, storing the only table
> >> which will be updated. So that when saving data to the database, I can
> >> use the image DataSet's Update Command directly and needn't pass so
> >> many parameters. But it seems that the synchonization between these 2
> >> dataset is quite troublesome!
> >> How you guys think? Which isbetter? or there is somebettersolution
> >> which I may not know?
> >> I am using VS.Net 2003 Enterprise to do the development, language is
> >> VB .Net and the Database is Oracle 10g.
> >> People using the application ~10-20, not a big number but the
> >>performanceof application must be as fast as possible due to the
> >> business requirement.
> >> Thanks in advanced.
> >> Regards,
> >> Cherry- ÁôÂóQ¤Þ¥Î¤å¦r -
> - Åã¥Ü³Q¤Þ¥Î¤å¦r -
Then for timestamp, it will be unique? Is it a automatic updated field
in table?
In my plan, I will use SP and dataset.
when I am updating a record, i will compare the last updated date in
the database with the original last updated date value in the row of
the dataset.
So, it can prevent one from overwriting others' changes...of course,
the violation throwing exception should be handled.
Regards,
Cherry
On 3=E6=9C=8831=E6=97=A5, =E4=B8=8A=E5=8D=886=E6=99=8219=E5=88=86, "William= \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> Keep in mind that a timestamp column does not store a date/time value. Wh=en
> the INSERT ads a new row, two rows can be assigned the same datetime value
> from GetDate().
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----=--=C2=AD---
> "cherry" <cherryparadise...@.gmail.com> wrote in message
> news:1175246213.455878.159160@.r56g2000hsd.googlegroups.com...
> Thanks all.
> Right now I am thinking whether I can use a existing DateTime column
> (ie last_update_date) as the control instead of creating a new
> TimeStamp.
> In this way, I can save the storage (there are quite many records) and
> the time in creating the column (and also quite many tables), right?
> The result of using datetime or timestamp should be the same, right?
> will there be any potential risk?
> Thanks and Regards,
> Cherry
> On 3=C2=A4=C3=AB23=C2=A4=C3=A9, =C2=A4U=C2=A4=C3=881=C2=AE=C3=8927=C2=A4==C3=80, "RobinS" <Rob...@.NoSpam.yah.none> wrote:
>
> > > Creating applications that party down on
> > > these base tables is a plan for disaster. One morning you'll wake up
> > > with
> > > a terrible security headache that can't be cured with a glass of toma=to
> > > juice and a raw egg.
> > LOL. Great turn of phrase.
> > Robin S.
> > ---
> > "William (Bill) Vaughn" <billvaRemoveT...@.betav.com> wrote in
> > messagenews:eEzxCdKbHHA.4656@.TK2MSFTNGP03.phx.gbl...
> > > This question has been asked (and answered) many, many times here and= in
> > > my (and other's) books over the years.
> > > The CommandBuilder (CB) is a mechanism with many limitations. As you
> > > have
> > > found, it fails to deal with more sophisticated designs--all too ofte=n,
> > > even those as simple as yours. Yes, many developers have migrated to
> > > stored procedures which use TimeStamps to monitorconcurrency--this
> > > approach is far faster and easier to code. The newest CB also uses th=is
> > > approach as well (ADO.NET 2.0 and VS2005).
> > > As I describe in my latest book (if I may be so bold to mention it
> > > again), the real solution is often in the design of the application a=nd
> > > the way it accesses data. Collisions occur because your design permits
> > > more than one application (even the same application) to access the s=ame
> > > row at the same time--where both applications try to change the row.
> > > This
> > > is like designing traffic intersections without signals and worrying
> > > about where to put the ambulances and fire trucks to deal with the
> > > collisions. We all work with relational database designs and more
> > > importantly DBAs that don't expose the base table for a litany of
> > > reasons. Creating applications that party down on these base tables i=s a
> > > plan for disaster. One morning you'll wake up with a terrible security
> > > headache that can't be cured with a glass of tomato juice and a raw e=gg.
> > > --
> > > ____________________________________
> > > William (Bill) Vaughn
> > > Author, Mentor, Consultant
> > > Microsoft MVP
> > > INETA Speaker
> > >www.betav.com/blog/billva
> > >www.betav.com
> > > Please reply only to the newsgroup so that others can benefit.
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > __________________________________
> > > Visitwww.hitchhikerguides.nettoget more information on my latest book:
> > > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> > > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> > > ---=--=C2=AD---
> > > "cherry" <cherryparadise...@.gmail.com> wrote in message
> > >news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
> > >>I am now thinking a method to handle theconcurrencyissuein my
> > >> program.
> > >> In .Net, the Command Builder Class will generate SQL automatically in
> > >> which it compares all the fields' original version with the version =in
> > >> database as the update criteria and thus avoid overwriting others'
> > >> data.
> > >> In my case, I have a dataset retrieving data from joining several
> > >> tables and thus encounter error when use the DataSet.Update() method
> > >> of adapter directly.(cannot generate sql coz more 1 table in the
> > >> select command).
> > >> Therefore, I create stored procedure for the update command and assi=gn
> > >> them to the dataset so that it can do the update. However, using the
> > >> concept of commandbuilder to handleconcurrencyissue, I have to
> > >> passing all the fields' original version to the stored procedure as
> > >> the where conditiion, the number of parameters might rise to about 30
> > >> to 40.
> > >> Will the preformance (esp. client) downgrade very much in using such
> > >> update method?
> > >> I was suggested by colleague to use 2 datasets, one is that I am usi=ng
> > >> right now and another will act as a image, storing the only table
> > >> which will be updated. So that when saving data to the database, I c=an
> > >> use the image DataSet's Update Command directly and needn't pass so
> > >> many parameters. But it seems that the synchonization between these 2
> > >> dataset is quite troublesome!
> > >> How you guys think? Which isbetter? or there is somebettersolution
> > >> which I may not know?
> > >> I am using VS.Net 2003 Enterprise to do the development, language is
> > >> VB .Net and the Database is Oracle 10g.
> > >> People using the application ~10-20, not a big number but the
> > >>performanceof application must be as fast as possible due to the
> > >> business requirement.
> > >> Thanks in advanced.
> > >> Regards,
> > >> Cherry- =C3=81=C3=B4=C3=82=C3=83=C2=B3Q=C2=A4=C3=9E=C2=A5=C3=8E=C2==A4=C3=A5=C2=A6r -
> > - =C3=85=C3=A3=C2=A5=C3=9C=C2=B3Q=C2=A4=C3=9E=C2=A5=C3=8E=C2=A4=C3=A5==C2=A6r -- =E9=9A=B1=E8=97=8F=E8=A2=AB=E5=BC=95=E7=94=A8=E6=96=87=E5=AD=97 -
> - =E9=A1=AF=E7=A4=BA=E8=A2=AB=E5=BC=95=E7=94=A8=E6=96=87=E5=AD=97 -
Timestamps are guaranteed unique. they are simply an auto-incrementing
integer value. They are designed to determine if the row was changed since
the last time the row was read.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"cherry" <cherryparadise001@.gmail.com> wrote in message
news:1175306883.170345.196620@.n59g2000hsh.googlegroups.com...
Then for timestamp, it will be unique? Is it a automatic updated field
in table?
In my plan, I will use SP and dataset.
when I am updating a record, i will compare the last updated date in
the database with the original last updated date value in the row of
the dataset.
So, it can prevent one from overwriting others' changes...of course,
the violation throwing exception should be handled.
Regards,
Cherry
On 3?31?, '6?19?, "William \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> Keep in mind that a timestamp column does not store a date/time value.
> When
> the INSERT ads a new row, two rows can be assigned the same datetime value
> from GetDate().
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----­---
> "cherry" <cherryparadise...@.gmail.com> wrote in message
> news:1175246213.455878.159160@.r56g2000hsd.googlegroups.com...
> Thanks all.
> Right now I am thinking whether I can use a existing DateTime column
> (ie last_update_date) as the control instead of creating a new
> TimeStamp.
> In this way, I can save the storage (there are quite many records) and
> the time in creating the column (and also quite many tables), right?
> The result of using datetime or timestamp should be the same, right?
> will there be any potential risk?
> Thanks and Regards,
> Cherry
> On 3¤ë23¤é, ¤U¤È1®É27¤À, "RobinS" <Rob...@.NoSpam.yah.none> wrote:
>
> > > Creating applications that party down on
> > > these base tables is a plan for disaster. One morning you'll wake up
> > > with
> > > a terrible security headache that can't be cured with a glass of
> > > tomato
> > > juice and a raw egg.
> > LOL. Great turn of phrase.
> > Robin S.
> > ---
> > "William (Bill) Vaughn" <billvaRemoveT...@.betav.com> wrote in
> > messagenews:eEzxCdKbHHA.4656@.TK2MSFTNGP03.phx.gbl...
> > > This question has been asked (and answered) many, many times here and
> > > in
> > > my (and other's) books over the years.
> > > The CommandBuilder (CB) is a mechanism with many limitations. As you
> > > have
> > > found, it fails to deal with more sophisticated designs--all too
> > > often,
> > > even those as simple as yours. Yes, many developers have migrated to
> > > stored procedures which use TimeStamps to monitorconcurrency--this
> > > approach is far faster and easier to code. The newest CB also uses
> > > this
> > > approach as well (ADO.NET 2.0 and VS2005).
> > > As I describe in my latest book (if I may be so bold to mention it
> > > again), the real solution is often in the design of the application
> > > and
> > > the way it accesses data. Collisions occur because your design permits
> > > more than one application (even the same application) to access the
> > > same
> > > row at the same time--where both applications try to change the row.
> > > This
> > > is like designing traffic intersections without signals and worrying
> > > about where to put the ambulances and fire trucks to deal with the
> > > collisions. We all work with relational database designs and more
> > > importantly DBAs that don't expose the base table for a litany of
> > > reasons. Creating applications that party down on these base tables is
> > > a
> > > plan for disaster. One morning you'll wake up with a terrible security
> > > headache that can't be cured with a glass of tomato juice and a raw
> > > egg.
> > > --
> > > ____________________________________
> > > William (Bill) Vaughn
> > > Author, Mentor, Consultant
> > > Microsoft MVP
> > > INETA Speaker
> > >www.betav.com/blog/billva
> > >www.betav.com
> > > Please reply only to the newsgroup so that others can benefit.
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > __________________________________
> > > Visitwww.hitchhikerguides.nettoget more information on my latest book:
> > > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> > > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> > > ----­---
> > > "cherry" <cherryparadise...@.gmail.com> wrote in message
> > >news:1174529058.747242.168860@.e1g2000hsg.googlegroups.com...
> > >>I am now thinking a method to handle theconcurrencyissuein my
> > >> program.
> > >> In .Net, the Command Builder Class will generate SQL automatically in
> > >> which it compares all the fields' original version with the version
> > >> in
> > >> database as the update criteria and thus avoid overwriting others'
> > >> data.
> > >> In my case, I have a dataset retrieving data from joining several
> > >> tables and thus encounter error when use the DataSet.Update() method
> > >> of adapter directly.(cannot generate sql coz more 1 table in the
> > >> select command).
> > >> Therefore, I create stored procedure for the update command and
> > >> assign
> > >> them to the dataset so that it can do the update. However, using the
> > >> concept of commandbuilder to handleconcurrencyissue, I have to
> > >> passing all the fields' original version to the stored procedure as
> > >> the where conditiion, the number of parameters might rise to about 30
> > >> to 40.
> > >> Will the preformance (esp. client) downgrade very much in using such
> > >> update method?
> > >> I was suggested by colleague to use 2 datasets, one is that I am
> > >> using
> > >> right now and another will act as a image, storing the only table
> > >> which will be updated. So that when saving data to the database, I
> > >> can
> > >> use the image DataSet's Update Command directly and needn't pass so
> > >> many parameters. But it seems that the synchonization between these 2
> > >> dataset is quite troublesome!
> > >> How you guys think? Which isbetter? or there is somebettersolution
> > >> which I may not know?
> > >> I am using VS.Net 2003 Enterprise to do the development, language is
> > >> VB .Net and the Database is Oracle 10g.
> > >> People using the application ~10-20, not a big number but the
> > >>performanceof application must be as fast as possible due to the
> > >> business requirement.
> > >> Thanks in advanced.
> > >> Regards,
> > >> Cherry- ÁôÂóQ¤Þ¥Î¤å¦r -
> > - Åã¥Ü³Q¤Þ¥Î¤å¦r -- '? -
> - '? -
Timestamps are great. Cast them as integers and use them in the where
clause.
--
StatusLookup
--
SELECT StatusLookupID, Status, Office, EndUser, Consultant,
ConsultantOffice, LastUpdated, LastUpdatedBy, CAST(TS AS INT) AS TS FROM
AgencyNET.StatusLookup
INSERT INTO [AgencyNET].[StatusLookup] ([Status], [Office], [EndUser],
[Consultant], [ConsultantOffice], [LastUpdatedBy]) VALUES (@.Status, @.Office,
@.EndUser, @.Consultant, @.ConsultantOffice, @.LastUpdatedBy);SELECT
StatusLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.StatusLookup WHERE
(StatusLookupID = SCOPE_IDENTITY())
UPDATE [AgencyNET].[StatusLookup] SET [Status] = @.Status, [Office] =@.Office, [EndUser] = @.EndUser, [Consultant] = @.Consultant,
[ConsultantOffice] = @.ConsultantOffice, [LastUpdated] = getutcdate(),
[LastUpdatedBy] = @.LastUpdatedBy WHERE (([StatusLookupID] =@.Original_StatusLookupID) AND (CAST(TS AS INT) = @.Original_TS));SELECT
CAST(TS AS INT) AS TS FROM AgencyNET.StatusLookup WHERE (StatusLookupID =@.StatusLookupID)
DELETE FROM [AgencyNET].[StatusLookup] WHERE (([StatusLookupID] =@.Original_StatusLookupID) AND (CAST(TS AS INT) = @.Original_TS))