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?
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:

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

Processing XML data in a SQL Server field

Has anyone cracked this? I want to process xml data which is contained within
a SQL server field. I'm using other fields in the table in the normal way.
The xml field actually contains numerous data values which I want to pass
into the the chart object.
So far there seems to be two options to me:
1) Export the entire row of the table to xml and then use a custom data
extension which knows about xml to read it. This route is described in Teos'
book.
2) Call out to a custom assembly and have it process the xml.
My views on the above are:
1) Seems clunky. Already have the data and now I need to export it to a file
and then read it back in?
2) Seems ok but how do I handle multiple items,, such as the data points?
Anyone have any other ideas before I wade in with my boots?!using a custom assembly works like a charm - We have entire XML docs stored
in blob fields and parse them on the fly to get the information we need form
them.

Processing NULL values

I have an input file that may have NULL's in it's fields. The fields are of a various data types.

The NULL value is represented by ? - question mark.

Is there a way to set package or file connection up in a way - so it treats ? as NULL?

The only other way I found is to use Derived column - and so I have to create derived field for most of the fields (150 of them) since a lot of them may contain ? - or NULL.

Any ideas?

You could build your own custom source adapter but it be quicker to use the derived column.

-Jamie

|||

what adds pain is that two operations must occur for each column: relace ? with null and data conversion.

So, I have to create following expression in Derived Column for most columns:

[fiel1] == "?" ? NULL : (DT_DTAE) [fiel1]

and repeat this 150 times...