Irritating INSERT INTO Error

[rn5a]

During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am
using MS-Access 2000 database table for this app. Note that the
datatype of all the fields mentioned above are Text. Apart from the
above columns, there's another column in the DB table named
'RegDateTime' whose datatype is Date/Time which is populated with the
date & time at which the user registered.

When a user submits these details, a ASPX function first checks
whether the UserName entered by the user already exists or not in the
DB table by comparing the UserName supplied by the user to the
UserNames that already exist in the Access DB table. If the supplied
UserName already exists, he is shown a message saying "UserName
already exists. Please use a different UserName".

Assuming that the UserName supplied by the user does not exist in the
Access DB table, the same ASPX function then checks whether the email
supplied by the user already exists in the DB table or not. If the
supplied email already exists, then he is shown a message saying
"EMail ID already exists! Please use a different EMail ID".

If both the UserName & the EMail supplied by the user doesn't exist in
the DB table, this ASPX function invokes another ASPX sub-routine
which finally inserts this new record in the Access DB table. This is
the sub-routine that inserts the new record in the DB table (nothing
extraordinary.....it's the same usual thing that I have done a hundred
thousand times in my life):

Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader

oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))

oledbCmd = New OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, Password, Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" & LastName1
& "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1 & "', '" &
Address1 & "', '" & City1 & "', '" & State1 & "', '" & Country1 & "',
'" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now & "')", oledbConn)

'oledbCmd.CommandType = CommandType.Text

System.Web.HttpContext.Current.Response.Write("INSERT INTO Users
(FirstName, LastName, EMail, UserName, Password, Address, City, State,
Country, Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" &
LastName1 & "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1
& "', '" & Address1 & "', '" & City1 & "', '" & State1 & "', '" &
Country1 & "', '" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now &
"')" & "<br>")

oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
'iUID = oledbCmd.ExecuteScalar
oledbConn.Close()
End Sub

When I try to insert records in the DB table, the following error gets
generated:


System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement.

which points to the ExecuteReader line shown in the above sub-routine.
That's the reason why I have added the Response.Write line so as
detect where's the syntax error in the INSERT INTO statement but I
don't find any syntax error in the SQL query. I also tried replacing
the single quotes around the 'RegDateTime' record with # (since it's
datatype is Date/Time) but that doesn't make any difference. When I
comment the last 4 lines in the above sub-routine, then one such
Response.Write line generates the SQL query shown below:

INSERT INTO Users (FirstName, LastName, EMail, UserName, Password,
Address, City, State, Country, Zip, Phone, RegDateTime) VALUES
('Peter', 'North', '(e-mail address removed)', 'peter', 'peter', 'Some
Address', 'Some City', 'Some State', 'Poland', '889775', '123456789',
'12/02/2007 2:33:03 AM')

When I copy & paste the above SQL output in a Query window in MS-
Access, the above records get populated successfully but I am
continuously getting the above error message when I try to execute the
same SQL query through this ASP.NET app.

Can someone please point out where I could be going wrong?

It's driving me nuts since last 3 hours & I just can't seem to locate
what's exactly causing the error!

 

[rn5a]

During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am
using MS-Access 2000 database table for this app. Note that the
datatype of all the fields mentioned above are Text. Apart from the
above columns, there's another column in the DB table named
'RegDateTime' whose datatype is Date/Time which is populated with the
date & time at which the user registered.

When a user submits these details, a ASPX function first checks
whether the UserName entered by the user already exists or not in the
DB table by comparing the UserName supplied by the user to the
UserNames that already exist in the Access DB table. If the supplied
UserName already exists, he is shown a message saying "UserName
already exists. Please use a different UserName".

Assuming that the UserName supplied by the user does not exist in the
Access DB table, the same ASPX function then checks whether the email
supplied by the user already exists in the DB table or not. If the
supplied email already exists, then he is shown a message saying
"EMail ID already exists! Please use a different EMail ID".

If both the UserName & the EMail supplied by the user doesn't exist in
the DB table, this ASPX function invokes another ASPX sub-routine
which finally inserts this new record in the Access DB table. This is
the sub-routine that inserts the new record in the DB table (nothing
extraordinary.....it's the same usual thing that I have done a hundred
thousand times in my life):

Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader

oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))

oledbCmd = New OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, Password, Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" & LastName1
& "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1 & "', '" &
Address1 & "', '" & City1 & "', '" & State1 & "', '" & Country1 & "',
'" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now & "')", oledbConn)

'oledbCmd.CommandType = CommandType.Text

System.Web.HttpContext.Current.Response.Write("INSERT INTO Users
(FirstName, LastName, EMail, UserName, Password, Address, City, State,
Country, Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" &
LastName1 & "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1
& "', '" & Address1 & "', '" & City1 & "', '" & State1 & "', '" &
Country1 & "', '" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now &
"')" & "<br>")

oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
'iUID = oledbCmd.ExecuteScalar
oledbConn.Close()
End Sub

When I try to insert records in the DB table, the following error gets
generated:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement.

which points to the ExecuteReader line shown in the above sub-routine.
That's the reason why I have added the Response.Write line so as
detect where's the syntax error in the INSERT INTO statement but I
don't find any syntax error in the SQL query. I also tried replacing
the single quotes around the 'RegDateTime' record with # (since it's
datatype is Date/Time) but that doesn't make any difference. When I
comment the last 4 lines in the above sub-routine, then one such
Response.Write line generates the SQL query shown below:

INSERT INTO Users (FirstName, LastName, EMail, UserName, Password,
Address, City, State, Country, Zip, Phone, RegDateTime) VALUES
('Peter', 'North', '(e-mail address removed)', 'peter', 'peter', 'Some
Address', 'Some City', 'Some State', 'Poland', '889775', '123456789',
'12/02/2007 2:33:03 AM')

When I copy & paste the above SQL output in a Query window in MS-
Access, the above records get populated successfully but I am
continuously getting the above error message when I try to execute the
same SQL query through this ASP.NET app.

Can someone please point out where I could be going wrong?

It's driving me nuts since last 3 hours & I just can't seem to locate
what's exactly causing the error!

After a few experiments I found that if I remove the column 'Password'
& it's value from the INSERT statement, then the INSERT query works
fine but I don't understand how does the 'Password' column or the
'Password' value entered by the user pose a problem!

The datatype of the 'Password' column in the Access DB table is 'Text'
just like the datatype of the other columns. I am not even entering
any special characters in the password nor am I exceeding the maximum
length of the password which is set to 50 in the Access DB table.

In fact, the 'Allow Zero Length' property of the 'Password' column has
been set to No; so how come Access is allowing zero length values in
the 'Password' column?

Oh Gosh...Good Heavens...someone please come to my rescue else I might
have to get myself admitted in a mental asylum soon!

 

[Guest]

Hi there,

Password is a reserved word in Access 2000 SQL. You can bracket reserved
words in the INSERT statement:

INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country)

Please also note your code is vulnerable for SQL-injection type attacks. Use
OleDbParameter and "?" parameter placeholder in your query:

Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader

oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))


oledbCmd = new OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", oledbConn)


oledbCmd.Parameters.Add(FirstName1)
oledbCmd.Parameters.Add(LastName1)
oledbCmd.Parameters.Add(Email1)
oledbCmd.Parameters.Add(UserName1)
oledbCmd.Parameters.Add(Password1)
oledbCmd.Parameters.Add(Address1)
oledbCmd.Parameters.Add(City1)
oledbCmd.Parameters.Add(State1)
oledbCmd.Parameters.Add(Country1)
oledbCmd.Parameters.Add(Zip1)
oledbCmd.Parameters.Add(Phone1)
oledbCmd.Parameters.Add(DateTime.Now)

try
oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
while oledbReader.Read()
' do something here
loop

catch ex as Exception
throw ex
finally
oledbConn.Close()

 

[rn5a]

Hi there,

Password is a reserved word in Access 2000 SQL. You can bracket reserved
words in the INSERT statement:

INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country)

Please also note your code is vulnerable for SQL-injection type attacks. Use
OleDbParameter and "?" parameter placeholder in your query:

Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader

oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))

oledbCmd = new OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", oledbConn)

