I'm building an ASP.NET intranet application. I have a table that stores a range of Bill of Lading numbers each day (say 1-10). I want to pull in the range of BOLs based on the max(date) and assign them to a variable. That way, the BOLs in the dataset become the beginning numbers for the new day, and the user just has to input the ending BOLs for that day. Question is, how can I assign the DataSet to a variable (array) that, in turn, can be re-used in an INSERT statement? I know this is a combo SQL/ASP.NET question, but I'm really concerned with the variable assignment, not the INSERT statement.

Here's my code, note the use of the Datagrid was for my visual purposes.

<script language = "VB" runat="server">
sub Page_Load(sender as Object, e as EventArgs)
    dim cnn as sqlconnection = new sqlconnection("server=blah;")
        try

        cnn.open()
        dim comm as sqlDataAdapter = New SqlDataAdapter ("SELECT [225_ENDING_BOL],[235_ENDING_BOL],[260_ENDING_BOL],[302_ENDING_BOL],[306_ENDING_BOL],[310_ENDING_BOL],[314_ENDING_BOL], [318_ENDING_BOL], [322_ENDING_BOL], [326_ENDING_BOL], [334_ENDING_BOL] FROM KM_BOL_RANGE WHERE lifted_date=(select max(lifted_date) from dbo.km_bol_range)", cnn)
        dim DS as new DataSet
        comm.fill(DS,"BOL")
        DataGrid1.DataSource=DS.Tables("BOL").DefaultView
        DataGrid1.Databind()

            catch ex as SqlException
            Status.Text = ex.Message

                Finally
                cnn.close()
        end try
End Sub
</script>

It seems this task may better suit the use of the SqlDataReader and SqlCommand object. If you used the SqlDataReader object then you could use the SqlDataReader.GetValues() method which returns an array of objects, one for each column in the result set. This sounds like exactly what you are looking for.

Peter
------------------------------------------------------
Work smarter, not harder.

Thanks, Peter, you seem to always answer my questions. But I was just thinking, if I really just want to update the new row with yesterday's number plus today's number, could I use the DataTable/DataRow objects to push the data to the new row?

i think you could use datarow like this

        Dim SqlConnection1 As New SqlClient.SqlConnection(myConnectionString)
        Dim cmd As New SqlClient.SqlCommand()
        cmd.CommandText = "SELECT ProductID, ProductName,QuantityPerUnit FROM Products"
        cmd.CommandType = CommandType.Text
        cmd.Connection = SqlConnection1
        SqlConnection1.Open()

        Dim DA As New SqlClient.SqlDataAdapter()
        Dim dr As DataRow
        DA.SelectCommand = cmd
        DA.Fill(DS, "Products")
        dr = DS.Tables("Products").Rows.Item(certainIndex)
        txtQuantityPerUnit.Text = dr("QuantityPerUnit")
        txtProductName.Text = dr("ProductName")
        txtProductID.Text = dr("ProductID")

Ahmed Ali
Software Developer