Results 1 to 8 of 8

Thread: Easy Question

  1. #1
    Join Date
    Feb 2008
    Location
    Northern California
    Posts
    2

    Unanswered: Easy Question

    I'm used to forming Select statements for IBM System i imbedded SQL in High Level source programs. I now find myself converting System i RPG programs to VB.NET classes. What I want to know is how to insert the value of a class variable into a SQL Select string. In the IBM world, I would do:

    Select STCTYC,STCRID,STTITL,STSTTY From CSLSTT01 Where STCTYC = :County

    where County is a program defined variable. How do I get the value in the County variable into the Select string? I tried concatenation but that didn't work either. Thanks.

  2. #2
    Join Date
    Jan 2005
    Posts
    29
    Hi Wabbit13,

    As long as I've understood your problem correctly, you certainly should be able to use string concatenation to append your value. Might it be that you need to type-cast your value first?

    Please post a sample of code for me if type-casting isn't the issue.

    Again, if I understand you properly this question is probably better directed to a VB.NET forum too.
    Last edited by karlmdv; 02-20-08 at 02:11.

  3. #3
    Join Date
    Feb 2008
    Location
    Northern California
    Posts
    2
    Here is the code with concatenation:

    Dim sqlStmt As String = "Select STCTYC, STCRID, STTITL, STSTTY From CSLSTT01 Where STCTYC = ‘" & CNTY & "’
    Using conn As SqlConnection = New SqlConnection(connStr)
    conn.Open()
    Using cmd As SqlCommand = New SqlCommand(sqlstmt, conn)
    Using dr As SqlDataReader = cmd.ExecuteReader()

  4. #4
    Join Date
    Jan 2005
    Posts
    29
    It looks like your string isn't properly quoted
    Code:
    it should end
    ....& CNTY & ""
    not
    & CNTY & "
    Depending on the type of the CNTY object and your "Strict" setting you may need to use CNTY.ToString

    If you're getting an error, please post it. If you're getting no rows and think you should be getting some try the output of
    Code:
    Debug.Print(sqlStmt)
    into Query Analyzer.

    But I'm still guessing what your real problem is.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would very strongly suggest that you investigate parameterized queries instead of using string concatenation. Parameterized queries avoid all kinds of coding problems and security risks that are inherant to dynamic SQL (such as concatenating strings to make an SQL statement). Parameterized queries are also almost identical to the approach that you're used to with HLLAPI and DB2, so the learning curve should be a lot smaller, which is a nice side benefit!

    -PatP

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    SQL Server best practice here would be to create a stored procedure using a variable as input and then execute the sproc from your application supplying the value for the variable.

    For example, in the pubs database there is a table named authors. If you run the query
    Code:
     
    select au_id, au_lname, au_fname, phone,address, city,state, zip, contract
    from authors
    you will get 23 rows returned, two of which have the last name Ringer.

    the stored proc to return data from the authors table given a specific last name would be
    Code:
     
    CREATE PROCEDURE uspGetAuthorInfo @LastName varchar(40)
    AS
     
    SET NOCOUNT ON
     
    select au_id, au_lname, au_fname, phone,address, city,state, zip, contract
    from authors
    where author.au_lname = @LastName
    then to retrieve the authors with the last name 'Ringer' you would execute the sproc after you make the connection to the database thusly

    Code:
     
    declare @lname varchar(40)
    set @lname = 'Ringer'
     
    exec uspGetAuthorInfo @lname

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Pat Phelan
    I would very strongly suggest that you investigate parameterized queries ...
    +100. Or stored procedures, perhaps?

    Regards,

    hmscott
    Have you hugged your backup today?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As tomh53 and hmscott pointed out, there is nothing wrong with using a parameterized query to drive a stored procedure, to gain the benefits of both. I see the parameterized query as coming first (mostly due to the security problems that it fixes automagically), and the stored procedure following close behind (for the performance and flexibility opportunities that it provides). I see both options as wins for the developer, the operations staff, IT management, and the application users.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •