Wednesday, March 09, 2005

[itsdifferent] ADO.NET DataAdapter Update command --- Help req.

---------- Forwarded message ----------
From: Umang_Bhavsar/CONSULTANT/ITNSE.NSEIT@nseit.biz

Date: Tue, 8 Mar 2005 12:00:02 +0530
Subject: [itsdifferent] ADO.NET DataAdapter Update command --- Help req.
To: itsdifferent@yahoogroups.com

Hi All.

We have a ASP.Net page in which we need to generate a report. By the
conventiontion, we follow the following steps.

1. Create a temp table (##table) --- This is done by executing
dynamic sql (tables formed at runtime) by a stroed procedure
2. Fill the data in that from fetching different tables/views
3 Maniputate data in temp table
4 Fetch the data which have been maniputated according to our need and
display it.

For the step 3, we use SQL DataAdapter class,
the code snipset is as follow...

'*************code STRATS

SVSql = "SELECT
id,Running_balance,DebitAmount,CreditAmount,Narration,VoucherUniqueNo,Dummy5
,voucher_date FROM dbo." & SVLedgerTableName & " WHERE Dummy3 = ''
Order by id "
' SVLedgerTableName is a temp. table generated in step 1
' the columan id-- is primary key and identity column in this temp table
Try
objDataAdapter = New SqlDataAdapter(SVSql, objConn)
' Create command builder. This line automatically
generates the update commands for you, so you don't
' have to provide or create your own.
myDataRowsCommandBuilder = New
SqlCommandBuilder(objDataAdapter)

objDataAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey

objDataAdapter.Fill(dsLedger, SVLedgerTableName)
Dim clmArr(0) As Data.DataColumn
clmArr(0) = dsLedger.Tables(0).Columns("id")
dsLedger.Tables(0).PrimaryKey = clmArr

Dim i As Int32
If dsLedger.Tables(0).Rows.Count > 0 Then '
' Manipulation goes here
Loops etc

objDataAdapter.Update(dsLedger, SVLedgerTableName)
'<----------ERROR GENERATION
endIf
catch
end try

'*************code over

It is working perfectly as it should be. but on development server only.
On testing server it gives following error./ exception

Dynamic SQL generation for the UpdateCommand is not supported against
a SelectCommand that does not return any key column information.

NOTE :
1. for all the steps mentioned above, "onnection" is kept open.
2 at debugging mode, while viewing TEMP TABLE 'S property the column
id is shown as a primary key (sp_help temp table )
3 While creating database, the script is run under sa user, but
application connects db with a different user say userOne
4 useOne is dbOwner for the perticular database.
5 There is no exception at development server --- at testing server
(with different Database server/ web server) environement it gives
above exception.

Waiting for a quick feedback... pls
Regards

Umang Bhavsar

Probos
NSE iT Ltd.
1st Floor, Trade Glob,
J.B. Nagar, Andheri Kurla Raod,
Mumbai.
(022) 28277842 (Direct)
ubhavsar@nseit.biz

Note: This Group is not a Job Searching Group, so please co-operate
and dont transfer any kind of job related material across this
Group.AnyOne doing so can be banned from the Group
Thanx , Group Co-Ordinators

Yahoo! Groups Sponsor
ADVERTISEMENT

________________________________
Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/itsdifferent/

To unsubscribe from this group, send an email to:
itsdifferent-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

--
--
Regards...
/*******************************
itsDifferent
http://its-Different.blogspot.com
http://groups.yahoo.com/group/itsdifferent/join
http://groups.msn.com/its-Different/join
http://groups-beta.google.com/group/itsdifferent
*******************************/

No comments: