ASP - Building a Query - Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ASP - Building a Query - Question

Author

Topic

BrianS
Starting Member

3 Posts

Posted - 2007-02-12 : 15:01:48

Good Afternoon!

I have a (hopefully) simple question.
I have recently been bumped into an applications developer position. I took a week of ASP training a few months ago, but outside of that my exposure to ASP and SQL has been EXTREMELY limited.

I just undertook my first project using these skills. We have an application where I track "Letters". I fill out a form with information regarding the Letter, and save it so there is a record of the letters existence and what its status is.

Afterwards, we want to update the status, so I find the saved form, make my edits, and save it.

This is where my problem starts.

When I first save the letter, I have no problems whatsoever.
When I save it a second time after making my edits, I get an error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'yearID'.
/AuditFlowChart/UpdateAudit.asp, line 206

What I am doing with the ASP is basically created a SQL statement to update the table with the new letter information. I added a line to print out the store proceedure that is being created by the following line

Set rsRecordsetObject = objCOMComponentClassObject.ExecuteSQLStatement(spStoredProcedure,vntSQLServerName,dbDatabaseCatalog)

I print out spStoredProcedure and I get the following

UpdateCorrespondence @p_idCorr = 416,
@p_dtOfLetter = '8/7/2008',
@p_dtRcvdByRespAtty = '2/19/2007',
@p_dtRcvdByAdmin = '2/23/2007',
@p_descOfEntity = 'This is a test 2009 letter. Dated 2/8/2007',
@p_ClientCode = '01074',
@p_MatterCode = '0055',
@p_dtDue = '3/15/2007',
@p_dtEffective = '4/18/2007',
@p_dtYE = '4/8/2007',
@p_respAtty = '0330',
@p_respAttyFull = 'Abelson, H. Edward',
@p_prepParalegal = 'Aldous, Anne Marie',
@p_prepAtty = 'Abromowitz, David',
@p_prepLitParalegal = 'Allegrini, J. Samuel',
@p_dtGivenToParalegal = '4/20/2007',
@p_dtGivenToAtty = '2/28/2007',
@p_dtMemoCirculated = '2/18/2007',
@p_dtRespSentToLitParalegal = '2/10/2007',
@p_dtRespSentToPrepAtty = '2/26/2007',
@p_dtSentToAccountant = '2/15/2007',
@p_memoDSid = 1111111,
@p_respLetterDSid = 2222222,
@p_sideLetterDSid = 3333333,
@p_comments = NULL,
@p_yearID = rsLetterDetail1('yearID'),
@p_opinion = 1,
@p_complete = 0,
@p_userID = 'GS\SUSO1517'

When I run the above thru Query Analyzer, I get the following:

Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near 'yearID'.

So now I know where my error lies, but I am not sure where to look next. Does anyone have any ideas? Is there a problem I am not seeing with the query?

Any help would be wonderful and much appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 15:09:57

Are you trying to use the result from a recordset into a parameter in the stored procedure?
You must concat the result from the recordset, and use the value as parameter assignment.

Peter Larsson
Helsingborg, Sweden

BrianS
Starting Member

3 Posts

Posted - 2007-02-12 : 15:37:25

strange.. I got it working.

It was a problem in that the include in the follwing line of text

<option id="cboLtrYear" selected="selected" value= <%=rsLetterDetail1("yearID")%> > <%=rsLetterDetail1("yearID")%></option>

I had to putt he spaces in after value= and the %> and it worked. I am using Homesite as an editor and I noticed the script was not being flagged as a script, just html.

Still, thanks for the response... :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources