Remove From My Forums

Data type mismatch in criteria expression.

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>

<body>

<table>

<tr >

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"

AutoGenerateColumns="False" CellPadding="4" DataKeyNames="ID"

DataSourceID="AccessDataSource1" ForeColor="#333333" GridLines="None">

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

</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] = ?">

<asp:Parameter Name="ID" Type="Int32" />

</DeleteParameters>

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

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

</tr>

</table>

</body>

</html>

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

Sub Log_out (Sender as Object, E as EventArgs)

FormsAuthentication.SignOut()

Session.Clear()

Response.Redirect("Default.aspx")

End Sub

</script>

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

0

Sign in to vote
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

0

Sign in to vote

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
0

Sign in to vote
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

Answered by:

Data type mismatch in criteria expression.

Question

Answers

All replies