Showing posts with label net. Show all posts
Showing posts with label net. Show all posts

Wednesday, March 28, 2012

Profile, SQLDataSource and GridView

Hello.

When I create a user at the ASP.NET database, I need to insert more fields than the defaults, and I do it like this:

Dim customProfile As ProfileCommon = ProfileCommon.Create(CreateUserWizard1.UserName, True)

customProfile.telephone =(CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("TelephoneText"),TextBox)).Text
(telephone example)


Those data are inserted at the DB at the aspnet_Profile table, in the fields PropertyNames & PropertyValuesString, but they are saved together (see image above)


I want to separate those properties in the GridView as long as each property appears in a column, is it possible?Tongue Tied
Thank you very much, i'm expecting your answers.

If you want an additional column you could change existing TSQL
SELECT A, B, C FROM TABLE
to
SELECT A, B, C, '' AS D FROM TABLE

|||

I want to separate these properties in columns, not to create a new empty column.

|||Regrettably you published an image - please publish the text that you need split and I will write some TSQL for you.|||

GridView shows me this:

GridView bad

and I need this:


|||Please copy out the text and post that. The image is useless to me.|||

The text is the following:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" CellPadding="4" CssClass="gridview" DataSourceID="SqlDataSource1"
ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:BoundField DataField="UserName" HeaderText="ID" SortExpression="UserName" />
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
<asp:BoundField DataField="PropertyNames" HeaderText="Propiedades" SortExpression="PropertyNames" />
<asp:BoundField DataField="PropertyValuesString" HeaderText="Valores" SortExpression="PropertyValuesString" />
</Columns>
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\webfax.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT aspnet_Membership.Email, aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesString, aspnet_Users.UserName FROM aspnet_Membership INNER JOIN aspnet_Profile ON aspnet_Membership.UserId = aspnet_Profile.UserId INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId">
</asp:SqlDataSource>

|||Thank you for posting the program source. However what I need is the text of the displayed field that needs to sub-divided. I can then modifySELECT aspnet_Membership.Email, aspnet_Profile.PropertyNames,aspnet_Profile.PropertyValuesString, aspnet_Users.UserName FROMaspnet_Membership INNER JOIN aspnet_Profile ON aspnet_Membership.UserId= aspnet_Profile.UserId INNER JOIN aspnet_Users ONaspnet_Membership.UserId = aspnet_Users.UserIdto bsub-divide the fields.|||

ok, both texts are:

empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:

and

UBuLa cartera45678que yo tengo


these are for the first case, for the second case:

empresa:S:0:3:nombre:S:3:8:telefono:S:11:5:apellidos:S:16:13

and

453el huevo76534de la gallina

thanks

|||

Presumably you need
empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:

broken down to
empresa:S:0:3:
nombre:S:3:10:
telefono:s:13:5:
apellidos:S:18:12:

using the delimiters I have marked in bold

|||

Based on the presumption in my previous post

DECLARE @.VAR VARCHAR(100)
DECLARE @.EMPRESSA VARCHAR(100)
DECLARE @.NOMBRE VARCHAR(100)
DECLARE @.telefono VARCHAR(100)
DECLARE @.apellidos VARCHAR(100)
SET @.VAR = 'empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:'
PRINT @.VAR
--empresa:S:0:3:
--nombre:S:3:10:
--telefono:s:13:5:
--apellidos:S:18:12:
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('nombre:', @.VAR)
PRINT @.IMARK1
IF @.IMARK1 > 0 BEGIN
SET @.EMPRESSA = SUBSTRING(@.VAR, 1, @.IMARK1-1)
PRINT '@.EMPRESSA [' + @.EMPRESSA + ']'
END
SET @.IMARK1 = CHARINDEX('apellidos:', @.VAR)
IF @.IMARK1 > 0 BEGIN
SET @.apellidos = SUBSTRING(@.VAR, @.IMARK1,DATALENGTH(@.VAR))
PRINT '@.apellidos [' + @.apellidos + ']'
END
SET @.IMARK1 = CHARINDEX('apellidos:', @.VAR)
SET @.IMARK2 = CHARINDEX('telefono:', @.VAR)
IF @.IMARK1 > 0 AND @.IMARK2 > 0 AND @.IMARK1 > @.IMARK2 BEGIN
SET @.telefono = SUBSTRING(@.VAR, @.IMARK2,@.IMARK1 - @.IMARK2)
PRINT '@.telefono [' + @.telefono + ']'
END
SET @.IMARK1 = CHARINDEX('telefono:', @.VAR)
SET @.IMARK2 = CHARINDEX('nombre:', @.VAR)
IF @.IMARK1 > 0 AND @.IMARK2 > 0 AND @.IMARK1 > @.IMARK2 BEGIN
SET @.nombre = SUBSTRING(@.VAR, @.IMARK2,@.IMARK1 - @.IMARK2)
PRINT '@.nombre [' + @.nombre + ']'
END

