[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

concatenate string of %variable% to SQL from VB

Posted on 2008-06-19
6
Medium Priority
?
1,935 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

826 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