Click here to Skip to main content
14,385,176 members
Rate this:
Please Sign up or sign in to vote.
See more:
// Define the Variables for Connection to the SQL Server

            String ConnectionString2 = @"Data Source = hemsql02;Initial Catalog=OTITest;User ID=wgtest;Password=Testit$1";
            SqlConnection conn = new SqlConnection(ConnectionString2);
            con.Open();   // Open up the path to the Database.....

            // Setup Sql Statement for Selection of Records......
            String SqlSelectRecords = ("select g.[Year] ,g.[EffectiveDate],g.[Volume] ,g.[Currency] ,g.[RateType] ,g.[MinimumRate] ,i.[State], i.[Location], i.[IndexEffective], i.[Page], i.[PageDescription] FROM[MultiYearWageGuide]as g join[MultiYearWageIndex] as i ON g.Volume= i.Volume AND g.Page = i.Page AND g.LocCode = i.Location WHERE store= " + int.Parse(textBox2.Text));
                        
            // Assign the variable cmd the value of the selection with the connection.......
            SqlCommand cmd = new SqlCommand(SqlSelectRecords, con);

            // Assign the DataReader to a variable......
            SqlDataReader sqldr = cmd.ExecuteReader();


            // Keep reading thru the file for values from the selection and move them to the appropriate area of the Form .....                          
              if (sqldr.Read())       
              {
                
                    // Load the DataGrid Table1 with Values from DataBase Table ......

                    dataGridView1.Rows.Add(YearVal, EffDateVal, VolumeVal, CurrVal, RateTypeVal, MinRateVal);

                    // Load the DataGrid Table2 with Values from Database Table.....

                    //dataGridView2.Rows.Add(YearVal, EffDateVal, VolumeVal, CurrVal, RateTypeVal, MinRateVal);


What I have tried:

I have tried everything but dont know enough C# to do this
Posted
Updated 7-Nov-19 9:27am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

At the beginning...

First of all...
String SqlSelectRecords = ("select g.[Year] ,g.[EffectiveDate],g.[Volume] ,g.[Currency] ,g.[RateType] ,g.[MinimumRate] ,i.[State], i.[Location], i.[IndexEffective], i.[Page], i.[PageDescription] FROM[MultiYearWageGuide]as g join[MultiYearWageIndex] as i ON g.Volume= i.Volume AND g.Page = i.Page AND g.LocCode = i.Location WHERE store= " + int.Parse(textBox2.Text));

Such of query is sql injection[^] vulnerable!
Do not use concatenated strings! Use parameterized queries[^] instead!

Second of all...
String is not the same as string! In a short:
Quote:
The string type represents a sequence of zero or more Unicode characters. string is an alias for System.String in .NET.

FOr further details, please see:
String Class (System) | Microsoft Docs[^]
string[^]


As to your question...
I have no idea why do you want to use loop! Use DataSet, DataAdapter as Richard MacCutchan mentioned. You can use a DataTable[^] too.

Tip: use using statement[^] to easily manage disposable objects. For example:

//first
dataGridView1.AutoGenerateColumns = true;

//then
DataTable dt = new DataTable();
string sCs = "...";
string sCommand = "SELECT ... WHERE store = @aStore";
using (SqlConnection connection = new SqlConnection(sCs))
    using(SqlCommand command = new SqlCommand(sCommand, connection))
    {
        connection.Open();
        command.Parameters.AddWithValue("@aStore", textBox2.Text)
        using(SqlDataReader reader = command.ExecuteReader())
            dt.Load(reader);
    }

//finally...
dataGridView1.DataSource = dt;
   
Comments
Debashis3850 7-Nov-19 15:44pm
   
I am close with the coding I specified the only problem I am having is the program it is NOT reading the second record and putting the values into the DataGridView. It only displayed the first record.

These are the two records I expected to see in my DataGridView:

Year Effective Date volume Currency RateType MinimumRate
2018 2018-07-01 A US H 16.81
2018 2018-07-01 A US H 19.48




But when I run my program, I get the following in my DataGridView:

Year Effective Date volume Currency RateType MinimumRate
2018 2018-07-01 A US H 16.81
Debashis3850 7-Nov-19 15:45pm
   
Notice it is only inserting the first record from the database table but not the second record.....any ideas of why? I know I am close....LOL
Maciej Los 7-Nov-19 16:13pm
   
So, check out what the database returns when you run your query...
Richard MacCutchan 8-Nov-19 2:53am
   
Because you only read a single record from the returned set. You should use a while loop that continues until reader.Read() returns false.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Use a DataAdapter and DataSet that binds to the DataGridView. See Populating a DataSet from a DataAdapter | Microsoft Docs[^].

[edit]
Alternatively just a simple while loop:
while (sqldr.Read())       
{
    // Load the DataGrid Table1 with Values from DataBase Table ......
    // use the reader.GetXXX methods to find the different values
    // or index to each field - see below
    DatadataGridView1.Rows.Add( insert values here ...);
}

SqlDataReader.Item[Int32] Property (System.Data.SqlClient) | Microsoft Docs[^]

[/edit]
   
v2
Comments
Maciej Los 7-Nov-19 16:06pm
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100