recordset problem

[GTN170777]

Hi Guys, me again!!!!

I've got a little recordset problem, basically I'm trying to build a jobs by
email feature, so I have the following -

Jobs by Email table which includes -

category
location
keyword

a form where an advertiser posts their vacancy, the form submits the data to
the database and then passes the data on to the page that processes the jobs
by email query -

currently the recordset for selecting all users with matching jobs by email
notifications looks like this --

<%
Dim jbeusers__MMColParam3
jbeusers__MMColParam3 = "0"
If (Session("SITEID") <> "") Then
jbeusers__MMColParam3 = Session("SITEID")
End If
%>
<%
Dim jbeusers__MMColParam
jbeusers__MMColParam = "0"
If (Session("NEWVACANCYLOCATION") <> "") Then
jbeusers__MMColParam = Session("NEWVACANCYLOCATION")
End If
%>
<%
Dim jbeusers__MMColParam2
jbeusers__MMColParam2 = "0"
If (Session("NEWVACANCYCATEGORY") <> "") Then
jbeusers__MMColParam2 = Session("NEWVACANCYCATEGORY")
End If
%>
<%
Dim jbeusers__MMColParam4
jbeusers__MMColParam4 = "%"
If (Session("NEWVACANCYDESCRIPTION") <> "") Then
jbeusers__MMColParam4 = Session("NEWVACANCYDESCRIPTION")
End If
%>
<%
Dim jbeusers
Dim jbeusers_cmd
Dim jbeusers_numRows

Set jbeusers_cmd = Server.CreateObject ("ADODB.Command")
jbeusers_cmd.ActiveConnection = MM_recruta2_STRING
jbeusers_cmd.CommandText = "SELECT JBENusername, JBENPassword, JBENname FROM
dbo.JBEmailNotification WHERE JBENsiteID = ? AND ? = JBENlocation AND ? =
JBENcategory AND JBENKeyword LIKE ?"
jbeusers_cmd.Prepared = true
jbeusers_cmd.Parameters.Append jbeusers_cmd.CreateParameter("param1", 5, 1,
-1, jbeusers__MMColParam3) ' adDouble
jbeusers_cmd.Parameters.Append jbeusers_cmd.CreateParameter("param2", 200,
1, 255, jbeusers__MMColParam) ' adVarChar
jbeusers_cmd.Parameters.Append jbeusers_cmd.CreateParameter("param3", 200,
1, 255, jbeusers__MMColParam2) ' adVarChar
jbeusers_cmd.Parameters.Append jbeusers_cmd.CreateParameter("param4", 200,
1, 255, "%" + jbeusers__MMColParam4 + "%") ' adVarChar

Set jbeusers = jbeusers_cmd.Execute
jbeusers_numRows = 0
%>

this works fine if the variables are -

MMColParam3 = 31
MMColParam = Hereford
MMColParam2 = IT
MMColParam4 = dreamweaver

as I have two records in the jobs by email table that match this criteria...

However as MMColParam4 is the advert description, it would contain more than
just dreamweaverr in it, for instance when i try and use the value "we
urgently require dreamweaver developers"

The recordset returns no records...

effectively what I'm trying to do is filter records where the siteid,
location and category match of the advert and the jobs by email keyword is
contained within the description,..
Anyone got any ideas what I'm doing wrong?

Thank you

 

[Bob Barrows [MVP]]

Hi Guys, me again!!!!

I've got a little recordset problem, basically I'm trying to build a
jobs by email feature, so I have the following -

Jobs by Email table which includes -

What database are you using?

category
location
keyword

a form where an advertiser posts their vacancy, the form submits the
data to the database and then passes the data on to the page that
processes the jobs by email query -

currently the recordset for selecting all users with matching jobs by
email notifications looks like this --

jbeusers_cmd.CommandText = "SELECT JBENusername, JBENPassword,
JBENname FROM dbo.JBEmailNotification WHERE JBENsiteID = ? AND ? =
JBENlocation AND ? = JBENcategory AND JBENKeyword LIKE ?"

jbeusers_cmd.Parameters.Append jbeusers_cmd.CreateParameter("param4",
200, 1, ' adVarChar

Well, it appears that what you have done here should work ... you're going
to a little too much trouble (you could use a variant array to pass the
parameter values rather than going to the trouble of building the Parameters
collection) but that should not be causing this symptom.

Try appending the wildcards in your query rather than in the parameter
value:

If Access:
.... JBENKeyword LIKE '%' & ? & '%'"

If SQL Server:
.... JBENKeyword LIKE '%' + ? + '%'"

and change your CreateParameter call to:
255, jbeusers__MMColParam4)


********************************************************************************
I'm curious:
why do you write "? = JBENcategory" instead of "JBENcategory = ?"

 

[GTN170777]

Hi Bob,

Just tried changing that, I've put it together as an SP - shown below,

Again the recordset works fine if the description is only dreamweaver, but
if it is for example, we need dreamweaver developers, nothing is returned --

so really what I am trying to do is select a record where the keyword is
contained in the description variuable, as well as the other criteria??

CREATE PROCEDURE TEST
@SiteID int,
@Description nvarchar(1000),
@location nvarchar(50),
@category nvarchar(50)
AS
SELECT JBENUsername, JBENname, JBENPassword
FROM JBEmailNotification
WHERE JBENsiteID = @SiteID AND JBENlocation = @location AND JBENcategory =
@category AND JBENKeyword LIKE '%'+'@Description'+'%'
GO

Thanks for your help, any other thoughts would be great?

 

[Bob Barrows [MVP]]

Hi Bob,

Just tried changing that, I've put it together as an SP - shown below,

Again the recordset works fine if the description is only
dreamweaver, but
if it is for example, we need dreamweaver developers, nothing is
returned --

so really what I am trying to do is select a record where the keyword
is contained in the description variuable, as well as the other
criteria??

CREATE PROCEDURE TEST
@SiteID int,
@Description nvarchar(1000),
@location nvarchar(50),
@category nvarchar(50)
AS
SELECT JBENUsername, JBENname, JBENPassword
FROM JBEmailNotification
WHERE JBENsiteID = @SiteID AND JBENlocation = @location AND
JBENcategory = @category AND JBENKeyword LIKE '%'+'@Description'+'%'
GO

So this is SQL Server ... what version?
When you run this procedure in Query Analyzer or SSMS, does it return the
correct results? If so, the problem is in the vbscript code.

If not, you need to show me how to reproduce the symptoms. Generate a CREATE
TABLE script for your JBEmailNotification table, create some insert
statements to insert some sample data that I can run, and I will try to
repro this.

 

[GTN170777]

Hi Bob,

MS SQL Server 2000,

the DDL for the JBEmailNotification table is

CREATE TABLE [dbo].[JBEmailNotification] (
[JBENotificationID] int IDENTITY(1, 1) NOT NULL,
[JBENsiteID] int NULL,
[JBENlocation] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JBENcategory] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JBENname] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JBENusername] nvarchar(225) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JBENcandidateID] int NULL,
[JBENPassword] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JBENDateRegistered] datetime CONSTRAINT [DF__JBEmailNo__JBEND__3FD07829]
DEFAULT convert(char(19),getdate(),120) NULL,
[JBENKeyword] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [aaaaaJBEmailNotification_PK] PRIMARY KEY NONCLUSTERED
([JBENotificationID])
)
ON [PRIMARY]
GO

Not to sure how to write an insert statement, currently though i just have
one record -

JBENsiteID - 31
JBENlocation = Hereford
JBENcategory = IT
JBENname = Gareth
JBENusername = (e-mail address removed)
JBENPassword = lorgar
JBENKeyword = dreamweaver

Hope this is of use and you can highlight what I'm doing wrong?

Thanks

 

[Bob Barrows [MVP]]

Hi Bob,

MS SQL Server 2000,

the DDL for the JBEmailNotification table is

