[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

returning results of sql query...

Posted on 2005-05-03
113
Medium Priority
?
425 Views
Last Modified: 2010-04-23
I need to loop through the CheckedArray and get the cost for the elements in the array. Here is what I have so far...

For i = 0 To CheckedArray.Length - 1
SQLStr = "SELECT Cost FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
            
MyCommand = New SqlCommand(SQLstr, dbConnection)
Numa = MyCommand.ExecuteScalar()
Next

My question is:

1. How do I store the cost for all these items, perhaps in an array, and then sum them so I can use that value in other places?
0
Comment
Question by:jandhb
  • 66
  • 43
  • 4
113 Comments
 
LVL 13

Expert Comment

by:Torrwin
ID: 13920746
Well, if you wanted to sum them you could just do it in your SQL statement.

SQLStr = SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 13920762
I left out a quote at the beginning of that statement, so add it in if you copy/paste. =)
0
 
LVL 1

Author Comment

by:jandhb
ID: 13920801
ok...but....

1. How would I then use that value in other places. I can't use sqlstr because that is just the string value, not the actual cost amount. Make sense?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:jandhb
ID: 13920817
I would think there is a way to store the results of the query in some variable, say costResults, and then be able to use costResults in other places. That is what I am having problems with...How to store the value so that I can use it in other places.
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 13920851
Dim myCommand as new SqlClient.SqlCommand
Dim myAdapter as new SqlClient.SqlDataAdapter
Dim myConnection as new SqlClient.SqlConnection
Dim myQuery as String
Dim costResults as Integer

myConnection.ConnectionString = "Server=SERVER_IP_ADDRESS;initial catalog=DATABASE_NAME;uid=USER_ID;pwd=USER_PASSWORD;"
myCommand.Connection = myConnection

myQuery = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"

myCommand.CommandText = myQuery
myAdapter.SelectCommand = myCommand
myAdapter.Fill(Dataset1, "Results")

costResults = Dataset1.Tables(0).Rows(0).Item(0)
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13920915
1. You're generating the SQL query incorrectly.
Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
  .Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
  For i = 0 To CheckedArray.Length - 1
    .Append("ProductCode = '" & CheckedArray(i) & "' OR ")
  Next i
  .Remove(.Length - 4, 4) ' This might be str.Length-3
End With

2. If all you want is the sum stored in a variable, use Dim sum as Single = myCommand.ExecuteScalar
0
 
LVL 1

Author Comment

by:jandhb
ID: 13920927
Torrwin,

This is what I currently have...Can you tell me how I would modify this to what your saying here...Note: 1) I'm not filling a dataset here. I'm just wanting the total cost from the array elements which in this case would come from the results of the sql query.

For i = 0 To CheckedArray.Length - 1
SQLStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
            
MyCommand = New SqlCommand(SQLstr, dbConnection)
Numa = MyCommand.ExecuteScalar()
Next
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13920975
jandhb,

Again, you're iterating through a loop and querying the database in each loop. You're much better off to query the database only once.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13920989
Chaosian,

I have implemented your second point so I have...

For i = 0 To CheckedArray.Length - 1
SQLStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
            
MyCommand = New SqlCommand(SQLstr, dbConnection)
costResults = MyCommand.ExecuteScalar()
Next

However, when I response.write(costResults) it is only capturing the last element cost in the array. Do I have something wrong? For example, if the checkedarray length is 3 the sqlstr will only show the last element in the where clause. So if I had 20, 30, 40 instead of costResults being 90 it would display as 40.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921000
Chaosian,

It makes sense what your saying and I understand conceptually, but how would this be done code wise?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921021
1. You're not summing the results of your looped queries.  Try this:
costResults += MyCommand.ExecuteScalar()

2. "It makes sense what your saying and I understand conceptually, but how would this be done code wise?"  See part 1 of my previous post. It creates a single SQL statement and gets the sum for all product codes in CheckedArray.

0
 
LVL 13

Expert Comment

by:Torrwin
ID: 13921077
Ok, just to make sure we're on the same page, did you want the sum of each individual product like:

Product A 500
Product B 250

Or, the sum of all of them like:

Products 750

If you want the sum of them individually, you will need to query the database in each loop.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921082
If you want the sum of them individually, you need to use
CostResults(i) = MyCommand.ExecuteScalar
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921092
ok...I tried that....here is what I have now...

Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
  .Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
  For i = 0 To CheckedArray.Length - 1
    .Append("ProductCode = '" & CheckedArray(i) & "' OR ")
  Next i
  .Remove(.Length - 4, 4)
End With

            MyCommand = New SqlCommand(SQLstr, dbConnection)
            costResults += MyCommand.ExecuteScalar()
            
            Response.Write(costResults)

---------------------
I get this error...
System.InvalidOperationException: ExecuteReader: CommandText property has not been initialized

0
 
LVL 1

Author Comment

by:jandhb
ID: 13921114
I dont want the sum of them individually...I just need the total sum. So if my array has 3 elements in it and the results of the query string is 20, 30, 40 then what I need is 90 stored in some variable that I can use in other places. Make sense?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921138
MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921161
More accurately,

Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
  .Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
  For i = 0 To CheckedArray.Length - 1
    .Append("ProductCode = '" & CheckedArray(i) & "' OR ")
  Next i
  .Remove(.Length - 4, 4)
End With

Dim dbConnection as SqlConnection = NewSqlConnection
dbConnection.ConnectionString = "insert your connection string here"
dbConnection.Open
Dim MyCommand as SqlCommand = New SqlCommand(SQLstr, dbConnection)
Dim costResults as Single = MyCommand.ExecuteScalar()
dbConnection.Close
   
Response.Write(costResults)
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921164
Object reference not set to an instance of an object....on this line - MyCommand = New SqlCommand(SQLstr.ToString(), dbConnection)
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921170
Oops... missed the cut-and-paste correction I just made! But, so did you... I'm using strSQL, not SQLstr.

Dim MyCommand as SqlCommand = New SqlCommand(strSQL.ToString(), dbConnection)
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921250
It seems to be working...I have to be honest I have never seen code like that before with append and string builder...Would you mind taking a minute and explaining what the code is doing so I can better understand...thanks

Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
.Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
For i = 0 To CheckedArray.Length - 1
.Append("ProductCode = '" & CheckedArray(i) & "' OR ")
Next i
.Remove(.Length - 4, 4)
End With

MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
costResults += MyCommand.ExecuteScalar()
            
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921306
Sure...

StringBuilder is an object designed to perform large number of concatenations (and other string operations) efficiently. The problem with using strings is that every concatenation actually creates a new string variable, adds the old string plus the new string, then puts it in the new variable and points the old variable pointer at the new variable... at least as near as I understand the process. Some of the gurus around here might be able to fine-tune that explanation.

