p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: RE: SQL update


Message #1 by "Drew, Ron" <RDrew@B...> on Mon, 10 Jun 2002 15:18:14 -0400
You are correct in your reply and similar to the reply I gave.  The only
additional comment I made was, make sure you rearrange the statement so
the checkbox if statement has a comma at the end or you will get a
syntax error

-----Original Message-----
From: dale Borchardt [mailto:dale@d...]
Sent: Monday, June 10, 2002 3:27 PM
To: ASP Databases
Subject: [asp_databases] RE: SQL update

> I assume that your database is storing StatusClosed as a yes/no field?

And
I assume that you are using a checkbox on your web page?  When you
submit
the form, if the checkbox is sent, then the value for that input is
sent.
So what you could do is make your checkbox value "True", and then change

the
code below to look like this:

	'Start building the SQL string
	sql6 =3D "UPDATE tblJobTicket SET tblJobTicket.Subject =3D '" &
Request.Form("Subject") & "', "
	sql6 =3D sql6 & " tblJobTicket.DeptCode =3D '" &
Request.Form("DeptCode") & "', "
	sql6 =3D sql6 & " tblJobTicket.AssignedTo =3D '" &
Request.Form("AssignedTo") & "', "
	if Len(Request.Form("StatusClosed")) > 0 Then
		sql6 =3D sql6 & " tblJobTicket.StatusClosed =3D " &
Request.Form("StatusClosed") & " "
	End If
	sql6 =3D sql6 & " WHERE tblJobTicket.ID =3D " &
Request.Form("JobTicNum") & ";"

Note that because the field in the database stores True/False or Yes/No,

you
do *NOT* surround the value with quotes.

Give this a try.
Regards,
Peter


> -----Original Message-----
> From: dale Borchardt [mailto:dale@d...]
> Sent: Monday, June 10, 2002 4:08 PM
> To: ASP Databases
> Subject: [asp_databases] SQL update
>
>
> I am haveing a problem with the below page. The fields will not be
> updated unless Status closed was checked. I guess this tells me that
> there is something wrong with the way it sees StatusClosed in its
> unchecked state. If I get rid of the check box the rest will
> update fine.
> I have had problems with check boxes before but this is the
> first time I
> used SQL Update with a checkbox. I am updateing an Access
> database. If I
> turn off On Error Resume Next I will get an error. on the
> check box. How
> do I handle this checkbox to not get this problem. I have tried a few
> ways of setting the on or off to variables but failed.
>
> <%
> on Error Resume Next	'if not set we get "Data mismatch in criteria
> expression." error because of the checkbox.
> 								=09
> 'Declare variables needed
> Dim strInsert, oConn6, sql6, strSubject2, oCmd6, adCmdText, oRS6a
>
> 'Set required variables
> adCmdText =3D 1
>
> 	'Start building the SQL string
> 	sql6 =3D "UPDATE tblJobTicket SET tblJobTicket.Subject =3D '" &
> Request.Form("Subject") & "', "
> 	sql6 =3D sql6 & " tblJobTicket.DeptCode =3D '" & Request.Form
> ("DeptCode") & "', "
> 	sql6 =3D sql6 & " tblJobTicket.AssignedTo =3D '" & Request.Form
> ("AssignedTo") & "', "
> 	sql6 =3D sql6 & " tblJobTicket.StatusClosed =3D '" & Request.Form
> ("StatusClosed") & "' "
> 	sql6 =3D sql6 & " WHERE tblJobTicket.ID =3D " & Request.Form
> ("JobTicNum") & ";"
>
> 	'Create and open the database object
> 	Set oConn6 =3D Server.CreateObject("ADODB.Connection")
> 	oConn6.Open "DSN=3DMaintReq"
>
> 	'Create the command object
> 	Set oCmd6 =3D Server.CreateObject("ADODB.Command")
>
> 	'Set the command object properties
> 	Set oCmd6.ActiveConnection =3D oConn6
> 	oCmd6.CommandText =3D sql6
> 	oCmd6.CommandType =3D adCmdText
>
> 	'Execute the command
> 	oCmd6.Execute
>
> '********** Open Recordset for new Journal Entry
> 'Open recordset for journal entry
> 'On Error Resume Next
> Set oRS6a =3D Server.CreateObject ("ADODB.Recordset")
> oRS6a.Open "tblWorkJournal", oConn6, adOpenKeyset, adLockOptimistic
>
> oRS6a.AddNew
> oRS6a ("JournalEntry") =3D Request.Form("JournalEntry")
> oRS6a ("Employee") =3D Request.Form("Employee")
> oRS6a ("ID") =3D Request.Form("JobTicNum")
> oRS6a ("EditDate") =3D Request.Form("EditDate")
> oRS6a.Update
> '*********** End of RS for Journal Entry
>
> 'Close and dereference database objects
> Set oCmd6 =3D Nothing
> oConn6.Close
> Set oConn6 =3D Nothing
>
> Response.Redirect("3CheckOpenCalls.asp")
> %>
>