CREATE TABLE [dbo].[JBEmailNotification] (
[JBENotificationID] int IDENTITY(1, 1) NOT NULL,
[JBENsiteID] int NULL,
[JBENlocation] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL, [JBENcategory] nvarchar(50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL, [JBENname] nvarchar(50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL, [JBENusername] nvarchar(225)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [JBENcandidateID] int
NULL, [JBENPassword] nvarchar(50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL, [JBENDateRegistered] datetime
CONSTRAINT [DF__JBEmailNo__JBEND__3FD07829] DEFAULT
convert(char(19),getdate(),120) NULL, [JBENKeyword] nvarchar(100)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT
[aaaaaJBEmailNotification_PK] PRIMARY KEY NONCLUSTERED
([JBENotificationID]) )
ON [PRIMARY]
GO

Not to sure how to write an insert statement,

For future reference:
INsert tablename (columns)
VALUES (<list of hard-coded values>)

There are tools to enable these statements to be generated, including this
code from Vyas:
http://vyaskn.tripod.com/code.htm#inserts

currently though i just
have one record -

JBENsiteID - 31
JBENlocation = Hereford
JBENcategory = IT
JBENname = Gareth
JBENusername = (e-mail address removed)
JBENPassword = lorgar
JBENKeyword = dreamweaver

I just spotted your problem: you enclosed the @description parameter in
quotes:
AND JBENKeyword LIKE '%'+'@Description'+'%'

'@description' does not exist in your data, correct? it should of course be
AND JBENKeyword LIKE '%'+@Description+'%'

UPDATE dbo.JBEmailNotification
SET JBENKeyword = 'dreamweaver'
exec TEST 31,'dreamweaver','Hereford','IT'
UPDATE dbo.JBEmailNotification
SET JBENKeyword = 'dreamweaver engineer'
exec TEST 31,'dreamweaver','Hereford','IT'

The above script returns the correct results for me.

 

[Old Pedant]

I don't think Bob Barrows has completely diagnosed your problem.

He is patently correct about getting rid of the '...' around '@description',
but I think that's just part of the problem.

Let's consider what happens when you use
WHERE ...JBENKeyword LIKE '%'+@Description+'%'
and then pass in an @Description value such as
'we need dreamweaver developers'

Okay, so AFTER the substitution, you will be doing
WHERE ...JBENKeyword LIKE '%we need dreamweaver developers%'

And if you have *ONLY* the one record with the value
JBENKeyword = 'dreamweaver'
then you are essentially trying to do
WHERE ...'dreamweaver' LIKE '%we need dreamweaver developers%'

Guess what? You ain't never gonna find no match on that!!!!

*NOW* try doing
WHERE ...'we need dreamweaver developers' LIKE '%' + JBENKeyword + '%'

You see it?

If not, look here:
http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx

 

[Old Pedant]

So if it's not obvious, you should be doing
WHERE ... @Description LIKE '%' + JBENKeyword '%'

Remember. the '%' character when used with LIKE means "and any number of
other characters". So '%Dreamweaver%' means '[any
stuff]Dreamwearver[anystuff]'.

Caution: This still isn't going to find a description such as
'we need dream weaver developers'
(note the space between 'dream' and 'weaver')

Since you are using SQL Server, perhaps you would want to consider full text
searching, and then allowing your job postings to have nice long keyword
strings with every possibly fun matching keyword in them?

 

[GTN170777]

Hi Both, Thanks for your help so far, I am sadly still having major problems
and can't get it to return any records now...

I've tried the below but get nothing

CREATE PROCEDURE TEST
@SiteID int,
@Description nvarchar(1000),
@location nvarchar(50),
@category nvarchar(50)
AS
SELECT JBENUsername, JBENname, JBENPassword
FROM JBEmailNotification
WHERE JBENsiteID = @SiteID AND JBENlocation = @location AND JBENcategory =
@category AND @Description LIKE '%+JBENKeyword%'

When i tried @Description LIKE '%'+JBENKeyword'%' I got a Incorrect syntax
near '%'. error message

??

 

[Bob Barrows [MVP]]

Hi Both, Thanks for your help so far, I am sadly still having major
problems and can't get it to return any records now...

I've tried the below but get nothing

CREATE PROCEDURE TEST
@SiteID int,
@Description nvarchar(1000),
@location nvarchar(50),
@category nvarchar(50)
AS
SELECT JBENUsername, JBENname, JBENPassword
FROM JBEmailNotification
WHERE JBENsiteID = @SiteID AND JBENlocation = @location AND
JBENcategory = @category AND @Description LIKE '%+JBENKeyword%'

When i tried @Description LIKE '%'+JBENKeyword'%' I got a Incorrect
syntax near '%'. error message

That's because it should have said this:

@Description LIKE '%' & JBENKeyword & '%'
(I tend to use & for concatenation to avoid unexpected results)

 

[Old Pedant]

That's because it should have said this:

@Description LIKE '%' & JBENKeyword & '%'
(I tend to use & for concatenation to avoid unexpected results)

Well, yes, you SHOULD use & for concatenation in VBScript and VB and even
Access SQL.

BUT *NEVER NEVER* in SQL Server! GTN even said so.

For SQL Server, look here:
http://msdn.microsoft.com/en-us/library/aa276846(SQL.80).aspx

The & operator in T-SQL is *ONLY* used to mean bit-wise anding.

You *MUST* use + for string concatenation in T-SQL. Period.



And this *IS* SQL Server 2000.

And

 

[Old Pedant]

*NOW* try doing
WHERE ...'we need dreamweaver developers' LIKE '%' + JBENKeyword + '%'

*YOU* coded

... AND @Description LIKE '%+JBENKeyword%'

Those two are not even *CLOSE* to this same thing.

Yes, the second time I posted I typoed and left out the + operator when I
wrote

So if it's not obvious, you should be doing
WHERE ... @Description LIKE '%' + JBENKeyword '%'

Clearly that should have been

WHERE ... @Description LIKE '%' + JBENKeyword + '%'

We are trying to BUILD UP a string that has the keyword in the middle and a
% character on each end. And the only good way to do that in SQL Server
(T-SQL, same thing) is to use the + operator.

 

[Old Pedant]

Sorry for slight scrambling of order of paragraphs in that last post.

I hate these silly tiny <textarea>s for typing in posts.

Anyway.... + and not & for T-SQL string concatenation.

 

[Bob Barrows [MVP]]

Sorry for slight scrambling of order of paragraphs in that last post.

I hate these silly tiny <textarea>s for typing in posts.

Anyway.... + and not & for T-SQL string concatenation.

Oh, I forgot she said she was using SQL 2000.

 

[GTN170777]

Thanks Old Pedant, works a treat (sorry for the late responce - had a cold)
I'm hoping to get on an MS SQL course, am UK based, can you recommend any?

Again thanks for your help

GTN