.NET insert record form, not working, no errors

Code Cleric

Code Cleric

Perch
Hi. First off I am new to .NET... I have a MS Access database with one table, called "client_profile" that table has the following fields... "profile_ID" = autonumber the rest are text... "Company_Name", "Contact_Name", "Address", "City", "State", "Zip", "Phone", "Phone_2", "Fax", "Email"....I am using a simple form to accept user input for all fields except the autonumber ID. The page is producing no errors, however is not inserting the new record... If anyone can help to explain to me what I have done wrong, I would be eternally grateful. The following is the source...

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="vb" runat="server">

Dim myConnection as OleDbConnection
Dim strConnectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mwt.mdb")

Sub Page_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
GetConnection()
End If
End Sub

Sub AddNewClient(ByVal sender As System.Object, ByVal e As System.EventArgs)
GetConnection()

Dim strSQL as String = "INSERT INTO client_profile(Company_Name,Contact_Name,Address,City,State,Zip,Phone,Phone_2,Fax,Email) values('" & _
clientname.Text & "','" & _
clientcontact.Text & "','" & _
clientaddress.Text & "','" & _
clientcity.Text & "','" & _
clientstate.Text & "','" & _
clientzip.Text & "','" & _
clientphone.Text & "','"& _
clientphone2.Text & "','"& _
clientfax.Text & "','"& _
clientemail.Text & "')'"

Dim myCommand as New OleDbCommand(strSQL, myConnection)

ClearForm()
End Sub

Sub GetConnection()
myConnection = New OleDbConnection(strConnectionString)
myConnection.Open
End Sub

Sub ClearForm()
clientname.Text = ""
clientcontact.Text = ""
clientaddress.Text = ""
clientcity.Text = ""
clientstate.Text = ""
clientzip.Text = ""
clientphone.Text = ""
clientphone2.Text = ""
clientfax.Text = ""
clientemail.Text = ""
End Sub
</script>
<head>
<title>New Client Entry Sheet</title>
<link href="clientinfo.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#DBDBDB">
<form method="post" runat="server">
<table align="center" width="575" border="0">
<tr>
<td align="center" colspan="3">
<h1>New Client Entry Form</h1><br><br><br>
</td>
</tr>
<tr>
<td class="input_caption">Company Name: </td>
<td>
<asp:TextBox ID="clientname" CssClass="textbox" ToolTip="Enter Company Name for this Client" Columns="50" MaxLength="50" runat="server" />
</td>
</tr>
<tr>
<td class="input_caption">Contact Name: </td>
<td>
<asp:TextBox ID="clientcontact" CssClass="textbox" ToolTip="Enter Contact Name for this Client" Columns="50" MaxLength="25" runat="server" />
</td>
</tr>
<tr>
<td class="input_caption">Address: </td>
<td>
<asp:TextBox ID="clientaddress" CssClass="textbox" ToolTip="Enter the Address for this Client" Columns="50" MaxLength="25" runat="server" />
</td>
</tr>
<tr>
<td class="input_caption">City: </td>
<td>
<asp:TextBox ID="clientcity" CssClass="textbox" ToolTip="Enter the City for this Client" Columns="50" MaxLength="25" runat="server" />
</td>
</tr>
<tr>
<td class="input_caption">State: </td>
<td class="input_zip">
<nobr><asp:TextBox ID="clientstate" CssClass="textbox" ToolTip="Enter the State for this Client" Columns="20" MaxLength="15" runat="server" />
&nbsp;&nbsp;Zip: <asp:TextBox ID="clientzip" CssClass="textbox" ToolTip="Enter the Zip for this Client" Columns="18" MaxLength="5" runat="server" /></nobr>
</td>
</tr>
<tr>
<td class="input_caption">Phone: </td>
<td>
<asp:TextBox ID="clientphone" CssClass="textbox" ToolTip="Enter the Phone for this Client" Columns="50" MaxLength="12" runat="server" />
</td>
</tr>
<tr>
<td class="input_caption">Phone 2: </td>
<td>
<asp:TextBox ID="clientphone2" CssClass="textbox" ToolTip="Enter the Secondary Phone for this Client" Columns="50" MaxLength="12" runat="server" />
</td>
</tr>
<tr>
<td class="input_caption">Fax: </td>
<td>
<asp:TextBox ID="clientfax" CssClass="textbox" ToolTip="Enter the Fax for this Client" Columns="50" MaxLength="12" runat="server" />
</td>
</tr>
<tr>
<td class="input_caption">Email: </td>
<td>
<asp:TextBox ID="clientemail" CssClass="textbox" ToolTip="Enter the Email for this Client" Columns="50" MaxLength="12" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="addclient" OnClick="AddNewClient" runat="server" Text="Add Client" />
<input align="right" type="reset" value="Clear Form">
</td>
</tr>
</table>
</form>
</body>
</html>
 
Code Cleric said:
If anyone can help to explain to me what I have done wrong, I would be eternally grateful. The following is the source...
Click to expand...

You need to use a DataAdapter.

In the AddNewClient sub, after this:

Dim myCommand as New OleDbCommand(strSQL, myConnection)

Add this:

Dim da As New OleDb.OleDbDataAdapter
da.InsertCommand = myCommand
da.InsertCommand.ExecuteNonQuery()

riley
 
Thank-you for replying... I inserted the code and received an error saying, "Missing semicolon (;) at end of SQL statement. I added the semicolon and received the following message..
 

Attachments

  • debug1.gif
    debug1.gif
    2.4 KB · Views: 734
Code Cleric said:
Thank-you for replying... I inserted the code and received an error saying, "Missing semicolon (;) at end of SQL statement. I added the semicolon and received the following message..
Click to expand...

The SQL statement needs to be terminated with a semicolon (not the vb code).

Change
clientemail.Text & "')'"
to
clientemail.Text & "'');"

Also note that the single qoute after the right parenthesis would cause an error too.

riley
 
Riley thankyou very much... Your advice worked. The entries were inserted properly. I can't thankyou enough. I am new to SQL commands and .NET... Again thank-you for helping me.
 
Code Cleric said:
Riley thankyou very much... Your advice worked. The entries were inserted properly. I can't thankyou enough. I am new to SQL commands and .NET... Again thank-you for helping me.
Click to expand...

You're welcome. It seems you're doing pretty good; your code was very close to working.

As for SQL, I have found it very convenient to use the MS Access query builder tools. Once the query looks good, go to the SQL view and copy the code. You can then work that text into you vb code. I've been writing SQL for MS SQL databases for some time, but I don't consider myself an expert. I have found that sometimes SQL for MS Access is a bit touchy by comparison. Using the query builder helps eliminate that problem.

I would also suggest that you incorporate some error handling in your code. In .Net, you would use Try Catch blocks. Here is an example where an error would be trapped and an error message would be displayed in a .Net Label control.

Put this at the bottom of your form:

<asp:label id="myMessage" runat="server"></asp:label>

and put this in your code:

Try
da.InsertCommand.ExecuteNonQuery()
ClearForm()
myMessage.Visible = False

Catch ex As Exception
myMessage.Text = "Error inserting rows: " & ex.Message
myMessage.Visible = True

End Try

If your insert command works, the ClearForm() code will be called and the myMessage label will not be rendered. If there is an error inserting, the ClearForm() code will not be called and the myMessage lable will be rendered at the bottom of your form, containing a message describing the error.

riley
 
You must log in or register to reply here.
Back
Top