I cant believe I cant see this error!! It may be cause I have been programing non stop for over 48hours and I think its getting to me. Anyhow will one of you guys help me spot the syntax error so i can rest.lol
Bonus for newbies trying to connect to 2007-2010 databases in Visual studio 2008 this is how you do it in the code behind. Dont forget to download the accessdatabase libaryfor your computer or it wont see the database in your datasource.
I just found that this morning. I forgot that "ORDER" was reserved. Thanks for your help guys.
On another note this insertion into the access database was a quick and dirty solution. A parameterized insertion is better as long as you pay attention. The string. Format is probably the best way but wasn’t thinking about rightness just doneness. So now
it’s done. Plus any advice that works is good advice. I’m sure you won’t agree but it works for a reason. Different situations calls for different techniques
It is not, and would not have solved your problem. Patric is wrong. You must use parameters as you in fact did. Never dynamically construct SQL as you open yourself to the possibility of a SQL injection attack.
inkblot600
Member
44 Points
18 Posts
OleDbCommand Insert Syntax Error! LOL
Nov 01, 2011 07:41 PM|LINK
I cant believe I cant see this error!! It may be cause I have been programing non stop for over 48hours and I think its getting to me. Anyhow will one of you guys help me spot the syntax error so i can rest.lol
Bonus for newbies trying to connect to 2007-2010 databases in Visual studio 2008 this is how you do it in the code behind. Dont forget to download the accessdatabase libaryfor your computer or it wont see the database in your datasource.
Here is the Full Code: VB>NET ASP>NET 2008
'----------------------------------------------------------------------------
Protected
Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
' create new Client
Dim Connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" & Server.MapPath("~/App_Data/RepairDB.accdb"))
Connection.Open()
'This is where the Error Is ---------------------------------------------------------------------------->
Dim Command As New OleDbCommand("INSERT INTO ClientData" & _
"(FirstName, LastName,Address, City, State, Zip, Phone1, Phone2, Phone3, Email, BirthDate, ClientCode, SaleDate, UplineCode, Status, LoginName, LoginPassword, Role, Order, Cost)VALUES(@FirstName, @LastName, @Address,@City, @State, @Zip, @Phone1, @Phone2, @Phone3, @Email, @BirthDate, @ClientCode, @SaleDate, @UplineCode, @Status, @LoginName, @LoginPassword, @Role, @Order, @Cost)", Connection)
'End Error ---------------------------------------------------------------------------->
Command.Parameters.Add(
New OleDbParameter("@FirstName", Firstname.Text))
Command.Parameters.Add(
New OleDbParameter("@LastName", Lastname.Text))
Command.Parameters.Add(
New OleDbParameter("@Address", Address.Text))
Command.Parameters.Add(
New OleDbParameter("@City", City.Text))
Command.Parameters.Add(
New OleDbParameter("@State", State.Text))
Command.Parameters.Add(
New OleDbParameter("@Zip", Zip.Text))
Command.Parameters.Add(
New OleDbParameter("@Phone1", Phone1.Text))
Command.Parameters.Add(
New OleDbParameter("@Phone2", Phone2.Text))
Command.Parameters.Add(
New OleDbParameter("@Phone3", Phone3.Text))
Command.Parameters.Add(
New OleDbParameter("@Email", Email.Text))
Command.Parameters.Add(
New OleDbParameter("@Birthdate", Birthdate.Text))
Command.Parameters.Add(
New OleDbParameter("@ClientCode", ClientCode.Text))
Command.Parameters.Add(
New OleDbParameter("@SaleDate", SaleDate.Text))
Command.Parameters.Add(
New OleDbParameter("@UplineCode", UplineCode.Text))
Command.Parameters.Add(
New OleDbParameter("@Status", "Pending"))
Command.Parameters.Add(
New OleDbParameter("@LoginName", LoginName.Text))
Command.Parameters.Add(
New OleDbParameter("@LoginPassword", LoginPassword.Text))
Command.Parameters.Add(
New OleDbParameter("@Role", "Client"))
'Client Order
Dim sb As New StringBuilder()
For i As Integer = 0 To ListBox1.Items.Count - 1
Dim item As ListItem = ListBox1.Items(i)
sb.Append(item.Value)
If i < ListBox1.Items.Count - 1 Then
sb.Append(
", ")
End If
Next
Command.Parameters.Add(
New OleDbParameter("@Order", sb.ToString))
'Client Cost
Command.Parameters.Add(
New OleDbParameter("@Cost", cost.Text))
Command.ExecuteNonQuery()
Connection.Close()
Message.Visible =
True
Message.Text =
"Client Inserted."
End Sub
Mikesdotnett...
All-Star
154830 Points
19854 Posts
Moderator
MVP
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 07:43 AM|LINK
ORDER is a reserved word as far as the JET provider is concerned. I see you are using the ACE provider, but it may well still be the case.
80040E14 - MS Access Syntax Error messages
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
patricgreat
Participant
983 Points
278 Posts
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 12:53 PM|LINK
use the String.Format to construct your query instead of directly using with command object, because it wont replace your variable with the values.
Mikesdotnett...
All-Star
154830 Points
19854 Posts
Moderator
MVP
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 02:36 PM|LINK
Wrong. And wrong. Terrible advice.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
inkblot600
Member
44 Points
18 Posts
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 03:20 PM|LINK
I just found that this morning. I forgot that "ORDER" was reserved. Thanks for your help guys.
On another note this insertion into the access database was a quick and dirty solution. A parameterized insertion is better as long as you pay attention. The string. Format is probably the best way but wasn’t thinking about rightness just doneness. So now it’s done. Plus any advice that works is good advice. I’m sure you won’t agree but it works for a reason. Different situations calls for different techniques
inkblot600
Member
44 Points
18 Posts
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 03:23 PM|LINK
I changed order to "orderdetails" works now. Thanks. Stupid reserved words!!!! lol
inkblot600
Member
44 Points
18 Posts
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 03:28 PM|LINK
Patric thanks for your help. The snippet works great for me now!!. This is how microsoft wants you to insert it.
http://msdn.microsoft.com/en-us/library/bb208861(v=office.12).aspx
It replaces my values perfectly and without any error whatsoever. I update,delete,edit,create new records. Index's correctly each and everytime.
Maybe you guys can explain to me what exactly is wrong with using this type of technique.
Thanks
hans_v
All-Star
35986 Points
6550 Posts
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 07:41 PM|LINK
NO! Parameterized queries are the best way....
Mikesdotnett...
All-Star
154830 Points
19854 Posts
Moderator
MVP
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 08:30 PM|LINK
It is not, and would not have solved your problem. Patric is wrong. You must use parameters as you in fact did. Never dynamically construct SQL as you open yourself to the possibility of a SQL injection attack.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
inkblot600
Member
44 Points
18 Posts
Re: OleDbCommand Insert Syntax Error! LOL
Nov 02, 2011 09:21 PM|LINK
THANK YOU!!!!!!! Exactly. Thanks Mike.
We all learn everyday. LOL. Its all fun anyway Right????