M
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!
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!