×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SqlDataReader Windows Form

SqlDataReader Windows Form

SqlDataReader Windows Form

(OP)
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.

CODE

        {
            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

RE: SqlDataReader Windows Form

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.

RE: SqlDataReader Windows Form

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

CODE

//declare variable as global so they can be used in other parts of the code 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(); //Fill the datatable with the SP results sda.Fill(dt) //Do we have any rows, if so continue if(dt.Rows.Count> 0) { //Value fields with the data 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(); }

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close