stored procedure to retrieve data from radiobutton to table

john kanyora

kindly help on how i can implement a stored procedure on the code behind that once i use my radiobutton it loads the data to the table sms_outbox.

i have created the stored procedure below but once i implement in the code behind it does not work.this is my code behind;

protected void radioButton1_CheckedChanged(Object sender, EventArgs e)

{

SqlCommand SqlCmd = new SqlCommand();

SqlCmd.CommandText = "[enterprise].[send_sms]";

SqlCmd.CommandType = CommandType.StoredProcedure;

SqlCmd.Parameters.Add("@CompanyID", SqlDbType.NVarChar).Value = CompanyID;

SqlCmd.Parameters.Add("@BranchID", SqlDbType.NVarChar).Value = BranchID;

SqlCmd.Parameters.Add("@DepartmentID", SqlDbType.NVarChar).Value = DepartmentID;

SqlCmd.Parameters.Add("@EmployeeMobilePhone", SqlDbType.NVarChar).Value = EmployeeMobilePhone;

SqlCmd.Parameters.Add("@VendorPhone", SqlDbType.NVarChar).Value = VendorPhone;

SqlCmd.Parameters.Add("@PatientCellPhone", SqlDbType.NVarChar).Value = PatientCellPhone;

}

this is my stored procedure

Create PROCEDURE [enterprise].[send_sms]

(

@CompanyID NVARCHAR(72),

@BranchID NVARCHAR(36),

@DepartmentID NVARCHAR(36),

@Value nvarchar(36)=''

)

DECLARE @EmployeeName nvarchar(36)

DECLARE @EmployeeMobilePhone nvarchar(36)

DECLARE @VendorName nvarchar(36)

DECLARE @VendorPhone nvarchar(36)

DECLARE @PatientName nvarchar(36)

DECLARE @PatientCellPhone nvarchar(36)

BEGIN

IF @Value='Patients'

BEGIN

insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)

select CompanyID,BranchID,DepartmentID,PatientCellPhone from CustomerInformation

END

ELSE IF @Value='Suppliers'

BEGIN

insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)

select CompanyID,BranchID,DepartmentID,VendorPhone from VendorInformation

END

this is my aspx page with the radiobutton defined

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajneesh Chaubey8y ago
Accepted answer
If you have to just match values like whether patient or customer then why you are passing those variables in stored procedure as we didn't need these.
Your procedure would be like this if I am not wrong:
CREATE PROCEDURE [enterprise].[send_sms]
(
@Value nvarchar(36)
)
AS

BEGIN
IF @Value='Patients'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,PatientCellPhone from CustomerInformation
END
ELSE IF @Value='Suppliers'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,VendorPhone from VendorInformation
END
ELSE IF @Value='Employees'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,EmployeeMobilePhone from PayrollEmployees
END
END
And in your code behind pass only value parameter.
Hope you get it.
+2
john kanyora8y ago
Hi Rajneesh, i have effected the changes in stored procedure and code behind as below;

ALTER PROCEDURE [enterprise].[send_sms]
(
@Value nvarchar(36)
)
AS
BEGIN
IF @Value='Patients'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,PatientCellPhone from CustomerInformation
END
ELSE IF @Value='Suppliers'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,VendorPhone from VendorInformation
END
ELSE IF @Value='Employees'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,EmployeeMobilePhone from PayrollEmployees
END
END

and my code behind;

protected void SendSMS_Click(object sender, EventArgs e)
{
string Value = Radiobuttonlist1.SelectedItem.Value.ToString();
string constr = TraceBizCommon.Configuration.ConfigSettings.ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.CommandText = "[enterprise].[send_sms]";
{
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.Parameters.Add("@CompanyID", SqlDbType.NVarChar).Value = CompanyID;
SqlCmd.Parameters.Add("@BranchID", SqlDbType.NVarChar).Value = BranchID;
SqlCmd.Parameters.Add("@DepartmentID", SqlDbType.NVarChar).Value = DepartmentID;
SqlCmd.Parameters.Add("@Value", SqlDbType.NVarChar).Value = this.Value;
SqlCmd.Connection = con;
con.Open();
con.Close();
}}}

but still once i click on one of the radiobutton choices and clicking on send sms, it does not insert the data into my sms_outbox...i want the data which is phone number to be qued in that table called sms_outbox
0
john kanyora8y ago
I get what you mean, let me try again and see how it will work
0
john kanyora8y ago
i have a table called sms_outbox which has the fields CompanyID,BranchID,DepartmentID as the primary keys..so what i want to achieve is this: i have a radiobutton list with patients,suppliers and employees.i want once i choose either of them, lets say patients and then i click on send sms, then the information from patients is inserted in the sms_outbox.
0
Rajneesh Chaubey8y ago
Hi,
Can I know from where you are sending these values?(such as CompanyID, BranchID etc)
What exactly you want to do?
0
john kanyora8y ago
Hi Rajneesh, i have made the changes as you directed me

