Problems with an INSERT SP

Hi folks
I have a SP that inserts a record into a table. The text is as follows:
''''
'
CREATE PROCEDURE [dbo].[procClientAdd]
@.lastname varchar (50),
@.firstname varchar (50) = NULL,
@.othernames varchar (50) = NULL,
@.gender varchar (6),
@.dob smalldatetime,
@.comment varchar (50) = NULL,
@.note varchar (500) = NULL,
@.addr1 varchar (50)=NULL,
@.addr2 varchar (50) = NULL,
@.suburb int = NULL,
@.state varchar (3) = NULL
AS
INSERT INTO Clients (LastName, FirstName, OtherNames, Gender, DateOfBirth,
Comment, Note, AddressLine1, AddressLine2, SuburbID, StateCode)
VALUES (@.lastname, @.firstname, @.othernames, @.gender, @.dob, @.comment,
@.note, @.addr1, @.addr2, @.suburb, @.state)
'''
When I attempt to execute it within SQL Query Analyser, I get the following
message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
The EXEC statement is as follows:
EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross',,@.gender =
'Male', @.dob = 'Novermber 1 1952', @.comment = '?',, @.addr1 = '7Bay Road',,
@.suburb = 1, @.state = 'TAS'
I hope somebody can help - I am using Win XP SP2
Kind regards
Ross PetersenEXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross',,@.gender =
'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
@.suburb = 1, @.state = 'TAS'
This is the correct way. You had too many commas after comment and addr1
Yosh
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:706EF6DF-5FD3-45E6-9477-E2AEE35F32F2@.microsoft.com...
> Hi folks
> I have a SP that inserts a record into a table. The text is as follows:
> ''''
'
> CREATE PROCEDURE [dbo].[procClientAdd]
> @.lastname varchar (50),
> @.firstname varchar (50) = NULL,
> @.othernames varchar (50) = NULL,
> @.gender varchar (6),
> @.dob smalldatetime,
> @.comment varchar (50) = NULL,
> @.note varchar (500) = NULL,
> @.addr1 varchar (50)=NULL,
> @.addr2 varchar (50) = NULL,
> @.suburb int = NULL,
> @.state varchar (3) = NULL
> AS
> INSERT INTO Clients (LastName, FirstName, OtherNames, Gender, DateOfBirth,
> Comment, Note, AddressLine1, AddressLine2, SuburbID, StateCode)
> VALUES (@.lastname, @.firstname, @.othernames, @.gender, @.dob, @.comment,
> @.note, @.addr1, @.addr2, @.suburb, @.state)
> '''
> When I attempt to execute it within SQL Query Analyser, I get the
> following
> message:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ','.
> The EXEC statement is as follows:
> EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross',,@.gender =
> 'Male', @.dob = 'Novermber 1 1952', @.comment = '?',, @.addr1 = '7Bay Road',,
> @.suburb = 1, @.state = 'TAS'
> I hope somebody can help - I am using Win XP SP2
> Kind regards
> Ross Petersen|||Hi Yosh
Thanks for the quick reply.
I tried your suggestion & it has helped, but there is a problem still.
the EXEC statement looks like this now:
''
EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross', @.gender =
'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
@.suburb = 1, @.state = 'TAS'
''
I am now getting the following error msg:
'''
Server: Msg 8114, Level 16, State 4, Procedure procClientAdd, Line 0
Error converting data type varchar to smalldatetime.
''''
I have tried a few permutations of the date section, but the same error
occurs. Is there something wrong with how I am specifying the date?
Kind regards
Ross Petersen
"Yosh" wrote:

> EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross',,@.gender =
> 'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
> @.suburb = 1, @.state = 'TAS'
> This is the correct way. You had too many commas after comment and addr1
> Yosh
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:706EF6DF-5FD3-45E6-9477-E2AEE35F32F2@.microsoft.com...
>
>|||Hi,
"November" is misspelled. Try either
@.dob = 'November 1, 1952'
or
@.dob = '1952-11-01'
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:1F6942C3-B180-4627-BC6A-BAB556F67626@.microsoft.com...
> Hi Yosh
> Thanks for the quick reply.
> I tried your suggestion & it has helped, but there is a problem still.
> the EXEC statement looks like this now:
> ''
> EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross', @.gender =
> 'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
> @.suburb = 1, @.state = 'TAS'
> ''
> I am now getting the following error msg:
> '''
> Server: Msg 8114, Level 16, State 4, Procedure procClientAdd, Line 0
> Error converting data type varchar to smalldatetime.
> ''''
> I have tried a few permutations of the date section, but the same error
> occurs. Is there something wrong with how I am specifying the date?
> Kind regards
> Ross Petersen
>
> "Yosh" wrote:
>
=
Road',
follows:
DateOfBirth,
'Ross',,@.gender =
Road',,|||It is because your date is not valid. Always pass dates like:
declare @.test datetime
set @.test = '1952-11-01'
or
set @.test = '19521101'
which are the standard ways to pass dates. I like the version with dashes,
as it tends to be easier to read.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:1F6942C3-B180-4627-BC6A-BAB556F67626@.microsoft.com...
> Hi Yosh
> Thanks for the quick reply.
> I tried your suggestion & it has helped, but there is a problem still.
> the EXEC statement looks like this now:
> ''
> EXEC procClientAdd @.lastname = 'Petersen', @.firstname = 'Ross', @.gender =
> 'Male', @.dob = 'Novermber 1 1952', @.comment = '?', @.addr1 = '7Bay Road',
> @.suburb = 1, @.state = 'TAS'
> ''
> I am now getting the following error msg:
> '''
> Server: Msg 8114, Level 16, State 4, Procedure procClientAdd, Line 0
> Error converting data type varchar to smalldatetime.
> ''''
> I have tried a few permutations of the date section, but the same error
> occurs. Is there something wrong with how I am specifying the date?
> Kind regards
> Ross Petersen
>
> "Yosh" wrote:
>|||Louis,
The format with hyphens is not standard. The two standard formats are
'19521101' and '1952-11-01T00:00:00'
set nocount on
go
set language French
go
declare @.test datetime
set @.test = '1952-11-01'
select month(@.test)
set @.test = '19521101'
select month(@.test)
set @.test = '1952-11-01T00:00:00'
select month(@.test)
go
set language us_english
go
declare @.test datetime
set @.test = '1952-11-01'
select month(@.test)
set @.test = '19521101'
select month(@.test)
set @.test = '1952-11-01T00:00:00'
select month(@.test)
Steve Kass
Drew University
Louis Davidson wrote:

>It is because your date is not valid. Always pass dates like:
>declare @.test datetime
>set @.test = '1952-11-01'
>or
>set @.test = '19521101'
>which are the standard ways to pass dates. I like the version with dashes,
>as it tends to be easier to read.
>|||So with hyphens is not standard, unless it has time attached? I did not
realize that. I had always assumed that the ISO8601 date would default the
time by definition.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23oNancoYFHA.1368@.tk2msftngp13.phx.gbl...
> Louis,
> The format with hyphens is not standard. The two standard formats are
> '19521101' and '1952-11-01T00:00:00'
>
> set nocount on
> go
> set language French
> go
> declare @.test datetime
> set @.test = '1952-11-01'
> select month(@.test)
> set @.test = '19521101'
> select month(@.test)
> set @.test = '1952-11-01T00:00:00'
> select month(@.test)
> go
> set language us_english
> go
> declare @.test datetime
> set @.test = '1952-11-01'
> select month(@.test)
> set @.test = '19521101'
> select month(@.test)
> set @.test = '1952-11-01T00:00:00'
> select month(@.test)
>
> Steve Kass
> Drew University
> Louis Davidson wrote:
>