hw to display a autogenerate id value to text box? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

hw to display a autogenerate id value to text box?

Author

Topic

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-06 : 03:21:20

Hi,
since i'm new to sql server and i need to finish my final year project,hope someone can help me.
My problem now is i use sql server to auto generate an id as Employee ID. At register page, employee no need to enter the employee ID. How to write the code to retrieve the auto generate ID from sql server to display at the textbox??

Can anyone help me???i use sql server 2008 and c# asp.net.

Below is my code.
HTML code
<asp:Label ID="lblRegister" runat="server" Font-Bold="True" Font-Size="Large"
Text="Register"></asp:Label>
<table>
<tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
<asp:Label ID="lblInfo" runat="server" Width="361px" Font-Bold="True"
ForeColor="Red"></asp:Label></td>
</tr>
<tr>
<td style="width: 100px">
Employee ID</td>
<td style="width: 100px">
<asp:TextBox ID="txtEmpID" runat="server" ReadOnly="True" Width="148px"></asp:TextBox>
</td>
</tr>
<tr>
<td style="width: 100px">
Password</td>
<td style="width: 100px">
<asp:TextBox ID="txtPwd" runat="server" TextMode="Password" Width="149px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
<asp:Button ID="btnRegister" runat="server" Text="Register" Width="110px"
OnClick="btnRegister_Click" /></td>
</tr>
<tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtPwd"
ErrorMessage="RequiredFieldValidator" Width="363px">Password should not be empty.</asp:RequiredFieldValidator><br />
</td>
</tr>
</table>

code behind
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Register : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

public string GetConnectionString()
{
//we will set up the configuration which will call our
//web.config file to provide the database details because
//in configuration file we have created the <connectionStrings>
//in the process we draged and droped. It creates automatically.
//We normally put the database details in web.config file or
//machine.config file because it is very sensitive information
//usually there IP address of remote database, passwords and
//user names are stored.

return System.Configuration.ConfigurationManager.ConnectionStrings["cwConnectionString1"].ConnectionString;
//in above line "onlineapplicationformConnectionString1" is
//our configuration name which is inside the web.config file.
}

//declaring function to insert the value

private void execution(string EmployeeID, string Password)
{
//In above line we declaring different variables same as backend
SqlConnection conn = new SqlConnection(GetConnectionString());
//In above line we are calling connection
//string function which is defined already on top
string sql = "INSERT INTO Profile (EmployeeID, Password) VALUES "
+ " (@EmployeeID, @Password)";
//In above lines we are just storing the sql commands which
//will insert value in onlineapplication named table,
//using variable named sql.
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
//In above lines we are opening the connection to work and
//also storing connection name and sql command in cmd variable
//which has 'SqlCommand' type.
SqlParameter[] pram = new SqlParameter[2];
//In above lines we are defining 4 sql parameters will be use
//In below lines we will not disscuss about id column
pram[0] = new SqlParameter("@EmployeeID", SqlDbType.VarChar, 10);
pram[1] = new SqlParameter("@Password", SqlDbType.VarChar, 20);
//Now we set-uped all fiels in database in above lines
//Now we will set-up form field
pram[0].Value = EmployeeID;
pram[1].Value = Password;
//Now create loop to insert
for (int i = 0; i < pram.Length; i++)
{
cmd.Parameters.Add(pram[i]);
}
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex_msg)
{
//Here will be catch elements
string msg = "Error occured while inserting";
msg += ex_msg.Message;
throw new Exception(msg);
}
finally
{
//Here will be fially elements
conn.Close();
}

}

//here is from click event starts and check for duplication account

protected void btnRegister_Click(object sender, EventArgs e)
{
SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = ConfigurationManager.ConnectionStrings["cwConnectionString1"].ToString();

sds.SelectParameters.Add("EmployeeID", TypeCode.String, this.txtEmpID.Text);
sds.SelectParameters.Add("Password", TypeCode.String, this.txtPwd.Text);

//sds.InsertParameters.Add("name", TypeCode.String, this.name.Text);
//sds.InsertParameters.Add("username", TypeCode.String, this.username.Text);
//sds.InsertParameters.Add("password", TypeCode.String, this.password.Text);
//sds.InsertParameters.Add("emailid", TypeCode.String, this.emailid.Text);

sds.SelectCommand = "SELECT [EmployeeID], [Password] FROM [Profile] WHERE [EmployeeID] = @EmployeeID";

DataView dv = (DataView)sds.Select(DataSourceSelectArguments.Empty);

if (dv.Count != 0)
//{
// this.lblinfo.ForeColor = System.Drawing.Color.Red;
// this.lblinfo.Text = "The User Name already Exist!";
// return;
//}
//else
//{
//will call the insert function here from above function
execution(txtEmpID.Text, txtPwd.Text);
//this.SqlDataSource1.Insert();
this.lblInfo.Text = "Your account has been created, you can login now";
this.txtEmpID.Text = "";
this.txtPwd.Text = "";
Response.Redirect("RegisterDetail.aspx");
//}
}
}

Thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-06 : 13:21:32

create a procedure for insertion of values and add a output variable for returning the generating id through it. then in application use a output parameter to get the returned value and display it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-06 : 22:18:04

quote:
Originally posted by visakh16

create a procedure for insertion of values and add a output variable for returning the generating id through it. then in application use a output parameter to get the returned value and display it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thanks for reply...
but i can't get your means...
can you give me more details of your explanation???
i'm sorry, because i really new to asp.net and sql server...this is my 1st project...
thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources