INSERT Query Works in ACCESS but Not from Web Form

INSERT query works in ACCESS but not from Web Form??

Dev Shed Forums > Programming Languages - More > .Net Development > INSERT query works in ACCESS but not from Web Form??

表單的頂端

Search Dev Shed Forums:

表單的底部

Read With Formatting | Join The Dev Shed Community, Free! | Read Related Topics

.Net Development
Thread: INSERT query works in ACCESS but not from Web Form??

aircomba

I have been having this problem for a couple days now with no
resolution. Even a couple other posts of mine have had no results
in getting this problem fixed. I thought I would try one more time.
Private Sub bntSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntSubmit.Click
Dim conn As OleDbConnection
conn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" & Server.MapPath("database\dahliaSpa.mdb"))
conn.Open()
If Page.IsValid Then
Dim sql As String
Dim cmd As OleDbCommand
sql = "INSERT INTO dahliaCustomers (custFirstName,custLastName,"
sql = sql & "address,city,state,zipCode,phone,email,birthday,loginID,password)"
sql = sql & " VALUES "
sql = sql & "('" & txtFName.Text & "','" & txtLName.Text & "','" & txtAddress.Text & "','"
sql = sql & txtCity.Text & "','" & txtState.Text & "','" & txtZip.Text & "','" & txtPhone.Text
sql = sql & "','" & txtEmail.Text & "',#" & txtBirthday.Text
sql = sql & "#,'" & txtUserName.Text & "','" & txtPassword.Text & "')"
Response.Write(sql)
cmd = New OleDbCommand(sql, conn)
cmd.ExecuteNonQuery()
conn.Close()
End If
End Sub
The sql Line that it produces is: INSERT INTO dahliaCustomers (custFirstName,custLastName,address,city,state,zipCode,phone,email,birthday,loginID,password) VALUES ('Aaron','Schmidt','405 21st Ave N','Seattle','WA','98107','206-999-0234','',#04/27/2004#,'aircomba','1234asdf')
This line works correctly from within ACCESS but it keeps giving this error when trying to insert from the webform (aspx file):
Server Error in '/login' Application.
Syntax error in INSERT INTO statement.
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: Syntax error in INSERT INTO statement.
Source Error:
Line 57: Line 58: cmd = New OleDbCommand(sql, conn)Line 59: cmd.ExecuteNonQuery()Line 60: conn.Close()Line 61: End If
Source File: c:\inetpub\wwwroot\login\register.aspx.vb Line: 59
Stack Trace:
[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +67 login.register.bntSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\login\register.aspx.vb:59 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

Become A Member, Free!

INSERT query works in ACCESS but not from Web Form??

CSharp

To debug your code I used the StringBuilder class which far quicker than appending strings the way you did, also much easier to look at. So here is the code since I've done it already
Dim MyStringBuilder As New StringBuilder
MyStringBuilder.Append("INSERT INTO dahliaCustomers (custFirstName,custLastName,address,city,state,zipCode,phone,email,birthday,loginID,password)")
MyStringBuilder.Append("VALUES ")
MyStringBuilder.Append("('" & txtFName.Text & "','" & txtLName.Text & "','" & txtAddress.Text & "','")
MyStringBuilder.Append(txtCity.Text & "','" & txtState.Text & "','" & txtZip.Text & "','" & txtPhone.Text)
MyStringBuilder.Append("','" & txtEmail.Text & "','" & txtBirthday.Text)
MyStringBuilder.Append("','" & txtUserName.Text & "','" & txtPassword.Text & "')")
Response.Write(MyStringBuilder.ToString())
Make sure you import the following namespace in your page
Imports System.Text
The only thing I could think of that is going wrong with you code was using the # sign for date values. oleDB provider doesn't seem to like that. Of course within access that would work like a charm. I have already replaced the # sign to '. If still no luck then please answer the following questions
1. Is dahliaCustomers a valid Table name on the database?
2. The same date you have shown has the format of m/d/yyyy. Does your database accept such date format? if the database date format was dd/mm/yyyy then your SQL Statement will cause an error
3. Have you setup your permission to be bale to access the *.db file for read and write from IIS?
Good Luck

Read Formatted Version

INSERT query works in ACCESS but not from Web Form??

tsmetz

similar to #, which work in access and not via oledb, i had a syntax error while using the column name 'action'.
works in access but not in a oledb command.
to prevent that, use [columnname].
so if removing # is not enough, try
INSERT INTO [dahliaCustomers] ([custFirstName], ...

Read Related Topics

INSERT query works in ACCESS but not from Web Form??

aircomba

Thank you all for you input! Still I get the same error using all of your guys ideas. I checked permissions and made sure the database had read/write permissions, also available to anon access. I tried using [ ] with same results.
dahliaCustomers is a valid table name as are all the fields that I have in the SQL line.
The date field is of short date type (ie in access it shows dates in the form xx/xx/xxxx, but you can enter x/x/xxxx as valid also.
Any other help would be awesome

Become A Member, Free!

INSERT query works in ACCESS but not from Web Form??

aircomba

ps....
I noticed that when I browse to the database on my localhost
(ie C:\Inetpub\wwwroot\loginNew\database\dahliaSpa.mdb)
and try and open it, it says the database is read only etc etc. I check all permissions on the database file itself and the folder it is in through IIS and they all show it as READ/WRITE persmissions.
Anyone have any new ideas what setting I have wrong or what is going on?

Read Formatted Version

INSERT query works in ACCESS but not from Web Form??

CSharp

Set the "Everyone" NT account to read/write to the folder and it will work then.
Good Luck

Dev Shed Forums > Programming Languages - More > .Net Development > INSERT query works in ACCESS but not from Web Form??

SPONSORED LINK / what's this?
Get 128-bit SSL Web Site Encryption
Looking for SSL for your IIS server? Turn to the SSL experts. Protect your servers with 128-bit SSL encryption from VeriSign. Register for a free Internet security guide today. Click here.
www.verisign.com