sql server store procedure

Question

Please am trying to insert into two tables in sql server with store procedure but one table get inserted and the other table do not, Please any help will greatly help.

Mohammed Adamu · Answer

thanks man but this help much so let me throw more light on it for you i have two store procedure SP1: USE [EMAILDB] GO /****** Object: StoredProcedure [dbo].[Sp_InsertUserLoginInfo] Script Date: 05/25/2016 12:48:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Sp_InsertUserLoginInfo] ( @LoginName varchar(50), @Password varchar(50), @Question varchar(100), @Answer varchar(80) OUTPUT ) AS /* SET NOCOUNT ON */ INSERT INTO tbl_User_LoginAccount (LoginName, Password, Question, Answer,Active,Date) VALUES (@LoginName,@Password,@Question,@Answer,1,getdate()) RETURN PS2: USE [EMAILDB] GO /****** Object: StoredProcedure [dbo].[Sp_Insert_RegistrationInfo] Script Date: 05/25/2016 13:53:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[Sp_Insert_RegistrationInfo] ( @LoginId varchar(50), @FirstName varchar(50), @LastName varchar(50), @Country varchar(50), @Email varchar(50), @Phone varchar(20), @DOB datetime, @Gender varchar(10), @Interest varchar(300) OUTPUT ) AS /* SET NOCOUNT ON */ declare @Date datetime set @Date=(select convert(varchar(50),getdate(),103)) INSERT INTO tbl_UserFullInformation (LoginId, FirstName, LastName, Country, Email, Phone, DOB, Gender,Interest,Date) VALUES (@LoginId,@FirstName,@LastName,@Country,@Email,@Phone,@DOB,@Gender ,@Interest,@Date) RETURN and this is the c# code that take the parameters to the SP for insert : using System; using System.Data; using System.Configuration; 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; using System.Data.SqlClient; using SMTPMailClient.DAL; /// /// Summary description for UserRegistrationBL /// public class UserRegistrationBL:Connection { public static DataSet ds; public UserRegistrationBL() { // // TODO: Add constructor logic here // } private string _LoginName, _Password, _Question, _Answer,_LoginTime,_LogoutTime; public string LoginTime { get { return _LoginTime; } set { _LoginTime = value; } } public string LogoutTime { get { return _LogoutTime; } set { _LogoutTime = value; } } public string LoginName { get { return _LoginName; } set { _LoginName = value; } } public string Password { get { return _Password; } set { _Password = value; } } public string Question { get { return _Question; } set { _Question = value; } } public string Answer { get { return _Answer; } set { _Answer = value; } } private string _LoginId, _FirstName, _LastName, _Country, _Email, _Phone, _Gender, _Interest; public string LoginId { get { return _LoginId; } set { _LoginId = value; } } public string FirstName { get { return _FirstName; } set { _FirstName = value; } } public string LastName { get { return _LastName; } set { _LastName = value; } } public string Country { get { return _Country; } set { _Country = value; } } public string Email { get { return _Email; } set { _Email = value; } } public string Phone { get { return _Phone; } set { _Phone = value; } } private DateTime _DOB,_LoginDate,_LogoutDate,_Date; public DateTime Date { get { return _Date; } set { _Date = value; } } public DateTime DOB { get { return _DOB; } set { _DOB = value; } } public DateTime LoginDate { get { return _LoginDate; } set { _LoginDate = value; } } public DateTime LogoutDate { get { return _LogoutDate; } set { _LogoutDate = value; } } public string Gender { get { return _Gender; } set { _Gender = value; } } public string Interest { get { return _Interest; } set { _Interest = value; } } public void InsertUserLoginInfo() { SqlParameter[] p=new SqlParameter[4]; p[0] = new SqlParameter("@LoginName", this._LoginName); p[0].DbType=DbType.String; p[1]=new SqlParameter("@Password",this._Password); p[1].DbType=DbType.String; p[2]=new SqlParameter("@Question",this._Question); p[2].DbType=DbType.String; p[3]=new SqlParameter("@Answer",this._Answer); p[3].DbType=DbType.String; SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "Sp_InsertUserLoginInfo", p); } public void InsertRegistrationInfo() { SqlParameter[] p = new SqlParameter[10]; p[0] = new SqlParameter("@LoginId", this._LoginName); p[0].DbType = DbType.String; p[1] = new SqlParameter("@FirstName", this._FirstName); p[1].DbType = DbType.String; p[2] = new SqlParameter("@LastName", this._LastName); p[2].DbType = DbType.String; p[3] = new SqlParameter("@Country", this._Country); p[3].DbType = DbType.String; p[4] = new SqlParameter("@Email", this._Email); p[4].DbType = DbType.String; p[5] = new SqlParameter("@Phone", this._Phone); p[5].DbType = DbType.String; p[6] = new SqlParameter("@DOB", this._DOB); p[6].DbType = DbType.Date; p[7] = new SqlParameter("@Gender", this._Gender); p[7].DbType = DbType.String; p[8] = new SqlParameter("@Interest", this._Interest); p[8].DbType = DbType.String; p[9] = new SqlParameter("@Date", this._Date); p[9].DbType = DbType.Date; SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "Sp_Insert_RegistrationInfo", p); } and when you click the submit button this code takes the user input to the code above for insertion: using System; using System.Data; using System.Configuration; 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; using System.Data.SqlClient; public partial class Registration_frmUserRegistration : System.Web.UI.Page { Country country = new Country(); IntrestBL intrest = new IntrestBL(); UserRegistrationBL registration = new UserRegistrationBL(); protected void Page_Load(object sender, EventArgs e) { GMDatePicker1.MaxDate = System.DateTime.Now; GMDatePicker1.MinDate = System.DateTime.Now.AddYears(-100); BindRandomNumber(); if (!IsPostBack) { BindData(); } GMDatePicker1.Attributes.Add("readonly", "readonly()"); } //------------------------------------------------------------------------------ private void BindRandomNumber() { Random num = new Random(); } //--------------------------------------------------------------------------- private void BindData() { ddlCountry.DataSource = country.ShowCountry(); ddlCountry.DataTextField = "CountryName"; ddlCountry.DataBind(); ddlCountry.Items.Insert(0, "Choose One..."); chklistInrest.DataSource = intrest.ShowAllIntrest(); chklistInrest.DataTextField = "Interest"; chklistInrest.DataBind(); } protected void lnkAvailability_Click(object sender, EventArgs e) { if (txtName.Text.Trim().Length { lblAvailability.Text = "Plz Enter User Name...!"; txtName.Focus(); return; } registration.LoginName = txtName.Text.Trim(); if (registration.CheckUserAvailability() == true) { lblAvailability.Text = "User Name Already Exists...!"; } else { lblAvailability.Text = "User Name Not Exists...!"; } } protected void btnContinue_Click(object sender, EventArgs e) { try { registration.LoginName = txtName.Text.Trim(); if (txtPassword.Text.Trim().Length { lblMsg.Focus(); lblMsg.Text = "Password Should Have Atleast 6 Characters"; return; } else { registration.Password = txtPassword.Text.Trim(); registration.Question = txtQuestion.Text.Trim(); registration.Answer = txtAnswer.Text.Trim(); registration.FirstName = txtFName.Text.Trim(); registration.LastName = txtLName.Text.Trim(); registration.Country = ddlCountry.SelectedItem.Text.Trim(); registration.Email = txtMail.Text.Trim(); registration.Phone = txtPhone.Text.Trim(); registration.DOB = GMDatePicker1.Date; registration.Gender = ddlGender.SelectedItem.Text.Trim(); string Intrest = ""; for (int i = 0; i { if (chklistInrest.Items[i].Selected == true) Intrest = Intrest + chklistInrest.Items[i].Text + ","; } Intrest = Intrest.Remove(Intrest.Length - 1, 1); registration.Interest = Intrest; registration.Date = System.DateTime.Now.Date; registration.InsertUserLoginInfo(); registration.InsertRegistrationInfo(); Session["UserName"] = registration.LoginName; Response.Redirect("~/Registration/frmSignUpSuccessful.aspx"); } } catch (Exception ex) { lblMsg.Text = "User Name Already Exists...!"; } } } thanks man

Rajeesh Menoth · Answer

Hi, Check the following reference : http://stackoverflow.com/questions/13318924/how-do-i-insert-into-two-tables-all-at-once-in-a-stored-procedure

sql server store procedure

Answers (2)