How to pass parameter value to SqlDataSource

Question

I'm using SqlDataSource to populate a GridView. Here is my code that does that :

private void DataCall()
{
    //Object gets created for use from the class(clsDataConduit)
    clsDataDictionary AnOrder = new clsDataDictionary();
    //Declaring a new SqlDataSource from the inbuilt class library
    SqlDataSource sqlDS_ItemTable = new SqlDataSource();
    //Using our datasource object being cast onto our objects connectionstring
    sqlDS_ItemTable.ConnectionString = AnOrder.ConnectionString;
    //Our sql statement being passed through to our .SelectCommand method
    sqlDS_ItemTable.SelectCommand = "Select tblOrders.OrderId, tblItem.ItemName, tblOrders.DateOrdered from tblItem, tblOrders where tblItem.ItemId = tblOrders.ItemId AND tblOrders.AuthId = 5";
    //Adding controls to our SqlDataSource object
    this.Controls.Add(sqlDS_ItemTable);
    //Declares the DataSource for our gridview !
    grdOrder.DataSource = sqlDS_ItemTable;
    //Binds the data to refresh every time it's used
    grdOrder.DataBind();
}

As you can see in the SQL statement at the end i'm doing this tblOrders.AuthId = 5 . However I want to do something like this tblOrders.AuthId = SessionAuthId .

I read some posts about doing something alone the lines of command.Parameters.Add(new SqlParameter("Name", dogName)); but I don't know how I apply this to my code.

I'm doing this in an assignment using someone elses code (professors) but I want to edit it slightly because i'm going to be developping a login system etc.

Could someone take a look at that method and see how I would change it to pass the parameter to it. Also here is the full code behind : http://pastebin.com/sdrvW5Zn

Matthew · Accepted Answer · 2013-03-20 01:45:39Z

11

You can use the SelectParameters property to do a parameterized query.

sqlDS_ItemTable.SelectCommand = "Select tblOrders.OrderId, tblItem.ItemName, tblOrders.DateOrdered from tblItem, tblOrders where tblItem.ItemId = tblOrders.ItemId AND tblOrders.AuthId = @authID";

sqlDS_ItemTable.SelectParameters.Add(new SqlParameter("@authID", SessionAuthID));

answered Mar 20, 2013 at 1:45

Matthew

26k9 gold badges86 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JARRRRG Over a year ago

When I used these lines, it highlighted SqlParameter saying it's not an available namespace and also highlighted SessionAuthId saying it's not available in the current context.

Bathiya Ladduwahetty · Accepted Answer · 2013-03-20 02:17:06Z

0

 String authId = SessionAuthID;//pass your session ID to the variable or directly you can     pass this value as the parameter value:

 string strSQL = "Select tblOrders.OrderId, tblItem.ItemName, tblOrders.DateOrdered from   tblItem, tblOrders where tblItem.ItemId = tblOrders.ItemId AND tblOrders.AuthId = @ID";
 SqlCommand cmdItem = new SqlCommand(strSQL, clsMain_Connection.openConn());
 cmdItem.Parameters.AddWithValue("@ID", authId);

answered Mar 20, 2013 at 2:17

Bathiya Ladduwahetty

4751 gold badge6 silver badges12 bronze badges

Comments

tessi · Accepted Answer · 2013-03-20 02:40:17Z

The string.format method suggested by TheGreatCO looks like it would work well for your current implementation where you pass the SQL query as a string.

Otherwise you could create a stored procedure from your SQL snippet and set that up to take parameter.

I am assuming that the sessionAuthId is an int.

CREATE PROCEDURE SP_SAMPLEPROCEDURENAME

(
    @p1 int      -- here the input parameter is declared
)


AS
    BEGIN

Select 
tblOrders.OrderId, 
tblItem.ItemName, 
tblOrders.DateOrdered 
from tblItem, tblOrders 
where tblItem.ItemId = tblOrders.ItemId 
AND tblOrders.AuthId = @p1   -- here the input parameter is used

END

To call the stored proc from c# you need a method like this:

public YOURReturnObject PopulateGridView(int sessionid)
{

SqlConnection conn = new SqlConnection("YourDBConnectionString);
SqlCommand comm = new SqlCommand("SP_SAMPLEPROCEDURENAME", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@p1",sessionid);

YOURReturnObject o = new YOURReturnObject();

using (comm.Connection)
{
comm.Connection.Open();
while (reader.Read())
{
//read the results into return object 

}

}
return o;

David Smit · Accepted Answer · 2016-01-07 13:16:47Z

0

Use the the "SelectParameters"

sqlDS_ItemTable.SelectParameters.Clear(); 
sqlDS_ItemTable.SelectCommand = "Select tblOrders.OrderId, tblItem.ItemName, tblOrders.DateOrdered from tblItem, tblOrders where tblItem.ItemId = tblOrders.ItemId AND tblOrders.AuthId = @authID";
sqlDS_ItemTable.SelectParameters.Add("authID", SessionAuthID);

// sqlDS_ItemTable.SelectParameters.Add("stringParam",  TypeCode.String, stringParam); //like so for string validation

answered Jan 7, 2016 at 13:16

David Smit

8491 gold badge15 silver badges34 bronze badges

Comments

Pete Garafano · Accepted Answer · 2013-03-20 01:40:50Z

-5

Use string.Format() http://msdn.microsoft.com/en-us/library/system.string.format.aspx

Do something like:

sqlDS_ItemTable.SelectCommand = string.Format("Select tblOrders.OrderId, tblItem.ItemName, tblOrders.DateOrdered from tblItem, tblOrders where tblItem.ItemId = tblOrders.ItemId AND tblOrders.AuthId = {0}", SessionAuthId);

To answer your second question, you would change the signature of the method to include a parameter.

private void DataCall(string SessionAuthId)
{
    // your method here...
}

answered Mar 20, 2013 at 1:40

Pete Garafano

4,9331 gold badge25 silver badges41 bronze badges

5 Comments

Dan Bracuk Over a year ago

Formatting the string does not add a parameter. At most, it allows you to use a variable.

Pete Garafano Over a year ago

@DanBracuk That's exactly what he is trying to do. Add a variable to a SQL query. The method I provided does all the processing on the C# side, instead of in the SQL server.

JARRRRG Over a year ago

@Dan Bracuk This method worked for me and I understood it aswell.

Pete Garafano Over a year ago

@Jagga This method is not considered "best practice", it opens you up to SQL Injection attacks, however it is certainly easier. You should read this codinghorror.com/blog/2005/04/… and this johnhforrest.com/2010/10/parameterized-sql-queries-in-c

JARRRRG Over a year ago

Will do! Hopefully by the end of it can use paramaterized version then

Collectives™ on Stack Overflow

How to pass parameter value to SqlDataSource

5 Answers 5

1 Comment

Comments

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related