Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
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 1: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 455
1 Sergey Alexandrovich Kryukov 445
2 Maciej Los 290
3 ProgramFOX 275
4 CHill60 225
0 OriginalGriff 550
1 Sergey Alexandrovich Kryukov 470
2 Maciej Los 310
3 ProgramFOX 275
4 Peter Leow 250

Advertise | Privacy | Mobile
Web03 | 2.8.150331.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