473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

passing date variable to sp

in asp i have this line:
.....
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate, adParamInput,
50, invdate)
cmdUpdatex.Parameters.Append paramsx
...
(the invdate value is a dd/mm/yyyy date)
AND in sql server 2000 sp i have:

....
@invdate nvarchar(100)= null,
....
insert into inv
(
invdate,
)
values
(
@invdate,
)

when i run this it does not work...
so i run profiler and extracted the problem line.
in query analyser it gives me the:
Syntax error converting character string to smalldatetime data type.
what do you guys think is the problem?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004
Jul 22 '05 #1
6 6714
mirza i wrote:
in asp i have this line:
....
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate,
adParamInput, 50, invdate)
cmdUpdatex.Parameters.Append paramsx
..
(the invdate value is a dd/mm/yyyy date)
AND in sql server 2000 sp i have:

...
@invdate nvarchar(100)= null,
...
insert into inv
(
invdate,
)
values
(
@invdate,
)

when i run this it does not work...
so i run profiler and extracted the problem line.
in query analyser it gives me the:
Syntax error converting character string to smalldatetime data type.
what do you guys think is the problem?


Date format.
http://www.aspfaq.com/show.asp?id=2040

Bob Barrows
--
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.
Jul 22 '05 #2
In my case

Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate, adParamInput,
50, invdate)

=>
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", advarchar,
adParamInput,
20, invdate)

and in sql sp you have to change
@invdate varchar(20) to convert(smalldatetime, @invdate)
"mirza i" <us*****@hotmail.com> wrote in message
news:#c*************@TK2MSFTNGP09.phx.gbl...
in asp i have this line:
....
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate, adParamInput, 50, invdate)
cmdUpdatex.Parameters.Append paramsx
..
(the invdate value is a dd/mm/yyyy date)
AND in sql server 2000 sp i have:

...
@invdate nvarchar(100)= null,
...
insert into inv
(
invdate,
)
values
(
@invdate,
)

when i run this it does not work...
so i run profiler and extracted the problem line.
in query analyser it gives me the:
Syntax error converting character string to smalldatetime data type.
what do you guys think is the problem?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004

Jul 22 '05 #3
i have tried everything (lost 5hrs and gained 1000 gray hairs)
tomorrow i'll try your suggestion.

thanks
"¶Ë°*¾ÆÁö" <dd******@haja.or.kr.korea> wrote in message
news:ep**************@TK2MSFTNGP14.phx.gbl...
In my case

Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate,
adParamInput,
50, invdate)

=>
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", advarchar,
adParamInput,
20, invdate)

and in sql sp you have to change
@invdate varchar(20) to convert(smalldatetime, @invdate)
"mirza i" <us*****@hotmail.com> wrote in message
news:#c*************@TK2MSFTNGP09.phx.gbl...
in asp i have this line:
....
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate,

adParamInput,
50, invdate)
cmdUpdatex.Parameters.Append paramsx
..
(the invdate value is a dd/mm/yyyy date)
AND in sql server 2000 sp i have:

...
@invdate nvarchar(100)= null,
...
insert into inv
(
invdate,
)
values
(
@invdate,
)

when i run this it does not work...
so i run profiler and extracted the problem line.
in query analyser it gives me the:
Syntax error converting character string to smalldatetime data type.
what do you guys think is the problem?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004
Jul 22 '05 #4
CJM

"mirza i" <us*****@hotmail.com> wrote in message
news:eb***************@TK2MSFTNGP10.phx.gbl...
i have tried everything (lost 5hrs and gained 1000 gray hairs)
tomorrow i'll try your suggestion.

thanks


I think you'll find the link Bob posted has the answers...

In simple, use the ISO-style Date format: e.g. 20041210 (ISO date format
is actually 2004-12-10)

Chris
Jul 22 '05 #5

ok, figured it out.

i have tried everything from changing dateformat in datab to passing dates
instead of varchar and to my great happiness nothing worked.
in datab:
invdate is datetime,8

from asp:
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", adVarChar,
adParamInput, 50, invdate) cmdUpdatex.Parameters.Append paramsx

and in sql server:
(in @ declares)
@invdate nvarchar(100)= NULL,
and
(in prog)
SELECT @invdate = CONVERT(datetime, @invdate, 103)
....

by the way, this was strange one since i don't think i have changed anything
in prog, sproc or in database and yet the app did not work anymore...
so,
lessons learned: as always figuring out what the problem is in a sproc is a
******* nightmare...
in future i will always use yyyymmdd, this app in now too big for that kind
of change.

many thanks for all that have replied :)

regards

mirza

"CJM" <cj*******@newsgroups.nospam> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

"mirza i" <us*****@hotmail.com> wrote in message
news:eb***************@TK2MSFTNGP10.phx.gbl...
i have tried everything (lost 5hrs and gained 1000 gray hairs)
tomorrow i'll try your suggestion.

thanks


I think you'll find the link Bob posted has the answers...

In simple, use the ISO-style Date format: e.g. 20041210 (ISO date format
is actually 2004-12-10)

Chris

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004
Jul 22 '05 #6
"CJM" <cj*******@newsgroups.nospam> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

"mirza i" <us*****@hotmail.com> wrote in message
news:eb***************@TK2MSFTNGP10.phx.gbl...
i have tried everything (lost 5hrs and gained 1000 gray hairs)
tomorrow i'll try your suggestion.

thanks


I think you'll find the link Bob posted has the answers...

In simple, use the ISO-style Date format: e.g. 20041210 (ISO date format
is actually 2004-12-10)


Actually both are ISO-style formats. CCYYMMDD is the basic format and
CCYY-MM-DD is the extended format. Here's a related thread from Google
Groups:
http://groups-beta.google.com/group/...d03358416e3859
Jul 22 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Kevin Lyons | last post by:
Hello, I am trying to get all of my form elements passed correctly from the following URL: http://www.dslextreme.com/users/kevinlyons/selectBoxes.html to the following URL:...
6
by: Scott Zabolotzky | last post by:
I'm trying to pass a custom object back and forth between forms. This custom object is pulled into the app using an external reference to an assembly DLL that was given to me by a co-worker. A...
2
by: Keith | last post by:
Good Afternoon, New to .Net. I am trying to pass date/time values to a MS Access query depending on what value is selected from a dropdown list box (January, February, etc). I have declared...
3
by: IntraRELY | last post by:
I have the following function, Notice how I am passing the dateInterval as a string. What is the correct way to pass "DateInterval.Year" as a variable to a function? TIA, Steve Wofford...
9
by: laurenq uantrell | last post by:
I've gotten sort of fed up with dealing with regional date settings on the client side and am considering the following scheme - just wondering if anyone has a negative view of it or not: ...
1
by: darrel | last post by:
A common problem I encounter is having to enter either a date OR a null value into a date field in a DB. The problem I hace is that DBNull.value isn't a proper value for a date variable, so I...
2
by: Geoff Cox | last post by:
Hello, The code below is aimed at passing the date in the yyyyMMdd format from the javascript calendar in an html file to the php in a another file which then searches a MySQL database. For...
2
by: milo1955 | last post by:
I have a form that passes a single variable via the GET function to a mysql databse query with the results paginated. The first page of the results work fine, but the link to the second page yields...
5
by: gubbachchi | last post by:
Hi all, How to pass the php date variable to javascript function. Here is the code I have tried out, but this is not recovering the date correctly in the javascript function <html> <script...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.