oledbCmd.Parameters.Add(FirstName1)
oledbCmd.Parameters.Add(LastName1)
oledbCmd.Parameters.Add(Email1)
oledbCmd.Parameters.Add(UserName1)
oledbCmd.Parameters.Add(Password1)
oledbCmd.Parameters.Add(Address1)
oledbCmd.Parameters.Add(City1)
oledbCmd.Parameters.Add(State1)
oledbCmd.Parameters.Add(Country1)
oledbCmd.Parameters.Add(Zip1)
oledbCmd.Parameters.Add(Phone1)
oledbCmd.Parameters.Add(DateTime.Now)

try
oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
while oledbReader.Read()
' do something here
loop

catch ex as Exception
throw ex
finally
oledbConn.Close()

--
Milosz

After a few experiments I found that if I remove the column 'Password'
& it's value from the INSERT statement, then the INSERT query works
fine but I don't understand how does the 'Password' column or the
'Password' value entered by the user pose a problem!

The datatype of the 'Password' column in the Access DB table is 'Text'
just like the datatype of the other columns. I am not even entering
any special characters in the password nor am I exceeding the maximum
length of the password which is set to 50 in the Access DB table.

In fact, the 'Allow Zero Length' property of the 'Password' column has
been set to No; so how come Access is allowing zero length values in
the 'Password' column?

Oh Gosh...Good Heavens...someone please come to my rescue else I might
have to get myself admitted in a mental asylum soon!- Hide quoted text -

- Show quoted text -

Thanks a lot Milosz especially for the additional suggestion but sorry
to say that when I tried your code, it generated the following error:

--------------------------------
The OleDbParameterCollection only accepts non-null OleDbParameter type
objects, not String objects.
--------------------------------

pointing to the following line:

--------------------------------
oledbCmd.Parameters.Add(FirstName)
--------------------------------

How do I overcome this error?

To be honest I wasn't aware that one can use "?" parameter placeholder
with OledbParameters to prevent SQL Injection though I have got some
idea on SQL Injection.

Thanks once again,

Regards.

 

[Guest]

I'm sorry i cannot test my code at the moment, try:

oledbCmd.Parameters.Add("?", Data.OleDb.OleDbType.VarChar).Value = FirstName1
oledbCmd.Parameters.Add("?", Data.OleDb.OleDbType.VarChar).Value = LastName1
etc.

Remember to set the second parameter (Data.OleDb.OleDbType enumeration)
properly (based on database table column types).

--
Milosz

Hi there,

Password is a reserved word in Access 2000 SQL. You can bracket reserved
words in the INSERT statement:

INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country)

Please also note your code is vulnerable for SQL-injection type attacks. Use
OleDbParameter and "?" parameter placeholder in your query:

Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader

oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))

oledbCmd = new OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", oledbConn)

oledbCmd.Parameters.Add(FirstName1)
oledbCmd.Parameters.Add(LastName1)
oledbCmd.Parameters.Add(Email1)
oledbCmd.Parameters.Add(UserName1)
oledbCmd.Parameters.Add(Password1)
oledbCmd.Parameters.Add(Address1)
oledbCmd.Parameters.Add(City1)
oledbCmd.Parameters.Add(State1)
oledbCmd.Parameters.Add(Country1)
oledbCmd.Parameters.Add(Zip1)
oledbCmd.Parameters.Add(Phone1)
oledbCmd.Parameters.Add(DateTime.Now)

try
oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
while oledbReader.Read()
' do something here
loop

catch ex as Exception
throw ex
finally
oledbConn.Close()

--
Milosz

On Feb 12, 2:24 am, (e-mail address removed) wrote:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am
using MS-Access 2000 database table for this app. Note that the
datatype of all the fields mentioned above are Text. Apart from the
above columns, there's another column in the DB table named
'RegDateTime' whose datatype is Date/Time which is populated with the
date & time at which the user registered.

When a user submits these details, a ASPX function first checks
whether the UserName entered by the user already exists or not in the
DB table by comparing the UserName supplied by the user to the
UserNames that already exist in the Access DB table. If the supplied
UserName already exists, he is shown a message saying "UserName
already exists. Please use a different UserName".

Assuming that the UserName supplied by the user does not exist in the
Access DB table, the same ASPX function then checks whether the email
supplied by the user already exists in the DB table or not. If the
supplied email already exists, then he is shown a message saying
"EMail ID already exists! Please use a different EMail ID".

