• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Submit Button that Inserts to Access Db, validates and displays a confirmation

Hi Experts,

I'm a newbie using ASP.NET. I'm using VB and web matrix (for what it's worth).

I've got a page that has a "Submit" button which runs an INSERT query into an ACCESS db.  Clicking submit inserts a new record into a dB and that seems to be working.  

My problem:  Clicking submit inserts the record into the db, but it doesn't clear the fields, validate, or give any indication on the page that the record was updated.

I'm looking for straight-forward code that will validate the info, insert the record, and show a confirmation message to the user.

Here's my OnClick event.


 Sub SubmitSolution(sender As Object, e As EventArgs)
     

        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
           "Data Source=C:\matrix\db1.mdb"

        Dim dbConnection As New OleDbConnection(connectionString)
        dbConnection.Open()

        Dim commandString As String = "INSERT INTO tblSolutions(CategoryID, SolutionTitle, Solution) " & _
           "Values(@CategoryID, @SolutionTitle, @Solution)"

        Dim dbCommand As New OleDbCommand(commandString, dbConnection)

        Dim CategoryIDParam As New OleDbParameter("@CategoryID", OleDbType.VarChar, 10)
        CategoryIDParam.Value = DropDownList1.SelectedItem.Value
        dbCommand.Parameters.Add(CategoryIDParam)

        Dim SolutionTitleParam As New OleDbParameter("@SolutionTitle", OleDbType.VarChar, 100)
        SolutionTitleParam.Value = TextBox4.Text
        dbCommand.Parameters.Add(SolutionTitleParam)

        Dim SolutionParam As New OleDbParameter("@Solution", OleDbType.VarChar, 100)
        SolutionParam.Value = TextBox1.Text
        dbCommand.Parameters.Add(SolutionParam)


        dbCommand.ExecuteNonQuery()
        dbConnection.Close()

    End Sub


Thanks for your help.

I'm looking for specific code examples, tx.
0
TCantrell
Asked:
TCantrell
1 Solution
 
stengeljCommented:
Try this...

........................................
<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.OleDb" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script runat="server">
    Sub SubmitSolution(sender As Object, e As EventArgs)    
        Try
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
               "Data Source=C:\matrix\db1.mdb"
   
            Dim dbConnection As New OleDbConnection(connectionString)
            dbConnection.Open()
   
            Dim commandString As String = "INSERT INTO tblSolutions(CategoryID, SolutionTitle, Solution) " & _
               "Values(@CategoryID, @SolutionTitle, @Solution)"
   
            Dim dbCommand As New OleDbCommand(commandString, dbConnection)
   
            Dim CategoryIDParam As New OleDbParameter("@CategoryID", OleDbType.VarChar, 10)
            CategoryIDParam.Value = DropDownList1.SelectedItem.Value
            dbCommand.Parameters.Add(CategoryIDParam)
   
            Dim SolutionTitleParam As New OleDbParameter("@SolutionTitle", OleDbType.VarChar, 100)
            SolutionTitleParam.Value = TextBox4.Text
            dbCommand.Parameters.Add(SolutionTitleParam)
   
            Dim SolutionParam As New OleDbParameter("@Solution", OleDbType.VarChar, 100)
            SolutionParam.Value = TextBox1.Text
            dbCommand.Parameters.Add(SolutionParam)
   
   
            dbCommand.ExecuteNonQuery()
            dbConnection.Close()
            myForm.Visible = False
            lbl_Confirm.Text = "Form successfully submitted"
       
        Catch ex As Exception
            lbl_Confirm.Text = "There was an error adding your information to the database"
       
        End Try

    End Sub

</script>
</head>
<body>
<form runat="server" id="myForm">
    <asp:TextBox ID="TextBox1" runat="server" />
    <asp:RequiredFieldValidator ID="RequiredFieldValidator1"
        ControlToValidate="TextBox1"
        ErrorMessage="Required"
        Display="Dynamic" runat="server">
    </asp:RequiredFieldValidator>
    <br>
    <asp:TextBox ID="TextBox4" runat="server" />
    <asp:RequiredFieldValidator ID="RequiredFieldValidator2"
        ControlToValidate="TextBox4"
        ErrorMessage="Required"
        Display="Dynamic" runat="server">
    </asp:RequiredFieldValidator>
    <br>
    <asp:DropDownList ID="DropDownList1" runat="server">
        <asp:ListItem value="">[add item]</asp:ListItem>
        <asp:ListItem value="1">item1</asp:ListItem>
        <asp:ListItem value="2">item2</asp:ListItem>
    </asp:DropDownList>
    <asp:RequiredFieldValidator ID="RequiredFieldValidator3"
        ControlToValidate="DropDownList1"
        ErrorMessage="Required"
        Display="Dynamic" runat="server">
    </asp:RequiredFieldValidator>
    <br>
    <asp:Button ID="myButton" runat="server" Text="Submit" OnClick="SubmitSolution" />
</form>
<asp:Label ID="lbl_Confirm" runat="server"></asp:Label>
</body>
</html>
0
 
MikeMCSDCommented:
To validate data, use a RequiredFieldValidator. ex: using a textbox:

<asp:textbox runat="server" id="txtAddress1"></asp:textbox>
<asp:RequiredFieldValidator ControlToValidate="txtAddress1" ID="vAddress1"
        Runat="server"  ErrorMessage="Enter address."></asp:RequiredFieldValidator>


To check for update failure, can use:

Try
     dbCommand.ExecuteNonQuery()
     lblCat.Text = "Update Ok"
     dbConnection.Close()
Catch ex As Exception
      lblCat.Text = "Update Failed"
      dbConnection.Close()
End Try
0
 
TCantrellAuthor Commented:
Tx  stengelj

That worked like a charm.

Thanks!

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now