gives

empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:
15
@.EMPRESSA [empresa:S:0:3:]
@.apellidos [apellidos:S:18:12:]
@.telefono [telefono:s:13:5:]
@.nombre [nombre:S:3:10:]

The TSQL will need to be wrapped in in some scalar functions

|||

The functions

CREATE FUNCTION dbo.fnGetApellidos (@.VAR VARCHAR(250))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.RETURN VARCHAR(50)
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('apellidos:', @.VAR)
IF @.IMARK1 > 0 BEGIN
SET @.RETURN = SUBSTRING(@.VAR, @.IMARK1,DATALENGTH(@.VAR))
--PRINT '@.apellidos [' + @.RETURN + ']'
END
RETURN @.RETURN
END
GO
CREATE FUNCTION dbo.fnGetEMPRESSA (@.VAR VARCHAR(250))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.RETURN VARCHAR(50)
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('nombre:', @.VAR)
--PRINT @.IMARK1
IF @.IMARK1 > 0 BEGIN
SET @.RETURN = SUBSTRING(@.VAR, 1, @.IMARK1-1)
--PRINT '@.EMPRESSA [' + @.RETURN + ']'
END
RETURN @.RETURN
END
GO
CREATE FUNCTION dbo.fnGetTelefono (@.VAR VARCHAR(250))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.RETURN VARCHAR(50)
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('apellidos:', @.VAR)
SET @.IMARK2 = CHARINDEX('telefono:', @.VAR)
IF @.IMARK1 > 0 AND @.IMARK2 > 0 AND @.IMARK1 > @.IMARK2 BEGIN
SET @.RETURN = SUBSTRING(@.VAR, @.IMARK2,@.IMARK1 - @.IMARK2)
--PRINT '@.telefono [' + @.RETURN + ']'
END
RETURN @.RETURN
END
GO
CREATE FUNCTION dbo.fnGetNombre (@.VAR VARCHAR(250))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.RETURN VARCHAR(50)
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('telefono:', @.VAR)
SET @.IMARK2 = CHARINDEX('nombre:', @.VAR)
IF @.IMARK1 > 0 AND @.IMARK2 > 0 AND @.IMARK1 > @.IMARK2 BEGIN
SET @.RETURN = SUBSTRING(@.VAR, @.IMARK2,@.IMARK1 - @.IMARK2)
--PRINT '@.nombre [' + @.RETURN + ']'
END
RETURN @.RETURN
END
GO

When run by
DECLARE @.VAR VARCHAR(100)
SET @.VAR = 'empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:'
PRINT @.VAR
PRINT dbo.fnGetEmpressa(@.VAR)
PRINT dbo.fnGetNombre(@.VAR)
PRINT dbo.fnGetTelefono(@.VAR)
PRINT dbo.fnGetApellidos(@.VAR)

give
empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:
empresa:S:0:3:
nombre:S:3:10:
telefono:s:13:5:
apellidos:S:18:12:

Thus instead of
SELECT VAR FROM TABLENAME

