inserting into MS access using ASP.NET/VB

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hello experts,

I'm a newbee to ASP.NET. And I have been struggling solving
this problem. But I can't do it. Can you please help me get
over this wall? Below is the code and error message:

<%@ Page Language="vb" AutoEventWireup="true"
Codebehind="RegisterForm.aspx.vb"
Inherits="WebApplication1.RegisterForm"%>
<%@ import Namespace="System.data.oledb" %>
<%@ import Namespace="System.data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>RegisterForm</title>
<script language="vb" runat="server">
Sub SubmitBtn_Click (Source As Object, ByVal E as EventArgs)

dim listError as string
dim connectionString as string
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\myProject\WebApplication1\UserDB1.mdb;"
dim objConn = New OleDbConnection( connectionString )
dim objCommand
try
objConn.Open()
dim InsertSQL as string
Response.Write("Connection established! <br />")

dim fname as string
dim lname as string
dim sname as string
dim eml as string
dim pwd as string
dim sex as string
fname=FN.text
lname=LN.text
sname=ScreenName.text
eml=Email.text
sex=Request.Form("Sex")
pwd=Password.text

'Create Insert statement
InsertSQL ="insert into Users (Email, FirstName, LastName,
ScreenName, Sex, Password) "
InsertSQL = InsertSQL & "values('" & eml &"', '" & fname
&"', '" & lname &"', '" & sname &"','" & sex & "', '" & pwd
&"');"
' Create OleDbCommand object
dim cmdInsert as New OleDbCommand(InsertSQL, objConn)
cmdInsert.ExecuteNonQuery()

Response.Write("Data Recorded")

catch Err as Exception
listError = Err.ToString()
Response.Write(listError)
end try
End Sub
</script>

ERROR:

System.Data.OleDb.OleDbException: Syntax error in INSERT
INTO statement. at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at
ASP.RegisterForm_aspx.SubmitBtn_Click(Object Source,
EventArgs E) in
C:\myProject\WebApplication1\RegisterForm.aspx:line 39

Line 30 contains: cmdInsert.ExecuteNonQuery()

Thank you for your time,
 
hi,

the easiest way to spot your error would be to write you insert statement
out to screen
either using response.write or trace.write/warn.

then copy the statement and paste in directly into access, correct the
statement so it works and then make the
changes to you code.

probabally best to wrap the call to execute non query in a try block and
handle any error your self.

cheers

martin
Hello experts,

I'm a newbee to ASP.NET. And I have been struggling solving
this problem. But I can't do it. Can you please help me get
over this wall? Below is the code and error message:

<%@ Page Language="vb" AutoEventWireup="true"
Codebehind="RegisterForm.aspx.vb"
Inherits="WebApplication1.RegisterForm"%>
<%@ import Namespace="System.data.oledb" %>
<%@ import Namespace="System.data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>RegisterForm</title>
<script language="vb" runat="server">
Sub SubmitBtn_Click (Source As Object, ByVal E as EventArgs)

dim listError as string
dim connectionString as string
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\myProject\WebApplication1\UserDB1.mdb;"
dim objConn = New OleDbConnection( connectionString )
dim objCommand
try
objConn.Open()
dim InsertSQL as string
Response.Write("Connection established! <br />")

dim fname as string
dim lname as string
dim sname as string
dim eml as string
dim pwd as string
dim sex as string
fname=FN.text
lname=LN.text
sname=ScreenName.text
eml=Email.text
sex=Request.Form("Sex")
pwd=Password.text

'Create Insert statement
InsertSQL ="insert into Users (Email, FirstName, LastName,
ScreenName, Sex, Password) "
InsertSQL = InsertSQL & "values('" & eml &"', '" & fname
&"', '" & lname &"', '" & sname &"','" & sex & "', '" & pwd
&"');"
' Create OleDbCommand object
dim cmdInsert as New OleDbCommand(InsertSQL, objConn)
cmdInsert.ExecuteNonQuery()

Response.Write("Data Recorded")

catch Err as Exception
listError = Err.ToString()
Response.Write(listError)
end try
End Sub
</script>

ERROR:

System.Data.OleDb.OleDbException: Syntax error in INSERT
INTO statement. at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at
ASP.RegisterForm_aspx.SubmitBtn_Click(Object Source,
EventArgs E) in
C:\myProject\WebApplication1\RegisterForm.aspx:line 39

Line 30 contains: cmdInsert.ExecuteNonQuery()

Thank you for your time,
 
Hi Kevin,

You will find that you will get more replies if you some of the work for us.
Simply stating that you have a problem, posting all your code, and expecting
everyone to slog through your code and figure out what the problem is is
going to stop some people from trying to help you right off the bat. Here's
what you need to do:

1. State the nature of the problem ("When I try to insert a new record into
an Access database, such and such happens")
2. Post the error messages (if any) and line numbers referenced (if any).
3. Describe the behavior of your application as fully as possible
4. Only post the relevant code (the code at and near the error message), and
indicate which line of code was referenced in any error message you get

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Big Things are made up of
Lots of Little Things.


Hello experts,

I'm a newbee to ASP.NET. And I have been struggling solving
this problem. But I can't do it. Can you please help me get
over this wall? Below is the code and error message:

<%@ Page Language="vb" AutoEventWireup="true"
Codebehind="RegisterForm.aspx.vb"
Inherits="WebApplication1.RegisterForm"%>
<%@ import Namespace="System.data.oledb" %>
<%@ import Namespace="System.data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>RegisterForm</title>
<script language="vb" runat="server">
Sub SubmitBtn_Click (Source As Object, ByVal E as EventArgs)

dim listError as string
dim connectionString as string
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\myProject\WebApplication1\UserDB1.mdb;"
dim objConn = New OleDbConnection( connectionString )
dim objCommand
try
objConn.Open()
dim InsertSQL as string
Response.Write("Connection established! <br />")

dim fname as string
dim lname as string
dim sname as string
dim eml as string
dim pwd as string
dim sex as string
fname=FN.text
lname=LN.text
sname=ScreenName.text
eml=Email.text
sex=Request.Form("Sex")
pwd=Password.text

'Create Insert statement
InsertSQL ="insert into Users (Email, FirstName, LastName,
ScreenName, Sex, Password) "
InsertSQL = InsertSQL & "values('" & eml &"', '" & fname
&"', '" & lname &"', '" & sname &"','" & sex & "', '" & pwd
&"');"
' Create OleDbCommand object
dim cmdInsert as New OleDbCommand(InsertSQL, objConn)
cmdInsert.ExecuteNonQuery()

Response.Write("Data Recorded")

catch Err as Exception
listError = Err.ToString()
Response.Write(listError)
end try
End Sub
</script>

ERROR:

System.Data.OleDb.OleDbException: Syntax error in INSERT
INTO statement. at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at
ASP.RegisterForm_aspx.SubmitBtn_Click(Object Source,
EventArgs E) in
C:\myProject\WebApplication1\RegisterForm.aspx:line 39

Line 30 contains: cmdInsert.ExecuteNonQuery()

Thank you for your time,
 
Back
Top