Another SCOPE_IDENTITY() / SqlDataSource Issue

Hi Folks,

I've been Googling about for ages trying to get to the bottom of this, I've found plenty of stuff but nothing with the answer I'm looking for :(

What I'm trying to do is use SqlDataSource programatically (i.e. not the <asp:SqlDataSource ... /> route) to call a stored procedure which inserts values into a table, the Sproc ends with RETURN SCOPE_IDENTITY() in order to return the autoincrement identity of the row that was just created. My specific problem is getting the returned value assigned to any sort of variable at the C# end! At the moment it seems to be returning 1 for some reason, it used to return -1 so I guess this is a step in the right direction... Here's my code:

int importantReturnValue = 0;

SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = connString;
sds.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
sds.InsertCommand = "sp_insertValues";

sds.InsertParameters.Add("creator", creator);
sds.InsertParameters.Add("title", title);
// And so on.. Perhaps I need to add importantReturnValue to sds as some sort as parameter? - If so, how should I declare this at the Sproc end?

...

importantReturnValue = sds.Insert();

Label1.Text = importantReturnValue.ToString();

Thanks,

Ally

Hi,

the INSERT() method of the SqlDataSource returns the number of affected rows. A good way to retrieve the id is to use an output variable. See my reply to this post:http://forums.asp.net/thread/1603790.aspx

Regards
Marc André

|||

Hi Marc,

Many thanks for the prompt reply. I was attempting to use the SqlDataSource as opposed to the SqlCommand object you used in your example. I just couldn't find a way to assign the returned value from the Sproc to a variable, so I've changed it to now use the SqlCommand route instead. After a bit of playing about with the Sproc, all working now, many thanks for your help :) .

I seem to spend hours and hours trying to figure out how to get something SQL related to work, only to find out I'm using the wrong Sql c# object for my needs - is there some guide somewhere I'm missing?! Maybe I just need more practise at reading APIs...

All the best,

Ally

Analysis: How to put this in tables?

Hi,
I have some question:
How do I put the folowing stuff into tables:
210004000231023551079690000463 1000000000010000230204841010060
2302040370100
23000400020000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000331023551079690000463 1000000000022000230204841010060
2302040370100
23000400030000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000431023551079690000463 1000000000008000230204841010060
2302040370100
23000400040000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000531023551079690000463 1000000000008400230204841010110
2302040371100
23000400050000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 001
310004000631023551079690000463 84101000100430003 01100 00001408804 11
0 1
310004000731023551079690000463 841010001001BANQUE ACCORD
0 1
310004000831023551079690000463 841010001001RUE JEANNE MAILLOTTE 4/6
0 1
310004000931023551079690000463 84101000100159110 LA MADELEINE FR
0 1
310004001031023551079690000463 841010001002COM 2EME SEM
0 1
310004001131023551079690000463 841010001005SOGEFRPPLLM
0 1
310004001231023551079690000463 841010000 Pi ce justificative en annexe
Votre ordre de paiemen t de 1 0
3200040012 22.000,00 EUR R f rences de l'op ration:
H1040223K007969 Frais du ba nquier correspon 1 0
3300040012dant 1 0,00 EUR Commission de
paiement 0 1
It's really simple:
- 21-records: these are records beginning with "21": they are the
'main'-records containing the information
- 22 and 23-records starting with 22 or 23: details of the 21-record
- 31, 32, 33-records: also details of the 21-records.
positions in 21-record:
- On position 35 I have an indiciation of Credit/Debit
- 83-90: Coding of the 21-record
- 91: Strucutred or Non-Structured.
-> I need these 3 things to identify in some way the 21-record.
Now my question is: I have to put all this stuf in tables, and I need to be
able to do easily and fast query's with it.
I guess the most performant way would be to put every record in it's own
kidn of table: so I would create an tbl21, tbl22, tbl23, tbl31, etc Liek
that I would be able to put the Credit/Debit, Coding etc in there own Column
and than I can do some fast query's on it.
But on the other hand: When I put everything in different tables, it will be
more difficult to have a nice overview of the whole file (21 + 22 + 23 +
... ), and to be able to know which cmae after which one etc.
So I thought about putting everything in one table.
like this:
CREATE TABLE [dbo].[tblExtractChild128] (
[ExtractID] [int] NOT NULL , -> unique ID
[ParentID] [int] NULL , -> indicates which is
the
Parent of the 22, 23, 31-record (so the ID of the 21-record)
[Identification] [varchar] (2) COLLATE French_CI_AS NULL , ->
"21",
"22", "23", "31", "32", "33": to easily see which type of record it is
[Text] [varchar] (128) COLLATE French_CI_AS NULL , ->
contains the whole Line ("210004000231023551079690000463
1000000000010000230204841010060
2302040370100"
[CD] [bit] NULL
-> position 35 of the 21-line: Credit/Debit
[Amount] [decimal](18, 0) NULL
-> Amount = value in EURO of the record
[Coding] [varchar] (8) COLLATE French_CI_AS NULL , ->
83-90:
Coding of the 21-record
[Structured] [bit]
-> 91: Strucutred or Non-Structured.
) ON [PRIMARY]
GO
In which I put all the records, and for the 21-records I use the Columns CD,
Coding and Structured, for the others I don't.
Like this I can do query's:
SELECT DISTINCT r21.*
FROM tblExtractChild128 r21 LEFT JOIN tblExtractChild128 r23 ON (r21.ID =
r23.ParentID)
LEFT JOIN tblExtractChild128 r31 ON (r21.ID = r31.ParentID)
WHERE (r21.Identification = '21')
AND (r21.Coding= '84101006')
AND (r22.Text LIKE '%BANQUE ACCORD%')
AND (r31.Text LIKE '%RUE JEANNE MAILLOTTE%')
Ok, this works: but I have my doubts if this is really the best way to do
all this stuff. Is there a way which will have much more performance? Work
nicer, more easily for maintenance etc?
For those who read this untill the end and try to formulate an answer:
thanks a lot! hehe :-)
PieterPieter,
You may want to start explaining what this data is all about. Then start
identifying what the entity types, their attributes and relationships among
them. Without such information, it is hard to provide any worthy
suggestions.
Anith

Analysis Services 2005 Derived Member

Hi,

I can't seem to find any information about creating a derived member in SSAS 2005. I have found a bunch of stuff for 2000. I have some Calculated members that are giving me some performance pains that I was hoping to solve by creating derived members.

Any info would be greatly appreciated.

S

WHat is "derived member" ? Neither AS2000 nor AS2005 have such a concept. Can you point out to the information you found, so it will be easier for us to understand what you mean. Or, perhaps, you can describe your scenario in detail, and then this forum might be able to help.|||

I apologize. It was Derived Member.

Here is a link: http://www.databasejournal.com/features/mssql/article.php/3394681

|||

In this case, the "derived member" discussion in that link is the equivalent of adding named calculations within the DSV of an SSAS 2005 solution.

A named calculation is added to a table (or view) that has been included within a DSV. The named calculation takes an expression (in the SQL dialect of the underlying RDBMS involved) and provides a name for the resulting value. This then becomes another available column within the table from the perspective of the rest of the SSAS solution. Thus, a measure could be built using the named calculation (if it is in a fact table) or an attribute could be created using it (if it is in a dimension table).

HTH,

Dave Fackler