Solved

concatenate string of %variable% to SQL from VB

Posted on 2008-06-19
6
1,925 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

896 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