SelectCommand with SelectParameters.Add

[Morris Neuman]

Hi,

In my application's event I have a SelectCommand that works if I have a
concatenated select statement but does not work when I use parameters.

I have a SqlDataSource2 and AccessDataSource2 as follows:
<asp:AccessDataSource ID="AccessDataSource2" runat="server"
DataFile="C:\Program Files\CallMaster\Data\Callmaster.mdb"

SelectCommand="SELECT [TimeOfDayNumber], [TimeOfDayDescription] FROM
[AttTimeOfDayXref]">
</asp:AccessDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$
ConnectionStrings:CallMasterSQLConnectionString %>"
SelectCommand="SELECT TimeOfDayNumber, TimeOfDayDescription FROM
AttTimeOfDayXref">
</asp:SqlDataSource>


My click_event is as follows:

protected void ButtonImport_Click(object sender, EventArgs e)
{
PanelUpload.Visible=false;
PanelView.Visible=false;
PanelImport.Visible=true;

// reset to blank
LabelImport1.Text="";
LabelImport2.Text = "";

// retrieve the Select Command for the worksheet data
OleDbCommand objCommand = new OleDbCommand();
objCommand=ExcelConnection();

// create a DataReader
OleDbDataReader reader;
reader = objCommand.ExecuteReader();

string txtAttID="";
string txtTODDesc = "";
string txtExtToDial = "";
string txtDirList = "";

//check web.config if system app setting set for sql or access
//MyDataSource will always be either SqlDataSource1 or
AccessDataSource1
string id = ConfigurationManager.AppSettings["MyDataSource"];
Control datasourcecontrol = this.FindControl(id);

//for testing only
id = "SqlDataSource1";
//id = "AccessDataSource1";

//define a dataview
DataView dv = new DataView();

int intcount = 0;

while (reader.Read())
{
intcount = intcount + 1;
txtAttID = GetValueFromReader(reader, "AttID");
txtTODDesc = GetValueFromReader(reader, "Time Of Day Desc");
txtExtToDial = GetValueFromReader(reader, "Num To Dial");
txtDirList = GetValueFromReader(reader, "Directory Listing");
LabelImport1.Text = LabelImport1.Text + txtAttID + "," +
txtTODDesc + "," + txtExtToDial + "," + txtDirList;

if (id.Equals("SqlDataSource1"))
{
//find time od day number from the AttTimeOfDayXref
//The following concatenated select works
//SqlDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = '" + txtTODDesc + "'");

//The following parameterized select does not work
SqlDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
AttTimeOfDayXref.TimeOfDayDescription = @TODDescription");
SqlDataSource2.SelectParameters.Add("TODDescription",
TypeCode.String, txtTODDesc);

dv =
(DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
int selectrec = 0;
int intTOD = (int)dv.Table.Rows[selectrec]["TimeOfDayNumber"];
//LabelImport2.Text = LabelImport2.Text +
dv.Table.Rows[selectrec]["TimeOfDayNumber"].ToString();
LabelImport2.Text = LabelImport2.Text + intTOD.ToString();
}
else if (id.Equals("AccessDataSource1"))
{
//find time od day number from the AttTimeOfDayXref
//This works
//AccessDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = '" + txtTODDesc + "'");

//The following does not work
AccessDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = [?]");

AccessDataSource2.SelectParameters.Add("TimeOfDayDescription", txtTODDesc);


dv =
(DataView)AccessDataSource2.Select(DataSourceSelectArguments.Empty);
int selectrec = 0;
int intTOD = (int)
dv.Table.Rows[selectrec]["TimeOfDayNumber"];
//LabelImport2.Text = LabelImport2.Text +
dv.Table.Rows[selectrec]["TimeOfDayNumber"].ToString();
LabelImport2.Text = LabelImport2.Text + intTOD.ToString();
}
}
reader.Close();
}


I have tried various ways to make the Parameters.Add work but cannot. Help!

 

[Morris Neuman]

Hi,
I am also having a similar problem with the insertcommand

SqlDataSource2.InsertCommand = "Insert INTO Attendant
(AttendantID, TimeOfDay, TypeOfTransfer, ExtensionToDial) VALUES (@txtAttID,
@intTOD, @intTypeOfTransfer, @ExtToDial)";
SqlDataSource2.InsertParameters.Add("txtAttID",
TypeCode.String, txtAttID);
SqlDataSource2.InsertParameters.Add("intTOD",
TypeCode.Int32, txtTOD);
SqlDataSource2.InsertParameters.Add("intTypeOfTransfer",
TypeCode.Int32, "2");
SqlDataSource2.InsertParameters.Add("ExtToDial",
TypeCode.String, txtExtToDial);
SqlDataSource2.Insert(DataSourceSelectArguments.Empty);
--
Thanks
Morris

Hi,

In my application's event I have a SelectCommand that works if I have a
concatenated select statement but does not work when I use parameters.

I have a SqlDataSource2 and AccessDataSource2 as follows:
<asp:AccessDataSource ID="AccessDataSource2" runat="server"
DataFile="C:\Program Files\CallMaster\Data\Callmaster.mdb"

SelectCommand="SELECT [TimeOfDayNumber], [TimeOfDayDescription] FROM
[AttTimeOfDayXref]">
</asp:AccessDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$
ConnectionStrings:CallMasterSQLConnectionString %>"
SelectCommand="SELECT TimeOfDayNumber, TimeOfDayDescription FROM
AttTimeOfDayXref">
</asp:SqlDataSource>


My click_event is as follows:

protected void ButtonImport_Click(object sender, EventArgs e)
{
PanelUpload.Visible=false;
PanelView.Visible=false;
PanelImport.Visible=true;

// reset to blank
LabelImport1.Text="";
LabelImport2.Text = "";

// retrieve the Select Command for the worksheet data
OleDbCommand objCommand = new OleDbCommand();
objCommand=ExcelConnection();

// create a DataReader
OleDbDataReader reader;
reader = objCommand.ExecuteReader();

string txtAttID="";
string txtTODDesc = "";
string txtExtToDial = "";
string txtDirList = "";

//check web.config if system app setting set for sql or access
//MyDataSource will always be either SqlDataSource1 or
AccessDataSource1
string id = ConfigurationManager.AppSettings["MyDataSource"];
Control datasourcecontrol = this.FindControl(id);

//for testing only
id = "SqlDataSource1";
//id = "AccessDataSource1";

//define a dataview
DataView dv = new DataView();

int intcount = 0;

while (reader.Read())
{
intcount = intcount + 1;
txtAttID = GetValueFromReader(reader, "AttID");
txtTODDesc = GetValueFromReader(reader, "Time Of Day Desc");
txtExtToDial = GetValueFromReader(reader, "Num To Dial");
txtDirList = GetValueFromReader(reader, "Directory Listing");
LabelImport1.Text = LabelImport1.Text + txtAttID + "," +
txtTODDesc + "," + txtExtToDial + "," + txtDirList;

if (id.Equals("SqlDataSource1"))
{
//find time od day number from the AttTimeOfDayXref
//The following concatenated select works
//SqlDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = '" + txtTODDesc + "'");

//The following parameterized select does not work
SqlDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
AttTimeOfDayXref.TimeOfDayDescription = @TODDescription");
SqlDataSource2.SelectParameters.Add("TODDescription",
TypeCode.String, txtTODDesc);

dv =
(DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
int selectrec = 0;
int intTOD = (int)dv.Table.Rows[selectrec]["TimeOfDayNumber"];
//LabelImport2.Text = LabelImport2.Text +
dv.Table.Rows[selectrec]["TimeOfDayNumber"].ToString();
LabelImport2.Text = LabelImport2.Text + intTOD.ToString();
}
else if (id.Equals("AccessDataSource1"))
{
//find time od day number from the AttTimeOfDayXref
//This works
//AccessDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = '" + txtTODDesc + "'");

//The following does not work
AccessDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = [?]");

AccessDataSource2.SelectParameters.Add("TimeOfDayDescription", txtTODDesc);


dv =
(DataView)AccessDataSource2.Select(DataSourceSelectArguments.Empty);
int selectrec = 0;
int intTOD = (int)
dv.Table.Rows[selectrec]["TimeOfDayNumber"];
//LabelImport2.Text = LabelImport2.Text +
dv.Table.Rows[selectrec]["TimeOfDayNumber"].ToString();
LabelImport2.Text = LabelImport2.Text + intTOD.ToString();
}
}
reader.Close();
}


I have tried various ways to make the Parameters.Add work but cannot. Help!

 

[Allen Chen [MSFT]]

Hi Morris,

Let's solve the select problem first.

Quote from Morris==================================================
//The following parameterized select does not work
SqlDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
AttTimeOfDayXref.TimeOfDayDescription = @TODDescription");
SqlDataSource2.SelectParameters.Add("TODDescription",
TypeCode.String, txtTODDesc);

dv =
(DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
==================================================

The above code seems fine. Could you set a breakpoint after the above lines
to check the dv.Count? What's the result?

Quote from Morris ==================================================
//The following does not work
AccessDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = [?]");
==================================================

Please try to use "SELECT AttTimeOfDayXref.TimeOfDayNumber From
AttTimeOfDayXref Where

TimeOfDayDescription =?" instead. Can it work?

If it still doesn't work could you send me a repro project? My email is
(e-mail address removed) update here after sending the project in case
I missed that email.

Regards,
Allen Chen
Microsoft Online Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Morris Neuman]

Hi,

I was able to solve both the select and the insert with parameters issue by
using a parameter.clear after each event.

I do have a few questions:

1) why do I require a .clear?
2) is there a better way to do the select and insert with parameters in an
iterative process than using .add then .clear? Is there a way to just
refresh the parameters values?
3) is using table adapters more efficient than using datasource controls?
4) is there a way to get the count of the records inserted, similar to the
dataview.count? I tried using count = datasource1.Inserted(RecAddedCount)
but get error.
5) Is there a way (other than retrieving the record) of seeing the values of
the fields in the record inserted similar to the dataview fields in a
label.text?
6) on page load, how do I see the page number that was requested by the user
in a multipage gridview? I tried gridview1.pageindex but get random values,
not the number of the page requested.

