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

produce SQL statement for a database

hi
is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)(for SQL Server 2000...and maybe 7.0)

In the SQL Query Analyzer Object Browser, drill down to your table, right-click on the table name and select "Script Object...As" and it will generate the SQL for you.

Additionally, in Enterprise Manager, drill down to your "Tables" and select the table you want to query. Right-click on the table, Select Open Table --> Query. You SQL statement is generated dynamically as you select fields. A Select statement is the default. In the gray area where you see the table and all of it's fields, Right-click and there is a selection for "Change Type." In there you can change the query to an Insert, Create, etc...|||I prefer to build my own..

USE NorthWind

DECLARE @.TBName sysname, @.SQL varchar(8000)

SELECT @.TBName = 'Cust', @.SQL = ''

SELECT @.SQL = @.SQL + RTRIM(SQL) FROM (
--SELECT SQL FROM (
SELECT RTRIM(' SELECT ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(', ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT RTRIM(' FROM [' + RTRIM(TABLE_NAME) + ']') As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(' GO ') As SQL, TABLE_NAME, 5 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

SELECT @.SQL

--EXEC(@.SQL)

Produce data -> Store in table?

Hi!

I'm quite new to T-SQL and is about to build a small reporting db using SQL.
Most of the data I can move with normal INSERT INTO ... SELECT, but there are some tables that I want to
produce using T-SQL. For example I want to build a Date table like..

Date
Year
Quarter
Month
WeekDay
...

With some precalculated values for each date.

I've searched the forum but have not found any information on how to produce table contents in a good manner. I would appreciate if someone would have the time to point me in the right direction. I do not want to do this by code in my application.

My first thought is to use some kind of Insert Cursor in a While loop...

Pseudo:

declare cursor ex for 'Insert table ... '

while
begin

(produce data)
insert data

end

close cursor

While browsing the net I've got the feeling that you use cursor less in SQL Server than in other db-engines...

Have a nice day!

You could use WHILE loop without cursor:

Code Snippet

create table dates

(

Year int,

Quarter int,

Month int,

WeekDay int,

SomeData decimal

)

go

declare @.start_date datetime

declare @.end_date datetime

set @.start_date = '2007-01-01'

set @.end_date = getdate()

while @.start_date<@.end_date

begin

set @.start_date = dateadd(day, 1, @.start_date)

insert into dates values(

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

rand() --Just for demo

)

end

go

select * from Dates

|||

Best have a calendar table (you can add all the nessasary columns here), fill the table with required date range, join with your table & use the required calculations,

Code Snippet

create table calendar

(

year int,

quarter int,

month int,

weekday int,

date datetime primary key

)

Go

create proc fill_calendar(@.start_date datetime,@.end_date datetime)

as

begin

set nocount on;

while @.start_date <= @.end_date

begin

insert into calendar

select

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

@.start_date

where

not exists(select 1 from calendar where date=@.start_date);

set @.start_date = dateadd(day, 1, @.start_date)

end

end

go

--Fill your calendar when required

exec fill_calendar '1/1/2000','12/31/2007'

select * from calendar

go

select

year --other columns

,sum(yourtable.calculation)

from

calendar

inner join yourtable on yourtable.datefield = calendar.date

group by

year --other columns

processing each row in a multirow insert

I'm looking for a way to process each row in a insert ... select sentece. What i need is to update a column which is type int (not identity nor sequence) in a way it always get the max record + 1, in a sentence:

insert into my_table
(a,b,c,d)
select isnull((select max(id) from another_table),0) as a,
'b' as b, 'c' as c, 'd' as d
from table1 join table2 on t1=t2

the problem with such a sentence is the embbebed select is it is executed only once so it provides always the same value, i.e. assuming the subselect returns 7 and the join provides 2 rows, what would be inserted in my_table is:

a b c d
-- -- -- --
7 b c d
7 b c d

and not

7 b c d
8 b c d

what i expected

so what i looking for is a way to resolve such a sentence or at least some kind of fuction which generates a identity value in a similar way as identity() but being useful for no-identity tables. Maybe something similar to newid() but returning int value and it would be great if that value would be max(table_field)+1

any idea?i forgot to say don't want to use a cursor to process and insert each row|||This can be done using a single direct, if complicated, SQL statement. It would be simpler to insert your data into a temporary table with an autoincrement field, and then select from the temporary table, adding max(id) to the values in the autoincrement field.

Otherwise, you will need a select statement with a subquery that fabricates sequential values for your data (what order to you want them in? You have to choose an order with this method) and then add max(id) to these sequential values.

blindman

procedure to retrieve primary key

Hello!
I want to write a stored procedure, that will insert new row into a table and return the key of newly inserted row.check out SCOPE_IDENTITY() in bol

EDIT: i am assuming you are using an identity column for your pk. if you aren't, then you must already know the value you are inserting, right?|||That's exactly what I was looking for. Thanks.