Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

Status
Not open for further replies.
makumbi

makumbi

Programmer
Mar 15, 2011
12
UG
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ','.
/catalogue/DaEngine24.asp, line 78
When i tick one item it comes out well but when i tick more than one item the application gives the error above and i suspect this problem must be coming from moving to records more than one but i have failed to locate it please help

i dont want it to have multiple parameters are it to process every item individually but at the end of the day it should display all that has been ticked from page one

N.b i dont want my out put to be like 10,11 etc i want it to be one record at a time
Code 1
<%@LANGUAGE="VBSCRIPT"%>
<%
Dim MM_library_STRING
MM_library_STRING = "dsn=LocalServer;uid=sula;pwd=sula;"
%>
<html>
<head>
<title>Da Search Results!</title>
</head>

<body>
<script language="JavaScript">
function onDelete()
{
if(confirm('Do you want to delete ?')==true)
{
return true;
}
else
{
return false;
}
}
</script>
<form name="frmMain" action="DaEngine24.asp" method="post" OnSubmit="return onDelete();">

<%
Dim intPageCount, strsearch, intRecordCount, intPage, intRecord, intStart, intFinish, SqlJunk, sqljunk2,iResultsNum,sula,catego,myChkBoxes,chkbox ,conn
catego= Request.Form("TypeSearch")
strsearch= Request.Form("DaInBox")
sula = Request.Form("DaInBox")
myPage = Request.Form("myPage")
<!-------------- ADDED THIS ----------------------------------------------->
ChkBox = Request.Form("checkbox")
'<!-------------- ADDED THIS ----------------------------------------------->

IF myPage = "" THEN
myPage=1
END IF
IF Request.QueryString("mySQLStmt") <> "" THEN
'Need to Reconstruct the SQL Statement - Must have been submitted via a form
SqlJunk = Request.QueryString("mySQLStmt")
SqlJunk = replace( SqlJunk, "%25", "%")
SqlJunk = replace( SqlJunk, "!25!", "'")
SqlJunk = Replace( SqlJunk, "%20", " ")
IF myChkBoxes <> "" THEN
'SqlJunk = SqlJunk + " WHERE checks in("& myChkBoxes &")"
END IF
ELSE
'Constrcuting new SQL First time we have been here
'sqljunk2= "UPDATE Employees SET Employees.Phone = '0782911364'"
SqlJunk = "SELECT * FROM books"

If Request.Form("TypeSearch") = "Title" Then
SqlJunk = SqlJunk & " WHERE title LIKE '%" & Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Author" Then
SqlJunk = SqlJunk & " WHERE Author LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Shelve" Then
SqlJunk = SqlJunk & " WHERE Shelve LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Phrases" Then
SqlJunk = SqlJunk & " WHERE Phrases LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Callno" Then
SqlJunk = SqlJunk & " WHERE Callno LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
end if

'Set variables and open the record

Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.ConnectionString = "Driver={SQL Server};Server=kcc2;Database=slis;UID=sula;PWD=sul a;"
dbGlobalWeb.Open
rsGlobalWeb.CursorLocation = 3 'adUseClient
rsGlobalWeb.CursorType = 3 'adOpenStatic
'rsGlobalWeb.mode = 3 ' adModeReadWrite
rsGlobalWeb.ActiveConnection = dbGlobalWeb
'rsGlobalWeb.open "sendinquiry",dbGlobalWeb
rsGlobalWeb.Open Sqljunk
If NOT rsGlobalWeb.eof THEN
'We must have found some records
g_search="obs found"
rsGlobalWeb.movefirst

' Sets the number of observations per page to a max of 10
rsGlobalWeb.pagesize=30
' Set the pageCount of records are we in ?
rsGlobalWeb.absolutepage=myPage
' Get the total Number of Pages based on our PagesSize
myPageCount = rsGlobalWeb.pagecount

WITH RESPONSE
iResultsNum = ((myPage - 1) * rsGlobalWeb.pagesize)
' Go throug and list all the records
.Write "<TABLE>" &vbCRLF
.Write " <TR>" &vbCRLF
'THIS IS NEW COLUMN
.Write " <TD valign=top><span class='style4'><strong>Tick.</strong></span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>No.</strong></span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>Title</strong> </span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>Call No</strong> </span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>Author</strong> </span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>Subject</strong> </span></TD>" &vbCRLF
.Write " <TD valign=top>&nbsp;</TD>" &vbCRLF
.Write " </TR>" &vbCRLF
DO WHILE NOT rsGlobalWeb.eof and numofobs < rsGlobalWeb.pagesize
.Write " <TR>" &vbCRLF
.Write " <TR>" &vbCRLF
'THIS IS NEW LINE
iResultsNum = iResultsNum + 1

.Write " <TD valign=top><span class='style5'><input type='checkbox' name='chkdel' value=' "& rsGlobalWeb("autono") &"'></span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& iResultsNum &"</span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& rsGlobalWeb("title") &"</span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& rsGlobalWeb("callno") &"</span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& rsGlobalWeb("author") &"</span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& rsGlobalWeb("category") &"</span></TD>" &vbCRLF
.Write " <TD valign=top>&nbsp;</TD>" &vbCRLF
.Write " </TR>" &vbCRLF
.Write " <TR><TD Colspan='3'> </TD></TR>"&vbCRLF
rsGlobalWeb.movenext
' Counts the number of observations in the current page
numofobs=numofobs+1
LOOP
rsGlobalWeb.Close
dbGlobalWeb.Close
.Write "</TABLE>" &vbCRLF &vbCRLF
.Write "<input type='submit' value='ShowChk' name='bSubmit'>" &vbCRLF
.Write "</TABLE>" &vbCRLF &vbCRLF
.Write "</FORM>"
END WITH 'Response

'Prepare the SQL Statement for HTML TRANSPORT
'Used for Like
myHTMLSQLJunk = replace(SqlJunk, "%", "%25")
'USed for Dates
'myHTMLSQLJunk = replace(myHTMLSQLJunk, "#", "SOMECODEHERE")
'used for text
myHTMLSQLJunk = replace(myHTMLSQLJunk, "'", "!25!")
'Used for Spaces
myHTMLSQLJunk = replace(myHTMLSQLJunk, " ", "%20")

With Response
.Write "<TABLE><TR><TD>"
IF int(myPage) > 1 THEN
.Write "<form method='post' action='"& request.servervariables("script_name")&"?mySQLStmt="& myHTMLSQLJunk &"'>" &vbCRLF
.Write "<input type='hidden' name='myPage' value='"& myPage - 1 &"'>" &vbCRLF
'.Write "<input type='hidden' name='mySQLJunk' value='"& myHTMLSQLJunk &"'>" &vbCRLF
.Write "<input type='hidden' name='DaInBox' value='"& DaInBox &"'>" &vbCRLF
.Write "<input type='submit' value='<'>" &vbCRLF
.Write "</form></TD><TD>" &vbCRLF &vbCRLF
END IF

.Write " <form method='post' action='"& request.servervariables("script_name")&"?mySQLStmt="& myHTMLSQLJunk &"'>" &vbCRLF
.Write " <input type ='hidden' name='DaInBox' value='"& strsearch &"'>" &vbCRLF
.Write " <select name='myPage' size='1' onchange='this.form.submit();' name='bSubmit'>" &vbCRLF

' Starts loop from one to the maximum number of pages
For counter=1 to myPageCount
' This will select the current page in the dropdown menu
if cint(myPage) = cint(counter) then
.Write "<Option Selected value="& counter &">Page "& counter &" of "& myPageCount &"</option>" &vbCRLF
ELSE
.Write "<Option value="& counter &">Page "& counter &" of "& myPageCount &"</option>" &vbCRLF
END IF
Next
.Write "</select>" &vbCRLF
.Write "</form></TD><TD>" &vbCRLF &vbCRLF

IF int(myPage) < int(myPageCount) THEN
.Write "<form method='post' action='"& request.servervariables("script_name")&"?mySQLStmt="& myHTMLSQLJunk &"'>" &vbCRLF
.Write "<input type='hidden' name='myPage' value='"& myPage + 1 &"'>" &vbCRLF
'.Write "<input type='hidden' name='mySQLJunk' value='"& myHTMLSQLJunk &"'>" &vbCRLF
.Write "<input type='hidden' name='DaInBox' value='"& DaInBox &"'>" &vbCRLF
.Write "<input type='submit' value='>'>" &vbCRLF
.Write "</form>" &vbCRLF
END IF
.Write "</TD></TR></TABLE>" &vbCRLF
'.Write "<P>"& numofobs &" Results displayed on this page For Category Search <br></P> " &vbCRLF
'.Write "<P>"& sula &" Is The Category search <br></P> " &vbCRLF
END WITH 'Response
End IF ' Not EOF

'We Did not Find Records and We actually tried looking for them
IF g_search <> "obs found" AND DaInBox <> "" THEN
response.write "Nothing reults found for your search on <i>"& DaInBox &"</i><br>"
END IF
%>
</form>
</form>
<table width="430" border="2" cellpadding="2" cellspacing="2">
<tr>
<th width="202" scope="col">Your Searched Key Word = </th>
<th width="193" scope="col"><strong><%=sula%></strong>&nbsp;</th>
</tr>
<tr>
<th scope="row"><strong>Category Searched</strong> = </th>
<th width="193" scope="col"><strong><%=catego%></strong>&nbsp;</th>
</tr>
<tr>
<th scope="row">Total Results Disaplayed </th>
<td><div align="center"><strong><%=numofobs%></strong></div></td>
</tr>
</table>
<a href="ASPSearch.asp">NEW SEARCH</a>

code 2
<%@LANGUAGE="VBSCRIPT"%>
<%
Dim MM_library_STRING
MM_library_STRING = "dsn=LocalServer;uid=sula;pwd=sula;"
%>
<html>
<head>
<title>Da Search Results!</title>
</head>

<body>
<%
Dim intPageCount, strsearch, intRecordCount, intPage ,chkDel,intRecord, intStart, intFinish, SqlJunk, sqljunk2,iResultsNum,sula,catego,myChkBoxes,chkbox ,conn
catego= Request.Form("TypeSearch")
strsearch= Request.Form("DaInBox")
sula = Request.Form("DaInBox")
myPage = Request.Form("myPage")
<!-------------- ADDED THIS ----------------------------------------------->
chkDel = Request.Form("chkDel")
'<!-------------- ADDED THIS ----------------------------------------------->

IF myPage = "" THEN
myPage=1
END IF
IF Request.QueryString("mySQLStmt") <> "" THEN
'Need to Reconstruct the SQL Statement - Must have been submitted via a form
SqlJunk = Request.QueryString("mySQLStmt")
SqlJunk = replace( SqlJunk, "%25", "%")
SqlJunk = replace( SqlJunk, "!25!", "'")
SqlJunk = Replace( SqlJunk, "%20", " ")
IF myChkBoxes <> "" THEN
'SqlJunk = SqlJunk + " WHERE checks in("& myChkBoxes &")"
END IF
ELSE
'Constrcuting new SQL First time we have been here
'sqljunk2= "UPDATE Employees SET Employees.Phone = '0782911364'"

SqlJunk = "select * FROM books WHERE autono = "& chkDel

If Request.Form("TypeSearch") = "Title" Then
SqlJunk = SqlJunk & " WHERE title LIKE '%" & Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Author" Then
SqlJunk = SqlJunk & " WHERE Author LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Shelve" Then
SqlJunk = SqlJunk & " WHERE Shelve LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Phrases" Then
SqlJunk = SqlJunk & " WHERE Phrases LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Callno" Then
SqlJunk = SqlJunk & " WHERE Callno LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
'end if

'Set variables and open the record

Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.ConnectionString = "Driver={SQL Server};Server=kcc2;Database=slis;UID=sula;PWD=sul a;"
dbGlobalWeb.Open
rsGlobalWeb.CursorLocation = 3 'adUseClient
rsGlobalWeb.CursorType = 3 'adOpenStatic
'rsGlobalWeb.mode = 3 ' adModeReadWrite
rsGlobalWeb.ActiveConnection = dbGlobalWeb
'rsGlobalWeb.open "sendinquiry",dbGlobalWeb
'Response.write(Sqljunk)
' Response.end()
rsGlobalWeb.Open Sqljunk,dbGlobalWeb
If NOT rsGlobalWeb.eof THEN
'We must have found some records
g_search="obs found"
rsGlobalWeb.movefirst

' Sets the number of observations per page to a max of 10
rsGlobalWeb.pagesize=30
' Set the pageCount of records are we in ?
rsGlobalWeb.absolutepage=myPage
' Get the total Number of Pages based on our PagesSize
myPageCount = rsGlobalWeb.pagecount

WITH RESPONSE
iResultsNum = ((myPage - 1) * rsGlobalWeb.pagesize)
' Go throug and list all the records
.Write "<TABLE>" &vbCRLF
.Write " <TR>" &vbCRLF
'THIS IS NEW COLUMN
.Write " <TD valign=top><span class='style4'><strong>Tick.</strong></span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>No.</strong></span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>Title</strong> </span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>Call No</strong> </span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>Author</strong> </span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style4'><strong>Subject</strong> </span></TD>" &vbCRLF
.Write " <TD valign=top>&nbsp;</TD>" &vbCRLF
.Write " </TR>" &vbCRLF
DO WHILE NOT rsGlobalWeb.eof and numofobs < rsGlobalWeb.pagesize
.Write " <TR>" &vbCRLF
.Write " <TR>" &vbCRLF
'THIS IS NEW LINE
iResultsNum = iResultsNum + 1

.Write " <TD valign=top><span class='style5'><input type='checkbox' name='chkdel' value=' "& rsGlobalWeb("autono") &"'></span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& iResultsNum &"</span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& rsGlobalWeb("title") &"</span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& rsGlobalWeb("callno") &"</span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& rsGlobalWeb("author") &"</span></TD>" &vbCRLF
.Write " <TD valign=top><span class='style5'>"& rsGlobalWeb("category") &"</span></TD>" &vbCRLF
.Write " <TD valign=top>&nbsp;</TD>" &vbCRLF
.Write " </TR>" &vbCRLF
.Write " <TR><TD Colspan='3'> </TD></TR>"&vbCRLF
rsGlobalWeb.movenext

