3 Replies - 2971 Views - Last Post: 04 April 2013 - 07:25 AM Rate Topic: -----

#1 et_leen  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 22-March 13

ID increment from last data

Posted 22 March 2013 - 01:31 AM

Hi there.

I tried to add a row of data; But the id is will generate automatically by asp.net (increase +1 from the last data).
I tried to used dropdown to display the id from last row in the database and its run smoothly.
But I don't want to enable end user to see the id.
Below is my code:



<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">
 
  protected void Page_Load(object sender, EventArgs e)
  {
  } 

  protected void savebtn_Click(object sender, EventArgs e)
  {
     // Create the sql connection
      using (SqlConnection dataConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].ToString()))
      {
          // select statement to retrieve the Max ID
          SqlCommand cmd = new SqlCommand("SELECT Max(ID) as MAXID FROM users", dataConnection);
          
          //open the connection
          dataConnection.Open();
          
          string sqlQuery = "INSERT INTO USERS (Name, Country, Type, ID)";
          sqlQuery += " VALUES (@Name, @Country, @type, @ID)";

          //convert the selection id to integer
          //this code is to +1 the last row id
          int SelectedValue = Convert.ToInt32(ID2.SelectedValue);
          SelectedValue = SelectedValue + 1; 
          
         //add the value by row
          using (SqlCommand dataCommand = new SqlCommand(sqlQuery, dataConnection))
          {
             dataCommand.Parameters.AddWithValue("Name", Name.Text);
             dataCommand.Parameters.AddWithValue("Country", country.Text);
             dataCommand.Parameters.AddWithValue("Type", type.Text);
              // add the running ID
             dataCommand.Parameters.AddWithValue("ID", SelectedValue);
 
             dataCommand.ExecuteNonQuery();
             dataConnection.Close();
          }
    }  
 }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head2" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server" method="post">
   
    <div>
    Id: <asp:TextBox ID="ID" runat="server"></asp:TextBox>
        <br />
        
         <!--I don't want to display the dropdown to end user-->
        <asp:DropDownList ID="ID2" runat="server" DataSourceID="SqlDataSource1" DataTextField="ID" DataValueField="ID" Visible="true"> 
        </asp:DropDownList>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDbConn %>" SelectCommand="Select MAX(ID) as ID from users"></asp:SqlDataSource>
        <br />  

    Name : <asp:TextBox ID="Name" runat="server"></asp:TextBox>
        <br />
    Nationality :&nbsp;
        <asp:DropDownList ID="country" runat="server" DataSourceID="country_dbs" DataTextField="CountryName" DataValueField="CountryCode">
        </asp:DropDownList>
        
        <asp:SqlDataSource ID="country_dbs" runat="server" ConnectionString="<%$ ConnectionStrings:MyDbConn %>" SelectCommand="SELECT [CountryCode], [CountryName] FROM [Country]"></asp:SqlDataSource>
        
        <br />
        Registration type:&nbsp;

        <asp:DropDownList ID="type" runat="server" DataSourceID="reg_type_dbs" DataTextField="UserType" DataValueField="ID">
        </asp:DropDownList>
        <asp:SqlDataSource ID="reg_type_dbs" runat="server" ConnectionString="<%$ ConnectionStrings:MyDbConn %>" SelectCommand="SELECT [ID], [UserType] FROM [UserType]"></asp:SqlDataSource>

        <asp:Button ID="savebtn" runat="server" Text="save" onclick="savebtn_Click" style="width: 40px" />
      </div>
    </form>
</body>
</html>



Is This A Good Question/Topic? 0
  • +

Replies To: ID increment from last data

#2 GrooN  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 20
  • Joined: 27-February 13

Re: ID increment from last data

Posted 22 March 2013 - 01:43 AM

I am not sure if there is an easier way of doing this in .net. But you can get the last inserted ID in a database connection from the following SQL command:
SELECT LAST_INSERT_ID()

Hope this helps you :)
Was This Post Helpful? 0
  • +
  • -

#3 et_leen  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 22-March 13

Re: ID increment from last data

Posted 22 March 2013 - 02:07 AM

Hi Groon,

Thank you for the fast reply.

But I little bit blur how to used the
SELECT LAST_INSERT_ID()



Sorry about this. Can you explain it? :surrender:
Was This Post Helpful? 0
  • +
  • -

#4 merlin'sbeard  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 20
  • Joined: 27-December 12

Re: ID increment from last data

Posted 04 April 2013 - 07:25 AM

So if i understand correctly you managed to retrieve the id but you do not want the user to see it? I noticed you used the same id for the 'DataTextField' and 'DataValueField', if u do not want anything displayed in the drop down then why have it in the first place? Why not just hold the id in a variable?

Write your own function to retrieve the max(id) + 1 from your database and hold it in a variable.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1