dataadapter.Update() OR ExecuteNonQuery()

Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!

Hi

If you are only intending on working with one row at a time (presumably using the primary key for the data you are working with) then it would be more efficient to use the ExecuteNonQuery method of the command object. The Update method of the DataAdapter object will attempt to update all changes to rows within a DataSet/DataTable.

HTH

|||You said: "Update method of the DataAdapter object will attempt to update ALL changes to rows within a DataSet/DataTable", even if I write: dataadapter.UpdateCommand="update TableName set field1 with ? for .... "?|||

Hi

If you specify the DataAdapter's UpdateCommand to update only one row then that will be similar to the ExecuteNonQuery method. I assumed by DataAdapter.Update that you were going to pass it a DataSet/DataTable so that all changed rows would be persisted to the Database.

To be honest though, the DataAdapter's UpdateCommand is a command object so therefore, to update a row you would have configured a DataAdapter and then configured a Command object when you could simply have configured a Command object only. However, if you already had the DataAdapter available to you then this wouldn't be so much of an issue. It really depends on the type of aproach you are taking, especially when considering a Data Access Layer.

HTH

|||Yes, you are right. With dataadapter the work is double. Thank you.

Data type problem

Arithmetic overflow error converting numeric to data type numeric.
I'm getting this error when I'm trying to insert.
this is my SQL
insert into ABSCTRL
( ID, column1 )
values ('123',12)
ID is char (4)
Column1 is decimal(4,1) in the database table.
Please let me know why I'm getting this error.
Thanks
ChandraAre you quite certain you got the data types right?
ML
http://milambda.blogspot.com/|||Can you post the table script?|||yes..I was right about the data types. Why my insert statement is not workin
g'
"ML" wrote:

> Are you quite certain you got the data types right?
>
> ML
> --
> http://milambda.blogspot.com/|||Hi Chandra,
If your datatype is right, then the problem is with the value you are
trying to insert.
If your column1 datatype is decimal(4,1)
then the value you will have to insert should be between -1000 and 1000
(limits excluded). If not it will give an error that you mentioned.
Can you post the table create script and the insert that you tried to perfor
m.|||There's something else going on. Below does not return any errors:
CREATE TABLE ABSCTRL
(ID char (4),
Column1 decimal(4,1) )
insert into ABSCTRL
( ID, column1 )
values ('123',12)
SELECT * FROM ABSCTRL
If the table definition and the INSERT corresponds to your envoronment, then
it could be an inert
trigger on the ABSCTRL table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A77E1CD0-9FB7-417B-B926-1DE794713ABD@.microsoft.com...
> Arithmetic overflow error converting numeric to data type numeric.
> I'm getting this error when I'm trying to insert.
> this is my SQL
> insert into ABSCTRL
> ( ID, column1 )
> values ('123',12)
> ID is char (4)
> Column1 is decimal(4,1) in the database table.
> Please let me know why I'm getting this error.
>
> Thanks
> Chandra
>|||This is my SQL Script
CREATE TABLE [dbo].[ABSCTRL] (
[ID] [char] (4) NOT NULL ,
[column1] [decimal](1, 0) NOT NULL ,
) ON [PRIMARY]
GO
I don't know why I'm getting [decimal](1, 0) because in design I gave
length as 4 and precision as 1. Pleaselet me know where I made a mistake.
"Tibor Karaszi" wrote:

> There's something else going on. Below does not return any errors:
> CREATE TABLE ABSCTRL
> (ID char (4),
> Column1 decimal(4,1) )
>
> insert into ABSCTRL
> ( ID, column1 )
> values ('123',12)
> SELECT * FROM ABSCTRL
>
> If the table definition and the INSERT corresponds to your envoronment, th
en it could be an inert
> trigger on the ABSCTRL table.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:A77E1CD0-9FB7-417B-B926-1DE794713ABD@.microsoft.com...
>
>|||you should have given the precision as 4 and scale as 1
you had declared it wrong. Change it and try it.|||Thanks a lot.. it's working!!!
"Omnibuzz" wrote:

> you should have given the precision as 4 and scale as 1
> you had declared it wrong. Change it and try it.

Data type mismatch in criteria expresioin

Hi again, I'm having problems with an insert record page to insert records into a msaccess database via sql and dreamweaver mx.asp pages.

There are four date fields that can be entered (all not required), but it keep getting the

ERROR;Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

from my documentation there seems to be a known problem with msaccess and update/insert server behaviours....

Has anyone managed to overcome this issue, and if so, how ??

thanks for your help...That may be because of the Date format on the access database.
Try to insert the date in this format:
aaaa-mm-dd

______________
Paulo Gonalves|||thanks heaps...I actually got it sorted about 20 minutes before I read your reply...and you were right...I had the msaccess date formats set to an input mask that I don't think dreamweaver took a liking to...

cheers.

Data type in audit record

Hi,

I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:

TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)

TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change

So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.

Any thoughts/recommendations/criticism would be greatly appreciated.

Thanks
AlexSorry for replying to myself - I forgot to state that I'm using SQL
Server 2000 standard edition.|||WombatDeath@.gmail.com wrote:

Quote:

Originally Posted by

I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:
>
TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)
>
TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change
>
So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.
>
Any thoughts/recommendations/criticism would be greatly appreciated.

Do you actually have anything (or any reasonable prospect of having
anything in future) for which NVARCHAR(4000) wouldn't be good enough?

Whatever you do, I strongly recommend keeping tabs on how quickly it
grows, showing that trend information to the client, and (1) narrow it
down to the tables that really need an audit trail and/or (2) come up
with a sane archive-and-purge schedule.|||On Mar 30, 3:42 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

WombatDe...@.gmail.com wrote:

Quote:

Originally Posted by

I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:

>

Quote:

Originally Posted by

TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)

>

Quote:

Originally Posted by

TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change

>

Quote:

Originally Posted by

So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.

>

Quote:

Originally Posted by

Any thoughts/recommendations/criticism would be greatly appreciated.

>
Do you actually have anything (or any reasonable prospect of having
anything in future) for which NVARCHAR(4000) wouldn't be good enough?
>
Whatever you do, I strongly recommend keeping tabs on how quickly it
grows, showing that trend information to the client, and (1) narrow it
down to the tables that really need an audit trail and/or (2) come up
with a sane archive-and-purge schedule.

Yeah, unfortunately we do have several tables with a column of type
text. These generally don't hold anything close to 4000 chars but
there's nothing actually preventing them from doing so. But...if
there's no tidier option I think I may just truncate to 4000 and be
done with it. We're not auditing to fulfil legal obligations or
anything nasty like that so I don't think it will be a problem.

Your point about maintenance is well taken. I've specified that the
application's auditing must be configurable on an entity-by-entity
basis, and every so often we'll archive away any old data for fast-
changing entities.

Thanks very much for your input!|||(WombatDeath@.gmail.com) writes:

Quote:

Originally Posted by

I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:
>
TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)
>
TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change
>
So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.

That is not going to be fun in SQL 2000. In SQL 2005 you could build a
generic audit solution on the xml data type.

I would recommend that you research the market for audit products. I
know for instance that ApexSQL has a something they call SQLAudit
if memory serves.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I want my application to audit any data changes (update, insert, delete) made by the users. Rather than have an audit table mirroring each user table, I'd prefer to have a generic structure which can log anything. <<

Any chance you might post DDL instead of your personal pseudo-code?
And I hope you know that auto-numbering is not a relational key.
Finally, Google "EAV design flaw" for tens of thousands of words on
why this approach stinks. There is no such magical shape shifting
table in RDBMS. Data Versus metadata, etc.? Freshman database
course, 3rd week of the quarter?

While you might like this kludge your accountants and auditors will
not. NEVER keep audit trails on the same database or even the same
hardware as the database.

Quote:

Originally Posted by

Quote:

Originally Posted by

>Any thoughts/recommendations/criticism would be greatly appreciated. <<

Look at third party tools that follow the law and get a basic dat
modeling book.

Data Transpose - need help

Hi all,
Help me out, i am trying to get all values in Table A and insert into table B, i though of writing cursor. see endof the message

Table A:
CREATE TABLE [dbo].[M_SCANNEDSURVEY_AP] (
[M_CustomerSurveyID] [bigint] NULL ,
[SurveyID] [bigint] NULL ,
[LoadStatus] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[5] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[6] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[7] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[8] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[9] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[10] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[11] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[12] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO

Table B:
CREATE TABLE [dbo].[M_RESPONSE] (
[M_CustomerSurveyID] [bigint] NOT NULL ,
[SurveyID] [bigint] NOT NULL ,
[SeqNumber] [bigint] NOT NULL ,
[Response] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO

I want to insert everything in table A to table B
M_CustomerSurveyID -> M_CustomerSurveyID
SurveyID -> SurveyID
I will hardcode insert 1,2,3,4,5,6,7,8,9,10,11,12 for sequence number
values of 1,2,3,4,5,6,7,8,9,10,11,12 -> Response
DateSubmitted -> DateSubmitted

Declare @.Count_Scan INT

BEGIN
-- Get the count of scanned data in scanned data table with LoadStatus = N
SELECT @.Count_Scan = COUNT(*) from M_SCANNEDSURVEY_APTEST where
LoadStatus = 'N'

IF @.Count_Scan > 0
BEGIN
DECLARE ScanData_Cursor CURSOR FOR
SELECT * FROM M_SCANNEDSURVEY_AP WHERE LoadStatus = 'N'
OPEN ScanData_Cursor
FETCH NEXT FROM ScanData_Cursor
While (@.@.Fetch_Status <> -1)
Begin
If (@.@.Fetch_Status = -2)
Begin
FETCH NEXT FROM ScanData_Cursor
Continue
End
CLOSE ScanData_Cursor
DEALLOCATE ScanData_Cursor
END

SET @.CountSuccess = 'Y'
ENDUse your script which created TableA to create TableB. Export data from TableA and import it to TableB.|||I want to do it automatically once in a day using dts, how can i insert the data into table B|||INSERT INTO TableB(Field1, Field2, Field3, ...)
SELECT Field1, Field2, Field3, ... FROM TableA|||And never use SELECT * in code...

Data Transformation Services (DTS)(Bulk Insert)

Hi All,

I'm using DTS package, a tool to transfer data from a txt file to database(Bulk Insert).

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view.It seems that the Bulk Insert task supports only OLE DB connections for the destination database. But I want to use sql server authentication as OLEDB connection requires windows authentication.

So can the bulk insert be done using SQLServer authentication ? if yes then please help me.

I have given the code snippet below.

Code Sample:

Dim oPackage As New DTS.Package2()
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.BulkInsertTask
Try
oConnection = oPackage.Connections.New("SQLOLEDB")
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSBulkInsertTask")
oCustomTask = oTask.CustomTask
With oConnection
oConnection.Catalog = "pubs"
oConnection.DataSource = "(local)"
oConnection.ID = 1
oConnection.UseTrustedConnection = True
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"
End With
oPackage.Connections.Add(oConnection)
oConnection = Nothing
With oStep
.Name = "GenericPkgStep"
.ExecuteInMainThread = True
End With
With oCustomTask
.Name = "GenericPkgTask"
.DataFile = "c:\dts\authors.txt"
.ConnectionID = 1
.DestinationTableName = "pubs..authors"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With
oStep.TaskName = oCustomTask.Name
With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With
oPackage.Execute()
Catch ex As Exception
MsgBox("Error: " & CStr(Err.Number) & vbCrLf_
& Err.Description, vbExclamation, oPackage.Name)
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try

This is really a SSIS forum not DTS. There is a newsgroup for DTS, with a web/forum style interface.

Corrected Snippet

oConnection.UseTrustedConnection = False ' Must be false to use SQL Security
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"