6 Replies - 1050 Views - Last Post: 18 June 2016 - 02:30 AM

#1 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 149
  • View blog
  • Posts: 1,045
  • Joined: 17-November 10

Custom Made Auto Generate ID

Posted 13 June 2016 - 11:53 PM

Hello guys;

I am having an issue in regards to generating the DB IDs that were connected to the ASP.NET Webform.

The program has logical issue that I have been trying to deal with for some time now and I think it is time that I should ask for assistance.

The issue is, it would increase the ID as per, but than it would add the values twice. It would display the two different IDs as doubled than the last time.

namespace DummyProj1
{
    public partial class LoginInfo : System.Web.UI.Page
    {
        static string myConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        private void GenerateID()
        {
            SqlConnection myConnection = new SqlConnection(myConnectionString);

            myConnection.Open();
            SqlCommand cmd = new SqlCommand("Select Count(S_ID) from Student_Name", myConnection);
            cmd.CommandText = "Select Count(S_ID) from Student_Name";
            int addOneS_ID_Table1 = Convert.ToInt32(cmd.ExecuteScalar());
            myConnection.Close();
            addOneS_ID_Table1++;
            lblstdID.Text = lblstdID.Text + addOneS_ID_Table1.ToString();

            //myConnection.Open();
            //SqlCommand cmd2 = new SqlCommand ("Select Count(P_ID) from Student_Pass", myConnection);
            //int addOneP_ID_Table2 = Convert.ToInt32(cmd2.ExecuteScalar());
            //myConnection.Close();
            //addOneP_ID_Table2++;
            //lblstdPass.Text = lblstdID.Text + addOneP_ID_Table2.ToString();

            myConnection.Open();
            cmd.CommandText = "Select Count(P_ID) from Student_Pass";
            int addOneP_ID_Table2 = Convert.ToInt32(cmd.ExecuteScalar());
            myConnection.Close();
            addOneP_ID_Table2++;
            lblstdPass.Text = lblstdPass.Text + addOneP_ID_Table2.ToString();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                GenerateID();
            }
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(myConnectionString);

            string myQuery = "Insert into Student_Name(S_ID,STUDENT_NAME) VALUES" + "(@S_ID,@STUDENT_NAME)";

            SqlCommand cmd = new SqlCommand(myQuery, myConnection);

            cmd.Parameters.Add("@S_ID", SqlDbType.Int).Value = Convert.ToInt32(lblstdID.Text);
            cmd.Parameters.Add("@STUDENT_NAME", SqlDbType.VarChar).Value = txtstdName.Text;

            if(myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }

            cmd.ExecuteNonQuery();
            //cmd.Parameters.Clear();

            //Second Table

            cmd.CommandText = "Insert into Student_Pass(P_ID,PASSWORD) VALUES" + "(@P_ID,@PASSWORD)";
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add("@P_ID", SqlDbType.Int).Value = Convert.ToInt32(lblstdPass.Text);
            cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = txtStdPass.Text;

            cmd.ExecuteNonQuery();
            //cmd.Parameters.Clear();
            myConnection.Close();
            //GenerateID();
            lblResult.Text = "Successfully Saved";
            GridView1.DataBind();
        }
    }
}



I would really appreciate any help that I can get. Thanks.

This post has been edited by Nitewalkr: 13 June 2016 - 11:53 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Custom Made Auto Generate ID

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13562
  • View blog
  • Posts: 54,110
  • Joined: 12-June 08

Re: Custom Made Auto Generate ID

Posted 14 June 2016 - 07:03 AM

Is there any reason why you do not have the table auto generating ids, and/or using the auto increment?
Was This Post Helpful? 0
  • +
  • -

#3 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 149
  • View blog
  • Posts: 1,045
  • Joined: 17-November 10

Re: Custom Made Auto Generate ID

Posted 14 June 2016 - 09:08 AM