Wow, That was fast service. Thank you very much for your help!!
Message #2 by "dale Borchardt" <dale@d...> on Mon, 10 Jun 2002 19:27:01
> I assume that your database is storing StatusClosed as a yes/no field?  
And
I assume that you are using a checkbox on your web page?  When you submit
the form, if the checkbox is sent, then the value for that input is sent.
So what you could do is make your checkbox value "True", and then change 
the
code below to look like this:

	'Start building the SQL string
	sql6 = "UPDATE tblJobTicket SET tblJobTicket.Subject = '" &
Request.Form("Subject") & "', "
	sql6 = sql6 & " tblJobTicket.DeptCode = '" &
Request.Form("DeptCode") & "', " 
	sql6 = sql6 & " tblJobTicket.AssignedTo = '" &
Request.Form("AssignedTo") & "', "
	if Len(Request.Form("StatusClosed")) > 0 Then
		sql6 = sql6 & " tblJobTicket.StatusClosed = " &
Request.Form("StatusClosed") & " "
	End If
	sql6 = sql6 & " WHERE tblJobTicket.ID = " &
Request.Form("JobTicNum") & ";"

Note that because the field in the database stores True/False or Yes/No, 
you
do *NOT* surround the value with quotes.

Give this a try.
Regards,
Peter


> -----Original Message-----
> From: dale Borchardt [mailto:dale@d...]
> Sent: Monday, June 10, 2002 4:08 PM
> To: ASP Databases
> Subject: [asp_databases] SQL update
> 
> 
> I am haveing a problem with the below page. The fields will not be 
> updated unless Status closed was checked. I guess this tells me that 
> there is something wrong with the way it sees StatusClosed in its 
> unchecked state. If I get rid of the check box the rest will 
> update fine. 
> I have had problems with check boxes before but this is the 
> first time I 
> used SQL Update with a checkbox. I am updateing an Access 
> database. If I 
> turn off On Error Resume Next I will get an error. on the 
> check box. How 
> do I handle this checkbox to not get this problem. I have tried a few 
> ways of setting the on or off to variables but failed.
> 
> <%
> on Error Resume Next	'if not set we get "Data mismatch in criteria 
> expression." error because of the checkbox.
> 									
> 'Declare variables needed
> Dim strInsert, oConn6, sql6, strSubject2, oCmd6, adCmdText, oRS6a
> 
> 'Set required variables
> adCmdText = 1
> 
> 	'Start building the SQL string
> 	sql6 = "UPDATE tblJobTicket SET tblJobTicket.Subject = '" & 
> Request.Form("Subject") & "', "
> 	sql6 = sql6 & " tblJobTicket.DeptCode = '" & Request.Form
> ("DeptCode") & "', " 
> 	sql6 = sql6 & " tblJobTicket.AssignedTo = '" & Request.Form
> ("AssignedTo") & "', "
> 	sql6 = sql6 & " tblJobTicket.StatusClosed = '" & Request.Form
> ("StatusClosed") & "' "
> 	sql6 = sql6 & " WHERE tblJobTicket.ID = " & Request.Form
> ("JobTicNum") & ";"
> 
> 	'Create and open the database object
> 	Set oConn6 = Server.CreateObject("ADODB.Connection")
> 	oConn6.Open "DSN=MaintReq"
> 
> 	'Create the command object
> 	Set oCmd6 = Server.CreateObject("ADODB.Command")
> 
> 	'Set the command object properties
> 	Set oCmd6.ActiveConnection = oConn6
> 	oCmd6.CommandText = sql6
> 	oCmd6.CommandType = adCmdText
> 
> 	'Execute the command
> 	oCmd6.Execute
> 
> '********** Open Recordset for new Journal Entry
> 'Open recordset for journal entry
> 'On Error Resume Next
> Set oRS6a = Server.CreateObject ("ADODB.Recordset")
> oRS6a.Open "tblWorkJournal", oConn6, adOpenKeyset, adLockOptimistic
> 
> oRS6a.AddNew
> oRS6a ("JournalEntry") = Request.Form("JournalEntry")
> oRS6a ("Employee") = Request.Form("Employee") 
> oRS6a ("ID") = Request.Form("JobTicNum")
> oRS6a ("EditDate") = Request.Form("EditDate")
> oRS6a.Update
> '*********** End of RS for Journal Entry
> 
> 'Close and dereference database objects
> Set oCmd6 = Nothing
> oConn6.Close
> Set oConn6 = Nothing
> 
> Response.Redirect("3CheckOpenCalls.asp")
> %>
> 

