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 0: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 OriginalGriff 430
1 DamithSL 288
2 Sergey Alexandrovich Kryukov 220
3 Peter Leow 215
4 Maciej Los 195


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