none
How to get Last Record from Table in a veriable ? RRS feed

  • Question

  • Dear Sir(s),

    how to show Last++ user_id in message box ? I am using this code , its show in gridview but I need this value in a variable.

    can it is possible  that I can get last user_id++ in a variable without using grid ?

    Please help.

     con.Open();
                string query = "Select CASE WHEN Max(user_id) IS NOT NULL THEN Max(user_id) + 1 ELSE 1 END as maxnumber from user_info";
                SqlDataAdapter SDA = new SqlDataAdapter(query, con);
                DataTable dt = new DataTable();
                SDA.Fill(dt);
                dataGridView1.DataSource = dt;
                con.Close();




    • Edited by aw88750 Tuesday, March 13, 2018 10:06 AM
    Tuesday, March 13, 2018 4:17 AM

All replies

  • Hi aw88750,

    You can use the DataRow to store the last value from data table like below:

    DataRow lstRow = dt.Rows[dt.Rows.Count-1];

    The navigate the values in datarow and assign it into messagebox. Hope this will help you.


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    • Proposed as answer by Cor Ligthert Wednesday, March 14, 2018 11:15 AM
    Tuesday, March 13, 2018 4:36 AM
    Moderator
  • @Sabah Shariq , thanks for your reply, can it is  possible  that I can get last++ user_id in a variable without using grid ?
    • Edited by aw88750 Tuesday, March 13, 2018 4:56 AM
    Tuesday, March 13, 2018 4:45 AM
  • Can you please tell what do you mean by "last++ user_id"?

    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Tuesday, March 13, 2018 5:20 AM
    Moderator
  • in table user_id  is (filed name)  last means last record ++ mean add 1 , e.g last record  user_id is 3  I need it store 4 in a variable .

    :) Thanks for giving me your time.

    Regards,

    A.Waheed



    • Edited by aw88750 Tuesday, March 13, 2018 5:42 AM
    Tuesday, March 13, 2018 5:40 AM
  • You need to do something like below iterate through Rows cell value. I guess you do not need DGV and DataRow, you can do this within data table value, you just need to know how to iterate through column Rows cell value.. Hope this will help you.

            for (int i = 0; i < dt.Rows.Count-1; i++) //Looping through rows
            {
                var myValue = dt.Rows[i]["MyFieldName"]; //Getting my field value
    
            }


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    • Proposed as answer by ID GO Tuesday, March 13, 2018 9:04 AM
    • Unproposed as answer by ID GO Wednesday, March 14, 2018 7:08 AM
    Tuesday, March 13, 2018 6:51 AM
    Moderator
  • Thanks .

    Regards,

    A.Waheed

    Tuesday, March 13, 2018 6:54 AM
  • Hi aw88750,

    If your question has been solved, please mark the solution as answer. This will make answer searching in the forum and be beneficial to community members.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 14, 2018 5:36 AM
    Moderator
  • @Wendy Zang ,

    Thanks for advise, I will do Mark as answer when my problem will resolve.

    Regards,

    A.Waheed

    Wednesday, March 14, 2018 6:30 AM
  • @Sabah Shariq ,

    I am using below code now I need last user_id +1 in a variable. this code its working fine but in this code user enter User_ID

    actually  I want when End-user click on save button , its goes to table and index on user_id (field name) and add +1 and save it and show message to End-user that your record saved with this User ID.

     private void button1_Click(object sender, EventArgs e)
            {
               
                con.Open(); // for Connection Open
                string query = "INSERT INTO user_info (user_id,user_name,password,user_dept,user_desig,roles) VALUES ('" + txtUserID.Text + "','" + txtUserName.Text + "','" + txtPassword.Text + "','" + txtDepartment.Text + "','" + txtDesign.Text + "','" + txtRole.Text + "')";
                // query is variable contain Insert syntax for inserting textbox value into table
                SqlDataAdapter SDA = new SqlDataAdapter(query , con); // SDA is variable contain Query and connection values.
                SDA.SelectCommand.ExecuteNonQuery(); // for execute query by using con variable  
                con.Close(); // for Connection close
                MessageBox.Show("Record Saved"); // show message box to End-User
    
                this.ActiveControl = txtUserID; // SetFocus to txtUserID text box
                          
    
            }



    • Edited by aw88750 Wednesday, March 14, 2018 7:47 AM
    Wednesday, March 14, 2018 6:39 AM
  • Here is a pattern that will work for one person adding records, if more than one person this is not the way to go.

    using System;
    using System.Data.SqlClient;
    
    namespace WindowsFormsApp1
    {
        public class Operations
        {
            /// <summary>
            /// Replace with your SQL Server name
            /// </summary>
            private string Server = "KARENS-PC";
            /// <summary>
            /// Database in which data resides
            /// </summary>
            private string Catalog = "ForumExamples";
            /// <summary>
            /// Connection string for connecting to the database
            /// </summary>
            private string ConnectionString;
            /// <summary>
            /// Setup the connection string
            /// </summary>
            public Operations()
            {
                ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
            }
            public bool InsertDepartment(string pDepartment, ref int pIdentifier)
            {
                using (var cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (var cmd = new SqlCommand { Connection = cn })
                    {
                        try
                        {
                            cn.Open();
    
                            cmd.CommandText = @"INSERT INTO dbo.Departments (Name) VALUES (@Name);" +
                                              "SELECT CAST(scope_identity() AS int);";
    
                            pIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                            return true;
                        }
                        catch (Exception)
                        {
                            // I'm not handling exceptions 
                            // as that is not part of the question.
                            return false;
                        }
                    }
                }
            }
        }
    }
    

    Form code

    using System;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var ops = new Operations();
                var pId = 0;
                // if this is a single user system let's say the last id
                // prior to this is 10, on success pId will be 11. 
                if (ops.InsertDepartment("Human Resources", ref pId))
                {
                    // only for single user system
                    pId += 1; 
                    // pId would now be 12
                }
            }
        }
    }
    
    The part to focus on is highlighted, two queries, one to insert, one to get the new primary key. This is followed by ExecuteScalar to return the new key from the SELECT part of the command.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 14, 2018 9:41 AM
    Moderator

  •         for (int i = 0; i < dt.Rows.Count-1; i++) //Looping through rows
            {
                var myValue = dt.Rows[i]["MyFieldName"]; //Getting my field value
    
            }


    Thanks,
    Sabah Shariq


    Hi Sabah, 

    This is stupid code, it gives the same result as in your first answer. The OP did probably not even try it. 


    Success
    Cor


    Wednesday, March 14, 2018 11:18 AM
  • thanks for your time Cor Ligther, are you understand my question ? if yes please help me to resolve my issue please.

    I am using below code now I need last user_id +1 in a variable. this code its working fine but in this code user enter User_ID

    actually  I want when End-user click on save button , its goes to table and index on user_id (field name) and add +1 and save it and show message to End-user that your record saved with this User ID.

    private void button1_Click(object sender, EventArgs e)
            {
               
                con.Open(); // for Connection Open
                string query = "INSERT INTO user_info (user_id,user_name,password,user_dept,user_desig,roles) VALUES ('" + txtUserID.Text + "','" + txtUserName.Text + "','" + txtPassword.Text + "','" + txtDepartment.Text + "','" + txtDesign.Text + "','" + txtRole.Text + "')";
                // query is variable contain Insert syntax for inserting textbox value into table
                SqlDataAdapter SDA = new SqlDataAdapter(query , con); // SDA is variable contain Query and connection values.
                SDA.SelectCommand.ExecuteNonQuery(); // for execute query by using con variable  
                con.Close(); // for Connection close
                MessageBox.Show("Record Saved"); // show message box to End-User
    
                this.ActiveControl = txtUserID; // SetFocus to txtUserID text box
                          
    
            }

    Regards,

    A.Waheed


    • Edited by aw88750 Thursday, March 15, 2018 4:27 AM
    Thursday, March 15, 2018 4:25 AM

  •         for (int i = 0; i < dt.Rows.Count-1; i++) //Looping through rows
            {
                var myValue = dt.Rows[i]["MyFieldName"]; //Getting my field value
    
            }


    Thanks,
    Sabah Shariq


    Hi Sabah, 

    This is stupid code, it gives the same result as in your first answer. The OP did probably not even try it. 


    Success
    Cor


    The OP want's a way to get the last value/ID for a column and he will add "1" the result, I am just guiding the approach. Its up to OP how to write the full code.

    My first answer returns the total row count. Then I asked the OP what he meant by "last++ user_id" and my second answer is to guide a way to access the last value of a column.


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Thursday, March 15, 2018 7:24 PM
    Moderator
  • Hi Waheed,

    Let me guess if you are asking to achieve this solution. Lets say you want to save the following data:

    ID: 1

    Value: ABC

    So, when user press the save button in DB value will be save like this: Add 1 with the ID that user enter and output will be below:

    ID   Value

    2     ABC

    New input ID:5 Value: DEF... output will be:

    ID   Value

    2    ABC

    6    DEF

    Is this the goal you are trying to achieve?


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Thursday, March 15, 2018 7:32 PM
    Moderator
  • Yes Sabah Shariq thanks for understanding .

    Regards,

    A.Waheed

    Monday, March 19, 2018 5:34 AM
  • We can guide you what would be the approach but you have figure yourself how to implement this into your code. Just remove the ID parameter from your query. SQL will take care of it. Also,
    > Have you tried explicitly specifying field into AutoIncrement? 
    > Is ID column is primary key column? if so, you can specify it as auto increment in DB

    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]



    Friday, March 23, 2018 11:55 AM
    Moderator
  • Take another look at Karen's reply, and her use of the cmd.ExecuteScalar() ... I think that using ExecuteScalar() is definitely the way you want to go with this.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, March 24, 2018 1:49 AM
    Moderator
  • ID column is primary key YES and no autoincrement active ,  I don't want to autoincremtn activate because I want to do it manually when the SAVE button pressed , at that time I want it would check last record and add 1 and save it. in other form I will use this method and want to store SAL-2018-0001 , 0002 and so on  that's it Sabah Shariq.

    Thanks for your time .

    Monday, April 2, 2018 4:37 AM
  • OK, so you want to know what the new user_id will be before you Save the record, because you need to use that in the parameters for the INSERT. So, you would still want to use the ExecuteScalar() method, but you could do this with two calls to the database, like this:

    private void button1_Click(object sender, EventArgs e)
    {
       
        con.Open(); // for Connection Open
        string query = "SELECT TOP 1 user_id FROM user_info ORDER BY user_id DESC";
        SqlCommand sc = new SqlCommand(query, con);
        int user_id = (int)sc.ExecuteScalar();
        user_id++;  // add 1 to it
        
        query = "INSERT INTO user_info (user_id,user_name,password,user_dept,user_desig,roles) VALUES ('" + user_id + "','" + txtUserName.Text + "','" + txtPassword.Text + "','" + txtDepartment.Text + "','" + txtDesign.Text + "','" + txtRole.Text + "')";
        
        // You don't need to use a SqlDataAdapter for these types of commands
        //SqlDataAdapter SDA = new SqlDataAdapter(query , con); // SDA is variable contain Query and connection values.
        //SDA.SelectCommand.ExecuteNonQuery(); // for execute query by using con variable  
    
        sc.CommandText = query;
        sc.ExecuteNonQuery();
        con.Close(); // for Connection close
        MessageBox.Show("Record Saved, new User_ID is: " + user_id); // show message box to End-User
    
        // Maybe you want to put that in the TextBox:
        this.txtUserID.Text = user_id;
        this.ActiveControl = txtUserID; // SetFocus to txtUserID text box
    }
    

    Also, you should really be using Parameters instead of putting the values directly into the query string. Like this: 


       query = "INSERT INTO user_info (user_id,user_name,password,user_dept,user_desig,roles) VALUES (@UserID, @UserName, @Password, @Department, @Design, @Role)";
        
        sc.CommandText = query;
        sc.Parameters.Add("@UserID", SqlDbType.Int).Value = user_id;
        sc.Parameters.Add("@UserName", SqlDbType.VarChar).Value = txtUserName.Text;
        sc.Parameters.Add("@Password", SqlDbType.VarChar).Value = txtPassword.Text;
        sc.Parameters.Add("@Department", SqlDbType.VarChar).Value = txtDepartment.Text;
        sc.Parameters.Add("@Design", SqlDbType.VarChar).Value = txtDesign.Text;
        sc.Parameters.Add("@Role", SqlDbType.VarChar).Value = txtRole.Text;
        sc.ExecuteNonQuery();


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, April 8, 2018 4:49 PM
    Moderator
  • Hey @aw88750  ... it's been a bit over 3 weeks since you were last here on this thread. Have any of these replies helped you resolve your problem? If so, it would be nice to come back and mark the response(s) as an answer.

    Or, have you resolved the problem yourself? If so, let us know how you solved it!

    If you have started a new thread with a similar question and found the answer there, please post a link to that thread here.

    Coming back here to "close the circle" could help others with similar questions.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, April 27, 2018 3:08 PM
    Moderator