passing parameters using stored procedure with where clause??

Question

0.00/5 (No votes)

See more:

SQL-server-2000

, +

SQL

i have created this function with a parameter offset and i want to access the database using stored procedure but the code and stored procedure i have written below is not working...what is the right way to do it??

# public bool isoffsetexist(int offset)
#     {
#         conn.Open();
#  
#         SqlCommand cmd = new SqlCommand();
#         cmd.Connection = conn;
#         cmd.CommandType = CommandType.StoredProcedure;
#         cmd.CommandText = "sp_offset1";
#         SqlDataReader dr = cmd.ExecuteReader();
#  
#         int i = 0;
#         while (dr.Read())
#         {
#             i++;
#         }
#         if (i == 1)
#         {
#             conn.Close();
#             return true;
#         }
#         else
#         {
#             conn.Close();
#             return false;
#         }
#     }

my stored procedure "sp_offset1" is

# CREATE PROCEDURE [dbo].[sp_offset1]
# @offset numeric,
# @appname varchar(20),
# @os varchar(20)
#  AS
# select * from tb_offset
#  where @offset=" + offset + "

Posted 5-Apr-10 22:14pm

hemant_chauhan

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)

thatraja · Accepted Answer · 2010-04-06T00:01:00

Your sp should be like below

SQL

CREATE PROCEDURE [dbo].[sp_offset1]
@offset numeric,
@appname varchar(20),
@os varchar(20)
AS
select * from tb_offset
where offset=@offset

Then you should add parameter in your code

C#

SqlCommand cmd = new SqlCommand();
SqlParameter param;
string offset= txtOffset.text;
param = new SqlParameter("@offset", SqlDbType.Numeric);
param.Direction = ParameterDirection.Input;
param.Value = offset;
cmd.Parameters.Add(param);

Please change your code based on your need.

Then your sp contains 3 parameters, so you should pass values to 3 parameters in your code.(@appname, @os)