Arrays and update statements

[ChazaL]

I am working on arrays but i need to do a sql update statement.
Contents of the array are also part a primary key in my table.
How do I do an update using the array for example if my array is room names and my other table values are start date, end date and campus ID.

[jmcilhinney]

There is no such thing as a database column that contains an array, so what you're talking about doesn't make sense. If you're talking about multiple columns then you need to treat each array element as a separate value, which it is, and insert it into the appropriate column. If you're talking about multiple rows then you need to insert a row for each element in the array.

If that doesn't answer your question then you need to provide a more detailed explanation of your database and application design.

[ChazaL]

There is no such thing as a database column that contains an array, so what you're talking about doesn't make sense. If you're talking about multiple columns then you need to treat each array element as a separate value, which it is, and insert it into the appropriate column. If you're talking about multiple rows then you need to insert a row for each element in the array.

If that doesn't answer your question then you need to provide a more detailed explanation of your database and application design.

DBConn.BeginTrans
			'On Error Resume Next
			'DELETE data
			strSQL = "DELETE tblAvailable WHERE "
			strSQL = strSQL + "(intCampusID = " + 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))"
			
			
			'SY SINGH
			'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

This is the code that was originally there on the system I am working on. I don't want it to do a delete, I instead want it to do an update of the available records using the following quantity

strSQL = "SET intQTY=" + intQuantity(intCurrentData)

I realised that the previous programmer may have been doing a delete because of the use of arrays for the strRoom data which comes as an array but a delete over writes the exisiting data. I just want to do an update.

[ChazaL]

Anybody be able to help me or need further clarification?

[jmcilhinney]

Further clarification. I asked for a more detailed explanation and didn't really get it. Code is all well and good but it is not a substitute for a full and clear explanation of what you actually want to achieve.

[ChazaL]

Further clarification. I asked for a more detailed explanation and didn't really get it. Code is all well and good but it is not a substitute for a full and clear explanation of what you actually want to achieve.

well hopefully this makes sense to you:

There is an excel workbook in which room type, quantity and rates are entered. That is then exported into an asp page. The different Room types are stored in an array hence the array can have roomtype[0]-max depending on the number of rooms types entered in the excel

The existing code in the asp page is what I had pasted earlier. It deletes the existing room details that are there and then inserts new data. I do not want to delete the existing data but to update room prices and quantity based on the room type. My primary keys are roomtype, startdate, enddate, campusId. you can see that in the code from earlier