locked
Cannot pass datetime value programmatically through SqlDataSource to Access 2013 database RRS feed

  • Question

  • User-910585370 posted

    ASP.NET form:

                <asp:SqlDataSource ID="dsLoan" runat="server" ConnectionString="<%$ ConnectionStrings:connLibrarySystem %>"
                    ProviderName="<%$ ConnectionStrings:connLibrarySystem.ProviderName %>"
                    SelectCommand="SELECT TOP 1 LOAN.LoanID FROM (BOOKS INNER JOIN LOAN ON BOOKS.ACCESSION = LOAN.ACCESSION) WHERE (BOOKS.ACCESSION = @Accession) ORDER BY LOAN.LoanID DESC"
                    UpdateCommand="UPDATE LOAN SET DATE_OF_RETURN = @ReturnDate, OverDate = 3 WHERE (LOAN.LoanID = @LoanID)">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="tbAccession" Name="Accession" PropertyName="Text" Type="String" />
                    </SelectParameters>
                    <UpdateParameters>
                        <asp:SessionParameter SessionField="LoanID" Name="LoanID" Type="Int32" />
                        <asp:Parameter Name="ReturnDate" Type="DateTime" />
                    </UpdateParameters>
                </asp:SqlDataSource>

    C# Function

    protected void btnBookReturn_Click(object sender, EventArgs e)
        {
            // Select the latest LoanID based on the specified Book Accession
            int loanID = int.Parse((this.dsLoan.Select(DataSourceSelectArguments.Empty) as DataView).Table.Rows[0]["LoanID"].ToString());
            Session["LoanID"] = loanID;
    
            // Update the Return Date of the corresponding loan record to today
            this.dsLoan.UpdateParameters["ReturnDate"].DefaultValue = DateTime.Today.ToShortDateString();
    
            // Update the Loan record
            this.dsLoan.Update();

    After the update is completed, the Overdate column in the target record is updated but the DATE_OF_RETURN column remains unchanged.

    Even if I have insert the following code snippet inside the SqldDataSource and update the C# code as belows,

    OnUpdating="dsLoan_Updating"
        protected void dsLoan_Updating(object sender, SqlDataSourceCommandEventArgs e)
        {
            e.Command.Parameters["ReturnDate"].Value = DateTime.Today;
        }

    The problem still occurs.

    Please advice how to solve this problem. Thanks in advance.

    Friday, May 16, 2014 12:10 AM

Answers

  • User1918509225 posted

    Hi Frankie,

    I suggest you can use c# ,like below:

                string sql = "insert into sqldemo(Id,TimeDemo) values(@Id,@timedemo)";
                string connstring = "Data Source=xx;Initial Catalog=xx;User Id=sa;Password=xx;";
                SqlConnection conn = new SqlConnection(connstring);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Id", 2); 
    //below equals DateAdd('d',14,DateTime.Today); cmd.Parameters.AddWithValue("@timedemo",DateTime.Today.AddDays(14)); cmd.ExecuteNonQuery();

    Hope it can help you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 20, 2014 12:29 AM
  • User-910585370 posted

    Kevin,

    Thank you. The above problem has been solved by invoking the OnInserting event in the SqlDataSource object with the following code:

    e.Command.Parameter["DueDate"].Value = DateTime.Today.DateAdd(14);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 23, 2014 4:30 AM

All replies

  • User724169276 posted

    Hello Frankie,

    Try changing the format of your datetime.

    string result= String.Format("{0:yyyy-MM-dd hh:mm:ssss:ffffff}", DateTime.Today);
    e.Command.Parameters["ReturnDate"].Value = result;

    Friday, May 16, 2014 12:30 AM
  • User-1716253493 posted
    You can do without parameter SET DATE_OF_RETURN = Cast(getdate() as date)
    Friday, May 16, 2014 12:33 AM
  • User-910585370 posted

    Thank you for all your advices, the problem have been solved by changing the .asx code as follows:

    SET DATE_OF_RETURN = Date()

    Moreover, the insert function inside the SqlDataSource snippet at another web pages seems to have syntax error due to the DateAdd() function.

    InsertCommand="insert into LOAN (USERID, ACCESSION, DATE_OF_LOAN, DATE_OF_DUE) values (@UserID,@Accession,Date(),DateAdd('d', 14, Date())"

    So, is it possible to invoke Date() directly as a parameter inside DateAdd()?

    Any advice will be a great help.
    Thanks in advance.

    Sunday, May 18, 2014 10:41 PM
  • User1918509225 posted

    Hi Frankie Leung,

    For your problem,you can not invoke Date() directly in query string.

    Please try the code below:

    SET dateTime=Date();
    InsertCommand="insert into LOAN (USERID, ACCESSION, DATE_OF_LOAN, DATE_OF_DUE) values (@UserID,@Accession,Date(),DateAdd('d', 14,@dateTime)"
    

    Hope it can help you.

    Monday, May 19, 2014 1:13 AM
  • User-910585370 posted

    Thanks for your advice. However the program returns the following error after using the above code.

    Invalid SQL statement; expected to 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

    So, I have tried to using the .DefaultValue attribute in the C# code, i.e.

    this.dsBookBorrow.InsertParameters["dateTime"].DefaultValue = DateTime.Today.ToString();

    The following error returned:
    Query expression 'DateAdd (' d ', 14, @ today);' Syntax error.

    Please help.

    Monday, May 19, 2014 5:22 AM
  • User1918509225 posted

    Hi Frankie,

    I suggest you can use c# ,like below:

                string sql = "insert into sqldemo(Id,TimeDemo) values(@Id,@timedemo)";
                string connstring = "Data Source=xx;Initial Catalog=xx;User Id=sa;Password=xx;";
                SqlConnection conn = new SqlConnection(connstring);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Id", 2); 
    //below equals DateAdd('d',14,DateTime.Today); cmd.Parameters.AddWithValue("@timedemo",DateTime.Today.AddDays(14)); cmd.ExecuteNonQuery();

    Hope it can help you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 20, 2014 12:29 AM
  • User-910585370 posted

    Kevin,

    Thank you. The above problem has been solved by invoking the OnInserting event in the SqlDataSource object with the following code:

    e.Command.Parameter["DueDate"].Value = DateTime.Today.DateAdd(14);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 23, 2014 4:30 AM