having probs coding SQL table search

[TeaAddictedGeek]

The problem is trying to get the syntax right for a stored procedure (the tables are linked).<br>
<br>
I have this so far, but it's giving me the error "Microsoft VBScript compilation error '800a0401' Expected end of statement":<br>
<br>
<br>
storedProc = "sp_AM_DumpClients " + WHERE CStr( rsObjB( "c_name" )) LIKE '%" & frmKeyword & "%' + CStr( rsObjB( "CS_ID" ))<br>
<br>
<br>
<br>
Help?

 

[steveblum]

You have the word where outside the quotes. This causes a compiler error because Where is not the name of a string variable, nor a string constant in quotes, so it doesn't know what to do with it.<br>
<br>
On a more basic level, are you trying to execute a stored procedure and supply it a where clause as part of the call? I am not sure what connection object you are using and whether it will support this. If not, you need to modify the stored procedure to accept the three variables as parameters and then code your call as "sp_AM_DumpClients string1, string2, string3".<br>
<br>
To make this dynamic, as it is above, you would code:<br>
storedProc = "sp_AM_DumpClients " & CStr(rsObjB("c_name")) & ", " & frmKeyword & ", " & CStr(rsObjB("CS_ID"))<br>
<br>
Then you would need to modify the stored procedure to take these three parameters and put them into a where clause.<br>
<br>

 

[jfriestman]

(Repost of this answer to Kyrene's same question in the VBScript forum.)<br>
<br>
Try this...<br>
<br>
Consider this (generic) stored procedure:<br>
CREATE PROCEDURE UP_UpdateTable<br>
(<br>
@dbid char(10),<br>
@newval char(20)<br>
)<br>
AS<br>
UPDATE thetable<br>
SET column = @newval<br>
WHERE dbid = @dbid<br>
<br>
And this ASP code:<br>
<br>
Sub UpdateTable (dbid, newval)<br>
Dim oConn,strParameters<br>
Set oConn = Server.Createobject("ADODB.Connection")<br>
strParameters = "'" & dbid & "','" & newval & "'"<br>
oConn.Open "DSN=mydsn;uid=sa;pwd=;"<br>
oConn.Execute ("exec UP_UpdateTable " & strParameters)<br>
oConn.Close<br>
Set oConn = Nothing<br>
End Sub<br>
<br>