My code is as follows:

protected void ButtonImport_Click(object sender, EventArgs e)
{
PanelUpload.Visible=false;
PanelView.Visible=false;
PanelImport.Visible=true;

// reset to blank
LabelImport1.Text="";
LabelImport2.Text = "";

// retrieve the Select Command for the worksheet data
OleDbCommand objCommand = new OleDbCommand();
objCommand=ExcelConnection();

// create a DataReader
OleDbDataReader reader;
reader = objCommand.ExecuteReader();

string txtAttID="";
string txtTODDesc = "";
string txtExtToDial = "";
string txtDirList = "";

//check web.config if system app setting set for sql or access
//MyDataSource will always be either SqlDataSource1 or
AccessDataSource1
string id = ConfigurationManager.AppSettings["MyDataSource"];
Control datasourcecontrol = this.FindControl(id);

//for testing only
//id = "SqlDataSource1";
id = "AccessDataSource1";

//define a dataview
DataView dv = new DataView();
DataView dv2 = new DataView();

int intcount = 0;

while (reader.Read())
{
intcount = intcount + 1;
txtAttID = GetValueFromReader(reader, "AttID");
txtTODDesc = GetValueFromReader(reader, "Time Of Day Desc");
txtExtToDial = GetValueFromReader(reader, "Num To Dial");
txtDirList = GetValueFromReader(reader, "Directory Listing");
LabelImport1.Text = LabelImport1.Text + txtAttID + "," +
txtTODDesc + "," + txtExtToDial + "," + txtDirList;
string txtTOD = "0";
int selreccount = 0;
int selectrec = 0;

if (id.Equals("SqlDataSource1"))
{
//find time of day number from the AttTimeOfDayXref
//SqlDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = '" + txtTODDesc + "'");
SqlDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
AttTimeOfDayXref.TimeOfDayDescription = @TODDescription");
SqlDataSource2.SelectParameters.Add("TODDescription",
TypeCode.String, txtTODDesc);

dv =
(DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
SqlDataSource2.SelectParameters.Clear();
txtTOD =
(string)dv.Table.Rows[selectrec]["TimeOfDayNumber"].ToString();

//Try and add record to Attendant table
SqlDataSource1.InsertCommand = "Insert INTO Attendant
(AttendantID, TimeOfDay, TypeOfTransfer, ExtensionToDial) VALUES (@txtAttID,
@intTOD, @intTypeOfTransfer, @ExtToDial); SELECT SCOPE_IDENTITY()";

SqlDataSource1.InsertParameters.Add("txtAttID",
TypeCode.String, txtAttID);
SqlDataSource1.InsertParameters.Add("intTOD",
TypeCode.Int32, txtTOD);
SqlDataSource1.InsertParameters.Add("intTypeOfTransfer",
TypeCode.Int32, "2");
SqlDataSource1.InsertParameters.Add("ExtToDial",
TypeCode.String, txtExtToDial);

SqlDataSource1.Insert();
SqlDataSource1.InsertParameters.Clear();

}
else if (id.Equals("AccessDataSource1"))
{
//find time od day number from the AttTimeOfDayXref
//AccessDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = '" + txtTODDesc + "'");
AccessDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = ?");

AccessDataSource2.SelectParameters.Add("TimeOfDayDescription", txtTODDesc);

dv =
(DataView)AccessDataSource2.Select(DataSourceSelectArguments.Empty);
AccessDataSource2.SelectParameters.Clear();
txtTOD =
(string)dv.Table.Rows[selectrec]["TimeOfDayNumber"].ToString();

//Try and add record to Attendant table
AccessDataSource1.InsertParameters.Clear();
AccessDataSource1.InsertCommand = "Insert INTO Attendant
(AttendantID, TimeOfDay, TypeOfTransfer, ExtensionToDial) VALUES (?, ?, ?,
?)";

AccessDataSource1.InsertParameters.Add("txtAttID",
TypeCode.String, txtAttID);
AccessDataSource1.InsertParameters.Add("intTOD",
TypeCode.Int32, txtTOD);
AccessDataSource1.InsertParameters.Add("intTypeOfTransfer",
TypeCode.Int32, "2");
AccessDataSource1.InsertParameters.Add("ExtToDial",
TypeCode.String, txtExtToDial);
AccessDataSource1.Insert();
}
selreccount = dv.Count;
LabelImport2.Text = LabelImport2.Text + txtTOD;
LabelImport1.Text = LabelImport1.Text + ", reccount=" +
selreccount.ToString() + "; <br>";
}
reader.Close();
LabelImport2.Text = LabelImport2.Text + "--- " + intcount;

GridView1.DataBind();
GridView1.Visible = true;

}

Sorry for all the question. I try to do the research but do not always get
the answers on my own.
--
Thanks for your help.

Morris

Hi Morris,

Let's solve the select problem first.

Quote from Morris==================================================
//The following parameterized select does not work
SqlDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
AttTimeOfDayXref.TimeOfDayDescription = @TODDescription");
SqlDataSource2.SelectParameters.Add("TODDescription",
TypeCode.String, txtTODDesc);

dv =
(DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
==================================================

The above code seems fine. Could you set a breakpoint after the above lines
to check the dv.Count? What's the result?

Quote from Morris ==================================================
//The following does not work
AccessDataSource2.SelectCommand = ("SELECT
AttTimeOfDayXref.TimeOfDayNumber From AttTimeOfDayXref Where
TimeOfDayDescription = [?]");
==================================================

Please try to use "SELECT AttTimeOfDayXref.TimeOfDayNumber From
AttTimeOfDayXref Where

TimeOfDayDescription =?" instead. Can it work?

If it still doesn't work could you send me a repro project? My email is
(e-mail address removed) update here after sending the project in case
I missed that email.

Regards,
Allen Chen
Microsoft Online Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Allen Chen [MSFT]]

Hi Morris,

Thanks for your update.

1) why do I require a .clear?


I think it's because that you've already added some parameters before that.
But from your code I cannot see it. If you could provide all the code I may
help to find out the root cause.


2) is there a better way to do the select and insert with parameters in an

