locked
Convert empty string to Null value, Insert into a DB RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User1883103525 posted
    ;ve been getting the "Data Type Mismatch in Criteria expression." error. 
    <%@ 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\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="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\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.DBDate, 10, "DueDate")
            dbComm.Parameters.Add("Notes", OleDbType.VarChar, 250, "Notes")
            dbComm.Parameters.Add("DateCompleted", OleDbType.DBDate, 10, "DateCompleted")
            dbComm.Parameters.Add("DateInvoiced", OleDbType.DBDate, 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("ASPPAGE")
	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 been getting the "Data Type Mismatch in Criteria expression." error. 
There is no error if all the fields are filled in. Can someone give me the code to validate the empty fields so the insert command actually works?
Many thank you.
    Friday, October 22, 2010 11:30 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User1883103525 posted

    Got it. 

    I just needed to understand the correct syntax.

    (Chrome does not support clicking 'insert code' for some reason, here is copy paste) 

                If Field8.Text = "" Then

                    dbComm.Parameters.AddWithValue("WorkDetails", DBNull.Value)

                Else : dbComm.Parameters.AddWithValue("WorkDetails", Field6.Text)

                End If

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 5, 2010 12:19 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-1199946673 posted

     Simply use the AddWithValue Method, Less code and you don't have to think about datatypes and empty fields:

                dbComm.Parameters.AddWithValue("OrderID", Field1.Text)
            dbComm.Parameters.AddWithValue("LeadTech", Field2.Text)
            dbComm.Parameters.AddWithValue("ProgrammingDone", Field3.Text)
            dbComm.Parameters.AddWithValue("CircuitID", Field4.Text)
            dbComm.Parameters.AddWithValue("Status", Field5.Text)
            dbComm.Parameters.AddWithValue("DueDate", Field6.Text)
            dbComm.Parameters.AddWithValue("Notes", Field7.Text)
            dbComm.Parameters.AddWithValue("DateCompleted", Field8.Text)
            dbComm.Parameters.AddWithValue("DateInvoiced", Field9.Text)


     

    Friday, October 22, 2010 3:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User1883103525 posted

    I'm getting this error instead


    Data type mismatch in criteria expression.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

    Source Error: 

    Line 151:    Try
Line 152:                objConnection2.Open()
Line 153:                dbComm.ExecuteNonQuery()
Line 154:                '            Catch ex As Exception
Line 155:                '               Response.Write(ex.Message)

    Friday, October 22, 2010 3:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-1199946673 posted

    Hard to tell what is causing this error without knowing the datatypes of the fields in the table and the values you put in the textboxes? 

    Friday, October 22, 2010 4:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User1883103525 posted

    Text i'm putting in is date in the format dd mm yyyy. 


    Any null value gives me this error, is there a way to insert a typical null value in the database? 

    Monday, October 25, 2010 2:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User1883103525 posted

    Got it. 

    I just needed to understand the correct syntax.

    (Chrome does not support clicking 'insert code' for some reason, here is copy paste) 

                If Field8.Text = "" Then

                    dbComm.Parameters.AddWithValue("WorkDetails", DBNull.Value)

                Else : dbComm.Parameters.AddWithValue("WorkDetails", Field6.Text)

                End If

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 5, 2010 12:19 PM