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 gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Keep Getting Syntax Error! HELP!

Status
Not open for further replies.
robbie59

robbie59

Technical User
May 9, 2001
36
US
Syntax error in string in query expression '' WHERE JobNumber = 2940'.
manage1.asp, line 28


I keep Getting this above error when I submit any Ideas on what I am doing wrong Below is actual asp code

<%@LANGUAGE="VBSCRIPT"%>


<!--#include file="Connections/RS1.asp" -->

<%
If Request("Submit") <> "" Then
varRecIDs = Replace(Request("hidRecIDs"), "*", "") ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
varRecArray = Split(varRecIDs, ", ") ' Create an array, wich will contain just the IDs of the records we need to update
For i = 0 to Ubound(varRecArray) ' Loop trough the array
%>
<%
if(Request("SelectAssignTranid" & varRecArray(i)) <> "") then commUpdate__SelectAssignTranid = Request("SelectAssignTranid" & varRecArray(i))

if(Request("checkboxIPriorityLevel" & varRecArray(i)) <> "") then commUpdate__checkboxIPriorityLevel = Request("checkboxIPriorityLevel" & varRecArray(i))

if(Request("SelectState" & varRecArray(i)) <> "") then commUpdate__SelectState = Request("SelectState" & varRecArray(i))

if(Request("hidRecID" & varRecArray(i)) <> "") then commUpdate__varRecID = Request("hidRecID" & varRecArray(i))
%>
<%
set commUpdate = Server.CreateObject("ADODB.Command")
commUpdate.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\program files\voiceware server\voicedoc.mdb"
commUpdate.CommandText = "UPDATE Dictations SET AssignTranID = '" + Replace(commUpdate__SelectAssignTranid, "'", "''") + "', iPriorityLevel = " + Replace(commUpdate__checkboxIPriorityLevel, "'", "''") + "' WHERE JobNumber = " + Replace(commUpdate__varRecID, "'", "''") + ""
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
%>
<%
Next
Response.Redirect "manage1.asp"
End If
%>


<%
Dim Recordset1__varradiobutton
Recordset1__varradiobutton = "x"
If (request.form("radiobutton") <> "") Then
Recordset1__varradiobutton = request.form("radiobutton")
End If
%>
<%
Dim Recordset1__vartranny
Recordset1__vartranny = "0"
If (request.form("tranny") <> "") Then
Recordset1__vartranny = request.form("tranny")
End If
%>
<%
Dim Recordset1__Vardictator
Recordset1__Vardictator = "%"
If (request.form("dictatorselect") <> "") Then
Recordset1__Vardictator = request.form("dictatorselect")
End If
%>
<%
If (request.form("assignment") <> "0") Then
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_RS1_STRING
Recordset1.Source = "SELECT DISTINCT patientid,state, JobNumber, doctorid, SubjectName, DictatorName, AssignTranID, JobLength, iPriorityLevel, iPriorityLevelOrg, dtComplete FROM Dictations WHERE State like '" + Replace(Recordset1__varradiobutton, "'", "''") + "' and assigntranid like '" + Replace(Recordset1__vartranny, "'", "''") + "' and doctorid like '" + Replace(Recordset1__Vardictator, "'", "''") + "' ORDER BY Jobnumber ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
else



Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_RS1_STRING
Recordset1.Source = "SELECT DISTINCT patientid,state, JobNumber, doctorid, SubjectName, DictatorName, AssignTranID, JobLength, iPriorityLevel, iPriorityLevelOrg, dtComplete FROM Dictations WHERE State =10 and assigntranid =0 and doctorid like '" + Replace(Recordset1__Vardictator, "'", "''") + "' ORDER BY Jobnumber ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
end if

%>

<%

%>
<%
Dim RSTranscriptionist
Dim RSTranscriptionist_numRows