iterative process than using .add then .clear? Is there a way to just
refresh the parameters values?


To refresh the parameter you can try this in the code behind:
SqlDataSource2.SelectParameters["parameter_name"].DefaultValue

However, generally we probably will not hard code it in the code behind. We
may use the ControlParameter, etc., which is defined in the inline code
(aspx). You can refer to the following tutorial to learn how to use it:

Sample:

http://quickstarts.asp.net/QuickStartv20/aspnet/samples/data/GridViewDropDow
nList_vb.aspx

Code:

http://quickstarts.asp.net/QuickStartv20/util/srcview.aspx?path=~/aspnet/sam
ples/data/GridViewDropDownList.src&file=GridViewDropDownList_cs.aspx&lang=C%
23+Source


3) is using table adapters more efficient than using datasource controls?

I believe there's no significant performance gap here. It's recommended to
use the DataSource controls because they are more convenient. The
DataSource controls have built-in cache support and we can utilize the set
of Parameters such as the ControlParameter, QueryStringParameter, etc. They
are all very useful to us.

4) is there a way to get the count of the records inserted, similar to the
dataview.count? I tried using count = datasource1.Inserted(RecAddedCount)
but get error.

We can get the affected rows number in the Inserted event handler of the
SqlDataSource control. Here's the code:
protected void SqlDataSource1_Inserted(object sender,
SqlDataSourceStatusEventArgs e)
{
//e.AffectedRows

}
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
oninserted="SqlDataSource1_Inserted" ¡­

