?
Solved

concatenate string of %variable% to SQL from VB

Posted on 2008-06-19
6
Medium Priority
?
1,933 Views
Last Modified: 2013-11-25
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

0
Comment
Question by:NaughtyZute
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21823545
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))
0
 

Author Comment

by:NaughtyZute
ID: 21823855
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)
AS
DECLARE @PaintTypeLike varchar(50)
SET @PaintTypeLike  = '%' + @PaintTypeLongD + '%'



BEGIN

      SET NOCOUNT ON;

      SELECT PaintTypeID
      FROM dbo.tblPaintType
      WHERE PaintTypeDesc LIKE @PaintTypeLike
END

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
                        MsgBox(myReader("PaintTypeID"))
                    End While
                    myReader.Close()
                Next i
0
 

Author Comment

by:NaughtyZute
ID: 21823921
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21823926
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 ?
0
 
LVL 5

Accepted Solution

by:
Steve Dubyo earned 1500 total points
ID: 21824170
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)

0
 

Author Comment

by:NaughtyZute
ID: 21824497
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!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question