• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 905
  • Last Modified:

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
0
Ahelbling
Asked:
Ahelbling
  • 6
  • 5
1 Solution
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now