Syntax Error in Update

27 Jan 2006 2:29 PM

YAHOOid

Can't figure out why I am getting a syntax error on this
Incorrect syntax near '@RecordID'

RecordID is an integer

UPDATE CCI SET Insured = @Insured, State = @State, CCICode = @CCICode,
Occupation = @Occupation, PolicyNumber = @PolicyNumber,
PolicyEffective = @newPolicyEffective,
PolicyExpiration = @newPolicyExpiration,
CarrierPolicyNumber = @CarrierPolicyNumber, CarrierID = @CarrierID,
CarrierName = @CarrierName, InsuranceType = @InsuranceType,
DateModified = GETDATE(), RecAct = 'Edit', UserID = Right(@LogonUser,7)
WHERE RecordID = @RecordID

27 Jan 2006 2:36 PM

Bob Barrows [MVP]

YAHOOid wrote:

> Can't figure out why I am getting a syntax error on this
> Incorrect syntax near '@RecordID'

Are you testing this in QA?

>
> RecordID is an integer
>
>
> UPDATE CCI SET Insured = @Insured, State = @State, CCICode =
>   @CCICode, Occupation = @Occupation, PolicyNumber = @PolicyNumber,
>   PolicyEffective = @newPolicyEffective,
>   PolicyExpiration = @newPolicyExpiration,
>   CarrierPolicyNumber = @CarrierPolicyNumber, CarrierID = @CarrierID,
>   CarrierName = @CarrierName, InsuranceType = @InsuranceType,
>   DateModified = GETDATE(), RecAct = 'Edit', UserID =
>   Right(@LogonUser,7) WHERE RecordID = @RecordID

Could you provide the CREATE TABLE statement (aka DDL) for the CCI table? I
see nothing wrong with this statement and need to test it. Also, what
version of SQL Server are you using?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

27 Jan 2006 2:56 PM

YAHOOid

You know what it was, I didn't have the END statement at the end of my
procedure. Sorry to waste your time.

Show quote

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23c$cg80IGHA.3896@TK2MSFTNGP15.phx.gbl...
> YAHOOid wrote:
>> Can't figure out why I am getting a syntax error on this
>> Incorrect syntax near '@RecordID'
>
> Are you testing this in QA?
>
>>
>> RecordID is an integer
>>
>>
>> UPDATE CCI SET Insured = @Insured, State = @State, CCICode =
>>   @CCICode, Occupation = @Occupation, PolicyNumber = @PolicyNumber,
>>   PolicyEffective = @newPolicyEffective,
>>   PolicyExpiration = @newPolicyExpiration,
>>   CarrierPolicyNumber = @CarrierPolicyNumber, CarrierID = @CarrierID,
>>   CarrierName = @CarrierName, InsuranceType = @InsuranceType,
>>   DateModified = GETDATE(), RecAct = 'Edit', UserID =
>>   Right(@LogonUser,7) WHERE RecordID = @RecordID
>
> Could you provide the CREATE TABLE statement (aka DDL) for the CCI table?
> I
> see nothing wrong with this statement and need to test it. Also, what
> version of SQL Server are you using?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

27 Jan 2006 3:08 PM

Bob Barrows [MVP]

YAHOOid wrote:

> You know what it was, I didn't have the END statement at the end of my
> procedure. Sorry to waste your time.
>

'sOK. Glad you solved it.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

27 Jan 2006 3:16 PM

ML

If this were post #15 then it woudl have been a waste of time... :)

ML

---
http://milambda.blogspot.com/

27 Jan 2006 2:44 PM

ML

Are you sure the error originates in this piece of code? Maybe it's one of
the next lines.

ML

---
http://milambda.blogspot.com/

27 Jan 2006 2:47 PM

David Portas

YAHOOid wrote:
Show quote

> Can't figure out why I am getting a syntax error on this
> Incorrect syntax near '@RecordID'
>
> RecordID is an integer
>
>
> UPDATE CCI SET Insured = @Insured, State = @State, CCICode = @CCICode,
>   Occupation = @Occupation, PolicyNumber = @PolicyNumber,
>   PolicyEffective = @newPolicyEffective,
>   PolicyExpiration = @newPolicyExpiration,
>   CarrierPolicyNumber = @CarrierPolicyNumber, CarrierID = @CarrierID,
>   CarrierName = @CarrierName, InsuranceType = @InsuranceType,
>   DateModified = GETDATE(), RecAct = 'Edit', UserID = Right(@LogonUser,7)
>   WHERE RecordID = @RecordID

What was the EXACT text of the error message? The statement syntax
seems valid but I expect it could be a type conversion error involving
one of those values. Most commonly this happens when casting a string
to a numeric. For example, if Userid is an INTEGER check that
@logonuser contains only valid numerics. You can use the debugger to
watch the variables and check that the contents is as expected.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--