Solved

Having dynamically created an array how do I use a datareader to assign values to it

Posted on 2004-08-30
8
453 Views
Last Modified: 2012-08-14
I have managed to dynamically create an array size.

What is the best way in VB.NET to fill each array member with data.

How can I accomplish this with a datareader?

nCountOfRecords =countIDs  
    ReDim MyArray(nCountOfRecords)

The following code generates an error saying that objreader cannot be converted to type integer.

For I = 1 To nCountOfRecords
    MyArray(I) = objreader  
    Next

Thanks very much in advance.

PG
0
Comment
Question by:pgilfeather
  • 3
  • 2
8 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 11929979
Look first thing is what do you intend to store in the array.
I hope you want to store all the value in the datareader into the array.

You can either store each column values separately, or one row at a time into the array.

For getting the values from a DataReader you'll have to use its Read method to advance to next row and then store the values in the array

In your code you are trying to put the dataReader object itself in one element of the array which is perhaps not what you want.
0
 

Author Comment

by:pgilfeather
ID: 11930176
arif_eqbal,

I want to store the values from 1 database column into the array

If you could demonstrate what you are saying in code, this would be very helpful

I know that I need to loop through each row and assign each row value to the array, but how do you accomplish this in code.

Kind Regards

PG
0
 

Author Comment

by:pgilfeather
ID: 11930195
Sorry...forgot to say...

I do want to store EVERY value of the datareader into the array.

Cheers
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:anilmane
ID: 11930360
I hope this will give you entire code which u r searching for


using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace Anil
{
    public class SqlBrowser : System.Windows.Forms.Form
    {
        private System.Windows.Forms.ListBox orderIDsList;
        private System.Windows.Forms.ListView orderDetailsList;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Label label2;
        private System.ComponentModel.Container components = null;
        private System.Data.SqlClient.SqlConnection con;
        private System.Windows.Forms.Button btnStart;
        private bool _columnsSet = false;

        public SqlBrowser()
        {
            // Create Controls
            InitializeComponent();

            // Setup Connection
            string strCon = "server=xeon;uid=sa;" +
                "pwd=manager; database=northwind";
            con = new SqlConnection(strCon);
        }

        .....
        .....

        // The main entry point for the application.
        static void Main()
        {
            Application.Run(new SqlBrowser());
        }

        // Fill orderIDsList with OrderID's
        private void btnStart_Click(object sender, System.EventArgs e)
        {
            int iOrderID;

            // Create a command to select the Order IDs from the ORDERS table
            SqlCommand command = new SqlCommand
                ("SELECT OrderID from ORDERS", con);

            // Open the connection
            con.Open();

            // Get the data reader
            SqlDataReader reader = command.ExecuteReader();

            // Process each result
            while (reader.Read())
            {
                // Add each order ID in the result to the list
                // view containing the orders IDs. We have only
                // selected a single column in this code so we
                // can be pretty save in using reader.GetInit32(0)
                // there are no more columns in the data reader.
                iOrderID = reader.GetOrdinal("OrderID");
                orderIDsList.Items.Add (reader.GetInt32(iOrderID));
            }

            // Close the reader and the connection
            reader.Close();
            this.con.Close();
        }

        // Populate the list view with the order details
        //
        // Used Stored Procedure:
        // ----------------------
        // CREATE PROCEDURE CustOrdersDetail @OrderID int
        // AS
        // SELECT ProductName,
        //        UnitPrice=ROUND(Od.UnitPrice, 2),
        //        Quantity,
        //        Discount=CONVERT(int, Discount * 100),
        //        ExtendedPrice=ROUND(CONVERT(money, Quantity *
        //        (1 - Discount) * Od.UnitPrice), 2)
        //   FROM Products P, [Order Details] Od
        //  WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
        //
        private void orderIDsList_SelectedIndexChanged
                   (object sender, System.EventArgs e)
        {
            // Get the selected item and convert it to the correct order ID
            int orderID = Convert.ToInt32(orderIDsList.SelectedItem);

            // Create a command to execute a stored procedure
            SqlCommand storedProcCommand =
               new SqlCommand ("CustOrdersDetail", con);

            // Let the command know it will be executed a stored procedure
            storedProcCommand.CommandType = CommandType.StoredProcedure;

            // Add a parameter that's passed to the stored proc,
            // this is the order ID we selected
            storedProcCommand.Parameters.Add("@OrderID",orderID);

            // Open the connection
            con.Open ();

            // Create an ArrayList to hold the results
            ArrayList rowList = new ArrayList();

            // Get the reader
            SqlDataReader reader = storedProcCommand.ExecuteReader();

            // Process each result in the result set
            while (reader.Read())
            {
                // Create an array big enough to hold the column values
                object[] values = new object[reader.FieldCount];

                // Get the column values into the array
                reader.GetValues(values);

                // Add the array to the ArrayList
                rowList.Add(values);
            }

            // Have the columns already being added to the list view?
            if (_columnsSet == false)
            {
                // No, so get the schema for this result set
                DataTable schema = reader.GetSchemaTable();

                // And set the list view to reflect the
                // contents of the schema
                SetColumnHeaders(schema);
            }

            // Close the reader and the connection
            reader.Close ();
            con.Close ();

            // Now populate the list view with the order details
            PopulateOrderDetails(rowList);
        }

        // Populate the list view with the order details
        private void PopulateOrderDetails (ArrayList rowList)
        {
            // Clear any exsisting items from the orders view
            orderDetailsList.Items.Clear();

            // Now process each array in the arraylist
            foreach (object[] row in rowList)
            {
                // Create a string array large enough to hold all
                // the column values in this array
                string[] orderDetails = new string[row.Length];

                // Create a column index into the array
                int columnIndex = 0;

                // Now process each column value
                foreach (object column in row)
                {
                    // Convert the value to a string and stick
                    // it in the string array
                    orderDetails[columnIndex++] = Convert.ToString (column);
                }

                // Now use the string array to create a new item
                // to go in the list view
                ListViewItem newItem = new ListViewItem (orderDetails);

                // Finally add the new item to the view
                orderDetailsList.Items.Add (newItem);
            }
        }

        // Set the list view to reflect the contents of the schema
        private void SetColumnHeaders (DataTable schema)
        {
            // Process each row in the schema table
            foreach (DataRow row in schema.Rows)
            {
                // For each column add a new column to the list view
                orderDetailsList.Columns.Add((string)row["ColumnName"],
                    50, HorizontalAlignment.Left);
            }

            // Set this flag to stop the method being called again
            _columnsSet = true;
        }
    }
}