5) Is there a way (other than retrieving the record) of seeing the values
of
the fields in the record inserted similar to the dataview fields in a
label.text?

Another way is to get the information in Inserted event handler of the
SqlDataSource control. Like this:
protected void SqlDataSource1_Inserted(object sender,
SqlDataSourceStatusEventArgs e)
{
// label.text =e.Command.Parameters["@parameter_name"].Value. ToString();

}

6) on page load, how do I see the page number that was requested by the
user
in a multipage gridview? I tried gridview1.pageindex but get random
values,
not the number of the page requested.

Page_Load event is a too early event to do this task. You can try
Page_PreRender instead:
protected void Page_PreRender(object sender, EventArgs e)
{
//To test only
Response.Write(GridView1.PageIndex +1);

}


Please feel free to ask if you have further questions.

Regards,
Allen Chen
Microsoft Online Community Support

 

[Allen Chen [MSFT]]

Hi Morris,

Do you have any progress on this issue?

Regards,
Allen Chen
Microsoft Online Support

 

[Morris Neuman]

Hi Allen,

Have not had a chance to try your proposed code yet. Hope to catch up in
the next few weeks. If I have any questions I will post again.

Happy New Year and thanks for your help.

 

[Allen Chen [MSFT]]

Hi Morris,

Have you solved this issue?

Regards,
Allen Chen
Microsoft Online Support