Using Arrays

Question

cheiL 0 Newbie Poster

13 Years Ago

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

sql vb.net

4 Contributors
29 Replies
135 Views
7 Months Discussion Span
Latest Post 12 Years Ago Latest Post by cheiL

lolafuertes 145 Master Poster

13 Years Ago

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 13 Years Ago by lolafuertes because: n/a

ShahanDev 41 Junior Poster

13 Years Ago

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.

ShahanDev 41 Junior Poster

13 Years Ago

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.

ShahanDev 41 Junior Poster

13 Years Ago

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

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

ShahanDev 41 Junior Poster

13 Years Ago

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 13 Years Ago by ShahanDev because: line number mentioned wrong..

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

cheiL 0 Newbie Poster · Answer 1 · 2011-08-10T02:19:43+00:00

cheiL 0 Newbie Poster

13 Years Ago

Thanks I will try that and get back to you

cheiL 0 Newbie Poster · Answer 2 · 2011-08-10T03:02:55+00:00

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 0 Newbie Poster · Answer 3 · 2011-08-17T09:56:30+00:00

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

cheiL 0 Newbie Poster · Answer 4 · 2011-08-18T02:56:31+00:00

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?

cheiL 0 Newbie Poster · Answer 5 · 2011-08-18T03:20:33+00:00

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 0 Newbie Poster · Answer 6 · 2011-08-18T03:23:31+00:00

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!

cheiL 0 Newbie Poster · Answer 7 · 2011-08-18T03:40:50+00:00

cheiL 0 Newbie Poster

13 Years Ago

no it didn't work!

cheiL 0 Newbie Poster · Answer 8 · 2011-08-18T03:46:07+00:00

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 41 Junior Poster · Answer 9 · 2011-08-18T03:48:39+00:00

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

ShahanDev 41 Junior Poster · Answer 10 · 2011-08-18T03:50:34+00:00

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

cheiL 0 Newbie Poster · Answer 11 · 2011-08-18T03:52:38+00:00

cheiL 0 Newbie Poster

13 Years Ago

let me try...

cheiL 0 Newbie Poster · Answer 12 · 2011-08-18T03:54:03+00:00

cheiL 0 Newbie Poster

13 Years Ago

how do I do that? can you please explain?

adam_k 239 Master Poster · Answer 13 · 2011-08-18T03:58:14+00:00

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 0 Newbie Poster · Answer 14 · 2011-08-18T04:09:12+00:00

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

cheiL 0 Newbie Poster · Answer 15 · 2011-08-18T04:37:59+00:00

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

ShahanDev 41 Junior Poster · Answer 16 · 2011-08-18T05:40:44+00:00

Okay. before executing query write:

Debug.Print(strSQL)

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

cheiL 0 Newbie Poster · Answer 17 · 2011-08-18T06:12:38+00:00

Error below:

Microsoft VBScript runtime error '800a01f4'
Variable is undefined: 'print'
/upload_excel_v3.asp, line 223

cheiL 0 Newbie Poster · Answer 18 · 2011-08-18T06:13:43+00:00

Microsoft VBScript runtime error '800a01b6'

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

/upload_excel_v3.asp, line 223

adam_k 239 Master Poster · Answer 19 · 2011-08-18T16:22:36+00:00

Msgbox the variable or assign it's value to a textbox.

ShahanDev 41 Junior Poster · Answer 20 · 2011-08-18T23:47:05+00:00

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.

cheiL 0 Newbie Poster · Answer 21 · 2011-08-19T03:12:57+00:00

cheiL 0 Newbie Poster

13 Years Ago

ok Let me try that and thanks

ShahanDev 41 Junior Poster · Answer 22 · 2011-08-23T00:02:00+00:00

Hi!

Did you check that ? Waiting for your response....

cheiL 0 Newbie Poster · Answer 23 · 2011-08-23T02:23:20+00:00

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

cheiL 0 Newbie Poster · Answer 24 · 2012-03-28T04:49:20+00:00

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