Click here to Skip to main content
15,069,020 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
// 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 6-Sep-21 0:54am
v2

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:
C#
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!
At the beginning...

First of all...
C#
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:

C#
//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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900