If both the UserName & the EMail supplied by the user doesn't exist in
the DB table, this ASPX function invokes another ASPX sub-routine
which finally inserts this new record in the Access DB table. This is
the sub-routine that inserts the new record in the DB table (nothing
extraordinary.....it's the same usual thing that I have done a hundred
thousand times in my life):

Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader

oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))

oledbCmd = New OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, Password, Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" & LastName1
& "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1 & "', '" &
Address1 & "', '" & City1 & "', '" & State1 & "', '" & Country1 & "',
'" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now & "')", oledbConn)

'oledbCmd.CommandType = CommandType.Text

System.Web.HttpContext.Current.Response.Write("INSERT INTO Users
(FirstName, LastName, EMail, UserName, Password, Address, City, State,
Country, Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" &
LastName1 & "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1
& "', '" & Address1 & "', '" & City1 & "', '" & State1 & "', '" &
Country1 & "', '" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now &
"')" & "<br>")

oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
'iUID = oledbCmd.ExecuteScalar
oledbConn.Close()
End Sub

When I try to insert records in the DB table, the following error gets
generated:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement.

which points to the ExecuteReader line shown in the above sub-routine.
That's the reason why I have added the Response.Write line so as
detect where's the syntax error in the INSERT INTO statement but I
don't find any syntax error in the SQL query. I also tried replacing
the single quotes around the 'RegDateTime' record with # (since it's
datatype is Date/Time) but that doesn't make any difference. When I
comment the last 4 lines in the above sub-routine, then one such
Response.Write line generates the SQL query shown below:

INSERT INTO Users (FirstName, LastName, EMail, UserName, Password,
Address, City, State, Country, Zip, Phone, RegDateTime) VALUES
('Peter', 'North', '(e-mail address removed)', 'peter', 'peter', 'Some
Address', 'Some City', 'Some State', 'Poland', '889775', '123456789',
'12/02/2007 2:33:03 AM')

When I copy & paste the above SQL output in a Query window in MS-
Access, the above records get populated successfully but I am
continuously getting the above error message when I try to execute the
same SQL query through this ASP.NET app.

Can someone please point out where I could be going wrong?

It's driving me nuts since last 3 hours & I just can't seem to locate
what's exactly causing the error!

After a few experiments I found that if I remove the column 'Password'
& it's value from the INSERT statement, then the INSERT query works
fine but I don't understand how does the 'Password' column or the
'Password' value entered by the user pose a problem!

The datatype of the 'Password' column in the Access DB table is 'Text'
just like the datatype of the other columns. I am not even entering
any special characters in the password nor am I exceeding the maximum
length of the password which is set to 50 in the Access DB table.

In fact, the 'Allow Zero Length' property of the 'Password' column has
been set to No; so how come Access is allowing zero length values in
the 'Password' column?

Oh Gosh...Good Heavens...someone please come to my rescue else I might
have to get myself admitted in a mental asylum soon!- Hide quoted text -

- Show quoted text -

Thanks a lot Milosz especially for the additional suggestion but sorry
to say that when I tried your code, it generated the following error:

--------------------------------
The OleDbParameterCollection only accepts non-null OleDbParameter type
objects, not String objects.
--------------------------------

pointing to the following line:

--------------------------------
oledbCmd.Parameters.Add(FirstName)
--------------------------------

How do I overcome this error?

To be honest I wasn't aware that one can use "?" parameter placeholder
with OledbParameters to prevent SQL Injection though I have got some
idea on SQL Injection.

Thanks once again,

Regards.

 

[rn5a]

I'm sorry i cannot test my code at the moment, try:

oledbCmd.Parameters.Add("?", Data.OleDb.OleDbType.VarChar).Value = FirstName1
oledbCmd.Parameters.Add("?", Data.OleDb.OleDbType.VarChar).Value = LastName1
etc.

Remember to set the second parameter (Data.OleDb.OleDbType enumeration)
properly (based on database table column types).

--
Milosz

