.NET insert record form, not working, no errors

General Web Coding Microsoft Development
1

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” %>

New Client Entry Sheet

New Client Entry Form
Company Name:
Contact Name:
Address:
City:
State:   Zip:
Phone:
Phone 2:
Fax:
Email:
2

[QUOTE=Code Cleric]
If anyone can help to explain to me what I have done wrong, I would be eternally grateful. The following is the source…
[/QUOTE]

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

3

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

debug1.gif
debug1.gif450×135 2.42 KB

4

[QUOTE=Code Cleric]
Thank-you for replying… I inserted the code and received an error saying, "Missing semicolon (:wink: at end of SQL statement. I added the semicolon and received the following message..
[/QUOTE]

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

5

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.

6

[QUOTE=Code Cleric]
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.
[/QUOTE]

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