How to write stored procedure

Question

1.00/5 (1 vote)

See more:

Hi Everyone i have just started learning about stored procedure. As i was able to create SP(Stored Procedure) for simple query but got stuck into some queries.
as Shown below

Quote:
var collection = Repeater0.Items;

foreach (RepeaterItem item in collection)
{
HiddenField QID = (HiddenField)item.FindControl("QID");

int q = int.Parse(QID.Value);

string answer = Request.Form[q.ToString()];
string id = Convert.ToString(Session["UserID"]);
string id2 = Convert.ToString(Session["ID"]);
string connection = ConfigurationManager.AppSettings["connection"].ToString();
SqlConnection connec = new SqlConnection(connection);
connec.Open();
SqlCommand cmd = new SqlCommand("Insert into t_AnswerSheet(UserId,QId, AnswerId,AnswerResult) values('" + id + "','" + q + "',(select AID from t_Answers where QId='" + q + "' and AOptions ='" + answer + "'),(select AnswerResult from t_Answers where QId='" + q + "' and AOptions ='" + answer + "'))", connec);
//cmd.Parameters.Add("@sqlpara", id);
//cmd.Parameters.Add("@sqlparb",q);
//cmd.Parameters.Add("@sqlparc", answer);

cmd.ExecuteNonQuery();
connec.Close();
}
Response.Redirect("Successfullsubmit.aspx");
}

So if anyone can help me

What I have tried:

I am using

create procedure BtnSubmit_Click	
AS
Insert into t_AnswerSheet(UserId,QId, AnswerId,AnswerResult) values('" + id + "','" + q + "',(select AID from t_Answers where QId='" + q + "' and AOptions ='" + answer + "'),(select AnswerResult from t_Answers where QId='" + q + "' and AOptions ='" + answer + "'))
go

Posted 4-Jul-17 0:04am

Member 11644373

Updated 4-Jul-17 0:27am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-07-04T00:27:00

No.
For starters, never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
And it very particularly won't work at all when you write it as a stored procedure, because that is executed in SQL Server, which doesn't have any access to your C# code variables.

Create a simple stored procedure to insert:

SQL

CREATE PROC [dbo].Sample
@ID varchar(100)
@VAL varchar(100)
AS
BEGIN
    INSERT INTO MyTable (Id, ValueColumnName) VALUES (@ID, @VAL)
END

And then use it in your C# code:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("Sample", con))
        {
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@ID", id);
        com.Parameters.AddWithValue("@VAL", "This goes in the DB");
        com.ExecuteNonQuery();
        }
    con.Close();
    }

Try that, get it working on a test DB, and then use the same principles to create one suitable for your task.