Hi there,
Password is a reserved word in Access 2000 SQL. You can bracket reserved
words in the INSERT statement:
INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country)
Please also note your code is vulnerable for SQL-injection type attacks. Use
OleDbParameter and "?" parameter placeholder in your query:
Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader
oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))
oledbCmd = new OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", oledbConn)
oledbCmd.Parameters.Add(FirstName1)
oledbCmd.Parameters.Add(LastName1)
oledbCmd.Parameters.Add(Email1)
oledbCmd.Parameters.Add(UserName1)
oledbCmd.Parameters.Add(Password1)
oledbCmd.Parameters.Add(Address1)
oledbCmd.Parameters.Add(City1)
oledbCmd.Parameters.Add(State1)
oledbCmd.Parameters.Add(Country1)
oledbCmd.Parameters.Add(Zip1)
oledbCmd.Parameters.Add(Phone1)
oledbCmd.Parameters.Add(DateTime.Now)
try
oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
while oledbReader.Read()
' do something here
loop
catch ex as Exception
throw ex
finally
oledbConn.Close()
--
Milosz
:
On Feb 12, 2:24 am, (e-mail address removed) wrote:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am
using MS-Access 2000 database table for this app. Note that the
datatype of all the fields mentioned above are Text. Apart from the
above columns, there's another column in the DB table named
'RegDateTime' whose datatype is Date/Time which is populated with the
date & time at which the user registered.
When a user submits these details, a ASPX function first checks
whether the UserName entered by the user already exists or not in the
DB table by comparing the UserName supplied by the user to the
UserNames that already exist in the Access DB table. If the supplied
UserName already exists, he is shown a message saying "UserName
already exists. Please use a different UserName".
Assuming that the UserName supplied by the user does not exist in the
Access DB table, the same ASPX function then checks whether the email
supplied by the user already exists in the DB table or not. If the
supplied email already exists, then he is shown a message saying
"EMail ID already exists! Please use a different EMail ID".
If both the UserName & the EMail supplied by the user doesn't exist in
the DB table, this ASPX function invokes another ASPX sub-routine
which finally inserts this new record in the Access DB table. This is
the sub-routine that inserts the new record in the DB table (nothing
extraordinary.....it's the same usual thing that I have done a hundred
thousand times in my life):
Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader
oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))
oledbCmd = New OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, Password, Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" & LastName1
& "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1 & "', '" &
Address1 & "', '" & City1 & "', '" & State1 & "', '" & Country1 & "',
'" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now & "')", oledbConn)
'oledbCmd.CommandType = CommandType.Text
System.Web.HttpContext.Current.Response.Write("INSERT INTO Users
(FirstName, LastName, EMail, UserName, Password, Address, City, State,
Country, Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" &
LastName1 & "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1
& "', '" & Address1 & "', '" & City1 & "', '" & State1 & "', '" &
Country1 & "', '" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now &
"')" & "<br>")
oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
'iUID = oledbCmd.ExecuteScalar
oledbConn.Close()
End Sub
When I try to insert records in the DB table, the following error gets
generated:
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement.
which points to the ExecuteReader line shown in the above sub-routine.
That's the reason why I have added the Response.Write line so as
detect where's the syntax error in the INSERT INTO statement but I
don't find any syntax error in the SQL query. I also tried replacing
the single quotes around the 'RegDateTime' record with # (since it's
datatype is Date/Time) but that doesn't make any difference. When I
comment the last 4 lines in the above sub-routine, then one such
Response.Write line generates the SQL query shown below:
INSERT INTO Users (FirstName, LastName, EMail, UserName, Password,
Address, City, State, Country, Zip, Phone, RegDateTime) VALUES
('Peter', 'North', '(e-mail address removed)', 'peter', 'peter', 'Some
Address', 'Some City', 'Some State', 'Poland', '889775', '123456789',
'12/02/2007 2:33:03 AM')
When I copy & paste the above SQL output in a Query window in MS-
Access, the above records get populated successfully but I am
continuously getting the above error message when I try to execute the
same SQL query through this ASP.NET app.
Can someone please point out where I could be going wrong?
It's driving me nuts since last 3 hours & I just can't seem to locate
what's exactly causing the error!
After a few experiments I found that if I remove the column 'Password'
& it's value from the INSERT statement, then the INSERT query works
fine but I don't understand how does the 'Password' column or the
'Password' value entered by the user pose a problem!
The datatype of the 'Password' column in the Access DB table is 'Text'
just like the datatype of the other columns. I am not even entering
any special characters in the password nor am I exceeding the maximum
length of the password which is set to 50 in the Access DB table.
In fact, the 'Allow Zero Length' property of the 'Password' column has
been set to No; so how come Access is allowing zero length values in
the 'Password' column?
Oh Gosh...Good Heavens...someone please come to my rescue else I might
have to get myself admitted in a mental asylum soon!- Hide quoted text -
- Show quoted text -