Updated code with the same issue.

 public partial class LoginInfo : System.Web.UI.Page
    {
        static string myConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        private void GenerateID()
        {
            //SqlConnection myConnection = new SqlConnection(myConnectionString);
            //string myQuery1 = "Select Count(S_ID) from Student_Name";
            //SqlCommand cmd = new SqlCommand(myQuery1, myConnection);
            //myConnection.Open();
            //int addOneS_ID_Table1 = Convert.ToInt32(cmd.ExecuteScalar());
            //myConnection.Close();
            //addOneS_ID_Table1++;
            //lblstdID.Text = lblstdID.Text + addOneS_ID_Table1.ToString();

            //myConnection.Open();
            //cmd.CommandText = "Select Count(P_ID) from Student_Pass";
            //int addOneP_ID_Table2 = Convert.ToInt32(cmd.ExecuteScalar());
            //myConnection.Close();
            //addOneP_ID_Table2++;
            //lblstdPass.Text = lblstdPass.Text + addOneP_ID_Table2.ToString();
           
            SqlConnection myConnection = new SqlConnection(myConnectionString);
            string myQuery1 = "Select Count(S_ID) from Student_Name";
            SqlCommand cmd = new SqlCommand(myQuery1, myConnection);
            myConnection.Open();
            int addOneS_ID_Table1 = Convert.ToInt32(cmd.ExecuteScalar());
            myConnection.Close();
            addOneS_ID_Table1 += 1;
            lblstdID.Text = Convert.ToString(Convert.ToInt32(addOneS_ID_Table1));

            myConnection.Open();
            cmd.CommandText = "Select Count(P_ID) from Student_Pass";
            int addOneP_ID_Table2 = Convert.ToInt32(cmd.ExecuteScalar());
            myConnection.Close();
            addOneP_ID_Table2 += 1;
            lblstdPass.Text = Convert.ToString(Convert.ToInt32(addOneP_ID_Table2));
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                GenerateID();
            }
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(myConnectionString);

            string myQuery = "Insert into Student_Name(S_ID,STUDENT_NAME) VALUES" + "(@S_ID,@STUDENT_NAME)";

            SqlCommand cmd = new SqlCommand(myQuery,myConnection);
           
            cmd.Parameters.Add("@S_ID", SqlDbType.Int).Value = lblstdID.Text;
            cmd.Parameters.Add("@STUDENT_NAME", SqlDbType.VarChar).Value = txtstdName.Text;

            if(myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }

            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            //Second Table

            cmd.CommandText = "Insert into Student_Pass(P_ID,PASSWORD) VALUES" + "(@P_ID,@PASSWORD)";
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add("@P_ID", SqlDbType.Int).Value = lblstdPass.Text;
            cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = txtStdPass.Text;

            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            myConnection.Close();
            //GenerateID();
            lblResult.Text = "Successfully Saved";
            GridView1.DataBind();
        }
    }


Various valid reasons:

-Our professor requested us not to use the Auto Generated ID but create a function that Auto Increments. According to him it is a better practice.

-Understanding this will help understanding how it will work with the Master Detail procedures.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13562
  • View blog
  • Posts: 54,110
  • Joined: 12-June 08

Re: Custom Made Auto Generate ID

Posted 14 June 2016 - 09:11 AM

Why are you using 'count'? If you want the next id then it would be best to grab 'max' or find the last entered and add one.
Was This Post Helpful? 0
  • +
  • -

#5 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 149
  • View blog
  • Posts: 1,045
  • Joined: 17-November 10

Re: Custom Made Auto Generate ID

Posted 15 June 2016 - 09:16 PM

Thanks sir Modi. I did what you suggested by using Max and even TOP 1 [ID] [table] ORDERED BY. it will give the same result. I looked at the values in the database, they look as expected to be added, it is not adding double there, but it is showing that I added twice in grid view. hmm.

I am sure you guys use stored procedures instead of this.

So I am going to get started on the actual projects without the grid view. I will work on the sqlDatasource and gridview later.

This post has been edited by Nitewalkr: 15 June 2016 - 09:19 PM

Was This Post Helpful? 0
  • +
  • -

#6 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 149
  • View blog
  • Posts: 1,045
  • Joined: 17-November 10

Re: Custom Made Auto Generate ID

Posted 17 June 2016 - 11:42 PM

I have another issue related to the same dummy project before I actually get started on the actual project.

Our teacher doesn't like it when I changed it to MAX or TOP 1 ID FROM TABLE ORDER BY, so I have to stick with count. My guess is I will have to use count only once for this assignment, and pray to God that he allows me to use IDENTITY in the next which I will be using.

