locked
Data type mismatch in criteria expression. RRS feed

  • Question

  • User1883103525 posted

    <%@ Page Language="VB" AutoEventWireup="true" Debug="true"   %>
    <%@ Import namespace="System.Data" %>
    <%@ Import namespace="System.Data.OleDb" %>
    
    <html>
    <form runat="server">
    <body>
    <table>        
          <tr >
              <td colspan="11" >
              <asp:GridView ID="GridView1" runat="server" AllowSorting="True" 
                AutoGenerateColumns="False" CellPadding="4" DataKeyNames="ID" 
                DataSourceID="AccessDataSource1" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" />
                <Columns>
                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" 
                        ShowSelectButton="True" />
                    <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                        ReadOnly="True" SortExpression="ID" />
                    <asp:BoundField DataField="OrderID" HeaderText="OrderID" 
                        SortExpression="OrderID" />
                    <asp:BoundField DataField="LeadTech" HeaderText="LeadTech" 
                        SortExpression="LeadTech" />
                    <asp:BoundField DataField="OtherTechs" HeaderText="OtherTechs" 
                        SortExpression="OtherTechs" />
                    <asp:BoundField DataField="ProgrammingDone" HeaderText="ProgrammingDone" 
                        SortExpression="ProgrammingDone" />
                    <asp:BoundField DataField="CircuitID" HeaderText="CircuitID" 
                        SortExpression="CircuitID" />
                    <asp:BoundField DataField="Status" HeaderText="Status" 
                        SortExpression="Status" />
                    <asp:BoundField DataField="DueDate" HeaderText="DueDate" 
                        SortExpression="DueDate" />
                    <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
                    <asp:BoundField DataField="DateCompleted" HeaderText="DateCompleted" 
                        SortExpression="DateCompleted" />
                    <asp:BoundField DataField="DateInvoiced" HeaderText="DateInvoiced" 
                        SortExpression="DateInvoiced" />
                    <asp:BoundField DataField="HoursID" HeaderText="HoursID" 
                        SortExpression="HoursID" />
                </Columns>
                <EditRowStyle BackColor="#7C6F57" />
                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#E3EAEB" />
                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#F8FAFA" />
                <SortedAscendingHeaderStyle BackColor="#246B61" />
                <SortedDescendingCellStyle BackColor="#D4DFE1" />
                <SortedDescendingHeaderStyle BackColor="#15524A" />
            </asp:GridView>
            <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
                DataFile="DATABASE" 
    DeleteCommand="DELETE FROM [Tickets] WHERE [ID] = ?" InsertCommand="INSERT INTO [Tickets] ([ID], [OrderID], [LeadTech], [OtherTechs], [ProgrammingDone], [CircuitID], [Status], [DueDate], [Notes], [DateCompleted], [DateInvoiced], [HoursID]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" SelectCommand="SELECT * FROM [Tickets]" UpdateCommand="UPDATE [Tickets] SET [OrderID] = ?, [LeadTech] = ?, [OtherTechs] = ?, [ProgrammingDone] = ?, [CircuitID] = ?, [Status] = ?, [DueDate] = ?, [Notes] = ?, [DateCompleted] = ?, [DateInvoiced] = ?, [HoursID] = ? WHERE [ID] = ?"> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="OrderID" Type="Int32" /> <asp:Parameter Name="LeadTech" Type="Int32" /> <asp:Parameter Name="OtherTechs" Type="Int32" /> <asp:Parameter Name="ProgrammingDone" Type="String" /> <asp:Parameter Name="CircuitID" Type="Int32" /> <asp:Parameter Name="Status" Type="Int32" /> <asp:Parameter Name="DueDate" Type="DateTime" /> <asp:Parameter Name="Notes" Type="String" /> <asp:Parameter Name="DateCompleted" Type="DateTime" /> <asp:Parameter Name="DateInvoiced" Type="DateTime" /> <asp:Parameter Name="HoursID" Type="Int32" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="OrderID" Type="Int32" /> <asp:Parameter Name="LeadTech" Type="Int32" /> <asp:Parameter Name="OtherTechs" Type="Int32" /> <asp:Parameter Name="ProgrammingDone" Type="String" /> <asp:Parameter Name="CircuitID" Type="Int32" /> <asp:Parameter Name="Status" Type="Int32" /> <asp:Parameter Name="DueDate" Type="DateTime" /> <asp:Parameter Name="Notes" Type="String" /> <asp:Parameter Name="DateCompleted" Type="DateTime" /> <asp:Parameter Name="DateInvoiced" Type="DateTime" /> <asp:Parameter Name="HoursID" Type="Int32" /> </UpdateParameters> </asp:AccessDataSource> </td> </tr> <tr> <td>ID</td> <td>OrderID</td> <td>LeadTech</td> <td>Programming Done</td> <td>CircuitID</td> <td>Status</td> <td>DueDate</td> <td>Notes</td> <td>Date Completed</td> <td>Date Invoiced</td> </tr> <tr> <td><asp:Button id="btnInsert" runat="server" OnClick="btnInsert_Click" text="INSERT!" /></td> <td><asp:TextBox ID="Field1" runat="server" /></td> <td><asp:TextBox ID="Field2" runat="server" /></td> <td><asp:TextBox ID="Field3" runat="server" /></td> <td><asp:TextBox ID="Field4" runat="server" /></td> <td><asp:TextBox ID="Field5" runat="server" /></td> <td><asp:TextBox ID="Field6" runat="server" /></td> <td><asp:TextBox ID="Field7" runat="server" /></td> <td><asp:TextBox ID="Field8" runat="server" /></td> <td><asp:TextBox ID="Field9" runat="server" /></td> </tr> <tr> <td align="left"> <asp:Button ID="Button2" OnClick="Go_Back" Text="Main Menu" runat="server" /></td> </tr> <tr> <td align="left"> <asp:Button ID="Button1" OnClick="Log_Out" Text="Logout" runat="server" /></td> </tr> </table> </body> </html> <script language="VB" runat="server"> Dim objConnection2 As OleDbConnection Sub Page_Load(Source as Object, E as EventArgs) objConnection2 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " & _ "data source=" + MapPath("DATABASE")) End Sub Sub btnInsert_Click(Sender As Object, E As EventArgs) If Page.IsValid Then Dim strSQL As String = "INSERT INTO Tickets " & _ "(OrderID, LeadTech, ProgrammingDone, CircuitID, Status, DueDate, Notes, DateCompleted, DateInvoiced)" & _ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)" Dim dbComm As New OleDbCommand(strSQL, objConnection2) dbComm.Parameters.Add("OrderID", OleDbType.VarChar, 10, "OrderID") dbComm.Parameters.Add("LeadTech", OleDbType.VarChar, 10, "LeadTech") dbComm.Parameters.Add("ProgrammingDone", OleDbType.VarChar, 250, "ProgrammingDone") dbComm.Parameters.Add("CircuitID", OleDbType.VarChar, 30, "CircuitID") dbComm.Parameters.Add("Status", OleDbType.VarChar, 10, "Status") dbComm.Parameters.Add("DueDate", OleDbType.VarChar, 10, "DueDate") dbComm.Parameters.Add("Notes", OleDbType.VarChar, 250, "Notes") dbComm.Parameters.Add("DateCompleted", OleDbType.VarChar, 10, "DateCompleted") dbComm.Parameters.Add("DateInvoiced", OleDbType.VarChar, 10, "DateInvoiced") dbComm.Parameters("OrderID").Value = Field1.Text dbComm.Parameters("LeadTech").Value = Field2.Text dbComm.Parameters("ProgrammingDone").Value = Field3.Text dbComm.Parameters("CircuitID").Value = Field4.Text dbComm.Parameters("Status").Value = Field5.Text dbComm.Parameters("DueDate").Value = Field6.Text dbComm.Parameters("Notes").Value = Field7.Text dbComm.Parameters("DateCompleted").Value = Field8.Text dbComm.Parameters("DateInvoiced").Value = Field9.Text Try objConnection2.Open() dbComm.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message) Response.End() Finally If objConnection2.State = ConnectionState.Open Then objConnection2.Close() End If End Try Response.Redirect("site2") Response.End End If End Sub </script> </form> <% If Session("username")="" THEN Response.Redirect("Default.aspx") Else End If %> <script runat="server"> Sub Log_out (Sender as Object, E as EventArgs) FormsAuthentication.SignOut() Session.Clear() Response.Redirect("Default.aspx") End Sub </script> <script runat="server"> Sub Go_Back(ByVal Sender As Object, ByVal E As EventArgs) Response.Redirect("router.aspx") End Sub </script>



    <%@ Page Language="VB" AutoEventWireup="true" Debug="true"   %>
    <%@ Import namespace="System.Data" %>
    <%@ Import namespace="System.Data.OleDb" %>
    <html>
    <form runat="server">
    <body>
    <table>        
          <tr >
              <td colspan="11" >
              <asp:GridView ID="GridView1" runat="server" AllowSorting="True" 
                AutoGenerateColumns="False" CellPadding="4" DataKeyNames="ID" 
                DataSourceID="AccessDataSource1" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" />
                <Columns>
                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" 
                        ShowSelectButton="True" />
                    <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                        ReadOnly="True" SortExpression="ID" />
                    <asp:BoundField DataField="OrderID" HeaderText="OrderID" 
                        SortExpression="OrderID" />
                    <asp:BoundField DataField="LeadTech" HeaderText="LeadTech" 
                        SortExpression="LeadTech" />
                    <asp:BoundField DataField="OtherTechs" HeaderText="OtherTechs" 
                        SortExpression="OtherTechs" />
                    <asp:BoundField DataField="ProgrammingDone" HeaderText="ProgrammingDone" 
                        SortExpression="ProgrammingDone" />
                    <asp:BoundField DataField="CircuitID" HeaderText="CircuitID" 
                        SortExpression="CircuitID" />
                    <asp:BoundField DataField="Status" HeaderText="Status" 
                        SortExpression="Status" />
                    <asp:BoundField DataField="DueDate" HeaderText="DueDate" 
                        SortExpression="DueDate" />
                    <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
                    <asp:BoundField DataField="DateCompleted" HeaderText="DateCompleted" 
                        SortExpression="DateCompleted" />
                    <asp:BoundField DataField="DateInvoiced" HeaderText="DateInvoiced" 
                        SortExpression="DateInvoiced" />
                    <asp:BoundField DataField="HoursID" HeaderText="HoursID" 
                        SortExpression="HoursID" />
                </Columns>
                <EditRowStyle BackColor="#7C6F57" />
                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#E3EAEB" />
                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#F8FAFA" />
                <SortedAscendingHeaderStyle BackColor="#246B61" />
                <SortedDescendingCellStyle BackColor="#D4DFE1" />
                <SortedDescendingHeaderStyle BackColor="#15524A" />
            </asp:GridView>
            <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
                DataFile="~\App_Data\BaySt1861DBV1.accdb" 
                DeleteCommand="DELETE FROM [Tickets] WHERE [ID] = ?" 
                InsertCommand="INSERT INTO [Tickets] ([ID], [OrderID], [LeadTech], [OtherTechs], [ProgrammingDone], [CircuitID], [Status], [DueDate], [Notes], [DateCompleted], [DateInvoiced], [HoursID]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" 
                SelectCommand="SELECT * FROM [Tickets]" 
                UpdateCommand="UPDATE [Tickets] SET [OrderID] = ?, [LeadTech] = ?, [OtherTechs] = ?, [ProgrammingDone] = ?, [CircuitID] = ?, [Status] = ?, [DueDate] = ?, [Notes] = ?, [DateCompleted] = ?, [DateInvoiced] = ?, [HoursID] = ? WHERE [ID] = ?">
                <DeleteParameters>
                    <asp:Parameter Name="ID" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="ID" Type="Int32" />
                    <asp:Parameter Name="OrderID" Type="Int32" />
                    <asp:Parameter Name="LeadTech" Type="Int32" />
                    <asp:Parameter Name="OtherTechs" Type="Int32" />
                    <asp:Parameter Name="ProgrammingDone" Type="String" />
                    <asp:Parameter Name="CircuitID" Type="Int32" />
                    <asp:Parameter Name="Status" Type="Int32" />
                    <asp:Parameter Name="DueDate" Type="DateTime" />
                    <asp:Parameter Name="Notes" Type="String" />
                    <asp:Parameter Name="DateCompleted" Type="DateTime" />
                    <asp:Parameter Name="DateInvoiced" Type="DateTime" />
                    <asp:Parameter Name="HoursID" Type="Int32" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="OrderID" Type="Int32" />
                    <asp:Parameter Name="LeadTech" Type="Int32" />
                    <asp:Parameter Name="OtherTechs" Type="Int32" />
                    <asp:Parameter Name="ProgrammingDone" Type="String" />
                    <asp:Parameter Name="CircuitID" Type="Int32" />
                    <asp:Parameter Name="Status" Type="Int32" />
                    <asp:Parameter Name="DueDate" Type="DateTime" />
                    <asp:Parameter Name="Notes" Type="String" />
                    <asp:Parameter Name="DateCompleted" Type="DateTime" />
                    <asp:Parameter Name="DateInvoiced" Type="DateTime" />
                    <asp:Parameter Name="HoursID" Type="Int32" />
                    <asp:Parameter Name="ID" Type="Int32" />
                </UpdateParameters>
            </asp:AccessDataSource> </td>
            </tr>
            <tr> 
                <td>ID</td>
       <td>OrderID</td>
       <td>LeadTech</td>
       <td>Programming Done</td>
       <td>CircuitID</td>
       <td>Status</td>
       <td>DueDate</td>
       <td>Notes</td>
       <td>Date Completed</td>
       <td>Date Invoiced</td>
            </tr>
            <tr>
                <td><asp:Button id="btnInsert" runat="server" OnClick="btnInsert_Click" text="INSERT!" /></td>
                <td><asp:TextBox ID="Field1" runat="server" /></td>
       <td><asp:TextBox ID="Field2" runat="server"  /></td>
       <td><asp:TextBox ID="Field3" runat="server"  /></td>
       <td><asp:TextBox ID="Field4" runat="server"  /></td>
       <td><asp:TextBox ID="Field5" runat="server"  /></td>
       <td><asp:TextBox ID="Field6" runat="server"  /></td>
       <td><asp:TextBox ID="Field7" runat="server"  /></td>
       <td><asp:TextBox ID="Field8" runat="server"  /></td>
       <td><asp:TextBox ID="Field9" runat="server"  /></td>
            </tr>
            <tr> <td align="left"> <asp:Button  ID="Button2" OnClick="Go_Back" Text="Main Menu" runat="server" /></td> </tr>
            <tr> <td align="left"> <asp:Button  ID="Button1" OnClick="Log_Out" Text="Logout" runat="server" /></td> </tr>
    </table>
    </body>
    </html>
    <script language="VB" runat="server">
        Dim objConnection2 As OleDbConnection
    Sub Page_Load(Source as Object, E as EventArgs)
            objConnection2 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " & _
                            "data source=" + MapPath("~\App_Data\BaySt1861DBV1.accdb"))
    End Sub
    Sub btnInsert_Click(Sender As Object, E As EventArgs)
      If Page.IsValid Then
        Dim strSQL As String = "INSERT INTO Tickets " & _
                        "(OrderID, LeadTech, ProgrammingDone, CircuitID, Status, DueDate, Notes, DateCompleted, DateInvoiced)" & _
                        " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
               
                Dim dbComm As New OleDbCommand(strSQL, objConnection2)
                
                dbComm.Parameters.Add("OrderID", OleDbType.VarChar, 10, "OrderID")
                dbComm.Parameters.Add("LeadTech", OleDbType.VarChar, 10, "LeadTech")
                dbComm.Parameters.Add("ProgrammingDone", OleDbType.VarChar, 250, "ProgrammingDone")
                dbComm.Parameters.Add("CircuitID", OleDbType.VarChar, 30, "CircuitID")
                dbComm.Parameters.Add("Status", OleDbType.VarChar, 10, "Status")
                dbComm.Parameters.Add("DueDate", OleDbType.VarChar, 10, "DueDate")
                dbComm.Parameters.Add("Notes", OleDbType.VarChar, 40, "Notes")
                dbComm.Parameters.Add("DateCompleted", OleDbType.VarChar, 10, "DateCompleted")
                dbComm.Parameters.Add("DateInvoiced", OleDbType.VarChar, 10, "DateInvoiced")
                           
                dbComm.Parameters("OrderID").Value = Field1.Text
                dbComm.Parameters("LeadTech").Value = Field2.Text
                dbComm.Parameters("ProgrammingDone").Value = Field3.Text
                dbComm.Parameters("CircuitID").Value = Field4.Text
                dbComm.Parameters("Status").Value = Field5.Text
                dbComm.Parameters("DueDate").Value = Field6.Text
                dbComm.Parameters("Notes").Value = Field7.Text
                dbComm.Parameters("DateCompleted").Value = Field8.Text
                dbComm.Parameters("DateInvoiced").Value = Field9.Text
            
        Try
                    objConnection2.Open()
          dbComm.ExecuteNonQuery()
                Catch ex As Exception
                    Response.Write(ex.Message)
                    Response.End()
        Finally
                    If objConnection2.State = ConnectionState.Open Then
                        objConnection2.Close()
                    End If
        End Try
    Response.Redirect("a_Order_Details.aspx")
    Response.End
      End If
    End Sub
    </script>
    </form>
    <% If Session("username")="" THEN
    Response.Redirect("Default.aspx")
    Else
    End If
    %>
    <script runat="server">
    Sub Log_out (Sender as Object, E as EventArgs) 
            FormsAuthentication.SignOut()
            Session.Clear()
    Response.Redirect("Default.aspx") 
    End Sub
    </script>
    <script runat="server">
        Sub Go_Back(ByVal Sender As Object, ByVal E As EventArgs)
            Response.Redirect("router.aspx")
        End Sub
    </script>

    I've had this working just last week, now I cant figure out why it stopped working. I've made no modifications to the database. 


    any ideas? 

    Wednesday, October 20, 2010 2:56 PM

Answers

  • User1883103525 posted

    I found the problem. 

    For some reason the page does not allow me to enter a null value in some fields. That was the problem. I've checked the database, it fully allows null values in all the fields. 

    Is there a preference that can be set in the code in order to allow null values ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 21, 2010 1:30 PM

All replies

  • User-1199946673 posted

    I've had this working just last week, now I cant figure out why it stopped working. I've made no modifications to the database. 


    any ideas? 

    You show a lot of code, but without telling where or when exactly you get the error. I see 2 potential problems. In line 57, the update command. The order in which the update parameters (lines 76 - 87) must be the same as the parameters appear in the updatecommand, meaning the ID parameter must be added last and not first

    And then the  parameters in lines 144, 146 an 147. I suspect the datatype of those fields in the table are of type Datetime, but you declare them as VarChar

     

    Wednesday, October 20, 2010 4:57 PM
  • User1883103525 posted

    I found the problem. 

    For some reason the page does not allow me to enter a null value in some fields. That was the problem. I've checked the database, it fully allows null values in all the fields. 

    Is there a preference that can be set in the code in order to allow null values ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 21, 2010 1:30 PM