Having probs coding SQL table search with VBScript and ASP....

[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?

 

[jedi]

hi kyrene,<br>
forgive me if im wrong, but i dont think you can use a WHERE clause with a stored procedure name. youll<br>
have to create parameters (using ado) , append them to<br>
the parameters collection, and then assign the recordset<br>
values to the stored procedure parameters.<br>
<br>

 

[jfriestman]

jedi's right, you can't use a where clause in your stored procedure, the stored procedure should already have the where clause programmed into it with parameters available.<br>
<br>
There is however an easier way to call the Stored Procedure without the Command object and the Parameters collection (which is oh so messy.)<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>
Hope this helps.<br>