SqlDataReader Windows Form

[RJL1]

I'm working on a form that will return information from a DB. When the user presses the search button it wil lexecute a Stored Procedure (SP_WEPK_X12_LOOKUP_INBOUND01).

This stored procedure will return 6 columns

EDI_TRANSACTION
SEND_PARTNER
REFERENCE_ID
RECEIVED_ON
SA_CTRL_NUM
ACK

I like ot execute the store procedure and grab the results (6 firlds) and set each one to its own variable (string) so I can use them later on the application.

Here is the code I have that executes the stored procedure.

        {
            SqlCommand cmd = new SqlCommand();

            SqlParameterCollection sqlParameters = (SqlParameterCollection)cmd.Parameters;

            sqlParameters.AddWithValue("REFID", txtRefID.Text);

            var m = cmd.CommandText = "SP_WEPK_X12_LOOKUP_INBOUND01";
            cmd.CommandType = CommandType.StoredProcedure;

            cs.Open();
            cmd.Connection = cs;
            cmd.ExecuteNonQuery();
            cs.Close();                
        }

Any help with this is appreciated
Thanks RJL1

 

[bakershawnm]

I found this solution to work best for me and use it quite extensively:

SqlConnection CN = new SqlConnection(connectionString);
SqlCommand sqlcmd = new SqlCommand("", CN);

CN.Open();
try
{
sqlcmd.CommandText = "SELECT dbo.Drwng.Drawing, MAX(dbo.MfgPlan_WBRRs.ChngLtr) AS ADCN, dbo.Drwng.Eff, " +
"dbo.Drwng.DrawingTitle " +
"FROM dbo.Applcblty INNER JOIN dbo.Drwng " +
"ON dbo.Applcblty.Drawing = dbo.Drwng.Drawing " +
"AND dbo.Applcblty.ExtrctdDate = dbo.Drwng.Date_Extracted " +
"INNER JOIN dbo.MfgPlan_WBRRs " +
"ON dbo.Drwng.Drawing = dbo.MfgPlan_WBRRs.Drawing " +
"AND dbo.Drwng.Date_Extracted = dbo.MfgPlan_WBRRs.Date_Extracted " +
"WHERE dbo.Applcblty.EffectivityBegin = '" + effTextBox.Text.ToString() + "' " +
"GROUP BY dbo.Drwng.Drawing, dbo.Drwng.DrawingTitle, dbo.Applcblty.EffectivityBegin, " +
"dbo.Drwng.Date_Extracted " +
"HAVING dbo.Drwng.Drawing = '" + Drwng + "'";

SqlDataReader DrwngSmry = sqlcmd.ExecuteReader();

while (DrwngSmry.Read())
{
prgrmTextBox.Text = "MMA";
drwngTextBox.Text = DrwngSmry[0].ToString();
ADCN = DrwngSmry[1].ToString();
adcnTextBox.Text = DrwngSmry[3].ToString();
if (DrwngSmry[2].ToString().IndexOf("UNPRESSURIZED") > 0 ||
DrwngSmry[2].ToString().IndexOf("UPRESSURIZED") > 0)
{
UnPress.Checked = true;
}
else
{
Pressurized.Checked = true;
}
_8to12Inch.Checked = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
CN.Close();

And I even discovered an error in this code by posting it here. Not so much an error as a failure to do something.

 

[ralphtrent]

right off the bat, I see that you are calling ExecuteNonQuery(). This will not return you any data, just the number of rows effected.

If you are only expecting one row back why not write this code

[green]//declare variable as global so they can be used in other parts of the code[/green]          
string field1, field2, field3, field4, field5, field6;
private void dataGetAndSet()
{
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter sda = new SqlDataAdapter();
            SqlParameterCollection sqlParameters = (SqlParameterCollection)cmd.Parameters;
            sqlParameters.AddWithValue("REFID", txtRefID.Text);
            var m = cmd.CommandText = "SP_WEPK_X12_LOOKUP_INBOUND01";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cs;
            sda.SelectCommand = cmd;
            
            DataTable dtResults = new DataTable();          
            [green]//Fill the datatable with the SP results[/green]
            sda.Fill(dt)
            [green]//Do we have any rows, if so continue[/green]            
            if(dt.Rows.Count> 0)
            {
                        [green]//Value fields with the data[/green]
                        field1 = Convert.ToString(dt.Rows[0][0]);
                        field2 = Convert.ToString(dt.Rows[0][1]);
                        field3 = Convert.ToString(dt.Rows[0][2]);
                        field4 = Convert.ToString(dt.Rows[0][3]);
                        field5 = Convert.ToString(dt.Rows[0][4]);
                        field6 = Convert.ToString(dt.Rows[0][5]);
            }
            cs.Close();                
        }