#1
  1. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22

    Unhappy ASP.NET inserting DateTime into SQL Server table


    I'm getting a problem with inserting a value into a SQL Server table of type DateTime. Most of the relevant code is below. I get the exception
    Code:
    "The parameterized query '(@FirstName varchar(8000),@MiddleName varchar(8000),@LastName va' expects the parameter '@BirthDate', which was not supplied."
    It all compiles fine and the FindControl is working fine, but I just can't figure out how to get the DateTime from the AJAX calendar extender (which, as far as I know, has most of the same properties like SelectedDate as the Calendar control). My database field is a DateTime type with no default value, and I really want to try to avoid using a text value for the date and parsing it when I enter and retrieve it.

    Can anyone explain why I might be getting this exception?

    The only thing "odd" that I'm doing is the TextBox that the date appears in is Enabled="false" so that the user must use the calendar control rather than entering text for the date. But retrieving the AJAX Calendar Extender selected date should have nothing to do with the text box, right?

    Thanks!

    Part of the ASPX page:
    Code:
    <asp:WizardStep runat="server" ID="cpStep1_5" Title="Details" >
                <asp:Table runat="server" ID="Table2">
                    <asp:TableRow ID="TableRow16" runat="server">
                        <asp:TableCell ID="TableCell30" runat="server">
                            Birth Date
                        </asp:TableCell>
                        <asp:TableCell ID="TableCell33" runat="server">
                            <asp:TextBox runat="server" ID="txtBirthDate" MaxLength="20" AutoCompleteType="Disabled" Enabled="false" />&nbsp;
                            <asp:Image runat="server" ID="imgBirthDateCal" ImageUrl="~/images/icons/calendar.png" 
                                Width="16" Height="16" />
                            <ajax:CalendarExtender runat="server" ID="ajaxCalBirthDate" TargetControlID="txtBirthDate" 
                                Format="d" PopupButtonID="imgBirthDateCal" />
                        </asp:TableCell>
                    </asp:TableRow>
                    <asp:TableRow ID="TableRow13" runat="server">
                        <asp:TableCell ID="TableCell24" runat="server">
                            Hire Date
                        </asp:TableCell>
                        <asp:TableCell ID="TableCell25" runat="server">
                            <asp:TextBox runat="server" ID="txtHireDate" MaxLength="20" AutoCompleteType="Disabled" Enabled="false" />&nbsp;
                            <asp:Image runat="server" ID="imgHireDateCal" ImageUrl="~/images/icons/calendar.png" 
                                Width="16" Height="16" />
                            <ajax:CalendarExtender runat="server" ID="ajaxCalHireDate" TargetControlID="txtHireDate" 
                                Format="d" PopupButtonID="imgHireDateCal" />
                        </asp:TableCell>
                    </asp:TableRow>
                    <asp:TableRow ID="TableRow20" runat="server">
                        <asp:TableCell ID="TableCell38" runat="server">
                            Termination Date
                        </asp:TableCell>
                        <asp:TableCell ID="TableCell39" runat="server">
                            <asp:TextBox runat="server" ID="txtTerminationDate" MaxLength="10" AutoCompleteType="Disabled" Enabled="false" />&nbsp;
                            <asp:Image runat="server" ID="imgTerminationDateCal" ImageUrl="~/images/icons/calendar.png" 
                                Width="16" Height="16" />
                            <ajax:CalendarExtender runat="server" ID="ajaxCalTerminationDate" TargetControlID="txtTerminationDate" 
                                Format="d" PopupButtonID="imgTerminationDateCal" />
                        </asp:TableCell>
                    </asp:TableRow>
    
                    ...
    
                </asp:Table>
              </asp:WizardStep>
    Part of the code-behind:
    Code:
    strQuery = "INSERT INTO app_person " +
                "(first_name, " +       // 1
                 ...
                 "hire_date, " +        // 21
                 "termination_date, " + // 22
                 "date_of_birth, " +    // 37
                 ...
                 ") " +
    
                 "VALUES " +
    
                "(@FirstName, " +       // 1
                 ...
                 "@HireDate, " +        // 21
                 "@TerminationDate, " + // 22
                 "@BirthDate, " +     // 37
                 ...
                 "); " +             
                 "SELECT SCOPE_IDENTITY();"; // gets person_id of person inserted
    
    ...
    
    sqlCmd.Parameters.Add("@FirstName",     SqlDbType.VarChar).Value = ((TextBox)cpStep1.FindControl("txtFirstName")).Text;
    sqlCmd.Parameters.Add("@BirthDate",       SqlDbType.DateTime).Value = ((CalendarExtender)cpStep1_5.FindControl("ajaxCalBirthDate")).SelectedDate;
                    sqlCmd.Parameters["@BirthDate"].DbType = DbType.DateTime;
                sqlCmd.Parameters.Add("@HireDate",        SqlDbType.DateTime).Value = ((CalendarExtender)cpStep1_5.FindControl("ajaxCalHireDate")).SelectedDate;
                    sqlCmd.Parameters["@HireDate"].DbType = DbType.DateTime;
                sqlCmd.Parameters.Add("@TerminationDate", SqlDbType.DateTime).Value = ((CalendarExtender)cpStep1_5.FindControl("ajaxCalTerminationDate")).SelectedDate;
                    sqlCmd.Parameters["@TerminationDate"].DbType = DbType.DateTime;
    
    ...
    
    sqlCmd.CommandText = strQuery;
                
                strPersonID = sqlCmd.ExecuteScalar().ToString();  // EXCEPTION THROWN HERE
    
    ...
    Last edited by mateoc15; April 9th, 2009 at 12:26 PM. Reason: no title
    Discontent is the first necessity of progress. - Edison
  2. #2
  3. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    One more interesting thing is that the exception details show that the name field is size 8000 but the properties in the database explorer shows 50 (which is the way it was actually set up). Not sure if that helps or not.
    Discontent is the first necessity of progress. - Edison
  4. #3
  5. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,440
    Rep Power
    4539
    I don't do net, sorry. There is a net forum here if no one jumps in this thread. As a guess, the error message you showed sounded like the sp didn't receive one of the parameters you thought were being sent to it. There are some sql tracing and debug tools that may show you the exact sql that's being received by the db server.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo