Almost everything saving to the database but the dates? InsertCommand

Question

User-847567857 posted

When I try to insert a record the Filing Date and Report Time fields are always blank in the database.

The correct values are in the field when I press 'insert', but every time its blank in the db.

Can anyone give me some ideas how to fix this? 

When I try to insert a record the Filing Date and Report Time fields are always blank in the database.

The correct values are in the field when I press 'insert', but every time its blank in the db.

Can anyone give me some ideas how to fix this? 

<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="408px" 
        AutoGenerateInsertButton="True" AutoGenerateRows="False" DataKeyNames="ID" 
        DataSourceID="AccessDataSource1"  DefaultMode="Insert" 
        EnableModelValidation="True" HeaderText="Add Job to Board" 
        style="margin-right: 370px">
        <Fields>
            <asp:TemplateField HeaderText="Filing Date:" SortExpression="filingDate">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("filingDate") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="txtCDate" runat="server" Text='<%# FormatDateTime(dateformat.toshortdate) %>' ></asp:TextBox>
                   
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("filingDate") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="filingDay" HeaderText="Day:" 
                SortExpression="filingDay" />
            <asp:TemplateField HeaderText="Report Date:" SortExpression="reportDate">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("reportDate") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("reportDate") %>'></asp:TextBox><br />
                    <asp:RangeValidator ID="RangeValidator1" runat="server" ErrorMessage="RangeValidator" MaximumValue="12/31/2020" MinimumValue="01/01/2002" Type="Date" ControlToValidate="TextBox3" Text="enter in 00/00/2010 date format."></asp:RangeValidator>
                  
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label4" runat="server" Text='<%# Bind("reportDate") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Time:" SortExpression="reportTime">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("reportTime") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox2" runat="server" Text='<%# FormatDateTime(Now, DateFormat.longtime) %>'></asp:TextBox>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("reportTime") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="company" HeaderText="Company:" 
                SortExpression="company" />
            <asp:BoundField DataField="jobsite" HeaderText="Jobsite:" 
                SortExpression="jobsite" />
            <asp:BoundField DataField="location" HeaderText="Location:" 
                SortExpression="location" />
            <asp:BoundField DataField="county" HeaderText="County:" 
                SortExpression="county" Visible="False" />
            <asp:BoundField DataField="duration" HeaderText="Duration:" 
                SortExpression="duration" />
            <asp:BoundField DataField="orderedBy" HeaderText="Ordered By:" 
                SortExpression="orderedBy" Visible="False" />
            <asp:TemplateField HeaderText="Drug Test:" SortExpression="drugTestReq">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("drugTestReq") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                <asp:RadioButton ID="dt1" runat="server" Checked='<%# IIf(Eval("drugTestReq")=1,false,true) %>'
Text="Yes" GroupName="dt" />
<asp:RadioButton ID="dt2" runat="server" Checked='<%# IIf(Eval("drugTestReq")=0,false,true)%>'
Text="No" GroupName="dt" />
                    
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label3" runat="server" Text='<%# Bind("drugTestReq") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="foreman" HeaderText="Foreman:" 
                SortExpression="foreman" />
            <asp:BoundField DataField="instructions" HeaderText="Instructions:" 
                SortExpression="instructions" />
            <asp:BoundField DataField="phone" HeaderText="Phone:" SortExpression="phone" 
                Visible="False" />
            <asp:BoundField DataField="pagerNo" HeaderText="Pager No:" 
                SortExpression="pagerNo" Visible="False" />
            <asp:BoundField DataField="jobSteward" HeaderText="Job Steward:" 
                SortExpression="jobSteward" />
            <asp:BoundField DataField="callback" HeaderText="Callback:" 
                SortExpression="callback" Visible="False" />
            <asp:BoundField DataField="other" HeaderText="Other:" SortExpression="other" 
                Visible="False" />
            <asp:BoundField DataField="directions" HeaderText="Directions:" 
                SortExpression="directions" Visible="False" />
            <asp:TemplateField HeaderText="# Plumbers:" SortExpression="nPlumbers">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("nPlumbers") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("nPlumbers") %>'></asp:TextBox>
                    <br />
                    <asp:RegularExpressionValidator ID="RegularExpressionValcidator1" runat="server" ErrorMessage="Enter numbers only, no letters." ControlToValidate="TextBox4" ValidationExpression="^[0-9]*$"></asp:RegularExpressionValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label5" runat="server" Text='<%# Bind("nPlumbers") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="# Fitters:" SortExpression="nFitters">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("nFitters") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("nFitters") %>'></asp:TextBox>
                    <br /> <asp:RegularExpressionValidator ID="RegularvExpressionValidator1" runat="server" ErrorMessage="Enter numbers only, no letters." ControlToValidate="TextBox5" ValidationExpression="^[0-9]*$"></asp:RegularExpressionValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label6" runat="server" Text='<%# Bind("nFitters") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="# Welders:" SortExpression="nWelders">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox7" runat="server" Text='<%# Bind("nWelders") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("nWelders") %>'></asp:TextBox>
                    <br /> <asp:RegularExpressionValidator ID="RegularExpressdionValidator1" runat="server" ErrorMessage="Enter numbers only, no letters." ControlToValidate="TextBox6" ValidationExpression="^[0-9]*$"></asp:RegularExpressionValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label7" runat="server" Text='<%# Bind("nWelders") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="# Tradesmen:" SortExpression="nTradesmen">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox8" runat="server" Text='<%# Bind("nTradesmen") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox7" runat="server" Text='<%# Bind("nTradesmen") %>'></asp:TextBox>
                    <br /> <asp:RegularExpressionValidator ID="RegularExpressiodnValidator1" runat="server" ErrorMessage="Enter numbers only, no letters." ControlToValidate="TextBox7" ValidationExpression="^[0-9]*$"></asp:RegularExpressionValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label8" runat="server" Text='<%# Bind("nTradesmen") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="# PlAppr:" SortExpression="nPlAppr">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox9" runat="server" Text='<%# Bind("nPlAppr") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox8" runat="server" Text='<%# Bind("nPlAppr") %>'></asp:TextBox>
                    <br /> <asp:RegularExpressionValidator ID="RegularExpressionValidatoar1" runat="server" ErrorMessage="Enter numbers only, no letters." ControlToValidate="TextBox8" ValidationExpression="^[0-9]*$"></asp:RegularExpressionValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label9" runat="server" Text='<%# Bind("nPlAppr") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="# FitAppr:" SortExpression="nFitAppr">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox10" runat="server" Text='<%# Bind("nFitAppr") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox9" runat="server" Text='<%# Bind("nFitAppr") %>'></asp:TextBox>
                    <br /> <asp:RegularExpressionValidator ID="RegularExpressionValidator1a" runat="server" ErrorMessage="Enter numbers only, no letters." ControlToValidate="TextBox9" ValidationExpression="^[0-9]*$"></asp:RegularExpressionValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label10" runat="server" Text='<%# Bind("nFitAppr") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="# ServTech:" SortExpression="nServTech">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox11" runat="server" Text='<%# Bind("nServTech") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox10" runat="server" Text='<%# Bind("nServTech") %>'></asp:TextBox>
                    <br /> <asp:RegularExpressionValidator ID="RegularExpressionValidator10" runat="server" ErrorMessage="Enter numbers only, no letters." ControlToValidate="TextBox10" ValidationExpression="^[0-9]*$"></asp:RegularExpressionValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label11" runat="server" Text='<%# Bind("nServTech") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="# Helpers:" SortExpression="nHelpers">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox12" runat="server" Text='<%# Bind("nHelpers") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox11" runat="server" Text='<%# Bind("nHelpers") %>'></asp:TextBox>
                    <br /> <asp:RegularExpressionValidator ID="RegularExpressionValidator1x" runat="server" ErrorMessage="Enter numbers only, no letters." ControlToValidate="TextBox11" ValidationExpression="^[0-9]*$"></asp:RegularExpressionValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label12" runat="server" Text='<%# Bind("nHelpers") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="fiftyPercent" HeaderText="50% Letter:" 
                SortExpression="fiftyPercent" Visible="False" />
        </Fields>
    </asp:DetailsView>		
                                            </div>
    <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
        DataFile="~/Local72.mdb" SelectCommand="SELECT * FROM [tblJobs]" 
        DeleteCommand="DELETE FROM [tblJobs] WHERE [ID] = ?" 
        InsertCommand="INSERT INTO [tblJobs] ([filingDate], [filingDay], [reportDate], [reportTime], [company], [jobsite], [location], [county], [duration], [orderedBy], [drugTestReq], [foreman], [instructions], [phone], [pagerNo], [jobSteward], [callback], [other], [directions], [nPlumbers], [nFitters], [nWelders], [nTradesmen], [nPlAppr], [nFitAppr], [nServTech], [nHelpers], [fiftyPercent]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" 
        UpdateCommand="UPDATE [tblJobs] SET [filingDate] = ?, [filingDay] = ?, [reportDate] = ?, [reportTime] = ?, [company] = ?, [jobsite] = ?, [location] = ?, [county] = ?, [duration] = ?, [orderedBy] = ?, [drugTestReq] = ?, [foreman] = ?, [instructions] = ?, [phone] = ?, [pagerNo] = ?, [jobSteward] = ?, [callback] = ?, [other] = ?, [directions] = ?, [nPlumbers] = ?, [nFitters] = ?, [nWelders] = ?, [nTradesmen] = ?, [nPlAppr] = ?, [nFitAppr] = ?, [nServTech] = ?, [nHelpers] = ?, [fiftyPercent] = ?, [tPlumbers] = ?, [tFitters] = ?, [tWelders] = ?, [tTradesmen] = ?, [tPlAppr] = ?, [tFitAppr] = ?, [tServTech] = ?, [tHelpers] = ? WHERE [ID] = ?">
        <DeleteParameters>
            <asp:Parameter Name="ID" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="filingDate" Type="DateTime" />
            <asp:Parameter Name="filingDay" Type="String" />
            <asp:Parameter Name="reportDate" Type="DateTime" />
            <asp:Parameter Name="reportTime" Type="String" />
            <asp:Parameter Name="company" Type="String" />
            <asp:Parameter Name="jobsite" Type="String" />
            <asp:Parameter Name="location" Type="String" />
            <asp:Parameter Name="county" Type="String" />
            <asp:Parameter Name="duration" Type="String" />
            <asp:Parameter Name="orderedBy" Type="String" />
            <asp:Parameter Name="drugTestReq" Type="String" />
            <asp:Parameter Name="foreman" Type="String" />
            <asp:Parameter Name="instructions" Type="String" />
            <asp:Parameter Name="phone" Type="String" />
            <asp:Parameter Name="pagerNo" Type="String" />
            <asp:Parameter Name="jobSteward" Type="String" />
            <asp:Parameter Name="callback" Type="String" />
            <asp:Parameter Name="other" Type="String" />
            <asp:Parameter Name="directions" Type="String" />
            <asp:Parameter Name="nPlumbers" Type="Int32" />
            <asp:Parameter Name="nFitters" Type="Int32" />
            <asp:Parameter Name="nWelders" Type="Int32" />
            <asp:Parameter Name="nTradesmen" Type="Int32" />
            <asp:Parameter Name="nPlAppr" Type="Int32" />
            <asp:Parameter Name="nFitAppr" Type="Int32" />
            <asp:Parameter Name="nServTech" Type="Int32" />
            <asp:Parameter Name="nHelpers" Type="Int32" />
            <asp:Parameter Name="fiftyPercent" Type="String" />
            <asp:Parameter Name="tPlumbers" Type="String" />
            <asp:Parameter Name="tFitters" Type="String" />
            <asp:Parameter Name="tWelders" Type="String" />
            <asp:Parameter Name="tTradesmen" Type="String" />
            <asp:Parameter Name="tPlAppr" Type="String" />
            <asp:Parameter Name="tFitAppr" Type="String" />
            <asp:Parameter Name="tServTech" Type="String" />
            <asp:Parameter Name="tHelpers" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="filingDate" Type="DateTime" />
            <asp:Parameter Name="filingDay" Type="String" />
            <asp:Parameter Name="reportDate" Type="DateTime" />
            <asp:Parameter Name="reportTime" Type="String" />
            <asp:Parameter Name="company" Type="String" />
            <asp:Parameter Name="jobsite" Type="String" />
            <asp:Parameter Name="location" Type="String" />
            <asp:Parameter Name="county" Type="String" />
            <asp:Parameter Name="duration" Type="String" />
            <asp:Parameter Name="orderedBy" Type="String" />
            <asp:Parameter Name="drugTestReq" Type="String" />
            <asp:Parameter Name="foreman" Type="String" />
            <asp:Parameter Name="instructions" Type="String" />
            <asp:Parameter Name="phone" Type="String" />
            <asp:Parameter Name="pagerNo" Type="String" />
            <asp:Parameter Name="jobSteward" Type="String" />
            <asp:Parameter Name="callback" Type="String" />
            <asp:Parameter Name="other" Type="String" />
            <asp:Parameter Name="directions" Type="String" />
            <asp:Parameter Name="nPlumbers" Type="Int32" />
            <asp:Parameter Name="nFitters" Type="Int32" />
            <asp:Parameter Name="nWelders" Type="Int32" />
            <asp:Parameter Name="nTradesmen" Type="Int32" />
            <asp:Parameter Name="nPlAppr" Type="Int32" />
            <asp:Parameter Name="nFitAppr" Type="Int32" />
            <asp:Parameter Name="nServTech" Type="Int32" />
            <asp:Parameter Name="nHelpers" Type="Int32" />
            <asp:Parameter Name="fiftyPercent" Type="String" />
            <asp:Parameter Name="tPlumbers" Type="String" />
            <asp:Parameter Name="tFitters" Type="String" />
            <asp:Parameter Name="tWelders" Type="String" />
            <asp:Parameter Name="tTradesmen" Type="String" />
            <asp:Parameter Name="tPlAppr" Type="String" />
            <asp:Parameter Name="tFitAppr" Type="String" />
            <asp:Parameter Name="tServTech" Type="String" />
            <asp:Parameter Name="tHelpers" Type="String" />
            <asp:Parameter Name="ID" Type="Int32" />
        </UpdateParameters>
    </asp:AccessDataSource>

Answer 1

User271624304 posted

If u'll debug this code...u'll get to know why it's not getting updated into the databse.

The problem is related with the DataType.  Check it out.

 

.NET Data Types vs Sql Server Data Types 

Answer 2

User-847567857 posted

Not sure what you mean...

02/22/2010 should work, it should be the date data type for both.

What am I doing wrong?

Answer 3

User-1199946673 posted

Can anyone give me some ideas how to fix this? 

 

 

Next time, try to narrow your code to the problem that you have:

        <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
            DataKeyNames="ID" DataSourceID="AccessDataSource1">
            <Fields>
                <asp:BoundField DataField="fillingdate" HeaderText="fillingdate" SortExpression="fillingdate" />
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/App_Data/local72.mdb" 
            DeleteCommand="DELETE FROM tblJobs WHERE (ID = ?)" 
            InsertCommand="INSERT INTO tblJobs(fillingdate) VALUES (?)" 
            SelectCommand="SELECT ID, fillingdate FROM tblJobs" 
            UpdateCommand="UPDATE tblJobs SET fillingdate = ? WHERE (ID = ?)">
            <DeleteParameters>
                <asp:Parameter Name="ID" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="fillingdate" Type="DateTime" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="fillingdate" Type="DateTime" />
                <asp:Parameter Name="ID" Type="Int32" />
            </UpdateParameters>
        </asp:AccessDataSource>

This is working for me. Are your sure your databasefield(s) are of type DateTime?

Also note that I placed the databasefile in the App_Data folder, to prevent it from being downloaded directly...

 

Answer 4

User-847567857 posted

Yes, thanks for your time.

The type in the db is DateTime.

There is a value in the textbox, just went I hit save it saves everything but that.

Answer 5

User271624304 posted

The type i n db is DateTime and in Front-end...it's not matching with the database's datatype i think.  Have you checked this problem?