Wow, That was fast service. Thank you very much for your help!!
Message #3 by "Drew, Ron" <RDrew@B...> on Mon, 10 Jun 2002 11:14:33 -0400
You can rearrange your update as follows and test for not null.  Not
seeing the form, do you have multiple checkboxes with the same name?  If
so, this will cause problems.  Maybe show just the part of the form with
the checkbox(s).

	'Start building the SQL string
sql6 =3D "UPDATE tblJobTicket SET tblJobTicket.Subject =3D '" &
Request.Form("Subject") & "', "
sql6 =3D sql6 & " tblJobTicket.DeptCode =3D '" & 
Request.Form("DeptCode") &
"', "
If Request.Form("StatusClosed") <> "" then
  sql6 =3D sql6 & " tblJobTicket.StatusClosed =3D '" &
Request.Form("StatusClosed") & "', "
End If
sql6 =3D sql6 & " tblJobTicket.AssignedTo =3D '" &
Request.Form("AssignedTo") & "' "
sql6 =3D sql6 & " WHERE tblJobTicket.ID =3D " & 
Request.Form("JobTicNum") &
";"

-----Original Message-----
From: dale Borchardt [mailto:dale@d...]
Sent: Monday, June 10, 2002 12:08 PM
To: ASP Databases
Subject: [asp_databases] SQL update


I am haveing a problem with the below page. The fields will not be
updated unless Status closed was checked. I guess this tells me that
there is something wrong with the way it sees StatusClosed in its
unchecked state. If I get rid of the check box the rest will update
fine.
I have had problems with check boxes before but this is the first time I

used SQL Update with a checkbox. I am updateing an Access database. If I

turn off On Error Resume Next I will get an error. on the check box. How

do I handle this checkbox to not get this problem. I have tried a few
ways of setting the on or off to variables but failed.

<%
on Error Resume Next	'if not set we get "Data mismatch in criteria
expression." error because of the checkbox.
								=09
'Declare variables needed
Dim strInsert, oConn6, sql6, strSubject2, oCmd6, adCmdText, oRS6a

'Set required variables
adCmdText =3D 1

	'Start building the SQL string
sql6 =3D "UPDATE tblJobTicket SET tblJobTicket.Subject =3D '" &
Request.Form("Subject") & "', " sql6 =3D sql6 & " tblJobTicket.DeptCode 
=3D
'" & Request.Form("DeptCode") & "', "
sql6 =3D sql6 & " tblJobTicket.AssignedTo =3D '" &
Request.Form("AssignedTo") & "', " sql6 =3D sql6 & "
tblJobTicket.StatusClosed =3D '" & Request.Form("StatusClosed") & "' "
sql6 =3D sql6 & " WHERE tblJobTicket.ID =3D " & 
Request.Form("JobTicNum") &
";"

	'Create and open the database object
	Set oConn6 =3D Server.CreateObject("ADODB.Connection")
	oConn6.Open "DSN=3DMaintReq"

	'Create the command object
	Set oCmd6 =3D Server.CreateObject("ADODB.Command")

	'Set the command object properties
	Set oCmd6.ActiveConnection =3D oConn6
	oCmd6.CommandText =3D sql6
	oCmd6.CommandType =3D adCmdText

	'Execute the command
	oCmd6.Execute

'********** Open Recordset for new Journal Entry
'Open recordset for journal entry
'On Error Resume Next
Set oRS6a =3D Server.CreateObject ("ADODB.Recordset")
oRS6a.Open "tblWorkJournal", oConn6, adOpenKeyset, adLockOptimistic

oRS6a.AddNew
oRS6a ("JournalEntry") =3D Request.Form("JournalEntry")
oRS6a ("Employee") =3D Request.Form("Employee")
oRS6a ("ID") =3D Request.Form("JobTicNum")
oRS6a ("EditDate") =3D Request.Form("EditDate")
oRS6a.Update
'*********** End of RS for Journal Entry

'Close and dereference database objects
Set oCmd6 =3D Nothing
oConn6.Close
Set oConn6 =3D Nothing

Response.Redirect("3CheckOpenCalls.asp")
%>
Message #4 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 10 Jun 2002 11:22:57 -0400
I assume that your database is storing StatusClosed as a yes/no field?  And
I assume that you are using a checkbox on your web page?  When you submit
the form, if the checkbox is sent, then the value for that input is sent.
So what you could do is make your checkbox value "True", and then change the
code below to look like this:

	'Start building the SQL string
	sql6 = "UPDATE tblJobTicket SET tblJobTicket.Subject = '" &
Request.Form("Subject") & "', "
	sql6 = sql6 & " tblJobTicket.DeptCode = '" &
Request.Form("DeptCode") & "', " 
	sql6 = sql6 & " tblJobTicket.AssignedTo = '" &
Request.Form("AssignedTo") & "', "
	if Len(Request.Form("StatusClosed")) > 0 Then
		sql6 = sql6 & " tblJobTicket.StatusClosed = " &
Request.Form("StatusClosed") & " "
	End If
	sql6 = sql6 & " WHERE tblJobTicket.ID = " &
Request.Form("JobTicNum") & ";"

Note that because the field in the database stores True/False or Yes/No, you
do *NOT* surround the value with quotes.

Give this a try.
Regards,
Peter


> -----Original Message-----
> From: dale Borchardt [mailto:dale@d...]
> Sent: Monday, June 10, 2002 4:08 PM
> To: ASP Databases
> Subject: [asp_databases] SQL update
> 
> 
> I am haveing a problem with the below page. The fields will not be 
> updated unless Status closed was checked. I guess this tells me that 
> there is something wrong with the way it sees StatusClosed in its 
> unchecked state. If I get rid of the check box the rest will 
> update fine. 
> I have had problems with check boxes before but this is the 
> first time I 
> used SQL Update with a checkbox. I am updateing an Access 
> database. If I 
> turn off On Error Resume Next I will get an error. on the 
> check box. How 
> do I handle this checkbox to not get this problem. I have tried a few 
> ways of setting the on or off to variables but failed.
> 
> <%
> on Error Resume Next	'if not set we get "Data mismatch in criteria 
> expression." error because of the checkbox.
> 									
> 'Declare variables needed
> Dim strInsert, oConn6, sql6, strSubject2, oCmd6, adCmdText, oRS6a
> 
> 'Set required variables
> adCmdText = 1
> 
> 	'Start building the SQL string
> 	sql6 = "UPDATE tblJobTicket SET tblJobTicket.Subject = '" & 
> Request.Form("Subject") & "', "
> 	sql6 = sql6 & " tblJobTicket.DeptCode = '" & Request.Form
> ("DeptCode") & "', " 
> 	sql6 = sql6 & " tblJobTicket.AssignedTo = '" & Request.Form
> ("AssignedTo") & "', "
> 	sql6 = sql6 & " tblJobTicket.StatusClosed = '" & Request.Form
> ("StatusClosed") & "' "
> 	sql6 = sql6 & " WHERE tblJobTicket.ID = " & Request.Form
> ("JobTicNum") & ";"
> 
> 	'Create and open the database object
> 	Set oConn6 = Server.CreateObject("ADODB.Connection")
> 	oConn6.Open "DSN=MaintReq"
> 
> 	'Create the command object
> 	Set oCmd6 = Server.CreateObject("ADODB.Command")
> 
> 	'Set the command object properties
> 	Set oCmd6.ActiveConnection = oConn6
> 	oCmd6.CommandText = sql6
> 	oCmd6.CommandType = adCmdText
> 
> 	'Execute the command
> 	oCmd6.Execute
> 
> '********** Open Recordset for new Journal Entry
> 'Open recordset for journal entry
> 'On Error Resume Next
> Set oRS6a = Server.CreateObject ("ADODB.Recordset")
> oRS6a.Open "tblWorkJournal", oConn6, adOpenKeyset, adLockOptimistic
> 
> oRS6a.AddNew
> oRS6a ("JournalEntry") = Request.Form("JournalEntry")
> oRS6a ("Employee") = Request.Form("Employee") 
> oRS6a ("ID") = Request.Form("JobTicNum")
> oRS6a ("EditDate") = Request.Form("EditDate")
> oRS6a.Update
> '*********** End of RS for Journal Entry
> 
> 'Close and dereference database objects
> Set oCmd6 = Nothing
> oConn6.Close
> Set oConn6 = Nothing
> 
> Response.Redirect("3CheckOpenCalls.asp")
> %>
> 
Message #5 by "dale Borchardt" <dale@d...> on Mon, 10 Jun 2002 16:07:55
I am haveing a problem with the below page. The fields will not be 
updated unless Status closed was checked. I guess this tells me that 
there is something wrong with the way it sees StatusClosed in its 
unchecked state. If I get rid of the check box the rest will update fine. 
I have had problems with check boxes before but this is the first time I 
used SQL Update with a checkbox. I am updateing an Access database. If I 
turn off On Error Resume Next I will get an error. on the check box. How 
do I handle this checkbox to not get this problem. I have tried a few 
ways of setting the on or off to variables but failed.

<%
on Error Resume Next	'if not set we get "Data mismatch in criteria 
expression." error because of the checkbox.
									
'Declare variables needed
Dim strInsert, oConn6, sql6, strSubject2, oCmd6, adCmdText, oRS6a

'Set required variables
adCmdText = 1

	'Start building the SQL string
	sql6 = "UPDATE tblJobTicket SET tblJobTicket.Subject = '" & 
Request.Form("Subject") & "', "
	sql6 = sql6 & " tblJobTicket.DeptCode = '" & Request.Form
("DeptCode") & "', " 
	sql6 = sql6 & " tblJobTicket.AssignedTo = '" & Request.Form
("AssignedTo") & "', "
	sql6 = sql6 & " tblJobTicket.StatusClosed = '" & Request.Form
("StatusClosed") & "' "
	sql6 = sql6 & " WHERE tblJobTicket.ID = " & Request.Form
("JobTicNum") & ";"

	'Create and open the database object
	Set oConn6 = Server.CreateObject("ADODB.Connection")
	oConn6.Open "DSN=MaintReq"

	'Create the command object
	Set oCmd6 = Server.CreateObject("ADODB.Command")

	'Set the command object properties
	Set oCmd6.ActiveConnection = oConn6
	oCmd6.CommandText = sql6
	oCmd6.CommandType = adCmdText

	'Execute the command
	oCmd6.Execute

'********** Open Recordset for new Journal Entry
'Open recordset for journal entry
'On Error Resume Next
Set oRS6a = Server.CreateObject ("ADODB.Recordset")
oRS6a.Open "tblWorkJournal", oConn6, adOpenKeyset, adLockOptimistic

oRS6a.AddNew
oRS6a ("JournalEntry") = Request.Form("JournalEntry")
oRS6a ("Employee") = Request.Form("Employee") 
oRS6a ("ID") = Request.Form("JobTicNum")
oRS6a ("EditDate") = Request.Form("EditDate")
oRS6a.Update
'*********** End of RS for Journal Entry

'Close and dereference database objects
Set oCmd6 = Nothing
oConn6.Close
Set oConn6 = Nothing

Response.Redirect("3CheckOpenCalls.asp")
%>

  Return to Index