2007-10-29

Using SqlParameter Class

Represents a parameter to a SqlCommand and optionally its mapping to DataSet columns. This class cannot be inherited.

You should use parameters to filter queries in a secure manner. But I recommend to use parameters when you try to pass the datetime value in your query.

The process of using parameter contains two steps:

  • create SqlParameter object and insert there value with applicable properties
  • define the parameter in the SqlCommand command string, and assign the SqlParameter object to the SqlCommand object. When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.

Sample Code

Imports Namespace: System.Data.SqlClient

' Insert string
Dim sql As String = " INSERT INTO tblZipCode([ZIPCODE], [STATE], [CITY], [TestDate]) VALUES(@ZIPCODE, @STATE, @CITY), @TestDate"

' Create sql parameter
Dim param(3) As SqlParameter

param(0) = New SqlParameter("@ZIPCODE", SqlDbType.VarChar)
param(0).Value = "60000"

param(1) = New SqlParameter("@STATE", SqlDbType.VarChar)
param(1).Value = "Statename"

param(2) = New SqlParameter("@CITY", SqlDbType.VarChar)
param(2).Value = "Cityname"

' Recommend to use sql param when you try to send datetime value
param(3) = New SqlParameter("@TestDate", SqlDbType.DateTime)
param(3).Value = DateTime.Now

' Create Connection string
Dim sConnection As New SqlConnection("server=(local);uid=sa;pwd=pass;database=db")
sConnection.Open()

' Create Sql Command
Dim command As SqlCommand = sConnection.CreateCommand()
command.CommandText = sql

' Add Parameter to command
command.Parameters.AddRange(param)

' Execute command
Dim nResult As Integer = command.ExecuteNonQuery()

If nResult > 0 Then
Console.WriteLine("Insert completed")
End If

sConnection.Close()
command.Dispose()

34 comments:

Anonymous said...

Thanks for your sample code.
I tried to follow what you posted but an error message pop-up, "addrange is not a member of System.Data.SqlClient.SqlParameterCollection".
How can I fix it?

thanks.
K.H.

Jeffrey said...

' Insert string
Dim sql As String = " INSERT INTO tblZipCode([ZIPCODE], [STATE], [CITY], [TestDate]) VALUES(@ZIPCODE, @STATE, @CITY), @TestDate"

' Create Connection string
Dim sConnection As New SqlConnection("server=(local);uid=sa;pwd=pass;database=db")
sConnection.Open()

' Create Sql Command
Dim command As SqlCommand = sConnection.CreateCommand()
command.CommandText = sql

' Add Parameter to command
command.Parameters.AddWithValue("ZIPCODE", "60000")
command.Parameters.AddWithValue("STATE", "Statename")
command.Parameters.AddWithValue("CITY", "Cityname")
command.Parameters.AddWithValue("TestDate", DateTime.Now)

' Execute command
Dim nResult As Integer = command.ExecuteNonQuery()

If nResult > 0 Then
Console.WriteLine("Insert completed")
End If

sConnection.Close()
command.Dispose()

Anonymous said...

Just want to saу уour artіcle is aѕ astoniѕhing.

The сlаritу іn your publish iѕ simply nice and i сan
supрose you are a profеssional on this
ѕubject. Well togethег with уour permission let me to grasp
your RSS feed to stay uр tο date with approaching ρost.
Thanks 1,000,000 and please continuе the rewaгding
work.

Αlsο ѵiѕіt my weblog: hcg meal plan

Anonymous said...

Whеn somеοne ωritеs an poѕt he/she maintains the іdea оf
a user in his/hеr brain that how a user can undеrstand it.
Therefοrе that's why this article is amazing. Thanks!

Here is my webpage ... safe weight loss

yanmaneee said...

lebron 15
calvin klein outlet
yeezy boost
coach factory outlet
cheap jordans
christian louboutin outlet
lebron 11
yeezy boost 350
hogan outlet
longchamp

Alfred Avina said...

The main motive of the Hadoop big data solution is to spread the knowledge so that they can give more big data engineers to the world.

yasul.top said...

