ASP error, Urgent Help Needed!!

[Guest_imported]

I have been battling this error for 3 days.
I have baffled.
I am calling a stored proc via asp but I keep getting an error:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/EmpDB/call_stored_proc.asp, line 65

which is this line:
<% For Each param In oCmd.Parameters %>

Any help would be greatly appreciated.
Below is the entire code.

<%

sub saveData

'dim custID, cTopic, cName, cEmail, cHttp, cBody
dim custID
dim pCustID, pTopic, pName, pEmail, pHttp, pBody
dim pAction, pMessage

custID = Request.Form( &quot;id&quot; )
cTopic = Request.Form( &quot;topic&quot; )
cName = Request.Form( &quot;name&quot; )
cEmail = Request.Form( &quot;email&quot; )
cHttp = Request.Form( &quot;http&quot; )
cBody = Request.Form( &quot;body&quot; )

Set oCmd = Server.CreateObject(&quot;ADODB.Command&quot
oCmd.ActiveConnection = getDBConnection()
oCmd.CommandText = &quot;sel_record&quot;
oCmd.CommandType = 4

Set pCustID = oCmd.CreateParameter(&quot;custID&quot;,131,1,30,custID)
oCmd.Parameters.Append pCustID

Set pTopic = oCmd.CreateParameter(&quot;cTopic&quot;,200,2,30,cTopic)
oCmd.Parameters.Append pTopic

Set pName = oCmd.CreateParameter(&quot;cName&quot;,200,2,30,cName)
oCmd.Parameters.Append pName

Set pEmail = oCmd.CreateParameter(&quot;cEmail&quot;,200,2,30,cEmail)
oCmd.Parameters.Append pEmail

Set pHttp = oCmd.CreateParameter(&quot;cHttp&quot;,200,2,30,cHttp)
oCmd.Parameters.Append pHttp

Set pBody = oCmd.CreateParameter(&quot;cBody&quot;,200,2,30,cBody)
oCmd.Parameters.Append pBody

Set pAction = oCmd.CreateParameter(&quot;ioAction&quot;,131,3,50,0)
oCmd.Parameters.Append pAction

Set pMessage = oCmd.CreateParameter(&quot;ioMessage&quot;,200,3,50,&quot;&quot
oCmd.Parameters.Append pMessage

oCmd.Execute

if ( oCmd(&quot;ioAction&quot <> 0 ) then
Response.Write( oCmd(&quot;ioMessage&quot )
end if

end sub

%>

<Table Border=1>
<TR>
<TD><B>Topic</B></TD>
<TD><B>Name</B></TD>
<TD><B>Email</B></TD>
<TD><B>Http</B></TD>
<TD><B>Body</B></TD>

</TR>
<% For Each param In oCmd.Parameters %>
<TR>
<TD><%= param.topic %></TD>
<TD><%= param.name %></TD>
<TD><%= param.email %></TD>
<TD><%= param.http %></TD>
<TD><%= param.body%></TD>
</TR>
<%
Next
%>

 

[nomi2000]

Dear
define the param variable as
Dim Param
Set Param=OCmd.Parameters
before going into the loop

 

[rajkumarn]

Hi,

According to my knowledge you are using Access database.

In IIS --->operators u has to give the permission, to resolve this issue.

Please mail me, which database are you using.


with regards
rajkumar

 

[Guest_imported]

thanks for you guys reponse.
first, defining param as variable and assigning it values did not solve the problem.
second, i am using an oracle database.

 

[JuanitaC]

You are setting oCmd as an object within a Sub, then trying to access that object outside of the Sub. Because you have declared the object within the sub, its scope is local (only available within the sub). Try moving your declaration outside of the sub:

Set oCmd = Server.CreateObject(&quot;ADODB.Command&quot
Sub saveData
...
end sub

 

[nomi2000]

Dear Simfilx
First Juaitac is write you have to defined the oCmd before
Dim oCmd
Set oCmd = Server.CreateObject(&quot;ADODB.Command&quot'You can set it here OR inside the Sub

Sub Savedata
...
End Sub

beside try to use this
For i=0 to oCmd.Parameters.Count

<%= oCmd.Parameters(i).value %>

Next i

It will give u the same result though in this case the result will be based in the sequence u add parameters in the collection
Hope it will work for you
Regards
Nouman

 

[Guest_imported]

Thank you nomi2000 and JuanitaC!
I tried your suggestions but I am getting a different error.

Below is the error, followed by the new code.
All I am trying to do is to return the values of topic, name, email, http and body based on whatever the input values of msgid is.
How can I do this?

Error Type:
Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'topic'
/EmpDB/asp_stored_proc.asp, line 87


<%
Private Function GetDBConnection

Const cConnection = &quot;DSN=EmpDB;UID=scott;PWD=tiger&quot;


Dim objConnection

'* Create the connection object
Set objConnection = Server.CreateObject(&quot;ADODB.Connection&quot
objConnection.ConnectionString = cConnection
objConnection.CursorLocation = 3 '* adUseClient
objConnection.Open

'* Return the new connection back to the calling routine
Set GetDBConnection = objConnection

End Function
%>


<%
Set oCmd = Server.CreateObject(&quot;ADODB.Command&quot
oCmd.ActiveConnection = getDBConnection()
oCmd.CommandText = &quot;sel_record&quot;
oCmd.CommandType = 4
sub saveData

'dim custID, cTopic, cName, cEmail, cHttp, cBody
dim custID
dim pCustID, pTopic, pName, pEmail, pHttp, pBody
dim pAction, pMessage

custID = Request.Form( &quot;id&quot; )
cTopic = Request.Form( &quot;topic&quot; )
cName = Request.Form( &quot;name&quot; )
cEmail = Request.Form( &quot;email&quot; )
cHttp = Request.Form( &quot;http&quot; )
cBody = Request.Form( &quot;body&quot; )

Set pCustID = oCmd.CreateParameter(&quot;custID&quot;,131,1,30,custID)
oCmd.Parameters.Append pCustID

Set pTopic = oCmd.CreateParameter(&quot;cTopic&quot;,200,2,30,cTopic)
oCmd.Parameters.Append pTopic

Set pName = oCmd.CreateParameter(&quot;cName&quot;,200,2,30,cName)
oCmd.Parameters.Append pName

Set pEmail = oCmd.CreateParameter(&quot;cEmail&quot;,200,2,30,cEmail)
oCmd.Parameters.Append pEmail

Set pHttp = oCmd.CreateParameter(&quot;cHttp&quot;,200,2,30,cHttp)
oCmd.Parameters.Append pHttp

Set pBody = oCmd.CreateParameter(&quot;cBody&quot;,200,2,30,cBody)
oCmd.Parameters.Append pBody

Set pAction = oCmd.CreateParameter(&quot;ioAction&quot;,131,3,50,0)
oCmd.Parameters.Append pAction

Set pMessage = oCmd.CreateParameter(&quot;ioMessage&quot;,200,3,50,&quot;&quot
oCmd.Parameters.Append pMessage

oCmd.Execute

if ( oCmd(&quot;ioAction&quot <> 0 ) then
Response.Write( oCmd(&quot;ioMessage&quot )
end if

end sub

%>

<Table Border=1>
<TR>
<TD><B>Topic</B></TD>
<TD><B>Name</B></TD>
<TD><B>Email</B></TD>
<TD><B>Http</B></TD>
<TD><B>Body</B></TD>

</TR>
<% For Each param In oCmd.Parameters %>
<TR>
<TD><%= param.topic %></TD>
<TD><%= param.name %></TD>
<TD><%= param.email %></TD>
<TD><%= param.http %></TD>
<TD><%= param.body%></TD>
</TR>
<%
Next
%>

 

[nomi2000]

Dear Simflix
As i suggested why not you use this
for showing the values back to the Form? it will works

For i=0 to oCmd.Parameters.Count

<%= oCmd.Parameters(i).value %>

Next i
b) there is an alternative to your code but still i think you should be able to get the values from above code but if not then i will send u the complete code which should works
i mean ur code could be changed slightly to get what u wants as a hint use this techq

Dim objRsOrders

Set objRsOrders = Server.CreateObject(&quot;ADODB.Recordset&quot
With oCmd
.CommandType = 4
.CommandText = &quot;sel_record&quot;
.Parameters.Append .CreateParameter(&quot;@Param1&quot;, adInteger, adParamInput, 4, mintOrderItemID)
.Parameters.Append .CreateParameter(&quot;@Param2&quot;, adVarChar, adParamInput, 20, mstrStyleCode)
End With

objRsOrders.Open oCmd

Now you can loop through the Reocrdset fields for the values returns
For i=0 to objRsOrders.RecordCount
objRsOrders.Fields(i).value
Next i

I hope u got it..and hope it is helpful to you
Regards
Nouman

 

[harmmeijer]

<% For Each param In oCmd.Parameters %>
<TR>
<TD><%= Param.name %></TD>
<TD><%= Param.value %></TD>
</TR>
<%Next%>

 

[harmmeijer]

Sorry, this may be more clear and works in your code if you replace the for each.

<% set param = oCmd.Parameters %>
<TR>
<TD><%= param(&quot;cTopic&quot %></TD>
<TD><%= param(&quot;cName&quot %></TD>
<TD><%= param(&quot;cEmail&quot %></TD>
<TD><%= param(&quot;cHttp&quot %></TD>
<TD><%= param(&quot;cBody&quot%></TD>
</TR>

If you use the for each then param does not conain the paremetest collection but it will contain just one parameter of the collection each time so param(0) would not work.
Then you can use param.value because value is a property of a param item.
In the code above param contains the parameters collection so if I need the value of one item I have to specify which item like so:
param(0).value
.value is optional if you leave this out (like I did above) then the default property wil be used (which is .value).
param(0) wil take the first item of the parameters collection but if you need a parameter called cTopic you should adress the parameter by it's name to be sure to get the cTopic parameter (I did it above) it looks like this:
param(&quot;cTopic&quot or param(&quot;cTopic&quot.value

 

[Guest_imported]

Thanks to all of you who responded.
Thanks for your quik response and helpful hints and code samples.
I will try them and post back results to you in this forum.

 

[Guest_imported]

nomi2000, can you please send me the complete code.
I am still having all kinds of problems.
Thanks in advance and thanks to all you have tried to help.
I am more comfortable with the regular sql embedment but now that my boss wants me to use stored proc instead, I am having all kinds of problems.

 

[nomi2000]

Dear Simflex
its not diff to create Stored Procedure and run with ASP i have beein in the field of SQL Server 2000 with VB for some years and this combination works fine for me...
I can send you a CV Submission Page withe SQL-Insert statemant (Access 200)) but will modify you so it could talk with Stored-Procedure ,I know ADO will talk similar to the back-end ,i will send you the pages ,don't worry i will send you as i modified and check it here
Regards
Nouman

 

[nomi2000]

Dear Simfilex
I am pasting the code of 4 ASP Pages which actullay does
Insertion of CV and also Search of CV i have checked it here it

works perfect with ASP and SQL SERVER
you can change it as ur requirment

'For insertin record in table thru STORED PROC
1) CV_Submission.asp
2) SubmitResult.asp

'For Getting Record from table thru STORED PROC
3) SearchCV.asp
4) GetSubmitResult.asp

For Creating Stored Procedures which are used in this code
a) stp_TEST_InsertCV
b) stp_TEST_GetCV

I am also sending their Scripts its created on SQL-Server but you

could see it and can start to built in ORACLE,i don't have ORACLE

here

I hope this will help you
Regards
Nouman

-------------- CV_Submission.asp -------------------------

<html>
<head>
<meta NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;>
</head>
<body>
<FORM name=&quot;CVSubmit&quot; method=post action=&quot;SubmitResult.asp&quot; >

<table cellSpacing=&quot;0&quot; cellPadding=&quot;10&quot; width=&quot;570&quot; border=&quot;1&quot;

align=&quot;center&quot;>
<tr >
<td align=&quot;middle&quot;>
CV Submission
</td></tr>
<tr>
<td >
Welcome to XYZ (Pvt) Ltd. Place you CV here for current and

future Openings
</td>
</tr>
</table>
<table cellSpacing=&quot;0&quot; cellPadding=&quot;0&quot; width=&quot;570&quot; align=&quot;center&quot;

border=&quot;1&quot;>
<tr>
<TD > First name:</TD>
<td><input id=txtData0 name=txtData
> </td>
</tr>
<tr>
<TD > Last name:</TD>
<td><input id=txtData1 name=txtData> </td>
</tr>
<tr>
<TD >Email</TD>
<td><input id=txttData2 name=txtData> </td>
</tr>
<tr>
<TD >Phone</TD>
<td><input id=txtData3 name=txtData> </td>
</tr>
<tr>
<TD >Fax:</TD>
<td><input id=txtData4 name=txtData> </td>
</tr>
<tr>
<TD > Address1:</TD>
<td><input id=txtData5 name=txtData> </td>
</tr>
<tr>
<TD >Address2:</TD>
<td><input id=txtData6 name=txtData> </td>
</tr>
<tr>
<TD >City:</TD>
<td><input id=txtData7 name=txtData> </td>
</tr>
<tr>
<TD >State:</TD>
<td><input id=txtData8 name=txtData> </td>
</tr>
<tr>
<TD >Zip Code:</TD>
<td><input id=txtData9 name=txtData> </td>
</tr>
<TR>
<TD >Country:</TD>
<TD><INPUT id=txtData10 name=txtData></TD>
</TR>
<tr>
<tr>
<TD >Details of Eduction:</TD>
<td><TEXTAREA id=TxtAreaEducation style=&quot;WIDTH: 230px; HEIGHT:

85px&quot; name=txtData rows=4 cols=25></TEXTAREA></td>
</tr>
<TR>
<TD >Experince (No of Years)</TD>
<td><input id=txtData11 name=txtData> </td>
</TR>
<tr>
<TD >Details of Professional Experience:</TD>
<td><TEXTAREA id=TxtAreaProfExp style=&quot;WIDTH: 230px; HEIGHT:

85px&quot; name=txtData rows=4 cols=25></TEXTAREA></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input id=&quot;submit&quot; type=&quot;submit&quot; value=&quot;Submit&quot;

name=&quot;submit1&quot;></td></tr>
</table></FORM>
</body>
</html>

---------------------- SubmitResult.asp ----------------------

<%@ Language=VBScript %>
<html>
<head>
<meta NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;>
</head>
<body>

<%

const ArraySep = &quot;,&quot;
Dim objConn,objCmd
Set objConn = server.CreateObject(&quot;ADODB.Connection&quot
Set objCmd= server.CreateObject(&quot;ADODB.Command&quot
objConn.CursorLocation = 3
objConn.Open &quot;Provider=SQLOLEDB;Persist Security

Info=False;Initial Catalog=TEST;Data Source=PDC;User

Id=sa;Password=;&quot;


dim StRaData , StData
StRaData = Request.Form(&quot;txtData&quot & ArraySep
StData = Split(straData, ArraySep )

With objCmd
.CommandType = &H0004
Set .ActiveConnection = objConn
.CommandText = &quot;stp_TEST_InsertCV&quot;

.Parameters.Append .CreateParameter(&quot;@P_FirstName&quot;

,200, &H0001, 20, stData(0))
.Parameters.Append .CreateParameter(&quot;@P_LastName&quot;,

200, &H0001, 20, stData(1))
.Parameters.Append .CreateParameter(&quot;@P_Email&quot;, 200,

&H0001, 50, stData(2))
.Parameters.Append .CreateParameter(&quot;@P_Phone&quot;, 200,

&H0001, 20, stData(3))
.Parameters.Append .CreateParameter(&quot;@P_Fax&quot;, 200,

&H0001, 20, stData(4))
.Parameters.Append .CreateParameter(&quot;@P_Address1&quot;,

200, &H0001, 50, stData(5))
.Parameters.Append .CreateParameter(&quot;@P_Address2&quot;,

200, &H0001, 50, stData(6))
.Parameters.Append .CreateParameter(&quot;@P_City&quot;, 200,

&H0001, 20, stData(7))
.Parameters.Append .CreateParameter(&quot;@P_State&quot;, 200,

&H0001, 20, stData(8))
.Parameters.Append .CreateParameter(&quot;@P_ZipCode&quot;,

200, &H0001, 20, stData(9))
.Parameters.Append .CreateParameter(&quot;@P_Country&quot;,

200, &H0001, 20, stData(10))
.Parameters.Append

.CreateParameter(&quot;@P_EducationDetails&quot;, 200, &H0001, 255,

stData(11))
.Parameters.Append

.CreateParameter(&quot;@P_YearExperience&quot;, 200, &H0001, 5, stData(12))
.Parameters.Append

.CreateParameter(&quot;@P_ExperienceDetails&quot;, 200, &H0001, 255,

stData(13))

.Execute



End With

Set objCmd=nothing
Set Conn = nothing

%>


<TABLE cellSpacing=0 cellPadding=10 width=&quot;58%&quot; border=1

align=&quot;center&quot;>

<TR>
<TD align=middle>
CV Submission Result
</TD></TR>
<TR>
<TR><TD><%= Request.Form(&quot;txtData&quot(1) %></TD></TR>
<TR><TD><%= Request.Form(&quot;txtData&quot(2) %></TD></TR>
<TR><TD><%= Request.Form(&quot;txtData&quot(3) %></TD></TR>
<TR><TD><%= Request.Form(&quot;txtData&quot(4) %></TD></TR>
</TR>
<TR><TD align=&quot;middle&quot; >
Thank you for Submitting CV
Your Information has been stored in

our database
</TD>
</TR>
</TABLE>
</body>
</html>

----------------------- SearchCV.asp ------------------------------
<html>
<head>
<meta NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;>
</head>
<body>
<TABLE cellSpacing=0 cellPadding=10 width=&quot;570&quot; border=0

align=&quot;center&quot; ></FORM>

<form id=&quot;FORM2&quot; name=&quot;FORM2&quot; action=&quot;GetSubmitResult.asp&quot;

method=&quot;post&quot;>
<TBODY>
<tr>
<td>
<TABLE cellSpacing=1 cellPadding=1
width=&quot;300&quot; align=center border=1>
<TR>
<TD>&nbsp</TD>
<TD>CV Search</TD>
</TR>
<TR>
<TD>
<P>First Name</P></TD>
<TD><INPUT id=txtFirstName style=&quot;WIDTH: 125px; HEIGHT:

22px&quot; size=17
name=txtData></TD>
</TR>
<TR>
<TD>Last Name</TD>
<TD><INPUT id=txtLastName style=&quot;WIDTH: 124px; HEIGHT:

22px&quot;
size=17 name=txtData></TD>
</TR>
<TR>
<TD>Phone</TD>
<TD><INPUT id=txtPhone style=&quot;WIDTH: 124px; HEIGHT: 22px&quot;
size=17 name=txtData></TD>
</TR>
<TR>
<TD><input id=&quot;submit2&quot; type=&quot;submit&quot; value=&quot;Search CV&quot;

name=&quot;submit2&quot; style=&quot;WIDTH: 87px; HEIGHT: 20px&quot; size=14>
</TD>
</TR>

</TABLE>
</form></TD></TR></TBODY></TABLE>
</body>
</html>
---------- GetSubmitResult.asp ---------------------------------

<%@ Language=VBScript %>
<html>
<head>
<meta NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;>
</head>
<body>

<%

const ArraySep = &quot;,&quot;
Dim objConn,objCmd,objRS
Set objConn = server.CreateObject(&quot;ADODB.Connection&quot
Set objCmd= server.CreateObject(&quot;ADODB.Command&quot
Set objRs=server.CreateObject(&quot;ADODB.Recordset&quot

objConn.CursorLocation = 3
objConn.Open &quot;Provider=SQLOLEDB;Persist Security

Info=False;Initial Catalog=TEST;Data Source=PDC;User

Id=sa;Password=;&quot;

objRs.CursorLocation = 3
objRs.CursorType = 3
objRs.LockType = 4

dim StRaData , StData
StRaData = Request.Form(&quot;txtData&quot & ArraySep
StData = Split(straData, ArraySep )

With objCmd
.CommandType = &H0004
Set .ActiveConnection = objConn
.CommandText = &quot;stp_TEST_GetCV&quot;

.Parameters.Append .CreateParameter(&quot;@P_FirstName&quot;

,200, &H0001, 20, stData(0))
.Parameters.Append .CreateParameter(&quot;@P_LastName&quot;,

200, &H0001, 20, stData(1))
.Parameters.Append .CreateParameter(&quot;@P_Phone&quot;, 200,

&H0001, 20, stData(2))

End With

objRs.Open objCmd

Set objCmd=nothing
Set Conn = nothing

%>


<TABLE cellSpacing=0 cellPadding=10 width=&quot;58%&quot; border=1

align=&quot;center&quot;>

<TR>
<TD align=middle>
CV Search Result
</TD></TR>
<TR>
<% If Not objRs.EOF And objRs.BOF Then
While not objRs.Eof %>
<TR><TD><%= objRs.Fields(1).value

%></TD></TR>
<TR><TD><%= objRs.Fields(2).value

%></TD></TR>
<TR><TD><%= objRs.Fields(3).value

%></TD></TR>
<TR><TD><%= objRs.Fields(4).value

%></TD></TR>
<% obRs.moveNext
Wend
else
response.write stData(0) & stData(1) &

stData(2) %>
<TR><TD> No Records Found...</TD></TR>
<% end if
objRs.close
Set objRs=nothing
%>

</TR>

</TABLE>
</body>
</html>

----------- a) stp_TEST_InsertCV-----------

CREATE PROCEDURE stp_TEST_InsertCV

@P_FirstName varchar (20) ,
@P_LastName varchar (20) ,
@P_Email varchar(50),
@P_Phone varchar(20) ,
@P_Fax varchar(20),
@P_Address1 varchar(50) ,
@P_Address2 varchar(50) ,
@P_City varchar (20),
@P_State varchar (20),
@P_ZipCode varchar (20),
@P_Country varchar (20),
@P_EducationDetails varchar (255),
@P_YearExperience varchar (5),
@P_ExperienceDetails varchar (255)

/********************************************************
*Procedure Name : stp_TEST_InsertCV
*
*Auther: Nouman on 07/19/2001
*
*____________________________________________________
*Param Name Type Length
*____________________________________________________
* @P_FirstName varchar 20
* @P_LastName varchar 20
* @P_Email varchar 50
* @P_Phone varchar 20
* @P_Fax varchar 20
* @P_Address1 varchar 50
* @P_Address2 varchar 50
* @P_City varchar 20
* @P_State varchar 20
* @P_ZipCode varchar 20
* @P_Country varchar 20
* @P_EducationDetails varchar 255
* @P_YearExperience varchar 5
* @P_ExperienceDetails varchar 255
*____________________________________________________
*
*Note:
*
*Tables Accessed: CV_Submission
*
*Stored Procedure Accessed: -None-
*
*Temp tables created and used: -None-
*
*Reture Value: -None-
*
*Any Special Notes: -None-
*
*Revision#1
*
*
***************************************************************/

AS

BEGIN


INSERT INTO
CVSubmission
(
[FirstName],
[LastName],
,
[Phone],
[Fax],
[Address1],
[Address2],
[City],
[State],
[ZipCode],
[Country],
[EducationDetails],
[YearExperience],
[ExperienceDetails])
VALUES
(
@P_FirstName ,
@P_LastName,
@P_Email,
@P_Phone ,
@P_Fax ,
@P_Address1 ,
@P_Address2 ,
@P_City ,
@P_State,
@P_ZipCode,
@P_Country,
@P_EducationDetails,
@P_YearExperience,
@P_ExperienceDetails
)



END

GO

----------------- stp_TEST_GetCV -----------------------------




CREATE PROCEDURE stp_TEST_GetCV

@P_FirstName varchar(20) ,
@P_LastName varchar(20) ,
@P_Phone varchar(20)

/********************************************************
*Procedure Name : stp_TEST_GetCV
*
*Auther: Nouman
*
*_______________________________________________
*Param Name Type Length
*_______________________________________________
*
*@P_FirstName varchar 20
*@P_LastName varchar 20
*@P_Phone varchar 20
*________________________________________________
*
*Note:
*
*Tables Accessed: CVSubmission
*
*Stored Procedure Accessed: -None-
*
*Temp tables created and used: -None-
*
*Reture Value: RecordsSet
*
*Any Special Notes:
*
*Revision#1
*
*
***************************************************************/

AS

BEGIN

Declare @STR_SQL nvarchar(4000)

SET @P_FirstName = Replace(@P_FirstName, '''' , '''''')
SET @P_LastName = Replace(@P_LastName, '''' , '''''')
SET @P_Phone = Replace(@P_Phone , '''' , '''''')



Set @STR_SQL = 'SELECT * FROM CVSubmission
WHERE Isnull(LastName,'''') like '''+ @P_LastName

+'%''
AND Isnull(FirstName,'''') like '''+ @P_FirstName

+'%''
AND isnull(Phone,'''') like '''+ @P_Phone +'%'''

BEGIN

EXEC (@STR_SQL)

END


END

GO

 

[Guest_imported]

Nouman,you are a very kind and helpful individual.
I can't thank you enough for your time, patience and help.
Since I am using an Oracle database, I will need to make a lot of changes to suite our requirement.
I am sure this will do it.
I will let you know once the code starts working.
Thanks again.
sim

 

[Guest_imported]

Is this a database specific error.
I tried the codes sent to me Nouman(Thanks again).
It works in SQL Server but it is not working in Oracle.
I keep getting this error:

Error Type:
Microsoft VBScript compilation (0x800A0414)
Cannot use parentheses when calling a Sub
/My_AccessBB/SubmitResult.asp, line 26, column 76

which begins on this line:
.Parameters.Append.CreateParameter(&quot;P_FirstName&quot;,200, &H0001, 20, stData(0))
Here is a snippet of the code:

<%
const ArraySep = &quot;,&quot;
Dim objConn,objCmd
Set objConn = server.CreateObject(&quot;ADODB.Connection&quot
Set objCmd= server.CreateObject(&quot;ADODB.Command&quot
objConn.CursorLocation = 3
objConn.Open &quot;DSN=EmpDB;UID=scott;PWD=tiger&quot;


dim StRaData , StData
StRaData = Request.Form(&quot;txtData&quot & ArraySep
StData = Split(straData, ArraySep )

With objCmd
.CommandType = &H0004
Set.ActiveConnection = objConn
.CommandText = &quot;stp_TEST_InsertCV&quot;
.Parameters.Append.CreateParameter(&quot;P_FirstName&quot;,200, &H0001, 20, stData(0))
.Parameters.Append.CreateParameter(&quot;P_LastName&quot;,200, &H0001, 20, stData(1))
.Parameters.Append.CreateParameter(&quot;P_Email&quot;, 200,&H0001, 50, stData(2))
.Parameters.Append.CreateParameter(&quot;P_Phone&quot;, 200,&H0001, 20, stData(3))
.Parameters.Append.CreateParameter(&quot;P_Fax&quot;, 200,&H0001, 20, stData(4))
.Parameters.Append.CreateParameter(&quot;P_Address1&quot;,200, &H0001, 50, stData(5))
.Parameters.Append.CreateParameter(&quot;P_Address2&quot;,200, &H0001, 50, stData(6))
.Parameters.Append.CreateParameter(&quot;P_City&quot;, 200,&H0001, 20, stData(7))
.Parameters.Append.CreateParameter(&quot;P_State&quot;, 200,&H0001, 20, stData(8))
.Parameters.Append.CreateParameter(&quot;P_ZipCode&quot;,200, &H0001, 20, stData(9))
.Parameters.Append.CreateParameter(&quot;P_Country&quot;,200, &H0001, 20, stData(10))
.Parameters.Append.CreateParameter(&quot;P_EducationDetails&quot;, 200, &H0001,255,stData(11))
.Parameters.Append.CreateParameter(&quot;P_YearExperience&quot;, 200, &H0001, 5,stData(12))
.Parameters.Append.CreateParameter(&quot;P_ExperienceDetails&quot;, 200, &H0001,255,stData(13))
.Execute
End With

Set objCmd=nothing
Set Conn = nothing

%>

I am suffering!!!

 

[nomi2000]

Dear Sim
the ASP pages which i sent is not database specific as they are using ADO but yes the script for stored procedrues are for SQL server 2000
the asp pages i made for you and checked it ..its working fine here without any difficult
'i got one thing which is
Set.ActiveConnection = objConn
'it should have space like
Set .ActiveConnection = objConn
I am not an expert in ORACEl but if u can send me the table structure pluse ur ASP pages i can look into it
Regadrds
Nouman

 

[Guest_imported]

Nouman, thank you for your continued patience.
I don't think the problem has to do with the table structure or stored proc.
The good news is I know SQL Server as I have done a lot of projects with it. So I was able to convert the codes you sent me to Oracle easily but I could be wrong so here is my table structure, my oracle stored proc.
The asp code is the same as the one you sent me. The only difference is the connection string which you can see in my thread.

/*** Table structure***/
CREATE table CVSubmission(
FirstName varchar2(20) ,
LastName varchar2(20) ,
Email varchar2(50),
Phone varchar2(20) ,
Fax varchar2(20),
Address1 varchar2(50) ,
Address2 varchar2(50) ,
City varchar2(20),
State varchar2(20),
ZipCode varchar2(20),
Country varchar2(20),
EducationDetails varchar2(255),
YearExperience varchar2(5),
ExperienceDetails varchar2(255));

/******stored procs*****************/
----------- a) stp_TEST_InsertCV-----------

CREATE OR REPLACE PROCEDURE stp_TEST_InsertCV (

P_FirstName IN CVSubmission.FirstName%TYPE,
P_LastName IN CVSubmission.LastName%TYPE,
P_Email IN CVSubmission.Email%TYPE ,
P_Phone IN CVSubmission.Phone%TYPE ,
P_Fax IN CVSubmission.Fax%TYPE ,
P_Address1 IN CVSubmission.Address1%TYPE,
P_Address2 IN CVSubmission.Address2%TYPE,
P_City IN CVSubmission.City%TYPE ,
P_State IN CVSubmission.State%TYPE ,
P_ZipCode IN CVSubmission.ZipCode%TYPE ,
P_Country IN CVSubmission.Country%TYPE ,
P_EducationDetails IN CVSubmission.EducationDetails%,
P_YearExperience
CVSubmission.YearExperience%TYPE ,
P_ExperienceDetails
CVSubmission.ExperienceDetails%TYPE);

AS

BEGIN


INSERT INTO CVSubmission
(
FirstName,
LastName,
Email,
Phone,
Fax,
Address1,
Address2,
City,
State,
ZipCode,
Country,
EducationDetails,
YearExperience,
ExperienceDetails)
VALUES
(
P_FirstName ,
P_LastName,
P_Email,
P_Phone ,
P_Fax ,
P_Address1 ,
P_Address2 ,
P_City ,
P_State,
P_ZipCode,
P_Country,
P_EducationDetails,
P_YearExperience,
P_ExperienceDetails;
)

END;

 

[harmmeijer]

Your code has some spaces missing:

.Parameters .Append .CreateParameter(&quot;P_FirstName&quot;,200, &H0001, 20, stData(0))

Each word beginning with a . should have a space before it unless it is the first word of a sentince.

See above:
.Append did not have a space.
.CreateParameter did not have a space


You can correct this line and find out there are a lot more lines that have the space missing before the . .

 

[nomi2000]

Dear Sim
i don't figure any error in ur Stored Procedure but there are few things which u should do so u could be able to dug out what is causing the error
You Said the ASP Code i sent
--&quot;It works in SQL Server but it is not working in Oracle&quot; Let me know Is it TRUE?
--ALSO LET me know is it the complete code snippet which u post last?
By looking at ur Error,it tells me that its Front-End Related Problem But then i feel how it could be possible as it ASP pages work fine to me at my side...

Error Type:
Microsoft VBScript compilation (0x800A0414)
Cannot use parentheses when calling a Sub
/My_AccessBB/SubmitResult.asp, line 26, column 76

which begins on this line:
.Parameters.Append.CreateParameter(&quot;P_FirstName&quot;,200, &H0001, 20, stData(0))

now what u could do is debbug if the parameters are passing correctly from the first page
so add after lines
dim StRaData , StData
StRaData = Request.Form(&quot;txtData&quot & ArraySep
StData = Split(straData, ArraySep )

Response.write stRaData 'Check whether it has correct contents which u passed? ADD THIS

If the parameters are comming corrctly then the prob might be other but just do it so u could know about it
Don't worry about the error its you who have to dug out whats causing the problem i or anyone can just give u tips which can help u in dugging it out...
Hope it will help you,
Regards
Nouman