Getting error with SQL statement

Results 1 to 3 of 3

Thread: Getting error with SQL statement

  1. #1
    Join Date
    Dec 1969

    Default Getting error with SQL statement

    I have an SQL statement that returns a recordset that works unless it cannot find any data to return. I would like to know how to check if the SQL did not return any data and then be able to respond without have an error message get outputed. Here is the code. I have it set to Dy (Day) = 50 to simulate a day that is not in the table. The table only have 2 records now and when the sql returns one of those two record then there are no errors.<BR>--------------------------------<BR>Yr = Year(Date)<BR> Dy = 50 &#039Day(Date)<BR> <BR> If Dy = 365 then<BR> Dy = 364<BR> end if<BR> <BR> Wk = int(Dy / 7) + 1<BR> <BR> SQL_query = chr(34) & "SELECT * FROM tblTipofWeek WHERE Year = " & Yr & " AND Week=" & Wk & chr(34)<BR> <BR> Response.Write "&#060;p&#062;" & SQL_query & "&#060;/p&#062;"<BR> <BR> Conn.Execute("SQL_query") &#039this is where the error occurs<BR>-------------------------<BR>This is the error:<BR>Microsoft OLE DB Provider for ODBC Drivers error &#039 80040e14&#039 <BR><BR>[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected &#039DELETE&#039, &#039INSERT&#039, &#039PROCEDURE&#039, &#039SELECT&#039, or &#039UPDATE&#039. <BR><BR>/ds/tip_of_the_week.asp, line 190 <BR><BR><BR>This is the SQL string:<BR>"SELECT * FROM tblTipofWeek WHERE Year = 2000 AND Week=8"<BR><BR>Thanks <BR><BR>Matt

  2. #2
    Neil Popham Guest

    Default RE: Getting error with SQL statement

    I don&#039t understand the Chr(34) bit - just lose them and it should work...<BR><BR>I would say the error is occurring as the first character in your SQL statement is " - when its expecting D, I, P, S, or U<BR><BR>Also, don&#039t you need to allocate the recordset to a variable, using:<BR><BR>&#062; Set objRec = Conn.Execute("SQL_query")<BR><BR>You begin the query by asking how to check for an empty recordset. To achieve this use something like:<BR><BR>&#062; If objRec.BOF And objRec.EOF Then &#039 do some oh god there&#039s no records code

  3. #3
    Peter Valencic Guest

    Default RE: Getting error with SQL statement

    Try to do this<BR>========================================== =<BR>insert= "select * from database"<BR><BR>On Error Resume Next &#039 if the error ocur<BR>conn.Execute (insert, stev_pod) &#039 stev pod is optional! <BR>On Error GoTo 0<BR> If stev_pod &#062; 0 Then<BR> e = 0 &#039 there are data<BR> Else<BR> e = 1 &#039 there are no data<BR> End If<BR>============================================ ============<BR>with on error resume next the error will not be displayed, with the second parameter "stev pod" the number of rows will be returned.<BR><BR>See the visual basic ADO documentation...<BR><BR>It works fine also with update statments.......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts