October 27, 2008 at 6:38 am
I'm back to this one again. As I said above, I worked around the problem in a previous exercise and haven't been able to utilise the suggestions people made.
Today I realised I need to address it because it is a recurring need. I am actually working on an Excel based solution this time, but the principles must be the same. I have run my code with a watch window on the command I am passing to the store proc, and I can see the problem, but don't know how to fix it. The date is being passed by vba with a # before and after, and this forces the sp to treat it as NVARCHAR. The store procedure is expecting a parameter in datetime format. How do I get rid of the #?
Below are the relevant bits of VBA code.
Dim ParamDate As Date
.
.
stADO = "Provider=SQLOLEDB.1;Data Source=" & stdatasource & "; User Id=" & _
stUser & "; Password=" & stPwd & ";"
Set conn = New ADODB.Connection
Set objCom = New ADODB.Command
With conn
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
End With
With objCom
.CommandText = "[dbname].[cat]." & Trim(Range("Store_Proc").Value)
.CommandType = adCmdStoredProc
.ActiveConnection = conn.ConnectionString
.CommandTimeout = 0
End With
If IsDate(Trim(Range("ParmList").Cells(RowCounter, 2).Value)) Then
ParamDate = Range("ParmList").Cells(RowCounter, 2).Value
Set objPar1 = _
objCom.CreateParameter(Trim(Range("ParmList").Cells(RowCounter, 1).Value), _
adDate, adParamInput, , ParamDate)
Else
Set objPar1 = _
objCom.CreateParameter(Trim(Range("ParmList").Cells(RowCounter, 1).Value), _
adVarChar, adParamInput, 50, Trim(Range("ParmList").Cells(RowCounter, 2).Value))
End If
objCom.Parameters.Append objPar1
Thanks in advance
October 28, 2008 at 2:12 am
I have had a similar problem, and I think it's got something to do with me not using USA format dates. Though my SQL server, all my databases, and my users' pc are all set to the same UK format, it still doesn't want to play.
Edit the stored procedure on the SQL server so that the input parameter is varchar not datetime
Add a SET DATEFORMAT to the beginning of the stored procedure so that the SQL server can do the implicit conversion of varchar to datetime
Then simply build a string of the appropriate format (remembering leading zeros if appropriate) and send that string to the stored procedure as a parameter as shown in the many examples above
Alec
October 28, 2008 at 2:25 am
I'm always cautious with dates because of the US format, and I considered converting the date to character, but that seems pretty naff - get a date, convert it to character, then convert it back again. And between 2 Microsoft products no less!
I eventually solved the problem by passing the date as adVarChar and formatting it yyyymmdd. I left the parameter definition in the procedure as datetime, and it seems to cope with this.
I don't understand why it should be like this, and I haven't been able to find an explanation from any Microsoft web site.
October 28, 2008 at 2:33 am
Yeah you're right, it is naff
But as long as we reach a solution, that's the important thing.
October 28, 2008 at 8:05 am
born2bongo (8/19/2008)
... If I use a date in any format, and I put a single or a double quote around it, I get an error about converting from a varchar to a number.
(Disregard if nonsense...) Don't forget that in Access the delimiter for dates is #, not double quotes.
Sorry, I had not noticed the second page of postings when I replied. Now back to your regularly scheduled thread postings...
October 28, 2008 at 11:21 am
Not sure if it's relevant here, but Access only recognizes values as dates if they are delimited with pound signs: e.g. #10/1/2008#
Debby
October 29, 2008 at 2:06 am
Good point, but issue here is the stored procedure on SQL Server chucking an error over varchar-date conversion
Interesting the poster used almost the same solution I did - makes me feel a little better about my bodge 🙂
October 29, 2008 at 3:36 am
That's the thing tho'. I really hate bodging things, and what could be more of a bodge than having to convert a MICROSOFT vb date to a character format so that it can be read by a MICROSOFT Sql Server datetime parameter? If one outfit can't achieve consistency what hope is there across platforms?
If I could find any explanation of why this is necessary, or even a guide to a better way, I would be much happier. As it is, I am left with a sense of exclusion.
When I started out in IT (1977) we got training courses. Later we got good documentation from Microsoft. Now it's all some arcane mystery.:crazy:
October 29, 2008 at 6:39 am
Well good luck finding an explanation.
When I got the same problem I hunted high and low for an answer, couldn't find one, bodged it as above which worked.
Periodically I've asked the questions "Why?" and "Is there a better way?". Nobody's ever answered the second question save to post a variation on solution, and the first, well apart from lots of Americans (and countries who use their date format) telling me there is no problem, there's only been the odd few Europeans and Asians saying they've had to bodge it too.
Some SQL server chaps tend to look down their noses at those of us who can still find a use for a simple stable product like Access and find helping us to be beneath their dignity 😉
I think the root of the problem is this - Access takes it's date etc format settings from the system, SQL server doesn't. Once you add VBA into the mix which doesn't really have any international format options then you're going to come across issues - if only they were all this easy to work around.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply