Pulling from a table twice and pulling partial data 2

[SuperComputing]

Hello all, thanks again for all the help so far.

I have a table with 9 columns, I need to pull 4 columns and split up a 5th into peices. This is what I have so far and it is working:

Set con = Server.CreateObject("ADODB.Connection")
con.open "DSN=FTInventory"
strQuery1 = "SELECT Barcode, ItemNumber, ColorNumber, SizeNumber, FILLER1 FROM Barcode WHERE Barcode = '" & Replace(upc, "'", "''") & "' "
Set rsBarcodeData1 = con.Execute(strQuery1)

Now, the 5th cell, FILLER1, is a comment(text) cell that I would like to use to store and retreive csv text data. I'm not allowed to simply add new columns.

When I try the following:

locQuery1 = "SELECT LEFT (FILLER1, 5) FROM Barcode WHERE Barcode = '" & Replace(upc, "'", "''") & "' "
Set rsLoc1 = con.Execute(locQuery1)
.
.
.
<input name="rsLoc1" size="8" value="<%=rsLoc1%>">

(the last line is an input box that shows the current data and allows it to be changed)

I get
Error Type:
Response object, ASP 0185 (0x8002000E)
A default property was not found for the object.

Is this because I am connecting twice? Is there an easier way to do this?

I would like to have the data in the cell to be something like this:
1A01A, 1D14C, 1F07B, 1G02E
All will be 5 characters and can either be comma separated or space separated, and be able to pull the parts independently:

SELECT LEFT(FILLER1, 5)...
SELECT LEFT(FILLER1, ??? 8 - 12 ???) etc...

Thanks in advance for any suggestions!

 

[Sheco]

[tt]
<input name="rsLoc1" size="8" value="<%=[red]rsLoc1[/red]%>">[/tt]
You probably want to refer to the column by index like: [tt]<%= rsLoc1(0)%>[/tt]
(assuming the first column is index zero)



Either that or change your SQL to give the derived column a name:[tt]
SELECT LEFT(FILLER1, 5) [red]As 'Foo'[/red] FROM Barcode WHERE [...]
[/tt]
And then referece the column by name:
[tt]<%= rsLoc1("Foo")%>[/tt]

 

[gmmastros]

rsLoc1 is a recordset object. Normally, you use the column name to differentiate which column to display.

Ex:

Select Column1, Column2, etc...

In asp, you would use...

RS("Column1")

Really though, this is a shortcut using some default properties. The default property of the recordset object if the fields object. The default for the fields object is the item object. The default for the item object is the value property. So...

RS("Column1") could be written like...

RS.Fields("Column1")
RS.Fields.Item("Column1")
RS.Fields.Item("Column1").Value

Now... in your case, you don't actually have a column name, but there are things you can do to solve this problem.

You could use the ordinal position of the columns, like this...

locQuery1 = "SELECT LEFT (FILLER1, 5) FROM Barcode WHERE Barcode = '" & Replace(upc, "'", "''") & "' "
Set rsLoc1 = con.Execute(locQuery1)
.
.
.
<input name="rsLoc1" size="8" value="<%=rsLoc1[!](0)[/!]%>">

The 0th position would be the first column returned by your query.

Alternatively, you could alias your column in the query, like this...

locQuery1 = "SELECT LEFT (FILLER1, 5) [!]As AliasNameHere[/!] FROM Barcode WHERE Barcode = '" & Replace(upc, "'", "''") & "' "
Set rsLoc1 = con.Execute(locQuery1)
.
.
.
<input name="rsLoc1" size="8" value="<%=rsLoc1[!]("AliasNameHere")[/!]%>">

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SuperComputing]

Makes wonderful sense, I used the <%=rsLoc1(0)%> suggestion that you both made and that part of my code is working perfectly now.

Now, on to submiting the text entered into the four locations into a string in the cell.

<form action="results.asp" method="post" name="frmResults">
.
<input name="rsLoc1" size="8" value="<%=rsLoc1(0)%>">
<input name="rsLoc1" size="8" value="<%=rsLoc2(0)%>">
<input name="rsLoc1" size="8" value="<%=rsLoc3(0)%>">
<input name="rsLoc1" size="8" value="<%=rsLoc4(0)%>">
.
</form>
.
-- next page --
.
Loc1 = Request.Form("rsLoc1")
Loc2 = Request.Form("rsLoc2")
Loc3 = Request.Form("rsLoc3")
Loc4 = Request.Form("rsLoc4")
Set con = Server.CreateObject("ADODB.Connection")
con.open "DSN=FTInventory"
.
updateloc= "Update Barcode Set FILLER1 = '" & Replace(Loc1, "'", "''") & "' [COLOR=red] --- ??? --- [/color] Where Barcode = '" & Replace(upc, "'", "''") & "' "
con.Execute(updateloc)
.

Any hints and tips on the syntax above for getting a cell that looks like "XXXXX, XXXXX, XXXXX, XXXXX"

.

 

[Sheco]

If there are only 4 items you could do this:
[tt]
sFILLER1 = Request("rsLoc1") & "," & Request("rsLoc2") & "," & Request("rsLoc3") & "," & Request("rsLoc4")
[/tt]

 

[SuperComputing]

GREAT! Thanks again, I used your suggestion and this is what did it:

sFILLER1 = Request("rsLoc1") & "," & Request("rsLoc2") & "," & Request("rsLoc3") & "," & Request("rsLoc4") 
.
.
updateloc= "Update Barcode Set FILLER1 = '" & Replace(sFILLER1, "'", "''") & "' Where Barcode = '" & Replace(upc, "'", "''") & "' "
[\code]