Type mismatch rs in array - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Type mismatch rs in array

Author

Topic

knutso
Starting Member

20 Posts

Posted - 2006-05-06 : 17:39:25

I have a problem using 3 tables. The first recordset is my own invention (that means a very simple one). It reads product features from a table:

Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT * from prodfeatures"
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

'then I read the recordset into arrays like this:

While Not Rsx.eof
i=i+1
mte(i)=Rsx("id")
' every id has a corresponding featurename
mfnavn(mte(i))=Rsx("featurename")
Rsx.movenext
Wend

'this part works very well

Now comes the difficult part that the SQLTeam figured out (this combines an order base with an itemorder base):

Set Rs = Server.CreateObject("ADODB.RecordSet")
sSQL="select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & dDate & " AND Orders.orderid <> 1 group by oitems.catalogid,oitems.features"
Rs.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

'Then I write the content of the products with product features - the complexity of the sql is due to a use of many other operations:

While Not Rs.eof
response.write Rs("catalogid") & "features:" & Rs("features") & "Name:" & mfnavn(rs("features"))
Rs.movenext
Wend

The query runs, and starts well, writing 3 lines:

239 features:221 Name:Standard bil max 5m lang og 1,9 m høy
240 features:270 Name:Liggestol (gratis)
240 features:271 Name:C1 Enkeltseng i delt lugar u. bildekk. Kun vask

but it is then aborted with this message:

Microsoft VBScript runtime error '800a000d' Type mismatch: 'Rs(...)'
I know that the problematic part is the array
mfnavn(rs("features"))
...anyone with experience in this?

knutso
Starting Member

20 Posts

Posted - 2006-05-06 : 18:41:35

Hm.. propably an array error and not anything with recordset, This error could be irrelevant for the sql-forum. May be I should not have posted it here.

.. but in case anyone see a way to combine all three tables into one sql that could be interesting.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-06 : 21:43:56

Try posting your question in the MS Access Forumn http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-07 : 02:27:42

is there any linking field between this ur query and this table ..

select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & dDate & " AND Orders.orderid <> 1 group by oitems.catalogid,oitems.features

and

SELECT * from prodfeatures

I dont think you require to loop again.. just post the table structures and we can help you out..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

knutso
Starting Member

20 Posts

Posted - 2006-05-07 : 05:20:49

Actually it was an array error - I had to filter out one featurename with an if statement (that special prod was not defined in prodfeatures - so now it runs OK - (in spite of the complexity of code.) But here is the structure and the idea:
*********************************************************
tablestructure - prodfeatures
--------------------------------------------
id Autonumber order number in database
featurenum number number of prodfeature
featurename text name i.e. red car, blue car...
featureother number max in stock

sSQL= "SELECT * from prodfeatures"
taking the featurename and maxprods from prodfeatures, and put them into an array:

mte(i)=Rsx("id")
' every id has a corresponding featurename
mfnavn(mte(i))=Rsx("featurename")
mmax(mte(i))=Rsx("featureother")
*******************************************************

tablestructure - orders
-------------------------------------------
orderid Autonumber
oshippeddate Date
catalogid number

sSQL="select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & dDate & " AND Orders.orderid <> 1 group by oitems.catalogid,oitems.features"

Picking out oshippeddate in orders, finding corresponding orderid and filter out from oitems on the orderid. Then summing numitems in oitems. I do not think catalogid is necessary for the query, but I use it as a test

tablestructure - oitems
------------------------------------------
orderid number
catalogid number
numitems number number of sales of ech featurename
features text

some arrays:
mmmax=mmax(Rs("features"))
m2tt(Rs("catalogid"))=Rs("SumofItems") do not know why I use this, but it works!
mmrest=(mmax(Rs("features"))-m2tt(rs("features"))) test to see if empty stock

if mmrest is negativ or zero mledig is set to "full"

here is the response.write:

response.write mfnavn(Rs("features"))
response.write "PNr:" & "" & Rs("catalogid") & "ENr:" & Rs("features") & "" & "Sum:" & "" & Rs("SumofItems") & "Total:" & "" & mmmax & "Rest:" & "" & (mmmax-m2tt(rs("catalogid"))) & mledig & "<br>"

***********************************************************************************************
I think this is the idea (I could be wrong in some respect - but it works well now)
it is fantastic that the sqlTeam managed to get this up running with so little coding.
Here is some output:

Ingen Kjøretøy (Alle priser er eks. sjåfør.) PNr: 239 ENr:220 Sum: 5 Total: Rest:
Standard bil max 5m lang og 1,9 m høy PNr: 239 ENr:221 Sum: 13 Total: 120 Rest: 107
Minibuss max 2,1m høy og 8m lang PNr: 239 ENr:223 Sum: 6 Total: 5 Rest: -1 Full
Liggestol (gratis) PNr: 240 ENr:270 Sum: 9 Total: Rest:

knutso
Starting Member

20 Posts

Posted - 2006-05-07 : 10:45:13

Actually - if someone has a better way to write the sql - I would gladly know that - how to put two sql's into one single

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-08 : 03:36:35

it can be done in single query if you can tell us the link between the 3 tables...

orders, prodfeatures and oitems

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources