Solved

concatenate string of %variable% to SQL from VB

Posted on 2008-06-19
6
1,921 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now