Click here to Skip to main content
Rate this: bad
good
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
       cn.Close()
 
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
good
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()
  Permalink  
Comments
losmac at 18-Sep-12 12:24pm
   
Yep, 5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

ExecuteScalar will do...
           tb_countNumber.Text = Convert.ToString(cmd.ExecuteScalar())
  Permalink  
Comments
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 Sergey Alexandrovich Kryukov 555
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 369
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,466
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,897


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 18 Sep 2012
Copyright © CodeProject, 1999-2014
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