Microsoft OLE DB Provider for SQL Server error '80040e07'

Hello,
 I have everything working except being able to pull records based on a previous ASP pages popCal value.

I get

Microsoft OLE DB Provider for SQL Server error '80040e07'

Syntax error converting the varchar value '11/1/04' to a column of data type int.

/goldsweb/OpNumbers.asp, line 21



here is my ASP Code:

<% Option Explicit

Dim BegDate, Store, ConDB, rs, strSQL, i, flds, toggle, EmailAddr, Phones, Convert, sColor(1)

BegDate = Request.Form("txtBegDate")

 Set conDB = Server.CreateObject("ADODB.Connection")
 conDB.Open "Provider=sqloledb;" & _
           "Data Source=database-server;" & _
           "Initial Catalog=CorpWeb;" & _
           "User Id=empnum;" & _
           "Password=monktard"

strSQL = "SELECT * FROM GoldsNum WHERE BegDate = " & BegDate &" "

Set rs = ConDB.Execute(strSQL)

flds = rs.Fields.Count

%>

<html>

<head>
<title>TANCo Corporate Directory</title>
</head>

<body bgcolor="#000000" background="Images/blkmarble.gif" bgproperties="fixed" link="#84186E" vlink="#84186E" alink="#84186E">

<% =BegDate %>

<%                   
                  
                  sColor(0) = ""
                sColor(1) = ""
                  response.write "<br>"
                response.write "<center> <TABLE border='5' width=400 border=0 cellpadding=2 cellspacing=1>" & vbCrLf
                response.write "<TR>" & vbCrLf

                For i = 0 To flds - 1
                      If rs.Fields(i).Name = "BegDate" Then
                              response.write "<TD nowrap align='center'>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                                  ElseIf rs.Fields(i).Name = "EndDate" Then
                                         response.write "<TD nowrap align='center'>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                                               ElseIf rs.Fields(i).Name = "EmpName" Then
                                                     response.write "<TD nowrap align='center'>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                                                           ElseIf rs.Fields(i).Name = "Store" Then
                                                                 response.write "<TD nowrap align='center'>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                                                                       ElseIf rs.Fields(i).Name = "EmpPosition" Then
                                                                             response.write "<TD nowrap align='center'>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                                                                                   ElseIf rs.Fields(i).Name = "Sales" Then
                                                                                         response.write "<TD nowrap align='center'>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                                                                                               ElseIf rs.Fields(i).Name = "Members" Then
                                                                                                     response.write "<TD nowrap align='center'>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                                                                                                           ElseIf rs.Fields(i).Name = "Sessions" Then
                                                                                                                 response.write "<TD nowrap align='center'>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                                                                                                                        Else
                                                                                                                              response.write "<TD nowrap>" & "<B>" & "<font face='tahoma' color='#FFE200' size='2'>" & rs.Fields(i).Name & "</font>" & "<B>" & "</TD>"
                      End If
                Next

                response.write vbCrLf & "</TR>"

                rs.movefirst

                Do While Not rs.EOF

                      response.write "<TR " & sColor(toggle) & ">" & vbCrLf

                      For i = 0 To flds - 1

                                                 response.write "<TD nowrap align='center'>" & "<font face='tahoma' color='#FFFFFF' size='2'>" & rs.Fields(i).Value & "</font></TD>"

                      Next
                      response.write vbCrLf & "</TR>"
                      rs.MoveNext
                Loop

          rs.Close
          Set rs = Nothing
          response.write vbcrlf & "</TABLE>"

response.write vbcrlf & "</TABLE>"
response.write "<BR>"
%>

</body>
</html>

Thanks ALL.
Andrew Helbling
AhelblingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SashPCommented:
Hi Ahelbling

The column BegDate that you are using is an int datatype and you are passing a varchar '11/1/04' in you where clause, check the datatypes of the fields in the GoldsNum table.

Cheers Sash
0
AhelblingAuthor Commented:
it is a varchar
0
SashPCommented:
strSQL = "SELECT * FROM GoldsNum WHERE BegDate =  '" & BegDate &"'"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AhelblingAuthor Commented:
sorry - it is a varchar type in the GoldsNum table. Length of 50.
0
SashPCommented:
You must supply the varchar inside a pair of ' characters
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AhelblingAuthor Commented:
i will try it
0
SashPCommented:
Trying to identify dates using varchars is going to be a headache later on.  It means that you must always supply the date in the correct format and the data in the column must always be the correct format.

if possible change the datatype of the GoldsNum field to a datetime datatype.
0
AhelblingAuthor Commented:
man - thanks so much - i am a newbie and have been scratching my head trying to do some kinda type casting or something to fix it.  Right on!
0
AhelblingAuthor Commented:
okay - i will thanks again.
0
SashPCommented:
Ahelbling,


Also I prefer when searching against a datetime datatype to pass a varchar of the format 'd mmm yyyy' this prevents any problems with date formating due to different international date formats.

Cheers Sash
0
AhelblingAuthor Commented:
okay -noted.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.