Parameterized Expression: insert

Showing posts with label insert. Show all posts

Monday, March 12, 2012

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

Saturday, February 25, 2012

Parameterizing BULK INSERT

I have a statement in a stored procedure as follows - which works a dream.
BULK INSERT cashpost
FROM 'c:\carparkfines\data\cashpost.txt'
WITH
(
FORMATFILE = 'c:\carparkfines\data\cashpost_format.txt'
)
However I want to parameterize the two file paths.
I know how to get them as follows
select @.filename01 = (SELECT ImportCashFilePath FROM tblSystem WHERE RecNo =
1)
However TSQL objects if I write
BULK INSERT cashpost
FROM @.filename01
WITH
(
FORMATFILE = @.filename02
)
So who is going to tell the new boy how to do it? Thanks in anticipation.
Andy Willis
Use some dynamic SQL, e.g.
DECLARE @.Sql nvarchar(4000)
SET @.Sql = '
BULK INSERT cashpost
FROM ''' + @.filename01 + '''
WITH
(
FORMATFILE = ''' + @.filename02 + '''
)
'
PRINT @.Sql
EXEC(@.Sql)
Darren Green
http://www.sqldts.com
"Andy Willis" <andrewrwillis@.blueyonder.co.uk> wrote in message
news:%23gqfKAlaEHA.2908@.TK2MSFTNGP10.phx.gbl...
> I have a statement in a stored procedure as follows - which works a dream.
> BULK INSERT cashpost
> FROM 'c:\carparkfines\data\cashpost.txt'
> WITH
> (
> FORMATFILE = 'c:\carparkfines\data\cashpost_format.txt'
> )
> However I want to parameterize the two file paths.
> I know how to get them as follows
> select @.filename01 = (SELECT ImportCashFilePath FROM tblSystem WHERE RecNo
=
> 1)
> However TSQL objects if I write
> BULK INSERT cashpost
> FROM @.filename01
> WITH
> (
> FORMATFILE = @.filename02
> )
> So who is going to tell the new boy how to do it? Thanks in anticipation.
> Andy Willis
>

Monday, March 12, 2012

Parameters to insert data from form into SQL database

Parameters to insert data from form into SQL database

Parameters to insert data from form into SQL database

Saturday, February 25, 2012

Parameterizing BULK INSERT

Parameterized Expression

Blog Archive

About Me