Hi again,
Thanks to those who helped before but I'm afraid I waisted your time and
effort(especially Bob's) by not detailing the way I
was trying to dynamically create the select statement. I thought if I showed
you a simpler 'hard coded' version it would be
easier to give pointers but all it did was remove the crucial elements. Here
is what I'm attempting to do.
.................................................. ..
Dim intWeightTotal
Dim strCountry
Dim strZone
Dim strWeightBand
intWeightTotal = Request.Form("weightTotal")
strCountry = Request.Form("country")
Select Case strCountry
Case "Australia"
strZone = "Australia"
Case "Austria"
strZone = "A"
Case "Botswanna"
strZone = "B"
Case Else
strZone = "Australia"
End Select
Select Case True
Case intWeightTotal < 251
strWeightBand = "250"
Case intWeightTotal < 501
strWeightBand = "500"
Case intWeightTotal < 751
strWeightBand = "750"
Case intWeightTotal < 1001
strWeightBand = "1000"
Case Else
strWeightBand = "2000"
End Select
Dim strSQL
Dim rsShipping
strSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = " &
strZone & " AND WeightCategory = " & strWeightBand & ""
Set rsShipping = Server.CreateObject("ADODB.Recordset")
rsShipping.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Server.MapPath("../ppdatabase.mdb")
rsShipping.Source = strSQL
rsShipping.CursorType = 0
rsShipping.CursorLocation = 2
rsShipping.LockType = 1
rsShipping.Open()
.................................................. ....
I get error messages from the browser indicating "Syntax error", "expected
end of statement" and "expected parameters" when I
add and remove quotes. I'm sure the problem has something to do with the
string variable generated in the select case and
referenced in the SQL but no end of 'playing' has made it work.
If I comment out all the recordset stuff and place something like this
<%= strZone & "<BR>" %>
<%= strWeightBand %>
in the <body> then the page runs and this
A
500
is displayed so I know both select cases work fine.
Any ideas on how to pass strZone and strWeightBand to the SQL?
B.T.W, I have no line breaks in my code. I'm just useless at formatting
Usenet messages. In the database, both ZoneName and WeightCategory are text
fields.
Regards
Penny.