locked
inserting record not working RRS feed

  • Question

  • User-2137050114 posted

    Hi Guys, I have been beating my head over the last two days as a newbie.  My question or learning is simple, i am simply trying to use 2 text box and insert it into a table in Access2003

    I found a code on the web and tweeked it to work. But somehow this can not be inserted into my site master template.  So I try to recreate it using just textbox, connection to access and submit.  But it is showing not able to write null to a required field. Why can I not my scratch write which looks similar to the example from the web but it does not work.

    UPDATE:  I turned of the required field and verified it is going into it but adding empty rows.  So somehow, the values are not being passed

    Here is the one I created and it does not work

    <%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/Site.Master" CodeBehind="WebForm6.aspx.vb" Inherits="WebApplication4.WebForm6" %>
    <asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
        <style type="text/css">
            .style2
            {
                width: 500px;
            }
            .style3
            {
                height: 20px;
            }
        </style>
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
        <p>
            <br />
        </p>
        <table class="style2">
            <tr>
                <td>
                    Name</td>
                <td>
                    <asp:TextBox ID="Name" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:AccessDataSource ID="AccessDataSource33" runat="server" DataFile="~/App_Data/RedCrossTest.mdb" 
                        SelectCommand="SELECT [Name][Hours] FROM  [tblVolunteer Hours]"
                        InsertCommand="INSERT INTO [tblVolunteer Hours] ([Name],[Hours]) VALUES (?,?)">
                            <insertparameters>
                            <asp:formparameter name="Name" formfield="Name" />
                            <asp:formparameter name="Hours"  formfield="Hours" />
                            </insertparameters>
                    </asp:AccessDataSource>
                </td>
            </tr>
            <tr>
                <td>
                    Hours</td>
                <td>
                    <asp:TextBox ID="Hours" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style3">
                    </td>
                <td class="style3">
                    </td>
                <td class="style3">
                    </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="SubmitHours" runat="server" Text="Submit Hours" />
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
        </table>
    </asp:Content>
    

    AND THIS ONE WORKS
    <%@Page  Language="VB" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <script runat="server">
        Private Sub SubmitHours(ByVal Source As Object, ByVal e As EventArgs)
            AccessDataSource55.Insert()
        End Sub ' Submithours
    
        Protected Sub Page_Load(sender As Object, e As System.EventArgs)
    
        End Sub
    </script>
    <html xmlns="http://www.w3.org/1999/xhtml" >
      <head id="Head1" runat="server">
        <title>ASP.NET Example</title>
    </head>
    <body>
        
    <!-- Security Note: The SqlDataSource uses a FormParameter,
         Security Note: which does not perform validation of input from the client.
         Security Note: To validate the value of the FormParameter, handle the Inserting event. -->
         <form id="form2" runat="server">
    
          <asp:AccessDataSource 
             ID="AccessDataSource55" 
             runat="server" 
             DataFile="~/App_Data/RedCrossTest.mdb" 
             SelectCommand="SELECT [Name][Hours] FROM  [tblVolunteer Hours]"
             InsertCommand= "INSERT INTO [tblVolunteer Hours] ([Name],[Hours]) VALUES (?,?)">
             <insertparameters>
                <asp:formparameter name="Name" formfield="Name" />
                <asp:formparameter name="Hours"  formfield="Hours" />
              </insertparameters>
           </asp:AccessDataSource>
    
          <br /><asp:textbox
               id="Name"
               runat="server" />
    
    
          <br /><asp:textbox
               id="Hours"
               runat="server" />
    
    
    
          <br /><asp:button
               id="Button1"
               runat="server"
               text="Add Hours"
               onclick="SubmitHours" />
    
        </form>
      </body>
    </html>
    
    
    Can someone tell me what I am doing wrong??!!


    Friday, July 29, 2011 8:50 PM

Answers

  • User-2137050114 posted

    I figured it out. 

    <asp:AccessDataSource ID="AccessDataSource33" runat="server" DataFile="~/App_Data/RedCrossTest.mdb" 
                        SelectCommand="SELECT [Name][Hours] FROM  [tblVolunteer Hours]"
                        InsertCommand="INSERT INTO [tblVolunteer Hours] ([Name],[Hours]) VALUES (?,?)">
                            <insertparameters>
                            <asp:ControlParameter ControlID="Name1" />
                            <asp:ControlParameter ControlID="Hours1" />
                            </insertparameters>
                    </asp:AccessDataSource>
    
    
    Apparently, you have to use VALUES(?) and ControlParameters!!
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 30, 2011 2:15 AM

All replies

  • User1845063199 posted

    Could you please provide the exact error message...

    Saturday, July 30, 2011 12:45 AM
  • User-2137050114 posted

    Ok, I have now confirmed no error, it simply is adding a null record, or if I subsitute a value, it is getting into the table, but the way I have it now, it is passing null.  but adding the row.  Apparently somehow, i am not setting up or calling the textbox values Name1 and Hours1. 

    <td>
                    <asp:TextBox ID="Name1" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:AccessDataSource ID="AccessDataSource33" runat="server" DataFile="~/App_Data/RedCrossTest.mdb" 
                        SelectCommand="SELECT [Name][Hours] FROM  [tblVolunteer Hours]"
                        InsertCommand="INSERT INTO [tblVolunteer Hours] ([Name],[Hours]) VALUES (@Name1, @Hours1)">
                            <insertparameters>
                            <asp:parameter name="Name" Type="String"/>
                            <asp:parameter name="Hours"  Type="Int32" />
                            </insertparameters>
                    </asp:AccessDataSource>
                </td>
            </tr>
            <tr>
                <td>
                    Hours</td>
                <td>
                    <asp:TextBox ID="Hours1" runat="server"></asp:TextBox>
                </td>
    
    
    AND BELOW is the VB
    
    
        Protected Sub SubmitHours_Click(ByVal Source As Object, ByVal e As EventArgs) Handles SubmitHours.Click
            AccessDataSource33.Insert()
    
        End Sub
    Saturday, July 30, 2011 1:51 AM
  • User-2137050114 posted

    I figured it out. 

    <asp:AccessDataSource ID="AccessDataSource33" runat="server" DataFile="~/App_Data/RedCrossTest.mdb" 
                        SelectCommand="SELECT [Name][Hours] FROM  [tblVolunteer Hours]"
                        InsertCommand="INSERT INTO [tblVolunteer Hours] ([Name],[Hours]) VALUES (?,?)">
                            <insertparameters>
                            <asp:ControlParameter ControlID="Name1" />
                            <asp:ControlParameter ControlID="Hours1" />
                            </insertparameters>
                    </asp:AccessDataSource>
    
    
    Apparently, you have to use VALUES(?) and ControlParameters!!
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 30, 2011 2:15 AM