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

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

Edited 5 Years Ago by lolafuertes: n/a

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

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

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.

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 error

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near 'dtm'.

/upload_excel_v3.asp, line 228

where am I going wrong?

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.

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?

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!

insert <space> before "AND in your line 7:

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

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

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

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.

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

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.

Edited 5 Years Ago by ShahanDev: line number mentioned wrong..

This may not work because I have to upload the page onto a site inorder to test it. I place the breakpoint upload the page and still get the error message, execution doesn't halt anywhere

Okay. before executing query write:

Debug.Print(strSQL)

and then in output window copy/paste the value of StrSQL.

Error below:

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'print'

/upload_excel_v3.asp, line 223

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Debug.Print'

/upload_excel_v3.asp, line 223

Hi!

from your code:

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 & ")"

Please make sure Session("TypeID") contains some value. It should not empty. I have tested it and if Session("TypeID") is empty it throws the error you were pointing since yesterday.

Did you find a method Val() ?? If yes then you can write this:
Session("TypeID")

like this:
Val(Session("TypeID"))

so if Session("TypeID") is empty it will take zero instead of empty string.

Yes got a funny message, still trying to figure out what it means, I will paste it shortly...thanks

this is the error I am receiving:

dtm'dtm' OR strRoomType='obeqvb'dtm' OR strRoomType='obeqvb')
Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near 'obeqvb'.

/upload_excel_v3.asp, line 230

This question has already been answered. Start a new discussion instead.