protected void SendSMS_Click(object sender, EventArgs e)
{
string value = Radiobuttonlist1.SelectedItem.Value.ToString();
string constr = TraceBizCommon.Configuration.ConfigSettings.ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.CommandText = "[enterprise].[send_sms]";
{
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.Parameters.Add("@CompanyID", SqlDbType.NVarChar).Value = CompanyID;
SqlCmd.Parameters.Add("@BranchID", SqlDbType.NVarChar).Value = BranchID;
SqlCmd.Parameters.Add("@DepartmentID", SqlDbType.NVarChar).Value = DepartmentID;
SqlCmd.Parameters.Add("@EmployeeMobilePhone", SqlDbType.NVarChar).Value = EmployeeMobilePhone;
SqlCmd.Parameters.Add("@VendorPhone", SqlDbType.NVarChar).Value = VendorPhone;
SqlCmd.Parameters.Add("@PatientCellPhone", SqlDbType.NVarChar).Value = PatientCellPhone;
SqlCmd.Parameters.Add("@Value", SqlDbType.NVarChar).Value = Value;
SqlCmd.Connection = con;
con.Open();
con.Close();
}
}
}

but still no data is being inserted in the table
0
Rajneesh Chaubey8y ago
Hi John,
Best place to call your stored procedure inside the button onclick event.
Do proper null checking in your method then call your stored procedure.
First get the value of your radiobutton like this:
string value=Radiobuttonlist1.SelectedItem.Value.ToString();
SqlConnection con = new SqlConnection(@ "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand("send_sms", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Value", value);
con.Open();

Also your declaring multiple variables in your stored procedure but not using.(You are using only @value variable)
0
john kanyora8y ago
Sundaram , i have done so but still it dooes not retrieve data once i click on the radiobutton
let me share the screenshot of the interface i want to use .

i want once i click on either patients and i click send sms , then the data from patients goes to a table called sm_outbox..i have created a stored procedure to implement it..below is the code behind

..kindly help how i will implement my stored procedure..

below is the code behind without the stored procedure;
public partial class SMS_Management_SendSMS : PageBase
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
if (Request.QueryString["recipient"] != null)
{
txtrecipient.Text = Request.QueryString["recipient"].ToString();
}
}

}
protected void SendSMS_Click(object sender, EventArgs e)
{
if (txtMessage.Text.Length > 0 && txtrecipient.Text.Length > 0)
{
if (TraceBizCommon.Alerts.SMSUtilities.sendSMS(txtrecipient.Text, txtMessage.Text) == "OK")
{
txtrecipient.Text = string.Empty;
txtMessage.Text = string.Empty;
}
}
else
OnLoadActions.Alert(this, "Please ensure that there is a message and recipient before sending the SMS");
}
protected void btnClose_Click(object sender, EventArgs e)
{
Response.Redirect("~/SummaryCentre/Default.aspx");
}
}
below is the stored procedure
CREATE PROCEDURE [enterprise].[send_sms]
(
@CompanyID NVARCHAR(72),
@BranchID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@Value nvarchar(36)=''
)
AS
DECLARE @EmployeeName nvarchar(36)
DECLARE @EmployeeMobilePhone nvarchar(36)
DECLARE @VendorName nvarchar(36)
DECLARE @VendorPhone nvarchar(36)
DECLARE @PatientName nvarchar(36)
DECLARE @PatientCellPhone nvarchar(36)
BEGIN
IF @Value='Patients'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,PatientCellPhone from CustomerInformation
END
ELSE IF @Value='Suppliers'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,VendorPhone from VendorInformation
END
ELSE IF @Value='Employees'
BEGIN
insert into sms_outbox(CompanyID,BranchID,DepartmentID,Recipient)
select CompanyID,BranchID,DepartmentID,EmployeeMobilePhone from PayrollEmployees
END
END
and this the aspx code with the radiobutton defined;
<asp:RadioButtonList ID="Radiobuttonlist1" OnCheckedChange="Radiobuttonlist1_CheckedChange" runat="server">
<asp:ListItem Text="Patients" Value="Patients">asp:ListItem>
<asp:ListItem Text="Suppliers" Value="Suppliers">asp:ListItem>
<asp:ListItem Text="Employees" Value="Employees">asp:ListItem>
asp:RadioButtonList>
+2
Sundaram Subramanian8y ago
Hai,

in your SP, you have declared @value and in SP, you have IF Statement too as

IF @Value='Patients'

But in code behind you are not passing parameter for @value. Please kindly pass and let me know.

Hope this may helps you well. Thanks in Advance.
+1