(missing operator)????

[robbie59]

I keep getting this error when I submit any ideas on what is causing the error

Syntax error (missing operator) in query expression ''''WHERE Jobnumber = '2940'

Here is the update code:

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

If knowledge were power I would be a AAA battery!

 

[ralphtrent]

You have:
"''WHERE Jobnumber = '" & Replace commUpdate__varRecID, "'", "''") & ""

Try this:
"'WHERE Jobnumber = '" & Replace(commUpdate__varRecID, "'", "''") & "'"

also, for simplicity you might want to create variables for your replacements. It will make ready and debugging a lot easier.

Let me know if it helps.

 

[robbie59]

I tried your suggestions and Now I get this error

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

I can tell you That all three DB fields are number fields being updtaed by 2-Select menus and one of the fields being updated by a checkbox with a checked value of 7 would there be any reason this would be the problem







If knowledge were power I would be a AAA battery!

 

[robbie59]

Update : I told you wrong JOBNUMBER IS A AUTONUMBER FIELD

If knowledge were power I would be a AAA battery!

 

[Chopstik]

If JOBNUMBER is an autonumber field, then do not attempt to update this record. I believe this will throw an error and is likely the source of your problem.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[Chopstik]

To further clarify, autonumber fields mean that the system automatically generates a value for that field. The user should not attempt to do anything with that field.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[robbie59]

I am not trying to update the jobnumber field only trying to use it as the where function

If knowledge were power I would be a AAA battery!

 

[robbie59]

I am trying to use the code from this website "

http://www.drdev.net/article11.asp"

his db recid field is an autonumber field and he is using it as the where statement and it works just fine on his example I have been trying to get this to work for over a week now and can't seem to figure it out... All I have done is change the input names to match my menulist inputs... My Eyes are crossing Can someone please take a look and see if they can find my error: here is the full code to my page:


<%@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__SelectTranid, "'", "''") & "', 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(JobNumber){
var ThisID = "*" + (JobNumber) + "*"
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"><% 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="number" 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!