SelectCommand with SelectParameters.Add

Discussion in 'ASP .Net Web Controls' started by Morris Neuman, Dec 16, 2008.

  1. 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, Dec 16, 2008
    #1
    1. Advertisements

  2. 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


     
    Morris Neuman, Dec 17, 2008
    #2
    1. Advertisements

  3. 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
    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:
    .

    ==================================================
    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], Dec 17, 2008
    #3
  4. 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


     
    Morris Neuman, Dec 18, 2008
    #4
  5. 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], Dec 19, 2008
    #5
  6. Hi Morris,

    Do you have any progress on this issue?

    Regards,
    Allen Chen
    Microsoft Online Support
     
    Allen Chen [MSFT], Dec 23, 2008
    #6
  7. 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.
     
    Morris Neuman, Jan 4, 2009
    #7
  8. Hi Morris,

    Have you solved this issue?

    Regards,
    Allen Chen
    Microsoft Online Support
     
    Allen Chen [MSFT], Jan 13, 2009
    #8
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.