The issue is, the delete part. When I delete the data from the grid, it doesn`t really count back to the previous ID or if there are no rows left in the grid the label should go back to 1. It just continues on to the next one, am I looking for a loop here, or SQLDataadapter and dataset or Reader.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DummyProj1
{
    public partial class LoginInfo : System.Web.UI.Page
    {
        static string myConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        private void GenerateID()
        {
            SqlConnection myConnection = new SqlConnection(myConnectionString);
            myConnection.Open();
            SqlCommand cmd = new SqlCommand("SELECT COUNT(S_ID) FROM Student_Name", myConnection);
            int addOne = Convert.ToInt32(cmd.ExecuteScalar());
            addOne++;
            lblS_ID.Text = addOne.ToString();
            myConnection.Close();

            /*-----------------------------------------------------------------*/
            //SqlConnection myConnection = new SqlConnection(myConnectionString);

            //SqlCommand cmd = new SqlCommand("SELECT MAX(S_ID) as max_S_ID from Student_Name",myConnection);
            //cmd.CommandType = CommandType.Text;
            //myConnection.Open();
            //lblstdID.Text = Convert.ToString(cmd.ExecuteScalar());
            //cmd.CommandText = "SELECT MAX(P_ID) as max_P_ID FROM Student_Pass";
            //lblstdPass.Text = Convert.ToString(cmd.ExecuteScalar());
            //myConnection.Close();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (PreviousPage != null)
            {
                GridView GridView1 = (GridView)this.PreviousPage.FindControl("GridView1");
                GridView1.DataBind();
                GridViewRow selectedRow = GridView1.SelectedRow;

                lblS_ID.Text = selectedRow.Cells[1].Text;
                txtstdName.Text = selectedRow.Cells[2].Text;
                txtStdPass.Text = selectedRow.Cells[3].Text;
            }
                
                GenerateID();
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            
            SqlConnection myConnection = new SqlConnection(myConnectionString);

            SqlCommand cmd = new SqlCommand("Insert into Student_Name(S_ID,STUDENT_NAME) VALUES (@S_ID,@STUDENT_NAME)",myConnection);
            cmd.Parameters.Add("@S_ID", SqlDbType.VarChar).Value = lblS_ID.Text;
            cmd.Parameters.Add("@STUDENT_NAME", SqlDbType.VarChar).Value = txtstdName.Text;

            if(myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }

            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            GenerateID();
            
            //Second Table
            cmd.CommandText = "Insert into Student_Pass(PASSWORD) VALUES (@PASSWORD) SELECT SCOPE_IDENTITY()";

            cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = txtStdPass.Text;
            
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            myConnection.Close();

            GridView1.DataBind();
            
            
            lblResult.Text = "Successfully Saved";
            
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(myConnectionString);

            SqlCommand cmd = new SqlCommand("UPDATE Student_Name SET STUDENT_NAME = '"+ txtstdName.Text +"' WHERE S_ID ='" + GridView1.SelectedDataKey.Value + "'", myConnection);

            cmd.CommandText = "UPDATE Student_Pass SET PASSWORD = '" + txtStdPass.Text + "' WHERE P_ID ='" + GridView1.SelectedDataKey.Value + "'";

            if (myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }

            cmd.ExecuteNonQuery();

            myConnection.Close();
            //GridView1.DataSource = SqlDataSource1;
            GridView1.DataBind();
            lblResult.Text = "Selection Updated";
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(myConnectionString);

            SqlCommand cmd = new SqlCommand("DELETE FROM Student_Name WHERE S_ID = '"+GridView1.SelectedDataKey.Value +"'",myConnection);
            
            cmd.CommandText = "DELETE FROM Student_Pass WHERE P_ID = '" + GridView1.SelectedDataKey.Value + "'";
            
            if (myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }

            cmd.ExecuteNonQuery();
            cmd.CommandText = "SELECT MAX(S_ID) FROM Student_Name";
            int mySelect = Convert.ToInt32(cmd.ExecuteScalar());
            mySelect--;
           /*FIND A WAY TO BRING THE S_ID TO 1 IF THERE IS NO DATA ROW IN GRID*/
            lblS_ID.Text = mySelect.ToString();

            myConnection.Close();
            
            GridView1.DataBind();
            lblResult.Text = "Selection Deleted";
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            

            Label name, password;

            name = (Label)GridView1.SelectedRow.FindControl("NAME");
            password = (Label)GridView1.SelectedRow.FindControl("PASS");

            txtstdName.Text = name.Text;
            txtStdPass.Text = password.Text;
        }

    }
}

This post has been edited by Nitewalkr: 17 June 2016 - 11:53 PM

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Custom Made Auto Generate ID

Posted 18 June 2016 - 02:30 AM

I've only glanced at your code but there is an obvious contradiction between the use of COUNT and MAX. If you have 10 records and delete the first row the count will be 9 but the max will still be 10.

Rather than deleting rows you could have an extra column, Deleted or DeleteDate. This is common database practice as it should be rare that rows are deleted, typically as part of an archiving process.



You should also be binding to values encapsulated in a class (I don't know how WebForms' binding works), not treating UI properties as your data like this:
SqlCommand cmd = new SqlCommand("UPDATE Student_Name SET STUDENT_NAME = '"+ txtstdName.Text +"' WHERE S_ID ='" + GridView1.SelectedDataKey.Value + "'", myConnection);

and you should use parameters, not concatenate values directly into command statements.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1