you can have
SELECT dbo.fnGetEmpressa(VAR) ASEmpressa,dbo.fnGetNombre(VAR) ASNombre,
dbo.fnGetTelefono(VAR) ASTelefono,dbo.fnGetApellidos(VAR) ASApellidos FROM TABLENAME

this giving you the required split on columns

sql

Friday, March 9, 2012

Process the subscription from asp.net

I have a dot net web application that needs to, eventually, send a report by
e-mail It doesnâ't have a scheduled behavior.
Is there any way of processing subscriptions from asp.net?
Thanks!Perhaps this article can help you:
http://www.odetocode.com/Articles/114.aspx
"luczinski" wrote:
> I have a dot net web application that needs to, eventually, send a report by
> e-mail It doesnâ't have a scheduled behavior.
> Is there any way of processing subscriptions from asp.net?
> Thanks!
>

Process SQL Transactions? Easy Question :)

Hi,

Environment - VB.NET, ASP.NET, SQL Server 2000.

In SQL 2000, I am sending an XML, which carries data for two tables. Let's say, I am inserting half of the fields in TABLE1 and rest in TABLE2.

Specifically, I want to use Transaction Processing for inserting the rows in both tables. If insertion in one table fails, any inserted data should ROLLBACK and come out of procedure with relevant error code.

Please advice or send me any example links. Thanks

PankajHave you looked at the Begin Tansaction examples in the BOL? That contains an example with the commit transaction but not rollback transaction. I'm assuming you'll do the inserts in one SP. If that's true then you can follow those examples to help do your inserts and then test for errors and rollback if errors are encountered. Something like
SP...

BEGIN TRANSACTION
Insert code here

IF @.@.Error <> 0
ROLLBACK TRANS
ELSE
COMMIT TRANS

That's a rough idea but hopefully will get you started.

Wednesday, March 7, 2012

Process in SQL Server 2005

Hi, I am developing an application with vb.net and sql server 2005 express edition. If a user logs in (through the application), I want to query the computer host from sql server 2005 in which the user logs on. Is there any special table or procedure that I can use to achieve this? Thanks.

hi,

ghozan wrote:

Hi, I am developing an application with vb.net and sql server 2005 express edition. If a user logs in (through the application), I want to query the computer host from sql server 2005 in which the user logs on. Is there any special table or procedure that I can use to achieve this? Thanks.

can you please expand on your requirement? I did really not understand what you are looking for..

regards

|||

check system sp ... sp_who or

select *from sys.dm_exec_sessions

Madhu

Process in SQL Server 2005

Hi, I am developing an application with vb.net and sql server 2005 express edition. If a user logs in (through the application), I want to query the computer host from sql server 2005 in which the user logs on. Is there any special table or procedure that I can use to achieve this? Thanks.

hi,

ghozan wrote:

Hi, I am developing an application with vb.net and sql server 2005 express edition. If a user logs in (through the application), I want to query the computer host from sql server 2005 in which the user logs on. Is there any special table or procedure that I can use to achieve this? Thanks.

can you please expand on your requirement? I did really not understand what you are looking for..

regards

|||

check system sp ... sp_who or

select *from sys.dm_exec_sessions

Madhu

Monday, February 20, 2012

procedure, report parameters