StringBuilder creates an object that performs as you'd expect. I have a suspicion that it is actually a Char array underneath. However... when you instantiate it, you can specicy the starting size (default is probably 1 character). Whenever you exceed the capacity of the stringbuilder object, it doubles in size automatically. (By specifying 512 as the starting size, it just avoids eight or nine doublings.) Each doubling does what the string variable did -- creates a new object of the new size, copies the contents of the old object, etc.

The Append method just adds characters to the end of the stringbuilder. Remove removes the specified number of characters from the specified starting location. The biggest thing to watch is that a StringBuilder is NOT of type String, and there is no implicit cast to string (for some bizarre reason). So, you always have to use stringbuilder.tostring to get hte string version of the stringbuilder.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921325
As to the code itself...

I set up the SQL statement, then looped through each product and added an OR statement to the WHERE clause. After the loop, it should look like this...
SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = 'CheckedArray(0)' OR ProductCode = 'CheckedArray(1)' OR
 Then, the stringbuilder.remove statement just peels off the extra " OR ".... leaving you with a valid SQL query string...
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921339
I'm getting this error - Operator is not valid for type 'Single' and type 'DBNull'. on this line - costResults = costResults + MyCommand.ExecuteScalar()
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921368
Did I declare the costResults wrong by doing this - Dim costResults As Single
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921393
This is a side issue, but am I passing these two arguments correctly -

Response.Write("self.opener.SetOrderDetailsID('" & SelectId & "','" & costResults & "');")
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921424
The problem is that your SQL query is returning "Null". Do you have any data in this table? If so, the SQL query string may be incorrect. Put a breakpoint on the line with
MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)

Check to see that strSql.ToString() is what you'd expect. If ProductCode is a number, you need to remove the single quotes from the query string:
.Append("ProductCode = " & CheckedArray(i) & " OR ")

VB.Net uses System.DBNull.Value, which cannot be cast to any useful variable type. So...

Dim costResults As Single
Dim objTemp as Object
objTemp = myCommand.ExecuteScalar
if isdbnull(objTemp) then
  constResults = 0
else
  ' ** you can also add an extra layer of error-checking here by adding If IsNumeric(objTemp) Then
  costResults = Single.Parse(objTemp)
end if
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921465
1) There is data in the table.
2) ProductCode is a number

Is this what you mean by implementing the error-checking code...

Dim objTemp as Object
Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
.Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
For i = 0 To CheckedArray.Length - 1
.Append("ProductCode = " & CheckedArray(i) & " OR ")
Next i
.Remove(.Length - 4, 4)
End With

MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
if isdbnull(objTemp) then
  constResults = 0
objTemp = myCommand.ExecuteScalar
else
costResults = Single.Parse(objTemp)
costResults = costResults + MyCommand.ExecuteScalar()
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921500
I meant you could do this:

MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
Dim objTemp as Object = myCommand.ExecuteScalar
if isdbnull(objTemp) then
  costResults = 0
else
  If IsNumeric(objTemp) then
    costResults = Single.Parse(objTemp)
  Else
    costResults = 0
  End if
end if

However, if you KNOW there is data in the table, then we can just do this:
MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
costResults = myCommand.ExecuteScalar

This will throw an error if the SQL query string is incorrect.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921528
Hmm....Append("ProductCode = " & CheckedArray(i) & " OR ") generates Incorrect syntax near the keyword 'OR'.

I changed it to this because I know there is data in the table.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921548
When I change it back to this though - .Append("ProductCode = '" & CheckedArray(i) & "' OR ")

I get - Cast from type 'DBNull' to type 'Single' is not valid. I'm not understanding where we are casting it to type DBNull. Is this saying the query is null?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921565
Generally, that error is cuased by forgetting the spaces around an &

with strSQL
.Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
For i = 0 To CheckedArray.Length - 1
  .Append("ProductCode = ")
  .Append(CheckedArray(i))
  .Append(" OR ")
Next i
.Remove(.Length - 4, 4)
End With
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921582
Yes, it's saying the results of the query are null. Since System.DBNull.Value can't be cast to datetime, integer, single, double, byte, string or any other variable type, you need to use the code I had above to deal with this possibility:

Dim objTemp as Object = myCommand.ExecuteScalar
if isdbnull(objTemp) then
  costResults = 0
else
  If IsNumeric(objTemp) then
    costResults = Single.Parse(objTemp)
  Else
    costResults = 0
  End if
end if

What type of number is in CheckedArray?
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921641
Its a productcode, for example.... 123-456-789-34-123-32
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921677
I like the clean look of this but it does not like it...Do I need to have a break after each line like this or something...

 .Append("ProductCode = ") & _
  .Append(CheckedArray(i)) & _
  .Append(" OR ")
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921704
That's not a number -- it's a string!! Any time you include anything other than 1234567890, it's not a number... so you have to have the single quotes around the product code in the sql query string...

Now that we got that straightened out...

Try this code:

with strSQL
.Append("SELECT Sum(Cost) FROM tblProduct")
'For i = 0 To CheckedArray.Length - 1
'  .Append("ProductCode = ")
'  .Append(CheckedArray(i))
'  .Append(" OR ")
'Next i
'.Remove(.Length - 4, 4)
End With

This will check to see if the sum is being performed correctly. If you still get NULL back, you need to make sure your table name and field name are correct. As a silly question, what is the field type for your cost?  If it is a string (i.e. you have "$" in the field), then the problem is that you can't sum strings.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921714
Nope!! The Append method adds the strings together for you (this is the whole reason I like StringBuilder, besides the efficiency)
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921824
Cost has a money data type.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13921904
Okay... It should work...

When you put a breakpoint on the line with strSQL.ToString, does strSQL.ToString look like this?

SELECT     SUM(Cost)
FROM         tblProduct
WHERE     (ProductCode = '1') OR (ProductCode = '2') OR (ProductCode = '3')

Is your table tblProduct or tblProducts?
0
 
LVL 1

Author Comment

by:jandhb
ID: 13921933
tblProduct...

I've done some simple tests on it and it seems to be working...there were a few times when it looked like it was not calculating correctly, but....I'll have to check a little more.

I appreciate your help. Thank you.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13931111
Chaosian,

I've been working through this and it works...ALMOST. What seems to be happening is this....It does sum the cost of the array elements. However, in addition to doing this it needs to take into account what I type in for quantity. Sorry if I did not mention that before.

I'm storing the quantity like this...

      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next

So...StrQty might look like 1,2,4,

But then how do I say in this code that I do want the sum of whats in the array * the quantity. Make sense?

Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
        .Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
        For i = 0 To CheckedArray.Length - 1
            .Append("ProductCode = '" & CheckedArray(i) & "' OR ")
        Next i
        .Remove(.Length - 4, 4)
      End With

      MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
      costResults = MyCommand.ExecuteScalar()

0
 
LVL 1

Author Comment

by:jandhb
ID: 13931144
I cant just do....

