Solved

problem getting data to insert.

Posted on 2011-09-07
6
381 Views
Last Modified: 2013-11-28
I have the two following files: The purpose is a small database for a volunteer organization to allow teen age girls to register with them online. The database is an access database - connection is already established.
Any ideas on what I am doing wrong in the code?

one is the .aspx that is supposed to insert data into a table
The other is the html form
The column types are: Text, Text, Yes/No, Date/Time
Data provided was susyQ, myDreamDr, Yes, 01/11/2011

I would like to be able to use a dataTime picker but have not got that far yet.

When I test them I get the following error
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 30:    
Line 31:     ' execute the command
Line 32:     objCmd.ExecuteNonQuery()
Line 33:
Line 34:     lblStatus.Text = "Command run"

 
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="VB" runat="server">

  Sub Page_Load(Sender As Object, E As EventArgs)

    Dim objConnection As OleDbConnection
    Dim objCmd        As OleDbCommand
    Dim strConnection As String
    Dim strSQL        As String
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source="
  
    ' Create and open the connection object
    objConnection = New OleDbConnection(strConnection)
    objConnection.Open()
    
    
	strSQL = "INSERT INTO teens(Pname, Paddress, delivered, Ddate)" & _
                    " VALUES ( '" & request.form("text1") & "' , '" & request.form("text2") & "' ,'" & request.form("text3") & "','" & request.form("text4") & "')"
	
	
	'strSQL = "INSERT INTO Employees (LastName)" & _
                   '   "VALUES ('" & request.form("text1") & "')" 

    ' Create the Command and set its properties
    objCmd = New OleDbCommand(strSQL, objConnection)
    
    ' execute the command
    objCmd.ExecuteNonQuery()

    lblStatus.Text = "Command run"

  End Sub

</script>

<html>
  <body>
    <h2>Insert Data into Table</h2>
    <asp:Label id="lblStatus" runat="server"/>
    <p>
       
  </body>
 
</html>

Open in new window

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>
<form runat="server" action = "InsertNames.aspx" method = "post">
      
      Please enter  Patients Name:
      <input type = "Text" name = "text1"   style="color: red" size="20" />
      <br /><br />
      
      Please enter Patients Address:
      <input type = "Text" name = "text2"   style="color: red" size="20" />
      <br /><br />
      
      Has Patient Delivered:
      <input type = "Yes/No" name = "text2"   style="color: red" size="20" />
      <br /><br />
      
      Due Date:
      <input type = "Date/Time" name = "text2"   style="color: red" size="20" />
      <br /><br />
      

      <input type="Submit">
  </form>

</body>

</html>

Open in new window

0
Comment
Question by:Tagom
  • 5
6 Comments
 

Author Comment

by:Tagom
ID: 36500630
I already caught the duplicate names for the labels in the htm page.
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 36500752
OH the date for Access must be eclosed in # marks. not quotes, The yes/no is probaly going to a boolean field - no quotes at all pass eith 0 (false or No) or -1 (True/Yes)

Kelvin
0
 

Author Comment

by:Tagom
ID: 36502050
I am not sure where to change it in the code - in the form part (htm) or the aspx part...
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:Tagom
ID: 36502160
I changed the code to below

I get the following error
[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1003520
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +255
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +188
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +161
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
   ASP.hgdeposition_teens_insertnames_aspx.Page_Load(Object Sender, EventArgs E) in \.....\InsertNames.aspx:32
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="VB" runat="server">

  Sub Page_Load(Sender As Object, E As EventArgs)

    Dim objConnection As OleDbConnection
    Dim objCmd        As OleDbCommand
    Dim strConnection As String
    Dim strSQL        As String
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=....\Teens.mdb"
  
    ' Create and open the connection object
    objConnection = New OleDbConnection(strConnection)
    objConnection.Open()
    
    
	strSQL = "INSERT INTO teens(Pname, Paddress, delivered, Ddate)" & _
                    " VALUES ( '" & request.form("text1") & "' , '" & request.form("text2") & "' ,'" & request.form("text3") & "','" & request.form("#text4#") & "')"
	
	
	'strSQL = "INSERT INTO Employees (LastName)" & _
                   '   "VALUES ('" & request.form("text1") & "')" 

    ' Create the Command and set its properties
    objCmd = New OleDbCommand(strSQL, objConnection)
    
    ' execute the command
    objCmd.ExecuteNonQuery()

    lblStatus.Text = "Command run"

  End Sub

</script>

<html>
  <body>
    <h2>Insert Data into Table</h2>
    <asp:Label id="lblStatus" runat="server"/>
    <p>
       
  </body>
 
</html>

Open in new window

0
 

Author Comment

by:Tagom
ID: 36502264
Starting from scratch -
I have both text fields inserting data correctly.
I then added a boolean to the table - I get the following error
So I am unsure how to code or pas the boolean - can someone please help me.
code is below:
 
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>
<form runat="server" action = "InsertNames.aspx" method = "post">
      
      Please enter  Patients Name:
      <input type = "Text" name = "text1"   style="color: red" size="20" />
      <br /><br />
      
      Please enter Patients Address:
      <input type = "Text" name = "text2"   style="color: red" size="20" />
      <br /><br />
      
      Has Patient Delivered:
      <p>
        <label>
          Yes <input type="radio" name="test3" value="Yes" id="RadioGroup1_0">
          </label>
        <br>
        <label>
          No <input type="radio" name="test3" value="No" id="RadioGroup1_1">
         </label>
        <br>
      </p>
  

      <input type="Submit">
  </form>

</body>

</html>

Open in new window

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="VB" runat="server">

  Sub Page_Load(Sender As Object, E As EventArgs)

    Dim objConnection As OleDbConnection
    Dim objCmd        As OleDbCommand
    Dim strConnection As String
    Dim strSQL        As String
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=......Teens.mdb"
  
    ' Create and open the connection object
    objConnection = New OleDbConnection(strConnection)
    objConnection.Open()
    
    
	strSQL = "INSERT INTO teens(Pname, Paddress, delivered)" & _
                    " VALUES ( '" & request.form("text1") & "' , '" & request.form("text2") & "' ,'" & request.form("text3") & "')"
	
	
	'strSQL = "INSERT INTO Employees (LastName)" & _
                   '   "VALUES ('" & request.form("text1") & "')" 

    ' Create the Command and set its properties
    objCmd = New OleDbCommand(strSQL, objConnection)
    
    ' execute the command
    objCmd.ExecuteNonQuery()

    lblStatus.Text = "Command run"

  End Sub

</script>

<html>
  <body>
    <h2>Insert Data into Table</h2>
    <asp:Label id="lblStatus" runat="server"/>
    <p>
       
  </body>
 
</html>

Open in new window


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 30:    
Line 31:     ' execute the command
Line 32:     objCmd.ExecuteNonQuery()
Line 33:
Line 34:     lblStatus.Text = "Command run"


Source File: \....\InsertNames.aspx    Line: 32

Stack Trace:

[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1003520
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +255
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +188
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +161
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
   ASP.hgdeposition_teens_insertnames_aspx.Page_Load(Object Sender, EventArgs E) in \\.....\InsertNames.aspx:32
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627
0
 

Author Closing Comment

by:Tagom
ID: 36507141
Could not figure out how to pass data as the answer was vague
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question