(sql/rs v2K, VS .Net 2003)
I have a problem with several multi-dataset reports. I've got several
single-dataset reports working just fine, but these multi-datasets are
problematic. For example, I have one procedure which works w/out parameters,
just returns a statistics report. I have modified the proc to accept a
parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the 2nd
chunk, etc., all the way thru to @.ch=5. All of them together comprise the
whole report. The proc works just fine in query analyzer, like this:
exec procname 1
exec procname 2
exec procname 3
exec procname 4
exec procname 5
now, in RS, i have created 5 datasets. each with the fields returned by
each procedure invocation. mind you, these aren't actual fields, it's select
this as value, select this as count, select this as volume, etc.
the datasets each are Text command types, the query strings are simply
'procname 1', 'procname 2', etc.
all of them work just fine in the Data tab in VS.
in the layout tab, i've created several tables and dragged/dropped the
fields into each accordingly
in the preview tab, i've got data returned in the top two chunks. WOW! cuz
i have been trying this for a very long time, yesterday i had no data at all,
only column headings, i don't know what i changed, but, now i have data in
two chunks, none in the rest. only column headings.
please, can anybody provide some direction on the right way to wrap the
report(s) around procedures like this? remember, the procedures all all just
statisitcal analysis data...no user input is required. i just need to
deliver nicely via email on a scheduled basis.
-- LynnAnd, when I refresh the report (in Preview tab, VS), sometimes all 5 chunks
are there, as desired...but then, I refresh again, and two are blank. (just
the column headings, no data) then, refresh again, and a different two are
blank.
(by 'chunks', i just mean different sections of the report. this particular
report has five different sections...or 'chunks'. each chunk has a
corresponding dataset)
please advise. this is terribly urgent.
-- Lynn
"Lynn" wrote:
> (sql/rs v2K, VS .Net 2003)
> I have a problem with several multi-dataset reports. I've got several
> single-dataset reports working just fine, but these multi-datasets are
> problematic. For example, I have one procedure which works w/out parameters,
> just returns a statistics report. I have modified the proc to accept a
> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the 2nd
> chunk, etc., all the way thru to @.ch=5. All of them together comprise the
> whole report. The proc works just fine in query analyzer, like this:
> exec procname 1
> exec procname 2
> exec procname 3
> exec procname 4
> exec procname 5
> now, in RS, i have created 5 datasets. each with the fields returned by
> each procedure invocation. mind you, these aren't actual fields, it's select
> this as value, select this as count, select this as volume, etc.
> the datasets each are Text command types, the query strings are simply
> 'procname 1', 'procname 2', etc.
> all of them work just fine in the Data tab in VS.
> in the layout tab, i've created several tables and dragged/dropped the
> fields into each accordingly
> in the preview tab, i've got data returned in the top two chunks. WOW! cuz
> i have been trying this for a very long time, yesterday i had no data at all,
> only column headings, i don't know what i changed, but, now i have data in
> two chunks, none in the rest. only column headings.
> please, can anybody provide some direction on the right way to wrap the
> report(s) around procedures like this? remember, the procedures all all just
> statisitcal analysis data...no user input is required. i just need to
> deliver nicely via email on a scheduled basis.
> -- Lynn|||You might just be seeing an artifact of the development environment. Report
designer caches data. Look where your .rdl file is and you will see a
.rdl.data file. Delete it and you will be guaranteed that it will query your
database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:96659649-7A0D-488A-B6AD-C6BDD9A2651F@.microsoft.com...
> And, when I refresh the report (in Preview tab, VS), sometimes all 5
> chunks
> are there, as desired...but then, I refresh again, and two are blank.
> (just
> the column headings, no data) then, refresh again, and a different two
> are
> blank.
> (by 'chunks', i just mean different sections of the report. this
> particular
> report has five different sections...or 'chunks'. each chunk has a
> corresponding dataset)
> please advise. this is terribly urgent.
> -- Lynn
>
> "Lynn" wrote:
>> (sql/rs v2K, VS .Net 2003)
>> I have a problem with several multi-dataset reports. I've got several
>> single-dataset reports working just fine, but these multi-datasets are
>> problematic. For example, I have one procedure which works w/out
>> parameters,
>> just returns a statistics report. I have modified the proc to accept a
>> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the
>> 2nd
>> chunk, etc., all the way thru to @.ch=5. All of them together comprise
>> the
>> whole report. The proc works just fine in query analyzer, like this:
>> exec procname 1
>> exec procname 2
>> exec procname 3
>> exec procname 4
>> exec procname 5
>> now, in RS, i have created 5 datasets. each with the fields returned by
>> each procedure invocation. mind you, these aren't actual fields, it's
>> select
>> this as value, select this as count, select this as volume, etc.
>> the datasets each are Text command types, the query strings are simply
>> 'procname 1', 'procname 2', etc.
>> all of them work just fine in the Data tab in VS.
>> in the layout tab, i've created several tables and dragged/dropped the
>> fields into each accordingly
>> in the preview tab, i've got data returned in the top two chunks. WOW!
>> cuz
>> i have been trying this for a very long time, yesterday i had no data at
>> all,
>> only column headings, i don't know what i changed, but, now i have data
>> in
>> two chunks, none in the rest. only column headings.
>> please, can anybody provide some direction on the right way to wrap the
>> report(s) around procedures like this? remember, the procedures all all
>> just
>> statisitcal analysis data...no user input is required. i just need to
>> deliver nicely via email on a scheduled basis.
>> -- Lynn|||Hi Bruce. There are no data files there at all. ( I wasn't able to get back
to you sooner, as I had a bit of a family emergency. ) I'm not sure how to
proceed with this. I am looking at the rpt right now through
http://server/reports, it is doing the same thing. First invocation, it had
all chunks of the rpt except for chunk 3. After a refresh, that chunk was
there, too. Two more refreshes, entire resultset is there fine, but then
after another refresh, chunk 4 is now missing/blank.
Another much less urgent question would be subscription scheduling. Can
those things vary at all? Meaning, how can i run it on all production
weekdays, instead of just all weekdays? you know...how do I correctly
account for the holidays?
Please advise.
-- Lynn
"Bruce L-C [MVP]" wrote:
> You might just be seeing an artifact of the development environment. Report
> designer caches data. Look where your .rdl file is and you will see a
> ..rdl.data file. Delete it and you will be guaranteed that it will query your
> database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:96659649-7A0D-488A-B6AD-C6BDD9A2651F@.microsoft.com...
> > And, when I refresh the report (in Preview tab, VS), sometimes all 5
> > chunks
> > are there, as desired...but then, I refresh again, and two are blank.
> > (just
> > the column headings, no data) then, refresh again, and a different two
> > are
> > blank.
> >
> > (by 'chunks', i just mean different sections of the report. this
> > particular
> > report has five different sections...or 'chunks'. each chunk has a
> > corresponding dataset)
> >
> > please advise. this is terribly urgent.
> > -- Lynn
> >
> >
> > "Lynn" wrote:
> >
> >> (sql/rs v2K, VS .Net 2003)
> >> I have a problem with several multi-dataset reports. I've got several
> >> single-dataset reports working just fine, but these multi-datasets are
> >> problematic. For example, I have one procedure which works w/out
> >> parameters,
> >> just returns a statistics report. I have modified the proc to accept a
> >> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the
> >> 2nd
> >> chunk, etc., all the way thru to @.ch=5. All of them together comprise
> >> the
> >> whole report. The proc works just fine in query analyzer, like this:
> >>
> >> exec procname 1
> >> exec procname 2
> >> exec procname 3
> >> exec procname 4
> >> exec procname 5
> >>
> >> now, in RS, i have created 5 datasets. each with the fields returned by
> >> each procedure invocation. mind you, these aren't actual fields, it's
> >> select
> >> this as value, select this as count, select this as volume, etc.
> >>
> >> the datasets each are Text command types, the query strings are simply
> >> 'procname 1', 'procname 2', etc.
> >>
> >> all of them work just fine in the Data tab in VS.
> >> in the layout tab, i've created several tables and dragged/dropped the
> >> fields into each accordingly
> >>
> >> in the preview tab, i've got data returned in the top two chunks. WOW!
> >> cuz
> >> i have been trying this for a very long time, yesterday i had no data at
> >> all,
> >> only column headings, i don't know what i changed, but, now i have data
> >> in
> >> two chunks, none in the rest. only column headings.
> >>
> >> please, can anybody provide some direction on the right way to wrap the
> >> report(s) around procedures like this? remember, the procedures all all
> >> just
> >> statisitcal analysis data...no user input is required. i just need to
> >> deliver nicely via email on a scheduled basis.
> >>
> >> -- Lynn
>
>