Parsing string and Inserting each element?

Hi All,
I'll try and make this simple.
I have a column in a table that has one or more values separated by a comma.
Ex: 1234,456,322,33445,abce,ekksks
I want to go through each record and take this column data and parse it out,
then insert each element into another table.
ex:
INSERT Into GTable (RecordID, ItemValue)
Values (NewID(), <Parsed Values from each record's
Column> )
Any ideas would be greatly appreciated.
John.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"John Rugo" wrote:

> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a comm
a.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it ou
t,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>
>|||Do you mean inserting each value as a separate row? If I were to create the
insert statements manually for your example, would it look like:
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '1234')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '456')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '322')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '33445')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'abce')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'ekksks')
?
If so, then a cursor would probably be the best way to go (hopefully this is
a one time thing, because as we all know, cursors as the devil)...
If you want some quickly hacked code as an example of how the cursor would
work, post back and I'll see if I can put something together..
-Cliff
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:Ot0XwOsPFHA.4028@.tk2msftngp13.phx.gbl...
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a
comma.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it
out,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>|||John,
Here is an efficient way to do this - it's buried in one of the articles
Alejandro referred you to.
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(4000), @.time datetime
set @.time = getdate()
set @.x = replicate('foo,bar,foo,bar,ab,',30) + 'end'
select distinct Item from ListTable(@.x)
select datediff(ms,@.time,getdate())
set @.x = '10245 10345 98292 '
declare @.s varchar(400)
set @.s = replace(@.x,' ',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
John Rugo wrote:

>Hi All,
>I'll try and make this simple.
>I have a column in a table that has one or more values separated by a comma
.
>Ex: 1234,456,322,33445,abce,ekksks
>I want to go through each record and take this column data and parse it out
,
>then insert each element into another table.
>ex:
>INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
>Column> )
>Any ideas would be greatly appreciated.
>John.
>
>

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
NatyHi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:
> Hi,
> I am having problems with inserting data into a partitioned view that union
> a few tables that have a computed column (defined in the tables themselves).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
Naty
Hi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:

> Hi,
> I am having problems with inserting data into a partitioned view that union
> a few tables that have a computed column (defined in the tables themselves).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
NatyHi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:

> Hi,
> I am having problems with inserting data into a partitioned view that unio
n
> a few tables that have a computed column (defined in the tables themselves
).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains
a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Parameters to insert data from form into SQL database

Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0
That's what you inserted. :-)
Try:
cmdTest.Parameters("@.FirstN").Value = "John"
cmdTest.Parameters("@.LastN").Value = "Doe"
cmdTest.Parameters("@.Org").Value = "FLy by Night Airlines"
cmdTest.Parameters("@.Addr1").Value = "123 Main St"
cmdTest.Parameters("@.City").Value = "Anytown"
cmdTest.Parameters("@.Email").Value = nospam@.nospam.com
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<hery@.infoventures.com> wrote in message
news:1141152263.896973.136620@.p10g2000cwp.googlegr oups.com...
Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0
|||Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0
|||I'd check the database directly with Query Analyzer (QA) if you're using SQL
2000 or SQL Server Management Studio (SSMS) if you're using SQL 2005. Also,
consider using SQL Profiler to see what is being sent to SQL Server. There
could be a problem in your VB .NET code somewhere.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<hery@.infoventures.com> wrote in message
news:1141156805.957210.276400@.j33g2000cwa.googlegr oups.com...
Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0

Parameters to insert data from form into SQL database

Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0That's what you inserted. :-)
Try:
cmdTest.Parameters("@.FirstN").Value = "John"
cmdTest.Parameters("@.LastN").Value = "Doe"
cmdTest.Parameters("@.Org").Value = "FLy by Night Airlines"
cmdTest.Parameters("@.Addr1").Value = "123 Main St"
cmdTest.Parameters("@.City").Value = "Anytown"
cmdTest.Parameters("@.Email").Value = nospam@.nospam.com
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<hery@.infoventures.com> wrote in message
news:1141152263.896973.136620@.p10g2000cwp.googlegroups.com...
Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0|||Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0|||I'd check the database directly with Query Analyzer (QA) if you're using SQL
2000 or SQL Server Management Studio (SSMS) if you're using SQL 2005. Also,
consider using SQL Profiler to see what is being sent to SQL Server. There
could be a problem in your VB .NET code somewhere.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<hery@.infoventures.com> wrote in message
news:1141156805.957210.276400@.j33g2000cwa.googlegroups.com...
Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0

Parameters to insert data from form into SQL database

Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0That's what you inserted. :-)
Try:
cmdTest.Parameters("@.FirstN").Value = "John"
cmdTest.Parameters("@.LastN").Value = "Doe"
cmdTest.Parameters("@.Org").Value = "FLy by Night Airlines"
cmdTest.Parameters("@.Addr1").Value = "123 Main St"
cmdTest.Parameters("@.City").Value = "Anytown"
cmdTest.Parameters("@.Email").Value = nospam@.nospam.com
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<hery@.infoventures.com> wrote in message
news:1141152263.896973.136620@.p10g2000cwp.googlegroups.com...
Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0|||Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0|||I'd check the database directly with Query Analyzer (QA) if you're using SQL
2000 or SQL Server Management Studio (SSMS) if you're using SQL 2005. Also,
consider using SQL Profiler to see what is being sent to SQL Server. There
could be a problem in your VB .NET code somewhere.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<hery@.infoventures.com> wrote in message
news:1141156805.957210.276400@.j33g2000cwa.googlegroups.com...
Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0