Thanks a lot Milosz especially for the additional suggestion but sorry
to say that when I tried your code, it generated the following error:

pointing to the following line:
How do I overcome this error?

To be honest I wasn't aware that one can use "?" parameter placeholder
with OledbParameters to prevent SQL Injection though I have got some
idea on SQL Injection.

Thanks once again,

Regards.- Hide quoted text -

- Show quoted text -

Yes, Milosz, that does the trick.

One question regarding my original post - Password being a reserved
word in MS-Access has to be wrapped in square brackets (or change the
column name) but when I copied & pasted the SQL query output
(generated by the Response.Write line) in a Access Query (where in
Password was not enclosed in square brackets) & ran the query, why
didn't Access generate the same error (Incorrect syntax in INSERT INTO
statement)?

 

[Guest]

Good morning,

I suspect MS Access Query editor reformats the query behind the scenes.
--
Milosz

I'm sorry i cannot test my code at the moment, try:

oledbCmd.Parameters.Add("?", Data.OleDb.OleDbType.VarChar).Value = FirstName1
oledbCmd.Parameters.Add("?", Data.OleDb.OleDbType.VarChar).Value = LastName1
etc.

Remember to set the second parameter (Data.OleDb.OleDbType enumeration)
properly (based on database table column types).

--
Milosz

On Feb 12, 5:52 am, Milosz Skalecki [MCAD] <[email protected]>
wrote:
Hi there,

Password is a reserved word in Access 2000 SQL. You can bracket reserved
words in the INSERT statement:

INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country)

Please also note your code is vulnerable for SQL-injection type attacks. Use
OleDbParameter and "?" parameter placeholder in your query:

Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader

oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))

oledbCmd = new OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, [Password], Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", oledbConn)

oledbCmd.Parameters.Add(FirstName1)
oledbCmd.Parameters.Add(LastName1)
oledbCmd.Parameters.Add(Email1)
oledbCmd.Parameters.Add(UserName1)
oledbCmd.Parameters.Add(Password1)
oledbCmd.Parameters.Add(Address1)
oledbCmd.Parameters.Add(City1)
oledbCmd.Parameters.Add(State1)
oledbCmd.Parameters.Add(Country1)
oledbCmd.Parameters.Add(Zip1)
oledbCmd.Parameters.Add(Phone1)
oledbCmd.Parameters.Add(DateTime.Now)

try
oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
while oledbReader.Read()
' do something here
loop

catch ex as Exception
throw ex
finally
oledbConn.Close()
:
On Feb 12, 2:24 am, (e-mail address removed) wrote:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am
using MS-Access 2000 database table for this app. Note that the
datatype of all the fields mentioned above are Text. Apart from the
above columns, there's another column in the DB table named
'RegDateTime' whose datatype is Date/Time which is populated with the
date & time at which the user registered.

When a user submits these details, a ASPX function first checks
whether the UserName entered by the user already exists or not in the
DB table by comparing the UserName supplied by the user to the
UserNames that already exist in the Access DB table. If the supplied
UserName already exists, he is shown a message saying "UserName
already exists. Please use a different UserName".

Assuming that the UserName supplied by the user does not exist in the
Access DB table, the same ASPX function then checks whether the email
supplied by the user already exists in the DB table or not. If the
supplied email already exists, then he is shown a message saying
"EMail ID already exists! Please use a different EMail ID".

If both the UserName & the EMail supplied by the user doesn't exist in
the DB table, this ASPX function invokes another ASPX sub-routine
which finally inserts this new record in the Access DB table. This is
the sub-routine that inserts the new record in the DB table (nothing
extraordinary.....it's the same usual thing that I have done a hundred
thousand times in my life):