Thanks
Anil Mane
0
 

Author Comment

by:pgilfeather
ID: 11930386
Sorry but I use VB.NET. I said this in my opening question. I am not really familiar with C#.

I will try my best to glean what I can from your code. Thanks.

Kind Regards

PG
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 500 total points
ID: 11938755
You need to store values from one database column, say the column is String type then

        Dim Arr() As String
        Dim i As Integer = 0
        Dim Cn As OleDbConnection = New OleDbConnection("Provider=MSDAORA.1;Password=??;User ID=??;Data Source=??;Persist Security Info=True") 'GIVE YOUR CONNECTION STRING
        Dim Cmd As OleDbCommand = New OleDbCommand("Select * from Sales_Order", Cn)
        Cn.Open()
        Dim DR As OleDbDataReader
        DR = Cmd.ExecuteReader
        While DR.Read
            ReDim Preserve Arr(i)
            Arr(i) = DR.GetString(0)
            i += 1
        End While
        DR.Close()
        Cn.Close()

'To Read Back All Values
        For i = 0 To Arr.Length - 1
            MsgBox(Arr(i))
        Next


A few Points: I am using DataReader to fill the Array so I am not able to determine how many Rows are there so I am not able to set
Redim Arr(RowCount)
What I do is I Redim inside the loop for every Row

In case you use DataTable to fill the Array you can Redim just once before the loop
Redim Arr(DataTable1.Rows.Count -1)
then loop through the Rows of DataTable
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
maxMirror challenge 10 88
countHi challenge 25 84
recursion example 16 68
how to update exe applicatio from internet ? 6 38
RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now