' Counts the number of observations in the current page
numofobs=numofobs+1
LOOP
rsGlobalWeb.Close
dbGlobalWeb.Close
.Write "</TABLE>" &vbCRLF &vbCRLF
.Write "<input type='submit' value='ShowChk' name='bSubmit'>" &vbCRLF
.Write "</TABLE>" &vbCRLF &vbCRLF
.Write "</FORM>"
END WITH 'Response

'Prepare the SQL Statement for HTML TRANSPORT
'Used for Like
myHTMLSQLJunk = replace(SqlJunk, "%", "%25")
'USed for Dates
'myHTMLSQLJunk = replace(myHTMLSQLJunk, "#", "SOMECODEHERE")
'used for text
myHTMLSQLJunk = replace(myHTMLSQLJunk, "'", "!25!")
'Used for Spaces
myHTMLSQLJunk = replace(myHTMLSQLJunk, " ", "%20")

With Response
.Write "<TABLE><TR><TD>"
IF int(myPage) > 1 THEN
.Write "<form method='post' action='"& request.servervariables("script_name")&"?mySQLStmt="& myHTMLSQLJunk &"'>" &vbCRLF
.Write "<input type='hidden' name='myPage' value='"& myPage - 1 &"'>" &vbCRLF
'.Write "<input type='hidden' name='mySQLJunk' value='"& myHTMLSQLJunk &"'>" &vbCRLF
.Write "<input type='hidden' name='DaInBox' value='"& DaInBox &"'>" &vbCRLF
.Write "<input type='submit' value='<'>" &vbCRLF
.Write "</form></TD><TD>" &vbCRLF &vbCRLF
END IF

.Write " <form method='post' action='"& request.servervariables("script_name")&"?mySQLStmt="& myHTMLSQLJunk &"'>" &vbCRLF
.Write " <input type ='hidden' name='DaInBox' value='"& strsearch &"'>" &vbCRLF
.Write " <select name='myPage' size='1' onchange='this.form.submit();' name='bSubmit'>" &vbCRLF

' Starts loop from one to the maximum number of pages
For counter=1 to myPageCount
' This will select the current page in the dropdown menu
if cint(myPage) = cint(counter) then
.Write "<Option Selected value="& counter &">Page "& counter &" of "& myPageCount &"</option>" &vbCRLF
ELSE
.Write "<Option value="& counter &">Page "& counter &" of "& myPageCount &"</option>" &vbCRLF
END IF
Next
.Write "</select>" &vbCRLF
.Write "</form></TD><TD>" &vbCRLF &vbCRLF

IF int(myPage) < int(myPageCount) THEN
.Write "<form method='post' action='"& request.servervariables("script_name")&"?mySQLStmt="& myHTMLSQLJunk &"'>" &vbCRLF
.Write "<input type='hidden' name='myPage' value='"& myPage + 1 &"'>" &vbCRLF
'.Write "<input type='hidden' name='mySQLJunk' value='"& myHTMLSQLJunk &"'>" &vbCRLF
.Write "<input type='hidden' name='DaInBox' value='"& DaInBox &"'>" &vbCRLF
.Write "<input type='submit' value='>'>" &vbCRLF
.Write "</form>" &vbCRLF
END IF
.Write "</TD></TR></TABLE>" &vbCRLF
'.Write "<P>"& numofobs &" Results displayed on this page For Category Search <br></P> " &vbCRLF
'.Write "<P>"& sula &" Is The Category search <br></P> " &vbCRLF
END WITH 'Response
End IF ' Not EOF

'We Did not Find Records and We actually tried looking for them
IF g_search <> "obs found" AND DaInBox <> "" THEN
response.write "Nothing reults found for your search on <i>"& DaInBox &"</i><br>"
END IF
END IF
%>
 
Sort by date Sort by votes
In amongst all the code you must have an error in the SQL query concatenation.

response.write the query to the browser so you can see where the error is



Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
551
GriffMG
GriffMG
BunBo900
  • Locked
  • Question
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Replies
1
Views
169
BunBo900
BunBo900
vamsikirank
  • Locked
  • Question
Fortify Scan For Classic Asp Application - Issue
Replies
2
Views
151
guitarzan
guitarzan
PSchubert
  • Locked
  • Question
Data in DB table cell not displaying in asp page table cell
Replies
4
Views
125
Ed Mozley
Ed Mozley
leifoet
  • Locked
  • Question
Form Method=Post =&gt; how to process options ?
Replies
0
Views
86
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top