Set RSTranscriptionist = Server.CreateObject("ADODB.Recordset")
RSTranscriptionist.ActiveConnection = MM_RS1_STRING
RSTranscriptionist.Source = "SELECT distinct Name, lRecordID FROM Users WHERE Type=0"
RSTranscriptionist.CursorType = 0
RSTranscriptionist.CursorLocation = 2
RSTranscriptionist.LockType = 1
RSTranscriptionist.Open()

RSTranscriptionist_numRows = 0
%>
<%
Dim RStranscriptionist1
Dim RStranscriptionist1_numRows

Set RStranscriptionist1 = Server.CreateObject("ADODB.Recordset")
RStranscriptionist1.ActiveConnection = MM_RS1_STRING
RStranscriptionist1.Source = "SELECT Jobnumber,state,AssignTranID, lRecordID, Name FROM Dictations, Users WHERE AssignTranID = Lrecordid"
RStranscriptionist1.CursorType = 0
RStranscriptionist1.CursorLocation = 2
RStranscriptionist1.LockType = 1
RStranscriptionist1.Open()

RStranscriptionist1_numRows = 0
%>
<%
Dim RSDictators
Dim RSDictators_numRows

Set RSDictators = Server.CreateObject("ADODB.Recordset")
RSDictators.ActiveConnection = MM_RS1_STRING
RSDictators.Source = "SELECT lRecordID, Name, Type FROM Users where type=1"
RSDictators.CursorType = 0
RSDictators.CursorLocation = 2
RSDictators.LockType = 1
RSDictators.Open()