costResults = MyCommand.ExecuteScalar() * StrQty

because if the StrQty is 1,2 it multiplies it wrong.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13931208
Well... if your quantity exists only on the client... we're back to a SELECT statement in each loop.

Dim strSQL as string
For i = 0 To CheckedArray.Length - 1
  strSQL = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
  MyCommand = New SqlCommand(strSQL , dbConnection)
  Sum += Qty(i) * MyCommand.ExecuteScalar()
Next
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13931306
You could also retrieve your list of product codes and costs into a local table, set the quantities and perform the calculation using that datatable...IIRC, there is a way to add calculated fields to a table, so you'd just have to sum those.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13935576
I get this error...

Input string was not in a correct format.

On this line...

costResults += Qty(i) * MyCommand.ExecuteScalar()

Which is a part of this code...

Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
        .Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
        For i = 0 To CheckedArray.Length - 1
            .Append("ProductCode = '" & CheckedArray(i) & "' OR ")
        Next i
        .Remove(.Length - 4, 4)
      End With

      MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
      costResults += Qty(i) * MyCommand.ExecuteScalar()
0
 
LVL 1

Author Comment

by:jandhb
ID: 13935789
Just for clarity...

Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
        .Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
        For i = 0 To CheckedArray.Length - 1
            .Append("ProductCode = '" & CheckedArray(i) & "' OR ")
        Next i
        .Remove(.Length - 4, 4)
      End With

      MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
      costResults = MyCommand.ExecuteScalar()

This is working and will calculate the correct sum when the qty is 1 for each of the elements in the checkedArray. However, if the qty is more than one it still only is summing qty 1.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13936028
This is closer....

costResults = costResults + StrQty * MyCommand.ExecuteScalar()

It multiplies the quantity * cost for a specific element in the array and is correct...For example, if element one has a cost of 98 and is quantity two it will display 196.

However, the answer is not correct when there is more than one element in the array and their quantities are greater than 1.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13936108
I think the error is in StrQty, but not sure how to fix it....

StrQty would look like this if the quantity for two elements is one and two - 1,2,

So if the cost for these two elements is 98 and 251 the value I need is 600.

Make sense?

Here is how StrQty is being stored...

      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13936651
If you want the cost for each item so that you can multiply it by the quantity... use the code I posted above...
Dim strSQL as string
For i = 0 To CheckedArray.Length - 1
  strSQL = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
  MyCommand = New SqlCommand(strSQL , dbConnection)
  Sum += Qty(i) * MyCommand.ExecuteScalar()
Next

This replaces
Dim strSQL as System.Text.StringBuilder = New System.Text.StringBuilder(512)
with strSQL
       .Append("SELECT Sum(Cost) FROM tblProduct WHERE ")
       For i = 0 To CheckedArray.Length - 1
          .Append("ProductCode = '" & CheckedArray(i) & "' OR ")
       Next i
       .Remove(.Length - 4, 4)
     End With

     MyCommand = New SqlCommand(strSQL.ToString(), dbConnection)
     costResults = MyCommand.ExecuteScalar()
0
 
LVL 1

Author Comment

by:jandhb
ID: 13936745
For i = 0 To CheckedArray.Length - 1
  strSQL = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
  MyCommand = New SqlCommand(strSQL , dbConnection)
  Sum += Qty(i) * MyCommand.ExecuteScalar()
Next

This is working and will calculate the correct sum when the qty is more than one for ONLY one element in the array. However, if there are two elements in the array it gives me this.

Input string was not in a correct format.

On this line...

Sum += Qty(i) * MyCommand.ExecuteScalar()
0
 
LVL 1

Author Comment

by:jandhb
ID: 13936885
I think the problem might be because if there are two quantities in StrQty it will look like this 1,2 and it does not know how to calculate this?? but I'm not sure...
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937091
Can you help me figure this out, please.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937145
The problem is that, if you return only a sum from your database query, you have no way of relating that sum to your quantities. If you return a list of costs and product IDs, using SELECT ProductCode, Cost FROM... instead of SELECT Sum(Cost), you still have no way to relate the two.

strSQL = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"

should be

strSQL = "SELECT Cost FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"

Is Qty(i) accessible from the function in question? Also, what variable type is Qty()? You might need to do this, in case either Qty or the command is returning a string:
Sum += Single.Parse(Qty(i)) * Single.Parse(MyCommand.ExecuteScalar())
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937150
Chaosian,

I think this is close...

For i = 0 To CheckedArray.Length - 1
  sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
  MyCommand = New SqlCommand(sqlStr, dbConnection)
  costResults += Qty(i) * MyCommand.ExecuteScalar()
Next

It works with the first two elements in the array (sums them and multiplies the qty fine), but I get this error - Input string was not in a correct format. when I select an element(s) beyond the first two?? Please help.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937158
I don't suppose you have any costs that are NULL, do you?
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937169
Is Qty(i) accessible from the function in question? Yes, it is in the same function.
Also, what variable type is Qty()? String

0
 
LVL 1

Author Comment

by:jandhb
ID: 13937174
no
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937192
When it fails, what is Qty(i)?

