Problems with SQL, and ASP

[hblackorby]

I'm doing this:

SQL = "SELECT Cat_Num FROM tblEM_Parts_Fields WHERE (FieldID, Value) = (" & VoltsID & ",'" & volts & "')"
Response.Write &quot;SQL: &quot; & SQL & &quot;<br>&quot;
rs.Open SQL, sConn
while not rs.EOF
Response.Write rs(&quot;Cat_Num&quot & &quot;<br>&quot;
rs.MoveNext
wend
rs.close

This returns this:
SELECT Cat_Num FROM tblEM_Parts_Fields WHERE (FieldID, Value) = (21,'155/220')

I'm using a Row Value Constructor which is part of the SQL2 standard. It throws back a SQL syntax error at the first comma after FieldID. I'm using a SQL database running on WinNT SQL Server.

Does anyone know of any ussues with ASP and SQL2? This seems like the perfect solution to my problem, but it won't accept the SQL string. Any advice is appreciated. Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[Nanda]

shouldn't voltsID be also in quotes!!!!!
like ..where (fieldId, value) = ('&quot; & voltsID & &quot;','&quot; & volts &quot;'&quot
which will return ..where (fieldid, value) = ('21', '155/220')

 

[hblackorby]

No, because VoltsID is a numeric field, so it should not be in quotes. Just in case, I did test it with quotes. It didn't change the error. I think it is a SQL string problem. Maybe I can't use SQL2 queries on a SQL database? Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[Swany]

As far as I know SQL server supports SQL98 syntax. I've never seen a &quot;SQL 2&quot; query like yours before. Typically when SQL statements are constructed for .ASP pages they look like

sql = &quot;select col1, col2 from table where col1 = '&quot; & col1value & &quot;' and col2=&quot; & col2value

 

[hblackorby]

This is what I get for buying a SQL reference book. Anyway, after delving around the Microsoft SQL site, I have found that Microsoft SQL Server uses SQL-Transact as their SQL version. This does support some of the SQL-92 standard (or SQL2 as I like to call it), but it does not support row/value constructors like I am trying to use. So, I guess this was just wishful thinking.

Thanks Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[Swany]

However, you could write a quick procedure that
does the same thing (abc,def) = ('abc',def)

you would use the function as so:

response.write makeQuery("select * from mytable", Array("Col1", "Col2", "Col3"), Array("Value1","'Value2'","Value3"))

The result from the above would be:
select * from mytable where Col1 = Value1 and Col2 = 'Value2' and Col3 = Value3

function makeQuery(baseSQL, columns, values)
  if not isArray(columns) then
     if columns = "" then
        makeQuery = baseSQL
        exit function
     else
        cols(0) = columns
     end if
  else 'Columns is an array
     cols = columns
  end if

  if not isArray(values) then
     vals(0) = values
  else
     vals = values
  end if

  if ubound(columns) <> ubound(values) then
     response.write &quot;Values count != column count!&quot;
     makeQuery = &quot;&quot;
     exit function
  end if

  baseSQL = baseSQL & &quot; where &quot;
  for i = 0 to ubound(columns)
    baseSQL = baseSQL & colums(i) & &quot; = &quot; & values(i)
    if i < ubound(columns) then baseSQL = baseSQL & &quot; and &quot;
  next

  makeQuery = baseSQL
end function

 

[Swany]

You should change
baseSQL = baseSQL & colums(i) & &quot; = &quot; & values(i)
to
baseSQL = baseSQL & cols(i) & &quot; = &quot; & cstr(vals(i))

 

[hblackorby]

I am trying to pull a list of products with a matching given field value. Say I have volts and HP, and I'm trying to find products with volts of 115 and HP of 1/6. Sounds easy right? Problem is, because each product can have various fields associated with it, I have a table with FieldID's, ProductNums, and Values. The FieldID corresponds to a Field( volts, HP or RPM etc). I'm just trying to pull a productNum with values matching, but it has to match over more than one row because each row contains just one field's value (HP, etc). So really:
SELECT Cat_Num WHERE IF FieldID=1 THEN Value=115 AND IF FieldID=3 THEN Value='1/6'

I'm not sure there is any way to do this in SQL, so I'm starting to try it with different arrays. Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[Swany]

sounds like you could do something like the following.

Select case FieldID
case 0
Field=&quot;Volts&quot;
Value=&quot;'115/220'&quot;
case 1
Field=&quot;HP&quot;
Value=&quot;'1/6'&quot;
case 2
Field=&quot;RPM&quot;
Value=&quot;'5400'&quot;
end select

sql = makeQuery("select CAT_num from tblEM_Parts_Fields ",Array(Field),Array(value))

or​

sql = makeQuery(&quot;select CAT_num from tblEM_Parts_Fields &quot;,Array(&quot;FieldID&quot;,&quot;Value&quot,Array(FieldID , value))

The first statement will make a query that looks like:
select cat_num from tblEM_part_fields where volts = 115

The second statement will make a query that looks like:
select cat_num from tblEM_part_fields where fieldID = 21 and value = '115/200'