Im trying to updated some old ASP pages with some new .net pages and cant seem to get this update statement to work. I need to update the current ASP code below and then once thats working im going to use that same statement in a procedure for all our new
.Net pages..
So can anyone see what im doing wrong by inserting the 2nd column to the update statement?
strSqual = "update tas set date_completed='" & TransDate &
", trans_id=" & InsertedTransID &
"' where date_completed is null and sercontract_id="& strContractID &
" and DATEDIFF(dd,task_due_date,'" & strProcDate &
"')<=14"
I am trying to add an extra column to the update, but not sure if the syntax is correct.
", trans_id=" & InsertedTransID &
"'
Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
---------------------
Mark as Answered if it helped
strSqual = "update tas set date_completed='" & TransDate & "', trans_id='" & InsertedTransID & "' where date_completed is null and sercontract_id='"& strContractID & "' and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"
Just curious, because before and after the changes there were no errors, but the update was happening, just never updating that 2nd column.. and at this point still not updating that column :(
I have to be missing something.
Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
---------------------
Mark as Answered if it helped
I copied the statement from the ASP page code to SQL and inserted the values that are being used on the page and the update works on the SQL side.. so i know that its suppose to work. But for whatever reason the page code is not working.
Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
---------------------
Mark as Answered if it helped
Maybe seeing all the code that im having issues with will help someone see what is wrong. I can execute each of the selects and inserts and updates on the SQL side with no issues..
But for some reason it seems that only the first update in each condition is executed, but not the ones after. Notice that there are 3 updates per if condition.. the first update in each of the conditions is always executed based on the variables it looks
at, but the other 2 updates within the conditions dont fire..
if valueTwo = "13" or valueTwo = "14" then 'check transactions table, find all 11/12 who has same system_id and trans_response_date is empty.
strSqual = "update trans set trans_response_date='" & TransDate & "' " & strTechSQL & " where (trans_response_date is null or trans_response_date='') and system_id='"&strSystemID&"' and (trans_type_name='11' or trans_type_name='12') "
getStaticRecordSet(strSqual)
'check tasks table. see if there is a task(same contract_id) falls in 14 days window.
strSqual = "update task set date_completed='" & TransDate & ", trans_id='" & InsertedTransID & "' where date_completed is null and service_contract_id="& strContractID & " and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"
'flag this 13/14 will not be displayed on page,set alarm_show_ind=False
strSqual = "update trans set show_ind='F' " & strTechSQL & " where trans_id="&InsertedTransID
getStaticRecordSet(strSqual)
elseif valueTwo = "23" or valueTwo = "24" then 'check transactions table, find all 11/12 who has same system_id and trans_response_date is empty.
strSqual = "update trans set trans_response_date='" & TransDate & "' " & strTechSQL & " where (trans_response_date is null or trans_response_date='') and system_id='"&strSystemID&"' and (trans_type_name='21' or trans_type_name='22') "
getStaticRecordSet(strSqual)
'check tasks table. see if there is a task(same contract_id) falls in 14 days window.
strSqual = "update task set date_completed='" & TransDate & ", trans_id='" & InsertedTransID & "' where date_completed is null and service_contract_id="& strContractID & " and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"
'flag this 23/24 will not be displayed on page,set alarm_show_ind=False
strSqual = "update trans set show_ind='F' " & strTechSQL & " where trans_id="&InsertedTransID
getStaticRecordSet(strSqual)
Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
---------------------
Mark as Answered if it helped
Can anyone see anything wrong with what i have posted? Im not great at VB, but need to get this code working as expected while we work on newer C# versions of the pages as we develop and recreate some of the pages.
Thanks.
Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
---------------------
Mark as Answered if it helped
Can anyone see anything wrong with what i have posted?
Can you debug the application and post the value from the 'Immdeate Windows' (within VS.NET) for the value
strSqual
you can type ?strSqual in the Immdeate Window and press <Enter>
Also as recommended before, comparing integer values as strings (i.e. "14") uses implicit conversions and it would be cleaner and less error proned to compare integer types to integer values.
cubangt
Contributor
3052 Points
2402 Posts
Whats wrong with this statement
Aug 19, 2010 08:05 PM|LINK
Im trying to updated some old ASP pages with some new .net pages and cant seem to get this update statement to work. I need to update the current ASP code below and then once thats working im going to use that same statement in a procedure for all our new .Net pages..
So can anyone see what im doing wrong by inserting the 2nd column to the update statement?
strSqual = "update tas set date_completed='" & TransDate & ", trans_id=" & InsertedTransID & "' where date_completed is null and sercontract_id="& strContractID & " and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"
I am trying to add an extra column to the update, but not sure if the syntax is correct. ", trans_id=" & InsertedTransID & "'
---------------------
Mark as Answered if it helped
chapmanjw
Participant
904 Points
182 Posts
Re: Whats wrong with this statement
Aug 19, 2010 08:13 PM|LINK
You are missing a single apostrophe after trans_id=:
trans_id=" & InsertedTransID & "'
trans_id='" & InsertedTransID & "'
John Chapman
-----------------------------
John Chapman's Blog
hemamalinigr
Participant
1310 Points
345 Posts
Re: Whats wrong with this statement
Aug 19, 2010 08:20 PM|LINK
try this
strSqual = "update tas set date_completed='" & TransDate & "', trans_id='" & InsertedTransID & "' where date_completed is null and sercontract_id='"& strContractID & "' and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"
cubangt
Contributor
3052 Points
2402 Posts
Re: Whats wrong with this statement
Aug 19, 2010 08:21 PM|LINK
thats all? man that was simple, but let me ask.. would the whole statement fail because of that or just that column?
I ask, because the updates were working, just not updating that column..
---------------------
Mark as Answered if it helped
hemamalinigr
Participant
1310 Points
345 Posts
Re: Whats wrong with this statement
Aug 19, 2010 08:25 PM|LINK
i think if the columns datatype is integer and if you are trying to pass it as a string
i.e with single quotes then sql will throw exception
if its a varchar then it wont be a problem
cubangt
Contributor
3052 Points
2402 Posts
Re: Whats wrong with this statement
Aug 19, 2010 08:42 PM|LINK
Just curious, because before and after the changes there were no errors, but the update was happening, just never updating that 2nd column.. and at this point still not updating that column :(
I have to be missing something.
---------------------
Mark as Answered if it helped
cubangt
Contributor
3052 Points
2402 Posts
Re: Whats wrong with this statement
Aug 19, 2010 09:57 PM|LINK
I copied the statement from the ASP page code to SQL and inserted the values that are being used on the page and the update works on the SQL side.. so i know that its suppose to work. But for whatever reason the page code is not working.
---------------------
Mark as Answered if it helped
cubangt
Contributor
3052 Points
2402 Posts
Re: Whats wrong with this statement
Aug 19, 2010 10:51 PM|LINK
Maybe seeing all the code that im having issues with will help someone see what is wrong. I can execute each of the selects and inserts and updates on the SQL side with no issues..
But for some reason it seems that only the first update in each condition is executed, but not the ones after. Notice that there are 3 updates per if condition.. the first update in each of the conditions is always executed based on the variables it looks at, but the other 2 updates within the conditions dont fire..
---------------------
Mark as Answered if it helped
cubangt
Contributor
3052 Points
2402 Posts
Re: Whats wrong with this statement
Aug 20, 2010 02:15 PM|LINK
Can anyone see anything wrong with what i have posted? Im not great at VB, but need to get this code working as expected while we work on newer C# versions of the pages as we develop and recreate some of the pages.
Thanks.
---------------------
Mark as Answered if it helped
atconway
All-Star
16846 Points
2756 Posts
Re: Whats wrong with this statement
Aug 20, 2010 07:27 PM|LINK
Can you debug the application and post the value from the 'Immdeate Windows' (within VS.NET) for the value strSqual
you can type ?strSqual in the Immdeate Window and press <Enter>
Also as recommended before, comparing integer values as strings (i.e. "14") uses implicit conversions and it would be cleaner and less error proned to compare integer types to integer values.