• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1936
  • Last Modified:

concatenate string of %variable% to SQL from VB

Quick question.  Here's how I'm trying to send a text value into a SQL Stored Procedure for a LIKE query.  I can't seem to figure out how / where to put my quotes to get a proper string to SQL.  Thanks for your help!

findCmd.Parameters.Add(New SqlParameter("@PaintTypeLongD", "%" & strPaT & "%", SqlDbType.VarChar))

Open in new window

  • 3
  • 3
1 Solution
Steve DubyoCommented:
You seem to be building the string ok but I would add the %'s inside the stored procedure ideally, something like...

     declare @LikeString varchar(100)
     SET @LikeString = '%' + @PaintTypeLongD + '%'

That way you don't need to build the string in vb at all, just use...
     findCmd.Parameters.Add(New SqlParameter("@PaintTypeLongD", strPaT, SqlDbType.VarChar))
NaughtyZuteAuthor Commented:
Still doesn't work.  Here's my stored procedure now:

ALTER PROCEDURE [dbo].[usp_findPaintTypeID]
      -- Add the parameters for the stored procedure here
@PaintTypeLongD varchar(50)
DECLARE @PaintTypeLike varchar(50)
SET @PaintTypeLike  = '%' + @PaintTypeLongD + '%'



      SELECT PaintTypeID
      FROM dbo.tblPaintType
      WHERE PaintTypeDesc LIKE @PaintTypeLike

and here's my calling code:

                For i As Integer = 0 To Me.procData.Rows.Count - 1
                    'Sales Order ID
                    strSO = procData.Rows(i).Cells.Item(0).Value
                    'Paint Type Text Description
                    strPaT = procData.Rows(i).Cells.Item(1).Value.ToString
                    findCmd.Parameters.Add(New SqlParameter("@PaintTypeLongD", SqlDbType.VarChar)).Value = strPaT
                    Dim myReader As SqlDataReader = findCmd.ExecuteReader()
                    While myReader.Read
                    End While
                Next i
NaughtyZuteAuthor Commented:
I need to add something important about what I'm doing here.  The incoming data has extra characters in it.  Namely, a paint manufacturer code i.e., DUP for duPont.  I need to look-up the Paint Type ID in a table that does not include the manufacturer prefix.  So I'm wanting to find a substring from the table that's within the string of the incoming value.  If I ask for LESS than the whole Paint Description, I get what I want as results, but with the prefix, I get bupkas.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Steve DubyoCommented:
The SQL looks ok, does the procedure work ok if you run it manually ?

Have you tried breaking the VB to check the value in strPaT ?  Maybe it has white-space at the end, have you tried strPaT.Trim ?

Are you getting any error messages ?
Steve DubyoCommented:
Ok, if I understand correctly, strPaT has a 3 letter prefix but the PaintTypeDesc field in dbo.tblPaintType does not include this prefix ?  If this is the case then it won't find anything at the moment.
You will need to remove the prefix form the variable before you add it to the parameter value.  If it is always a 3 letter prefix you could try..
= Right(strPaT, strPaT.Length - 3)

NaughtyZuteAuthor Commented:
Hey thanks for your help.  But the prefixes are not all the same, from what you've said and my research on the web, it looks like I was going to have to get into a bunch of string manipulation, so I went to source Access db, merged all the tables and just ran Find / Replaces for the prefixes.

Thanks for your help.  You've got the points, because you did give me good info for future LIKE queries!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now