If Qty is string (BTW: if it's a number, it should be of a numeric type), you'll have issues.

For i = 0 To CheckedArray.Length - 1
  sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
  MyCommand = New SqlCommand(sqlStr, dbConnection)
  if IsNumeric(Qty(i)) then
    costResults += Single.Parse(Qty(i)) * MyCommand.ExecuteScalar()
  End If
Next
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937213
When I do this....

For i = 0 To CheckedArray.Length - 1
  sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
  MyCommand = New SqlCommand(sqlStr, dbConnection)
  If IsNumeric(Qty(i)) then
        costResults += Single.Parse(Qty(i)) * Single.Parse(MyCommand.ExecuteScalar())
  End If
Next

It does get rid of the error, however it only sums and multiplies the first array element and nothing else.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937261
What, exactly, is in Qty()? Check the actual values of each array element.

The If IsNumeric(Qty(i)) statement will keep the multiplication from happening for any Qty(i) that cannot be converted to a number.

0
 
LVL 1

Author Comment

by:jandhb
ID: 13937312
What, exactly, is in Qty()?

When I entered a qty 1 for element 1 and qty 1 for the last element and did this...

      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
            Response.Write(Qty(i))
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next

The output was 11
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937328
So in this case since element 1 of checkedarray is 98 and the last is 251 costResults should be 349, but instead it is 98.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937346
Can you post your entire code?
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937382
The SubmitData() looks like this...

Sub SubmitData(sender As Object, e As EventArgs)
Dim sqlStr As String
Dim MyCommand As SqlCommand
Dim Numa As Integer
Dim DatagridItem As DataGridItem
Dim FileSelectArray(100) As Integer
Dim strArray As String
Dim i As Integer
Dim FileSelectStr As String
Dim rowCount As Integer = 0
Dim CheckedArray(100) As String
Dim KitArray(100) As String
Dim CheckFile As String
Dim KitFile As String
Dim QTY() As String
Dim StrQty As String
Dim Row As DataRow
Dim DS As New DataSet
Dim costResults As Single
Dim Sum As Single

      CheckFile       = Request("CheckFile")
      KitFile       = Request("KitFile")
      
      If CheckFile <> "" Then
            CheckedArray = CheckFile.Split(",")
      End If      
      
      If Request("Qty") <> "" Then
            QTY = Split(Request("Qty"),",")
      End If
      
      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
            Response.Write(Qty(i))
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next
      StrQty = Left(StrQty, Len(StrQty)-1)

      
' +---------------------------------------------------------------------------+
For i = 0 To CheckedArray.Length - 1
  sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
  MyCommand = New SqlCommand(sqlStr, dbConnection)
  If IsNumeric(Qty(i)) then
        costResults += Single.Parse(Qty(i)) * Single.Parse(MyCommand.ExecuteScalar())
  End If
Next
' +---------------------------------------------------------------------------+
      
      If bInsert Then
            SQLStr = "SET NOCOUNT ON INSERT INTO tblMedia (kitfile) Values('') SELECT @@IDENTITY SET NOCOUNT OFF"
                  
            MyCommand = New SqlCommand(SQLstr, dbConnection)
            SelectID = MyCommand.ExecuteScalar()
      End If
            
      sqlStr = "UPDATE tblMedia SET KitFile = '" & CheckFile & "',Quantity='" & StrQty & "' WHERE UniqueId = '" & SelectId & "'"
            
      'SQLCOMMAND PERFORMS INSERT METHOD IN SQL STRING
      MyCommand = New SqlCommand(sqlStr, dbConnection)
      
      'EXECUTE NON QUERY BECAUSE NOTHING IS BEING RETURNED
      Numa = myCommand.ExecuteNonQuery()
      
      
      'CLOSE
      'Response.Write("<scr" & "ipt language=""javascript"">")
      'Response.Write("self.opener.SetOrderDetailsID('" & SelectId & "', '" & costResults & "');")
      'Response.Write("window.close();")
      'Response.Write("</scr" & "ipt>")
End Sub
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937457
Put a breakpoint on the sqlStr = "SELECT..." line. Examine the values of Qty(i) and CheckedArray(i) at this breakpoint to see if they are what you expect.

If you decalre Qty() as single, you might also gain some insight into what is going on.

For i = 0 To CheckedArray.Length - 1
  if IsNumeric(Qty(i)) AndAlso CheckedArray(i)<>"" AndAlso not Isnothing(CheckedArray(i))   then
    sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
    MyCommand = New SqlCommand(sqlStr, dbConnection)
    costResults += Single.Parse(Qty(i)) * Single.Parse(MyCommand.ExecuteScalar())
  End If
Next
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937480
It is strange...when I select a qty of 1 for every element it gives the correct cost...However, if I just do element 1 with qty of 1 and the last element with qty 1 costResults is 98, not 394?? Hmm....
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937498
You might have a mismatch between Qty and CheckedArray, so that the last Qty is "". Check the length of both arrays....
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937544
See this is a problem...When I put qty 1 for the first element and the last element the sqlStr is this...

SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '200-XPR-401-121-304-099'

However, it should have two productcode because I selected two elements....Might be on to something....
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937568
Nope... it only has one product code, because you're only asking for one... remember? We need to pull the cost for each product separately, so we can multiply them by the quantity...
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937575
ok
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937583
When I check the first three elements with qty 1 and do this...

For i = 0 To CheckedArray.Length - 1
Response.Write(CheckedArray.Length)

I get 333
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937598
In that scenario it actually calculates the right price and seems to work...

But again if I just do the first element and the last one it only gives me the cost * qty for the first one, not both
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937833
Right...

But what I'm suggesting is that the last element may be mismatched. Say you have 25 items on your page. You've checked items 1 and 25, and put quantities in place. What I'm advocating is that you put a breakpoint in your code and sheck that Qty(25), CheckedArray(25) and the result of the SQL query for product code 25 all return what you expect...
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937853
How do we fix this....

When I selected a qty 1 for element 2 and did this...

      If IsNumeric(Qty(i)) Then
      Response.Write("test")

It did display anything. It only displays when the first element has a qty....
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937886
When I enter qty 1 for first element and qty 3 for last element and do this..

      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  StrQty  = StrQty  &  Qty(i) & ","
                  Response.Write(StrQty)
            End If
      Next

I get 1,1,3,

It does not get past...

If IsNumeric(Qty(i)) AndAlso CheckedArray(i)<> "" AndAlso not Isnothing(CheckedArray(i)) Then
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937922
None of the other elements, besides the first one are getting past this line -

      If IsNumeric(Qty(i)) AndAlso CheckedArray(i) <> "" AndAlso not Isnothing(CheckedArray(i)) Then

Maybe its becaus of this -

      If Request("Qty") <> "" Then
            QTY = Split(Request("Qty"),",")
      End If
      
      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13937933
so... use the watch window to see what Qty(i) and CheckedArray(i) are when it fails...
Or....

For i = 0 To CheckedArray.Length - 1
  if IsNumeric(Qty(i)) AndAlso CheckedArray(i)<>"" AndAlso not Isnothing(CheckedArray(i))   then
    sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
    MyCommand = New SqlCommand(sqlStr, dbConnection)
    costResults += Single.Parse(Qty(i)) * Single.Parse(MyCommand.ExecuteScalar())
  Else
    Response.Write("Failed at i = " & i & " for Qty(i)=" & Qty(i) & " and CheckedArray(i) = " & CheckedArray(i))
  End If
Next
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937956
I put in qty 1 for first element and qty 1 for last element and got...

Failed at i = 1 for Qty(i)= and CheckedArray(i) = 200-XPR-470-121-304-099
0
 
LVL 1

Author Comment

by:jandhb
ID: 13937963
And this is the last element productcode - 200-XPR-470-121-304-099
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13938002
So... the problem is a mismatch between CheckedArray and the quantity array....

It looks like CheckFile is coming from another page... so  you might want to check that the request is properly formed:

CheckFile      = Request("CheckFile")

You should see CheckFile=Code1,,,,,,,,CodeN
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938024
CheckFile is coming through ok.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938031
I see...

200-XPR-401-121-304-099,200-XPR-470-121-304-099

first and last elements
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13938038
well..... the second element (i=1) of CheckedArray(i) is the last element. Was that the intent?

if so, then you need to look at the Qty element of the request... becuase the second element of it is empty...
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938591
That was the intent. Not sure how to go about what your saying....
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13938618
You've got Qty=1,1,1,1, or something similar in your HTTP request. Check to see that this is being populated correctly... because Qty() is missing an element (i.e. the last one)
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938698
Would I do that in here...

      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13938745
No. You do it here (or look at the URL you're passing).

If Request("Qty") <> "" Then
     QTY = Split(Request("Qty"),",")
End If

You're not using strQty.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938770
I did this...

      If Request("Qty") <> "" Then
            QTY = Split(Request("Qty"),",")
            Response.Write(QTY)
      End If

And received this...

System.String[]Failed at i = 1 for Qty(i)= and CheckedArray(i) = 200-XPR-470-121-304-099
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938787
But if do this then in...

      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
            Response.Write(Qty(i))
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next

I get 11

0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13938791
That's because QTY is an array, not a string. You have to pass a string to Response.Write method.

Right before this:
For i = 0 To CheckedArray.Length - 1
  if IsNumeric(Qty(i)) AndAlso CheckedArray(i)<>"" AndAlso not Isnothing(CheckedArray(i))   then
    sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
    MyCommand = New SqlCommand(sqlStr, dbConnection)
    costResults += Single.Parse(Qty(i)) * Single.Parse(MyCommand.ExecuteScalar())
  Else
    Response.Write("Failed at i = " & i & " for Qty(i)=" & Qty(i) & " and CheckedArray(i) = " & CheckedArray(i))
  End If
Next

Put this:
For i = 0 to Qty.Length-1
Response.Write("i=" & i & ", Qty(i)=" & qty(i) & ";")
next i
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938825
I get this...

i=0, Qty(i)=1;i=1, Qty(i)=1;i=2, Qty(i)=;i=3, Qty(i)=;i=4, Qty(i)=;i=5, Qty(i)=;i=6, Qty(i)=;i=7, Qty(i)=;i=8, Qty(i)=;i=9, Qty(i)=;i=10, Qty(i)=;i=11, Qty(i)=;i=12, Qty(i)=;i=13, Qty(i)=;i=14, Qty(i)=;i=15, Qty(i)=;i=16, Qty(i)=;i=17, Qty(i)=;i=18, Qty(i)=;i=19, Qty(i)=;i=20, Qty(i)=;i=21, Qty(i)=;i=22, Qty(i)=;i=23, Qty(i)=;i=24, Qty(i)=;
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938882
So what do you think is happening....
0
 
LVL 1

Author Comment

by:jandhb
ID: 13938928
When I did the first element and the last two I got....

i=0, Qty(i)=1;i=1, Qty(i)=;i=2, Qty(i)=;i=3, Qty(i)=;i=4, Qty(i)=;i=5, Qty(i)=;i=6, Qty(i)=;i=7, Qty(i)=;i=8, Qty(i)=;i=9, Qty(i)=;i=10, Qty(i)=;i=11, Qty(i)=;i=12, Qty(i)=;i=13, Qty(i)=;i=14, Qty(i)=;i=15, Qty(i)=;i=16, Qty(i)=;i=17, Qty(i)=;i=18, Qty(i)=;i=19, Qty(i)=;i=20, Qty(i)=;i=21, Qty(i)=;i=22, Qty(i)=;i=23, Qty(i)=5;i=24, Qty(i)=6;

Failed at i = 1 for Qty(i)= and CheckedArray(i) = 200-XPR-460-121-304-099
Failed at i = 2 for Qty(i)= and CheckedArray(i) = 200-XPR-470-121-304-099
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13939012
okay... you have to do one of the following:
1. Ensure that all of the empty product codes are passed (i.e. CheckFile= 200-XPR-460-121-304-091,,,,,,,,,,,,,,,,,,,, 200-XPR-460-121-304-099, 200-XPR-460-121-304-099)
OR
2. Ensure that the empty quantities are NOT passed. (i.e. Qty=1,5,6)
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939060


It almost appears like there is a problem when I don't select the first element. Maybe this is what your talking about? In other words, if I select qty 1 for both element 1 and 2 it works, but if I only select element 2 it does not get past....

If IsNumeric(Qty(i)) AndAlso CheckedArray(i) <> "" AndAlso not Isnothing(CheckedArray(i)) Then

I think it would be best to do #2. Can you show me...
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939089
okay...I just did this...checking with just element 2...

For i = 0 To CheckedArray.Length - 1
      If Qty(i) = "" Then
            Response.Write("test")
      End If

.....

and it displayed test, so therefore qty(i) is blank...but I put in 1....why?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13939094
The problem isn't on this page. It's on the page that sends the CheckFile and Qty in the URL as part of the HHTP request.
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939122
There is no other page that your talking about....

CheckFile is coming from this on the page....


                        If bDisplayCheckBox Then
                                                                        z+=1
                                                                        Dim i As Integer
                                                                        Dim CheckFile
                                                                        Dim check
                                                                        If Not CheckedArray is nothing Then
                                                                              For i = 0 to UBound(CheckedArray)
                                                                                    If z = CheckedArray(i) Then
                                                                                          Check = "Checked"
                                                                                    Exit for                                          
                                                                                    End If
                                                                              Next i
                                                                        End If
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939228
I think this section here is taking care of the blank quantities...maybe we should be using this...

      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939365
At this point I do not know what to do....
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 160 total points
ID: 13939386
Actually, that section is not taking care of anything... again, we're not using it.

If you want that section to eliminate blank entries, you need to do this:

For i = 0 to QTY.Length - 1
          If Qty(i) <> "" Then
               StrQty  = StrQty  &  Qty(i) & ","
          End If
     Next
 StrQty = Left(StrQty, Len(StrQty)-1)

QTY = Split(strQty,",")
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939430
I'm just trying to get it to work...and was trying to do #2 with eliminating blank entries...When I do that I get....

Operator '&' is not defined for types '1-dimensional array of String' and 'String'.

on this line

StrQty  = StrQty  &  Qty(i) & ","
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13939445
Doh!

Do this

For i = 0 to QTY.Length - 1

               StrQty  = StrQty  &  Qty(i) & ","

     Next
 StrQty = Left(StrQty, Len(StrQty)-1)
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13939475
I take it back... I was going to add code to remove the empty elements....


Why are there blank elements in Qty but not CheckedArray???? You need to build Qty the same way you build CheckedArray....
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939478
same error....

      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  'StrQty  = StrQty  &  Qty(i) & ","
                  StrQty = StrQty & Qty(i)&","
            End If
      Next
      StrQty = Left(StrQty, Len(StrQty)-1)
      QTY = Split(strQty,",")
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939488
CheckedArray is being built with this...

      If CheckFile <> "" Then
            CheckedArray = CheckFile.Split(",")
      End If      
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939507
However, qty is being built like this...

      If Request("Qty") <> "" Then
            QTY = Split(Request("Qty"),",")
      End If
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13939519
Yeah... I know... but what is CheckFile, and how is it built? What is Request("Qty") and how is it built?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13939533
For future reference, when you're asked to post your code, you need to post all the relevant code...
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939544
ok....you asked for it....here is the whole page.....maybe this will you to help me.... :)

<%@Page Language="vb" ValidateRequest="false"%>
<%@Import Namespace="System.Data"%>
<%@Import Namespace="System.Data.SqlClient"%>
<%@Import Namespace="System.Web.Security"%>

<!-- #include file="../IncludeFiles/globalVariablesASPX.aspx" -->
<SCRIPT LANGUAGE="VB" RUNAT="SERVER" AutoEventWireup="True">
Dim dbConnection As SqlConnection
Dim z As Integer = 0
Dim CheckedArray() As String
Dim IndexII As Integer = -1
Dim FileSelectStr As String
Dim RowChecked As String
Dim QtyText As String
Dim bDisplayCheckBox As Boolean = True
Dim SelectId As String
Dim bInsert As boolean
Dim CollectProductcode() As String
Dim CollectProductCodeQty() As String

' ==============================================================================
' +---------------------------------------------------------------------------+
' | Function: Page_Unload
' | Public
' +---------------------------------------------------------------------------+
' | Accepts: control, the form control.
' | Returns: Nothing.
' +---------------------------------------------------------------------------+
' | Description:
' | Executed on page unload. Close database connection.
' +---------------------------------------------------------------------------+
' ==============================================================================
Sub Page_Unload(Obj as Object, E as EventArgs)
      If NOT dbConnection IS NOTHING Then
            If dbConnection.State <> 0 Then
                  dbConnection.Close
                  dbConnection = NOTHING
            End If
      End If
End Sub


' ==============================================================================
' +---------------------------------------------------------------------------+
' | Function: Page_Load
' | Public
' +---------------------------------------------------------------------------+
' | Accepts: control, the form control.
' | Returns: Nothing.
' +---------------------------------------------------------------------------+
' | Description:
' | Executed on page load. Request data from order entry page.
' +---------------------------------------------------------------------------+
' ==============================================================================
Sub Page_Load(Obj as Object, E as EventArgs)
Dim sqlStr As String

      ProductCode.text = Request("PCode")

      'NEW SQL CONNECTION OBJECT CREATED
      dbConnection = New SqlConnection(GlobalConnectionString)
      dbConnection.open
      
      'CLOSE WINDOW BUTTON ACTIONS
      CloseWin.Attributes.Add("onClick","window.close();")

      SelectID = Request("SelectID")
      If SelectID = "" Then
            bInsert = True      
      Else
            If Not Page.IsPostBack Then
                  Dim DS  As DataSet
                  Dim MyDataAdapter As SqlDataAdapter
                  
                  SQLStr = "SELECT * FROM tblMedia WHERE UniqueID='" & SelectID & "'"
                  
                  dbConnection = New SqlConnection(GlobalConnectionString)
                  dbConnection.Open
            
                  DS = New DataSet()
            
                  MyDataAdapter = New SqlDataAdapter(SqlStr,dbConnection)
                  MyDataAdapter.Fill(DS,"SelectCollection")
                  
                  If DS.Tables("SelectCollection").Rows.Count > 0 Then
                        CollectProductcode = DS.Tables("SelectCollection").Rows(0).Item("Kitfile").Split(",")
                        CollectProductCodeQty = DS.Tables("SelectCollection").Rows(0).Item("Quantity").Split(",")
                  End If
            End If
      End If

      If Not Page.IsPostBack Then
            BindData()
      End If
End Sub

' ==============================================================================
' +---------------------------------------------------------------------------+
' | Function: BindData
' | Public
' +---------------------------------------------------------------------------+
' | Accepts: Nothing.
' | Returns: Nothing.
' +---------------------------------------------------------------------------+
' | Description:
' | Creates/opens connection to DB. Creates data adapter object.
' | Fills the dataset with the queried sql data. Bind data to ExpSkin Grid.
' +---------------------------------------------------------------------------+
' ==============================================================================
Sub BindData()
Dim DS As New DataSet
Dim Size,Process,SQLStr As String
Dim Row As DataRow
Dim Quantity As Integer
Dim CheckFile As String
Dim i As Integer

      'REQUEST DATA FROM ORDER ENTRY
      size         = Request("size")
      process   = Request("process")
      CheckFile = Request("CheckFile")
      
      If CheckFile <> "" Then
            CheckedArray = CheckFile.Split(",")
      End If

      sqlStr = "SELECT tblKit.Quantity, " & _
                   "(SELECT tblSize.sizefinal " & _
                   "FROM tblSize " & _
                   "WHERE tblSize.sizecode = substring(tblKit.productcode, 9, 3)) AS SizeFinal, " & _
                   "(SELECT product " & _
                   "FROM tblProduct " & _
                   "WHERE tblProduct.productCode = tblKit.productCode) AS Product, " & _
                   "(SELECT productcode " & _
                   "FROM tblProduct " & _
                   "WHERE tblProduct.productcode = tblKit.productCode) AS ProductCode " & _
                   "FROM tblKit INNER JOIN " & _
                   "tblProduct ON tblKit.ProductCodeKitID = tblProduct.ProductCode " & _
                   "WHERE (tblProduct.HardwareCode = 'XPR') AND (tblProduct.ProcessCode = '" & process & "') AND (tblProduct.SizeCode = '" & size & "')"


      'CREATE SQL DATA ADAPTER REQUIRED TO FILL DATASET
      Dim MyDataAdapter As New SqlDataAdapter
      MyDataAdapter = New SqlDataAdapter(sqlStr, dbConnection)
      
      'FILL THE DATASET AND GIVE TABLE OBJECT NAME
      MyDataAdapter.Fill(DS,"ExpSkin")

      'CREATE NEW DATA TABLE
      Dim DRow As DataRow
      Dim DT As New DataTable
      
      Dim dcSizeFinal As New DataColumn("SizeFinal",GetType(String))
      Dim dcSizeCode As New DataColumn("Product",GetType(String))
      Dim dcProductCode As New DataColumn("ProductCode",GetType(String))
      Dim dcSkinQuantity As New DataColumn("Quantity",GetType(String))
      
      DT.Columns.Add(dcSizeFinal)
      DT.Columns.Add(dcSizeCode)
      DT.Columns.Add(dcProductCode)
      DT.Columns.Add(dcSkinQuantity)
      
      For Each Row in DS.Tables("ExpSkin").Rows
            If Row.IsNull("Quantity") Then
                  Quantity = 1
            ElseIf Row("Quantity") = "" Then
                  Quantity = 1
            Else
                  Quantity = Row("Quantity")
            End If
            'For i = 1 To Quantity
                  DRow = DT.NewRow
                  DRow("SizeFinal")    = Row("SizeFinal")
                  DRow("Product")      = Row("Product")
                  DRow("ProductCode")  = Row("ProductCode")
                  DRow("Quantity")        = Row("Quantity")
                  DT.Rows.Add(DRow)
            'Next
      Next
      
       ExpSkin.Columns(1).Visible = false
      'CONDITIONS FOR WHAT COLUMNS SHOW. THIS IS TO HANDLE A LA CARTE.
      If Not size = "1C3" Then
      If Not size = "2C2" Then
      If Not size = "2C3" Then
      If Not size = "3C3" Then
      If Not size = "4C3" Then
      If Not size = "PCY" Then
          ExpSkin.Columns(0).Visible = false
          ExpSkin.Columns(2).Visible = true 'SIZE CODE
          ExpSkin.Columns(3).Visible = true 'SKIN DESCRIPTION
          ExpSkin.Columns(4).Visible = true
          Submit.Visible = False
          CloseWin.Visible = True
          bDisplayCheckBox = False
       End If
       End If
       End If
       End If
       End If
       End If
   
      ExpSkin.DataSource = DT
      ExpSkin.DataBind
End Sub


' ==============================================================================
' +---------------------------------------------------------------------------+
' | Function: BindData
' | Public
' +---------------------------------------------------------------------------+
' | Accepts: control, the form control.
' | Returns: Nothing.
' +---------------------------------------------------------------------------+
' | Description:
' | Creates/opens connection to DB. Creates data adapter object.
' +---------------------------------------------------------------------------+
' ==============================================================================
Sub SubmitData(sender As Object, e As EventArgs)
Dim sqlStr As String
Dim MyCommand As SqlCommand
Dim Numa As Integer
Dim DatagridItem As DataGridItem
Dim FileSelectArray(100) As Integer
Dim strArray As String
Dim i As Integer
Dim FileSelectStr As String
Dim rowCount As Integer = 0
Dim CheckedArray(100) As String
Dim KitArray(100) As String
Dim CheckFile As String
Dim KitFile As String
Dim QTY() As String
Dim StrQty(100) As String
Dim Row As DataRow
Dim DS As New DataSet
Dim costResults As Single
Dim Sum As Single

      CheckFile       = Request("CheckFile")
      KitFile       = Request("KitFile")
      
      'BUILD CHECKED ARRAY
      If CheckFile <> "" Then
            CheckedArray = CheckFile.Split(",")
      End If
      
      'BUILD QTY ARRAY
      If Request("Qty") <> "" Then
            QTY = Split(Request("Qty"),",")
      End If
      
      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next
      StrQty = Left(StrQty, Len(StrQty)-1)
      'QTY = Split(strQty,",")
      
' +---------------------------------------------------------------------------+
' | Description:
' | QTY is an array
' | Set up SQL statment, then loop through each product.
' | Stringbuilder.remove statement takes off the extra " OR "
' | System.DBNull.Value, cannot be cast to any useful variable type
' | The Append method adds the strings together.
' | If IsNumeric(Qty(i)) statement will keep the multiplication from happening
' | for any Qty(i) that cannot be converted to a number.
' | Pull cost for each product separately/then multiply them by quantity
' | AndAlso - once determined expression can't succeed it stops evaluating
' +---------------------------------------------------------------------------+
For i = 0 to Qty.Length-1
      Response.Write("i=" & i & ", Qty(i)=" & qty(i) & ";")
next i

For i = 0 To CheckedArray.Length - 1
      If Qty(i) = "" Then
            Response.Write("<br>" & "test")
      End If
      If IsNumeric(Qty(i)) AndAlso CheckedArray(i) <> "" AndAlso not Isnothing(CheckedArray(i)) Then
              sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
              MyCommand = New SqlCommand(sqlStr, dbConnection)
              costResults += Single.Parse(Qty(i)) * Single.Parse(MyCommand.ExecuteScalar())
              Response.Write("<br>" & costResults)
              Response.Write("<br>" & "Succeeded at i = " & i & " for Qty(i)=" & Qty(i) & " and CheckedArray(i) = " & CheckedArray(i))
        Else
             Response.Write("<br>" & "Failed at i = " & i & " for Qty(i)=" & Qty(i) & " and CheckedArray(i) = " & CheckedArray(i))
      End If
Next
' +---------------------------------------------------------------------------+
      
      If bInsert Then
            SQLStr = "SET NOCOUNT ON INSERT INTO tblMedia (kitfile) Values('') SELECT @@IDENTITY SET NOCOUNT OFF"
                  
            MyCommand = New SqlCommand(SQLstr, dbConnection)
            SelectID = MyCommand.ExecuteScalar()
      End If
            
      sqlStr = "UPDATE tblMedia SET KitFile = '" & CheckFile & "',Quantity='" & StrQty & "' WHERE UniqueId = '" & SelectId & "'"
            
      'SQLCOMMAND PERFORMS INSERT METHOD IN SQL STRING
      MyCommand = New SqlCommand(sqlStr, dbConnection)
      
      'EXECUTE NON QUERY BECAUSE NOTHING IS BEING RETURNED
      Numa = myCommand.ExecuteNonQuery()
      
      
      'CLOSE
      'Response.Write("<scr" & "ipt language=""javascript"">")
      'Response.Write("self.opener.SetOrderDetailsID('" & SelectId & "', '" & costResults & "');")
      'Response.Write("window.close();")
      'Response.Write("</scr" & "ipt>")
End Sub

Function LoadCheck(ProductCode) As String
      If NOT CollectProductcode Is Nothing Then
            Dim  i
            For i = 0 To CollectProductcode.Length - 1
                  If      CollectProductcode(i) = ProductCode Then
                        Return "Checked"
                        Exit For
                  End If
            Next i
            Return ""
      End If
End Function

Function LoadQuantity(ProductCode) As String
If NOT       CollectProductcode IS Nothing Then
      Dim  i
      For i = 0 To CollectProductcode.Length - 1
            'Response.Write(CollectProductcode(i) & ":" & CollectProductCodeQty(i) & "<br>" )
            If      CollectProductcode(i) = ProductCode Then
                  Return CollectProductCodeQty(i)
                  Exit For
            End If
      Next i
End If
End Function

</SCRIPT>
<html>
<head>      
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Xpressions</title>
<link rel="stylesheet" type="text/css" href="../Style.css">
<script language="javascript">
var iUse=false;
function ComfirmPair(ProductQty,CheckBox){
var obj = document.getElementById(ProductQty);
var obj1 = document.getElementById(CheckBox);

 if(!iUse)
 {      
      if(obj1.checked){
            if(obj.value == ''){
                  obj.focus();
            }
      }
      else
            obj.value = '';
 }

}      
      
function QtyBlur(ProductQty,CheckBox){
var obj = document.getElementById(ProductQty);
var obj1 = document.getElementById(CheckBox);

      if(obj1.checked){
            if(obj.value == ''){
                  iUse = true;
                  obj.focus();
            }
            else
                  iUse = false;
      }
      else
            obj.value = '';

}      
      
</script>
</head>
<body>
      <form id="expression" name="expression" action="" method="post" runat="server">
      <input type="hidden" name="FileSelectStr" value="<%=FileSelectStr%>">
      <table border="1" align="center">
            <tr>
                  <td>      
                        <table cellspacing="2" cellpadding="2">
                              <tr valign="top">
                                    <td><b><asp:Label ID="ProductCode" Font-Size="8" Font-Name="verdana" Runat="server"/>
                                    <br>
                                    </b><br>
                                    <asp:DataGrid id="ExpSkin" Runat="server" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True" AutoGenerateColumns="False" >
                                          <Columns>
                                                <asp:TemplateColumn HeaderText="&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Qty">
                                                      <ItemTemplate>
                                                            <%
                                                                  If bDisplayCheckBox Then
                                                                        z+=1
                                                                        Dim i As Integer
                                                                        Dim CheckFile
                                                                        Dim check
                                                                        If Not CheckedArray is nothing Then
                                                                              For i = 0 to UBound(CheckedArray)
                                                                                    If z = CheckedArray(i) Then
                                                                                          Check = "Checked"
                                                                                    Exit for                                          
                                                                                    End If
                                                                              Next i
                                                                        End If
                                                      %>
                                                      <table>
                                                            <tr>
                                                                  <td valign="top">
                                                                        <input type="checkbox" <%=Check%> name="CheckFile" <%# LoadCheck(DataBinder.Eval(Container.DataItem,"ProductCode"))%> id="CheckFile_<%=Z%%>" value="<%# Container.DataItem("ProductCode")%>" onclick="ComfirmPair('QTY_<%=Z%>','CheckFile_<%=Z%%>');">
                                                                  </td>
                                                                  <td>
                                                                        <input type="textbox" value='<%# LoadQuantity(DataBinder.Eval(Container.DataItem,"ProductCode"))%>' name="QTY" style="width:30px" maxlength=3 id="QTY_<%=Z%>" onblur="QtyBlur('QTY_<%=Z%>','CheckFile_<%=Z%%>');">      
                                                                  </td>
                                                            </tr>
                                                      </table>
                                                      <%End If%>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                
                                                <asp:TemplateColumn Visible="true" HeaderText="">
                                                      <ItemTemplate>
                                                            <asp:TextBox Width="100" ReadOnly="True" BackColor="white" BorderColor="white" BorderStyle=None Font-Name="verdana" Font-Size="8" Runat="server" ID="KitFile" DataValueField="ProductCode" DataTextField="ProductCode" Text='<%# Container.DataItem("ProductCode")%>'/>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                          
                                                <asp:TemplateColumn Visible="True" HeaderText="Size Code">
                                                      <ItemTemplate>
                                                            <asp:TextBox CssClass="TextBoxAlignCenter" Width="100" ReadOnly="True" BackColor="white" BorderColor="white" BorderStyle=None Font-Name="verdana" Font-Size="8" Runat="server" DataValueField="Product" DataTextField="Product" Text='<%# Container.DataItem("SizeFinal")%>'/>                        
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                
                                                <asp:TemplateColumn HeaderStyle-Width="160" ItemStyle-Font-Size="8" ItemStyle-Font-Name="verdana" ItemStyle-HorizontalAlign="center" HeaderStyle-HorizontalAlign="center" Visible="True" HeaderText="Skin Description">
                                                      <ItemTemplate>                                          
                                                             <%# Container.DataItem("Product") %>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                
                                                <asp:TemplateColumn HeaderStyle-Width="130" ItemStyle-Font-Size="8" ItemStyle-Font-Name="verdana" ItemStyle-HorizontalAlign="center" HeaderStyle-HorizontalAlign="center" Visible="False" HeaderText="Skin Quantity">
                                                      <ItemTemplate>                                          
                                                             <%# Container.DataItem("Quantity") %>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
 
                                          </Columns>
                                          </asp:DataGrid><p>&nbsp;</td>                        
                              </tr>
                        </table>
                                                
                        <table align="center">
                              <tr>
                                    <td colspan="3"><asp:Button Visible=False Text="Close Window" ID="CloseWin" Font-Name="verdana" Font-Size="10" BorderStyle="Solid" Runat="server"/></td>
                                    <td><asp:Button Visible=True OnClick="SubmitData" ID="Submit" Text="Submit" Font-Name="verdana" Font-Size="10" BorderStyle ="Solid" Runat="server"/></td>
                              </tr>
                  </td>
            </tr>      
      </table>      
      </form>
</body>
</html>
<style>
.TextBoxAlignCenter
{
    text-align : center;
}
</style>
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13939658
Okay, let's cheat.

Dim NewQty(Qty.Length-i) as String
dim j as integer =0
for i = 0 to Qty.Length-1
  if Qty(i)<>"" then
    NewQty(j)=Qty(i)
    j += 1
  end if
next i

Qty = NewQty

For i = 0 To CheckedArray.Length - 1
  if IsNumeric(Qty(i)) AndAlso CheckedArray(i)<>"" AndAlso not Isnothing(CheckedArray(i))   then
    sqlStr = "SELECT SUM(Cost) FROM tblProduct WHERE ProductCode = '" & CheckedArray(i) & "'"
    MyCommand = New SqlCommand(sqlStr, dbConnection)
    costResults += Single.Parse(Qty(i)) * Single.Parse(MyCommand.ExecuteScalar())
  Else
    Response.Write("Failed at i = " & i & " for Qty(i)=" & Qty(i) & " and CheckedArray(i) = " & CheckedArray(i))
  End If
Next
0
 
LVL 1

Author Comment

by:jandhb
ID: 13939663
I think its working!!!!!!!!!!!!!!!!!!!!!!!!!

We had to do this...

      If Request("Qty") <> "" Then
            QTY = Split(Request("Qty"),",")
      End If
      
      For i = 0 to QTY.Length - 1
            If Qty(i) <> "" Then
                  StrQty  = StrQty  &  Qty(i) & ","
            End If
      Next
      StrQty = Left(StrQty, Len(StrQty)-1)
------>      QTY = Split(strQty,",")
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 13939704
How does that differ from what I posted half an hour ago?

 Comment from Chaosian
Date: 05/05/2005 01:40PM MDT
      Your Comment       

Actually, that section is not taking care of anything... again, we're not using it.

If you want that section to eliminate blank entries, you need to do this:

For i = 0 to QTY.Length - 1
          If Qty(i) <> "" Then
               StrQty  = StrQty  &  Qty(i) & ","
          End If
     Next
 StrQty = Left(StrQty, Len(StrQty)-1)

QTY = Split(strQty,",")
0
 
LVL 1

Author Comment

by:jandhb
ID: 13940033
Good question...It may have been mixed with something else in the midst of all the posts. I'm not sure.

As I said before, thank you. This has been quite a little journey. I appreciate you taking the time to help.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month18 days, 17 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question