473,217 Members | 2,060 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,217 software developers and data experts.

Irritating INSERT INTO Error

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("INS ERT 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', 'p****@hotmail.com', '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!

Feb 11 '07 #1
6 3410
On Feb 12, 2:24 am, r...@rediffmail.com 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("INS ERT 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', 'pe...@hotmail.com', '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!

Feb 11 '07 #2
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
"rn**@rediffmail.com" wrote:
On Feb 12, 2:24 am, r...@rediffmail.com 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("INS ERT 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', 'pe...@hotmail.com', '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!

Feb 12 '07 #3
On Feb 12, 5:52 am, Milosz Skalecki [MCAD] <mily...@DONTLIKESPAMwp.pl>
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()

--
Milosz

"r...@rediffmail.com" wrote:
On Feb 12, 2:24 am, r...@rediffmail.com 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("INS ERT 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', 'pe...@hotmail.com', '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.

Feb 12 '07 #4
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
"rn**@rediffmail.com" wrote:
On Feb 12, 5:52 am, Milosz Skalecki [MCAD] <mily...@DONTLIKESPAMwp.pl>
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()

--
Milosz

"r...@rediffmail.com" wrote:
On Feb 12, 2:24 am, r...@rediffmail.com 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("INS ERT 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', 'pe...@hotmail.com', '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.

Feb 12 '07 #5
On Feb 12, 6:52 am, Milosz Skalecki [MCAD] <mily...@DONTLIKESPAMwp.pl>
wrote:
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

"r...@rediffmail.com" wrote:
On Feb 12, 5:52 am, Milosz Skalecki [MCAD] <mily...@DONTLIKESPAMwp.pl>
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()
--
Milosz
"r...@rediffmail.com" wrote:
On Feb 12, 2:24 am, r...@rediffmail.com 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("INS ERT 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', 'pe...@hotmail.com', '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.- 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)?

Feb 12 '07 #6
Good morning,

I suspect MS Access Query editor reformats the query behind the scenes.
--
Milosz
"rn**@rediffmail.com" wrote:
On Feb 12, 6:52 am, Milosz Skalecki [MCAD] <mily...@DONTLIKESPAMwp.pl>
wrote:
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

"r...@rediffmail.com" wrote:
On Feb 12, 5:52 am, Milosz Skalecki [MCAD] <mily...@DONTLIKESPAMwp.pl>
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()
--
Milosz
"r...@rediffmail.com" wrote:
On Feb 12, 2:24 am, r...@rediffmail.com 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("INS ERT 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', 'pe...@hotmail.com', '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.- 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)?

Feb 12 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Murali Kanaga | last post by:
Greetings! When I run the following SQL statement in Perl, I get an error stating: any help/pointers how this can be resolved? Thanks, -Murali SQL note_insert error: "There are more...
1
by: INSERT ERROR | last post by:
I am trying to INSERT record in MYSQL database, but each time the code tries to execute the command: MySQL_cmdGLAccount.ExecuteNonQuery(), it reports an error with the message "COLUMN COUNT...
3
by: INSERT ERROR | last post by:
I am trying to INSERT record in MYSQL database, but each time the code tries to execute the command: MySQL_cmdGLAccount.ExecuteNonQuery(), it reports an error with the message "COLUMN COUNT...
8
by: erin.sebastian | last post by:
Hi all, I have a really silly problem that i can't find the answer too. I am working with VB.NET and i am trying to insert a new record into my access database (pretty easy right?) well i am...
3
by: phil2phil | last post by:
Hi, I'm working on an asp where I wanted to do a simple insert using data from another table, structure is: Table A has columns - ID, Code, Type Table B has columns - key, FName, LNAme, DOB, cd,...
3
by: siamesedream | last post by:
Hello, I'm having trouble using the insert method of an ObjectDataSource that uses a custom DataObjectType. If I'm using a custom DataObjectType does my insert method have to take that object,...
2
by: technocraze | last post by:
Hi guys, I have encountered this error when updating the values to the MS Acess table. Error : Update on linked table failed. ODBC sql server error Timeout expired. MS Acess is my front end and...
3
by: Greg Corradini | last post by:
Hello, I'm trying to perform a simple insert statement into a table called Parcel_Test (see code below). Yet, I get an error message that I've never seen before (see traceback below). I've tried...
2
by: Greg Corradini | last post by:
Hello, I've never gotten this traceback error before using mx.ODBC. Any ideas about resolving this issue? The statement and the error it generates are listed below. curse.execute("Insert into...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.