Public Sub AddRecord(ByVal FirstName1 As String, ByVal LastName1 As
String, ByVal EMail1 As String, ByVal UserName1 As String, ByVal
Password1 As String, ByVal Address1 As String, ByVal City1 As String,
ByVal State1 As String, ByVal Country1 As String, ByVal Zip1 As
String, ByVal Phone1 As String)
Dim iUID As Integer
Dim oledbCmd As OledbCommand
Dim oledbConn As OledbConnection
Dim oledbReader As OledbDataReader

oledbConn = New OledbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & System.Web.HttpContext.Current.Server.MapPath("DB
\Users.mdb"))

oledbCmd = New OledbCommand("INSERT INTO Users (FirstName,
LastName, EMail, UserName, Password, Address, City, State, Country,
Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" & LastName1
& "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1 & "', '" &
Address1 & "', '" & City1 & "', '" & State1 & "', '" & Country1 & "',
'" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now & "')", oledbConn)

'oledbCmd.CommandType = CommandType.Text

System.Web.HttpContext.Current.Response.Write("INSERT INTO Users
(FirstName, LastName, EMail, UserName, Password, Address, City, State,
Country, Zip, Phone, RegDateTime) VALUES ('" & FirstName1 & "', '" &
LastName1 & "', '" & EMail1 & "', '" & UserName1 & "', '" & Password1
& "', '" & Address1 & "', '" & City1 & "', '" & State1 & "', '" &
Country1 & "', '" & Zip1 & "', '" & Phone1 & "', '" & DateTime.Now &
"')" & "<br>")

oledbConn.Open()
oledbReader = oledbCmd.ExecuteReader
'iUID = oledbCmd.ExecuteScalar
oledbConn.Close()
End Sub

When I try to insert records in the DB table, the following error gets
generated:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement.

which points to the ExecuteReader line shown in the above sub-routine.
That's the reason why I have added the Response.Write line so as
detect where's the syntax error in the INSERT INTO statement but I
don't find any syntax error in the SQL query. I also tried replacing
the single quotes around the 'RegDateTime' record with # (since it's
datatype is Date/Time) but that doesn't make any difference. When I
comment the last 4 lines in the above sub-routine, then one such
Response.Write line generates the SQL query shown below:

INSERT INTO Users (FirstName, LastName, EMail, UserName, Password,
Address, City, State, Country, Zip, Phone, RegDateTime) VALUES
('Peter', 'North', '(e-mail address removed)', 'peter', 'peter', 'Some
Address', 'Some City', 'Some State', 'Poland', '889775', '123456789',
'12/02/2007 2:33:03 AM')

When I copy & paste the above SQL output in a Query window in MS-
Access, the above records get populated successfully but I am
continuously getting the above error message when I try to execute the
same SQL query through this ASP.NET app.

Can someone please point out where I could be going wrong?

It's driving me nuts since last 3 hours & I just can't seem to locate
what's exactly causing the error!

After a few experiments I found that if I remove the column 'Password'
& it's value from the INSERT statement, then the INSERT query works
fine but I don't understand how does the 'Password' column or the
'Password' value entered by the user pose a problem!

The datatype of the 'Password' column in the Access DB table is 'Text'
just like the datatype of the other columns. I am not even entering
any special characters in the password nor am I exceeding the maximum
length of the password which is set to 50 in the Access DB table.

In fact, the 'Allow Zero Length' property of the 'Password' column has
been set to No; so how come Access is allowing zero length values in
the 'Password' column?

Oh Gosh...Good Heavens...someone please come to my rescue else I might
have to get myself admitted in a mental asylum soon!- Hide quoted text -

- Show quoted text -

Thanks a lot Milosz especially for the additional suggestion but sorry
to say that when I tried your code, it generated the following error:

pointing to the following line:
How do I overcome this error?

To be honest I wasn't aware that one can use "?" parameter placeholder
with OledbParameters to prevent SQL Injection though I have got some
idea on SQL Injection.

Thanks once again,

Regards.- Hide quoted text -

- Show quoted text -

Yes, Milosz, that does the trick.

One question regarding my original post - Password being a reserved
word in MS-Access has to be wrapped in square brackets (or change the
column name) but when I copied & pasted the SQL query output
(generated by the Response.Write line) in a Access Query (where in
Password was not enclosed in square brackets) & ran the query, why
didn't Access generate the same error (Incorrect syntax in INSERT INTO
statement)?