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 AnswerActiveDate) VALUES (@LoginName@Password@Question@Answer1getdate()) 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 GenderInterestDate) 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(@Passwordthis._Password); p[1].DbType=DbType.String; p[2]=new SqlParameter(@Questionthis._Question); p[2].DbType=DbType.String; p[3]=new SqlParameter(@Answerthis._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