Using Arrays

[cheiL]

Hi there

I have a few lines of code that I would like to ammend but have been unsuccessful in doing so. I have posted in a few other forums with no luck, hopefully somebody here can help me out. I have a delete statement which I have been asked to change to an update statement instead. The delete statement has arrays hence the difficulty
Code below:

ConnectionOpen
			DBConn.BeginTrans
			'On Error Resume Next
			'DELETE data
			strSQL = "DELETE tblAvailable WHERE "
			strSQL = strSQL + "(intResortID = " + Session("TypeID") + ")"
			strSQL = strSQL + " AND (dtm BETWEEN CONVERT(DATETIME,'" + cstr(Year(dtmStart)) + "-" + cstr(Month(dtmStart)) + "-" + cstr(Day(dtmStart)) + "', 102)"
			strSQL = strSQL + " AND CONVERT(DATETIME,'" + cstr(Year(dtmEnd)) + "-" + cstr(Month(dtmEnd)) + "-" + cstr(Day(dtmEnd)) + "', 102))"
			
			
			
			'Add code to only delete out room types contained in the spreadsheet
			Dim i
			strSQL = strSQL & "AND (strRoomType='" & strRooms(0) & "'"
			For i = 1 to m_Rooms
				strSQL = strSQL & " OR strRoomType='" & strRooms(i) & "'"
			next
			strSQL = strSQL & ")"
			
			
			'Response.Write strSQL
			DBConn.Execute strSQL

Instead of deleteing I want my code to be ammended to set curprice = curPrice(intCurrentData)

Hopefully someone has an answer for me

Thanks

[lolafuertes]

Basically the update will have the form of

UPDATE table SET field = vlaue WHERE update_condition;

If you need to update more than one field you can

UPDATE table SET field1 = value1, field2 = value2, ... WHERE update_condition

If you are referring to the arrays of strRooms(x) to create the update condition, it will work exactly as the delete contition. You can copy-paste it.
:)

Hope this helps

[cheiL]

Thanks I will try that and get back to you

[cheiL]

hi again
With the code above you will notice that strRooms is in a loop with i where i=0 to M_Rooms. Will it still be correct to copy and paste this code and ammend with an update and set

[cheiL]

hi there...I have a deadline to deliver this code by COB tomorrow was hoping somebody could provide me with some direction. Thanks

[ShahanDev]

Hi!

Could you please provide more detail ?

As you said:
Instead of deleteing I want my code to be ammended to set curprice = curPrice(intCurrentData)

You want to update 'CurPrice' but in your DELETE query it is doing a different thing, no sigh of 'curprice' manipulation etc...

Can please relate it and clarify your intentions a bit more?

lolafuertes is correct in his approach to UPDATE the field.

[cheiL]

here is the code after ammending the delete statement to change it to an update

DBConn.BeginTrans
			dim i
			'On Error Resume Next
			'DELETE data
			strSQL = "Update tblAvailable set curprice ="+ FixNumber(curPrice(intCurrentData))
			strSQL = strSQL +"WHERE intResortID = " + Session("TypeID")  
			strSQL = strSQL + "AND dtm = CONVERT(DATETIME,'" + cstr(Year(dtmStart)) + "-" + cstr(Month(dtmStart)) + "-" + cstr(Day(dtmStart)) + "', 102)"
			'strSQL = strSQL + "AND CONVERT(DATETIME,'" + cstr(Year(dtmEnd)) + "-" + cstr(Month(dtmEnd)) + "-" + cstr(Day(dtmEnd)) + "', 102)"
			strSQL = strSQL + "AND (strRoomType='" & strRooms(0) & "'"
			strSQL = strSQL & " OR strRoomType='" & strRooms(i) & "'"
			strSQL = strSQL & ")"
			
			
			'Response.Write strSQL
			DBConn.Execute strSQL

when I execute my page
I get the following errorMicrosoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near 'dtm'.

/upload_excel_v3.asp, line 228

where am I going wrong?

[ShahanDev]

Can you post the value of "StrSQL" here by putting a breakpoint on your line 15.

We need to see that your values are substituted correctly in the query.

[cheiL]

Hi there

I added a breakpoint to line 5 and it still gives the same error message

this line of code when run in sql works fine

Update tblAvailable set curprice =300
WHERE intResortID =100
AND dtm Between '2011-08-08'
AND '2011-08-11'
AND strRoomType='obeovr'
OR strRoomType='obemvr'

so I am guessing this has something to do with the quotes and the appostrophes
But the error point to dtm and next to dtm is AND

is that the problem?

[cheiL]

Also line 8 should actually be uncommented I only commented it for testing purposes.
I am running out of time really appreciate if you can help me thanks!

[ShahanDev]

insert before "AND in your line 7:

strSQL = strSQL + " AND dtm = CONVERT(DATETIME,'" + cstr(Year(dtmStart)) + "-" + cstr(Month(dtmStart)) + "-" + cstr(Day(dtmStart)) + "', 102)"

[cheiL]

no it didn't work!

[cheiL]

maybe if we look from the original delete code, is it possible that when changing to update I went wrong somewhere?Would you be able to tell me...

[ShahanDev]

No, your approach is correct there is something else missing...

[ShahanDev]

Did you copy/paste the strSQL in SQL server and then posted here ?
the query seems to be correct.

[cheiL]

let me try...

[cheiL]

how do I do that? can you please explain?

[adam_k]

I believe you are missing a space before AND in

"AND dtm = CONVERT(DATETIME,'" + cstr(Year(dtmStart)) + "-" + cstr(Month(dtmStart)) + "-"

Same for

"AND (strRoomType='" & strRooms(0) & "'"

Change them to have a space like " AND..." and then give it a try. If you still get an error post the value of strSQL just before the execution and we'll get it working.

[cheiL]

hi there
done that

my code now looks like this

strSQL = "Update tblAvailable set curprice ="+ FixNumber(curPrice(intCurrentData))
			strSQL = strSQL +"WHERE intResortID = " + Session("TypeID")  
			strSQL = strSQL + " AND dtm BETWEEN CONVERT(DATETIME,'" + cstr(Year(dtmStart)) + "-" + cstr(Month(dtmStart)) + "-" + cstr(Day(dtmStart)) + "', 102)"
			strSQL = strSQL + " AND CONVERT(DATETIME,'" + cstr(Year(dtmEnd)) + "-" + cstr(Month(dtmEnd)) + "-" + cstr(Day(dtmEnd)) + "', 102)"
			strSQL = strSQL + " AND (strRoomType='" & strRooms(0) & "'"
			strSQL = strSQL & " OR strRoomType='" & strRooms(i) & "'"
			strSQL = strSQL & ")"

Still same error
I had initially thought that it had something to do with the between and so I removed between and changed it to = and commeneted the lin before it but I get the same error regardless.

Can you please tell me how to post value of strSQl just before execution?

Thanks heaps

[ShahanDev]

put breakpoint after line 7(of your curernt code) and move cursor toward "StrSQL" stay for a moment you will see the value copy the value paste here.