RSDictators_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--
// When the value in a textfield is changed, notice the onChange="RecUpdate('<%= varRecID %>')"
// on each of the textfields, the value of the Record ID associated with that field
// is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
// This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(RecID){
var ThisID = "*" + (RecID) + "*"
if (document.UPFORM.hidRecIDs.value == ""){ // If the hidden field is empty
document.UPFORM.hidRecIDs.value = (ThisID) // Store the value in the hidden field (hidRecIDs) as it is.
}
if (document.UPFORM.hidRecIDs.value != ""){ // If the hidden field isn't empty
var str = document.UPFORM.hidRecIDs.value; // Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID); // Search str to see if this RecID is allready in it.
if (pos == -1) { // If the position returned is -1 it isn't allredy in there,
document.UPFORM.hidRecIDs.value = document.UPFORM.hidRecIDs.value + ", " + (ThisID)
} // so add ", " and this ID to what is already in hidRecIDs
} // to create a list like this *2*, *5*, *8* etc.
}
//-->
</script>
<style type="text/css">
option1 {background : #FFFFFF; color : #660000; font-family : verdana,arial,helvetica; font-size : 10pt; font-weight : strong}</style>
</head>
<body>
<form name="form1" method="post" action="manage1.asp">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr bgcolor="#0099FF">
<td width="26%"> <div align="left"><strong><font color="#FFFFFF">
<input type="radio" name="assignment" value="0">
Unassigned </font></strong></div></td>
<td width="27%"> <div align="left"><strong><font color="#FFFFFF">
<input type="radio" name="radiobutton" value="10">
Awaiting Transcription </font></strong></div></td>
<td width="32%"> <div align="left"><strong><font color="#FFFFFF">
<input type="radio" name="radiobutton" value="14">
At Transcriptionist</font></strong></div></td>
<td width="15%"> <div align="left"><strong><font color="#FFFFFF">
<input type="radio" name="radiobutton" value="%">
All Jobs</font></strong></div></td>
</tr>
<tr bgcolor="#FFFF00">
<td>&nbsp; </td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr bgcolor="#FFFF00">
<td><div align="center"></div></td>
<td><div align="center"></div></td>
<td><div align="center"></div></td>
<td><div align="center"></div></td>
</tr>
<tr bgcolor="#0099FF">
<td> <div align="left">
<select name="tranny" id="tranny">
<option value="%">All</option>
<%
While (NOT RSTranscriptionist.EOF)
%>
<option value="<%=(RSTranscriptionist.Fields.Item("lRecordID").Value)%>"><%=(RSTranscriptionist.Fields.Item("Name").Value)%></option>
<%
RSTranscriptionist.MoveNext()
Wend
If (RSTranscriptionist.CursorType > 0) Then
RSTranscriptionist.MoveFirst
Else
RSTranscriptionist.Requery
End If
%>
</select>
<font color="#FFFFFF"><strong>Transcriptionist </strong></font></div></td>
<td> <div align="left">
<select name="dictatorselect" id="dictatorselect">
<option value="%">All</option>
<%
While (NOT RSDictators.EOF)
%>
<option value="<%=(RSDictators.Fields.Item("lRecordID").Value)%>"><%=(RSDictators.Fields.Item("Name").Value)%></option>
<%
RSDictators.MoveNext()
Wend
If (RSDictators.CursorType > 0) Then
RSDictators.MoveFirst
Else
RSDictators.Requery
End If
%>
</select>
<font color="#FFFFFF"><strong>Dictator</strong></font> </div></td>
<td> <div align="left">
<input type="submit" name="Submit2" value="Submit">
</div></td>
<td><div align="right"> </div></td>
</tr>
</table>
</form>
<% If Not Recordset1.EOF Or Not Recordset1.BOF Then %>
<form action="manage1.asp" method="post" name="UPFORM" id="UPFORM" >
<p>&nbsp;</p>
<table width="100%" border="0" cellpadding="0" cellspacing="0" bordercolor="#000000">
<tr bgcolor="#0066FF">
<td><div align="center"><font color="#FFFFFF"><strong>JobNumber</strong></font></div></td>
<td><div align="center"><font color="#FFFFFF"><strong>Dictator</strong></font></div></td>
<td><div align="center"><font color="#FFFFFF"><strong>Status</strong></font></div></td>
<td><div align="center"><font color="#FFFFFF"><strong>Date Uploaded</strong></font></div></td>
<td><div align="center"><font color="#FFFFFF"><strong>Job Length</strong></font></div></td>
<td><div align="center"><font color="#FFFFFF"><strong>Voicefile</strong></font></div></td>
<td><div align="center"><font color="#FFFFFF"><strong><font size="2">Assignment/Re-Assign</font></strong></font></div></td>
<td><div align="center"><font color="#FFFFFF"><strong>Stat</strong></font></div></td>
<td><div align="center"><font color="#FFFFFF"><strong>Force State</strong></font></div></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>

<%
if intRowColor = 0 Then
Response.Write "<TR bgcolor=""#FFFF99"">"
intRowColor = 1
Else
Response.Write "<TR bgcolor=""#99CCFF"">"
intRowColor = 0
End if
%>
<td> <p align="center"><font size="1" face="Arial, Helvetica, sans-serif"><% dim varRecID
varRecID = Recordset1("Jobnumber")
%>
<input name="hidRecID<%= varRecID %>" type="hidden" value="<%= varRecID %>" size="5">
</font> <%=(Recordset1.Fields.Item("JobNumber").Value)%> </p></td>
<td><div align="center"><font color="#000000" size="2"><%=(Recordset1.Fields.Item("DictatorName").Value)%> </font></div></td>
<td> <div align="center"> <font color="#000000">
<% Select Case cInt(Recordset1("state"))

Case 10:
response.write "<Blink><FONT COLOR='#000000' SIZE='2'>Awaiting Transcriptionist</FONT></Blink>"
Case 14:
response.write "<FONT COLOR='#000000' SIZE='2'>At Transcriptionist</FONT>"
Case 15:
response.write "<FONT COLOR='#000000' SIZE='2'>Awaiting QA</FONT>"
Case 16:
response.write "<FONT COLOR='#000000' SIZE='2'>At QA</FONT>"
Case 18:
response.write "<FONT COLOR='#000000' SIZE='2'>Transcribed</FONT>"
Case Else
response.write " "
End Select %>
</font> </div></td>
<td> <div align="center"><font size="2"><%=(Recordset1.Fields.Item("dtComplete").Value)%></font> </div></td>
<td> <div align="center"><font size="2">
<% Select Case cInt(Recordset1("Joblength"))
Case 0:
response.write "Unknown"
Case else
response.write Left((Recordset1.Fields.Item("JobLength").Value)/60,4)

End Select
%>
</font> </div>
<td> <div align="center">
<option value='" &_
RSTranscriptionist.Fields.Item("lRecordID").Value &_
"'></option>
<option value='" &_
RSTranscriptionist.Fields.Item("lRecordID").Value &_
"'><font size="2">
<%
strName = (Recordset1.Fields.Item("JobNumber").Value)
intZeroes = 8-Len(strName)

For i = 1 To intZeroes
strName = "0" & strName
Next

Response.Write "<A HREF='./Voicefiles/" & strName & ".cmp'><img src=""images/Dot.gif"" height=""17""></A>"
%>
</font> </div></td>
<td bgcolor="#33CCFF"><div align="center">
<select name="SelectAssignTranid" class="option1" id="SelectAssignTranid" style="{background : #00FF66; color : #000000; font-family : verdana,arial,helvetica; font-size : 10pt; font-weight : 3000}&lt;/style&gt;" onChange="RecUpdate('<%= varRecID %>')" <%
if recordset1.fields.item("state") = "10" then
response.write("")
else response.write("disabled=disabled")
end if
%> font="strong">
<option value="0" <%If (Not isNull((Recordset1.Fields.Item("AssignTranID").Value))) Then If ("0" = CStr((Recordset1.Fields.Item("AssignTranID").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Unassigned</option>
<%
While (NOT RSTranscriptionist.EOF)
%>
<option value="<%=(RSTranscriptionist.Fields.Item("lRecordID").Value)%>" <%If (Not isNull((Recordset1.Fields.Item("AssignTranID").Value))) Then If (CStr(RSTranscriptionist.Fields.Item("lRecordID").Value) = CStr((Recordset1.Fields.Item("AssignTranID").Value))) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(RSTranscriptionist.Fields.Item("Name").Value)%></option>
<%
RSTranscriptionist.MoveNext()
Wend
If (RSTranscriptionist.CursorType > 0) Then
RSTranscriptionist.MoveFirst
Else
RSTranscriptionist.Requery
End If
%>
</select>
</div></td>
<td bgcolor="#33CCFF"><div align="center">
<input <%If (CStr((Recordset1.Fields.Item("iPriorityLevel").Value)) = CStr("7")) Then Response.Write("checked") : Response.Write("")%> type="checkbox" name="checkboxIPriorityLevel" id="checkboxIPriorityLevel" <%
if recordset1.fields.item("state") = "10" then
response.write("")
else response.write("disabled=disabled")
end if
%>onChange="RecUpdate('<%= varRecID %>')" value="7">
</div></td>
<td bgcolor="#33CCFF"> <div align="center">
<select name="SelectState" onChange="RecUpdate('<%= varRecID %>')" class="Class2" id="Force_<%=Temp_id%>" style="{background : #FF0000; color : #FFFFFF; font-family : verdana,arial,helvetica; font-size : 10pt; font-weight : 3000}&lt;/style&gt;">
<option value="10" <%If (Not isNull((Recordset1.Fields.Item("state").Value))) Then If ("10" = CStr((Recordset1.Fields.Item("state").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Awaiting
Transcription</option>
<option value="18" <%If (Not isNull((Recordset1.Fields.Item("state").Value))) Then If ("18" = CStr((Recordset1.Fields.Item("state").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Transcribed</option>
<option value="14" <%If (Not isNull((Recordset1.Fields.Item("state").Value))) Then If ("14" = CStr((Recordset1.Fields.Item("state").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>At
Transcriptionist</option>
<option value="15" <%If (Not isNull((Recordset1.Fields.Item("state").Value))) Then If ("15" = CStr((Recordset1.Fields.Item("state").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Awaiting
QA</option>
<option value="16" <%If (Not isNull((Recordset1.Fields.Item("state").Value))) Then If ("16" = CStr((Recordset1.Fields.Item("state").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>At
QA</option>
</select>
</div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>

<tr bgcolor="#0066FF">
<td colspan="9" bordercolor="#FFFFFF" bgcolor="#FFFFFF"> <div align="right" >
<input name="hidRecIDs" type="text" size="40">
</div>
<div align="right">
<input type="submit" name="Submit" value="Submit">
</div></td>
</tr>
</table>
<% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
<p>&nbsp; </p>
</form>


<% If Recordset1.EOF And Recordset1.BOF Then %>
<p>Sorry No Records </p>
<% End If ' end Recordset1.EOF And Recordset1.BOF %>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
RSTranscriptionist.Close()
Set RSTranscriptionist = Nothing
%>
<%
RStranscriptionist1.Close()
Set RStranscriptionist1 = Nothing
%>
<%
RSDictators.Close()
Set RSDictators = Nothing
%>


If knowledge were power I would be a AAA battery!
 
Sort by date Sort by votes
it looks like you might have a missing quote in your SQL statement for your iPriorityLevel field value...
Code: 
commUpdate.CommandText = "UPDATE Dictations SET AssignTranID = '" + Replace(commUpdate__SelectAssignTranid, "'", "''") + "', iPriorityLevel = [COLOR=red]'[/color]" + Replace(commUpdate__checkboxIPriorityLevel, "'", "''") + "' WHERE JobNumber = " + Replace(commUpdate__varRecID, "'", "''") + ""

Tony
reddot.gif WIDTH=500 HEIGHT=2 VSPACE=3

 
Upvote 0 Downvote
Thanks for the response:

Now I get this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

manage1.asp, line 28



If knowledge were power I would be a AAA battery!
 
Upvote 0 Downvote
what datatype is iPriorityLevel. If it is a number then you wont need the quotes around the value in the SQL statement.

Simonchristieis
Line 28 is the top instance of commUpdate.Execute() by the looks of things


Tony
reddot.gif WIDTH=500 HEIGHT=2 VSPACE=3

 
Upvote 0 Downvote
The data type is Number for iPriorityLevel the checkbox is supposed to change it to 7 if checked it is also intially unchecked if current value of iprioritylevel is other than 7

If knowledge were power I would be a AAA battery!
 
Upvote 0 Downvote
I spy with my little eye ....


an inverted comma missing

iPriorityLevel = '" + Replace(commUpdate__checkboxIPriorityLevel, "'", "''") + "'
 
Upvote 0 Downvote
faq333-4896 in short

___________________________________________________________________

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page
 
Upvote 0 Downvote
[lol] if you're in front of a PC you are home simonchristieis

___________________________________________________________________

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page
 
Upvote 0 Downvote
I removed quotes but it did not help

If knowledge were power I would be a AAA battery!
 
Upvote 0 Downvote
Would the fact that it is a checkbox be the problem should it be some sort of a different update statement for a checkbox

If knowledge were power I would be a AAA battery!
 
Upvote 0 Downvote
Any Other Ideas on this????

If knowledge were power I would be a AAA battery!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
581
Krus1972
Krus1972
leifoet
  • Locked
  • Question
Using date picker gives an error message, only at application start
Replies
1
Views
377
MarkSweetland
MarkSweetland
RonMc
  • Locked
  • Question
classic asp - getting dropdown selection and reposting - help
Replies
1
Views
296
ChrisHirst
ChrisHirst
jd.ford1
  • Locked
  • Question
Need help with syntax
Replies
10
Views
225
jd.ford1
jd.ford1
chikwendu
  • Locked
  • Question
OraOLEDB error '80040e4b'
Replies
1
Views
541
xwb
xwb

Part and Inventory Search

Sponsor

Back
Top