"Today, I went to the beach front with my children. I found a sea shell and gave it to my 4 year old daughter and said ""You can hear the ocean if you put this to your ear."" She put the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is entirely off topic but I had to tell someone!"

야동

ophunter.net said...

This may be a problem with my internet browser because I’ve had this happen previously. Cheers!! 오피

massage.blue said...

Hi! Do you know if they make any plugins to help with SEO?

마사지

gunma.top said...

I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good results. If you know of any please share.

건전마사지

roulettesitetop said...

Great blog you have here.. Itís difficult to find high quality writing like yours nowadays. I truly appreciate people like you! 온라인카지노

reelgame.site said...

You have mentioned very interesting points! 릴게임

casinositetop.com said...

I was reading some of your articles on this website and I conceive this web site is very instructive! Retain putting up.카지노사이트탑

badugisitenet said...

I was studying some of your articles on this site and I think this web site is really informative! Keep putting up.바둑이사이트

우리카지노 said...

Hello, I am one of the most impressed people in your article. 우리카지노 What you wrote was very helpful to me. Thank you. Actually, I run a site similar to you. If you have time, could you visit my site? Please leave your comments after reading what I wrote. If you do so, I will actively reflect your opinion. I think it will be a great help to run my site. Have a good day.


yadongbiz said...

I used to be checking continuously this weblog and I am impressed! Appreciate it for your efforts. Feel free to visit my website; 야설

japanyadongcom said...


Really satisfied with all the information I have found in this article. It gives immense knowledge on physical education, it is very helpful and quite generous to spread a good message. Feel free to visit my website; 한국야동



koreayadongcom said...

Everything is very open with a really clear explanation of the challenges. It was definitely informative. Your site is useful. Thank you for sharing. Feel free to visit my website;

국산야동

chinayadongnet said...


Hi! I just would like to give you a huge thumbs up for the great info you have got right here on this post. I'll be coming back to your site for more soon. Feel free to visit my website; 일본야동


yahanvideonet said...

Such a valuable post. I like it very much and I like your choice words also. I am waiting for your next valuable post. Feel free to visit my website;

일본야동

바카라사이트 said...

Looking at this article, I miss the time when I didn't wear a mask. 바카라사이트 Hopefully this corona will end soon. My blog is a blog that mainly posts pictures of daily life before Corona and landscapes at that time. If you want to remember that time again, please visit us.


온카지노 said...

First of all, thank you for your post. 온카지노 Your posts are neatly organized with the information I want, so there are plenty of resources to reference. I bookmark this site and will find your posts frequently in the future. Thanks again ^^


메가슬롯 said...

It's so nice to know you. I hope you also welcome me.메가슬롯If you welcome me, please visit my blog and write. My blog is on It's hard because of Covid - 19, but let's do our best!!


파워볼사이트 said...

I was impressed by your writing. Your writing is impressive. I want to write like you.파워볼사이트 I hope you can read my post and let me know what to modify. My writing is in I would like you to visit my blog.


cragro.com said...

The Toto site has a large number of registration numbers depending on the capabilities of the site. Food safety regulations and expert advice on the safest place to use the Toto game site in Private Toto. 토토사이트 뱃사공 안전놀이터

reelgame.site said...

ello there! I just wish to offer you a big thumbs up for the excellent info you have got here on this post. 릴게임

oncasino said...

oncasino

Anonymous said...

fake grass for balcony

Anonymous said...

Your Content is amazing and I am glad to read them. Thanks for sharing the Blog.this blog is very helpful information for every one.
Standalone VR Headset Price in Pakistan

oncasino said...

oncasino

Anonymous said...

artificial grass wall design ideas

sheatheaud said...

v8q35l4r99 o4v54c2t40 b3u67v1y64 s0i03f4n60 p9j94p2t39 s4o89w0q53

인터넷 경마 사이트 said...

to a two and a quarter length in 1m 11.37s over Packing Victory.인터넷 경마 사이트

Anonymous said...

jordan shoes
nike off white
golden goose
golden goose sale
palm angels outlet
jordan 6
yeezy boost 350
ggdb sneakers
goyard bag
hermes bags