Click here to Skip to main content
11,804,574 members (93,545 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: VB SQL-Server VB.NET
i am using visual studio 2010 and sql server 2008..
My problem is that how can i write the following queries using parameterized query

Call openconnection()
       Dim temp As String
       temp = "pending for client approval"
       Dim a As Integer
       str = "select Count(*) a from Mastertable where CaseStatus='" & temp & "'  "
       cmd = New SqlCommand(str, cn)
       dr = cmd.ExecuteReader
       If dr.Read Then
           tb_countNumber.Text = dr.Item(a).ToString
       End If

Can i write this code using parameterized query and for that what changes should i have to do..?
Thankx in advance..
Posted 18-Sep-12 0:23am
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

This is a very common question, and the answer can easily be found on google.[^]

Basically it will look something like this:
str = "select Count(*) a from Mastertable where CaseStatus=@parmCaseStatus"
cmd = New SqlCommand(str, cn)
cmd.Parameters.Add(New SqlParamater("@parmCaseStatus", temp))
Then, instead of a reader you can use the cmd.ExecuteScalar like Kuthuparakkal suggested in the other solution. like this

Dim strResult as String = cmd.ExecuteScalar()
losmac at 18-Sep-12 12:24pm
Yep, 5!
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

ExecuteScalar will do...
tb_countNumber.Text = Convert.ToString(cmd.ExecuteScalar())
Kuthuparakkal at 18-Sep-12 10:41am
why downvote, plz add some comments!
Kschuler at 18-Sep-12 12:28pm
Oops...I mean to give that a 3 stars not 2. I fixed. And it was just because the question was how to do parameters. I agree that the execute scalar is a lot easier to use than the reader.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 3,140
1 Maciej Los 1,910
2 KrunalRohit 1,907
3 CPallini 1,845
4 Richard MacCutchan 1,187

Advertise | Privacy | Mobile
Web04 | 2.8.151002.1 | Last Updated 18 Sep 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100