By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,084 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

error insert into

P: 25
hi guys,
i just new in asp.net world.

i have an error for my first web test.
here my code :

Expand|Select|Wrap|Line Numbers
  1. sub OnBtnSendClicked (s As Object, e As EventArgs)
  2.                 Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("surat.mdb") & ";"
  3.                 Dim MySQL As String = "INSERT INTO surat (No, NoSurat, TglSurat, kategori, Tujuan, Perihal, Deskripsi) VALUES ('" & txtno.Text & "','" & txtnosrt.Text & "','" & txttgl.Text & "','" & Txtkategori.Text & "','" & txttujuan.Text & "','" & txthal.Text & "','" & txtdeskripsi.Text & "')"
  4.     Dim MyConn as New OleDBConnection (strConn)
  5.     Dim cmd as New OleDBCommand (MySQL, MyConn)
  6.     MyConn.Open ()
  7.     cmd.ExecuteNonQuery ()
  8.     MyConn.Close ()
  9.  
  10.  
  11. end sub
when i tried to run this command i got error, and saying like this :

Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Expand|Select|Wrap|Line Numbers
  1. Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
  2.  
  3. Source Error: 
  4.  
  5.  
  6. Line 13:     Dim cmd as New OleDBCommand (MySQL, MyConn)
  7. Line 14:     MyConn.Open ()
  8. Line 15:     cmd.ExecuteNonQuery ()
  9. Line 16:     MyConn.Close ()
  10. Line 17:     
i already check that my insert into command were just fine, maybe you guys can help me to solve this matter. maybe i missed something, i don;t realize, since i new here.
thank you.
Apr 20 '09 #1
Share this Question
Share on Google+
18 Replies


Frinavale
Expert Mod 5K+
P: 9,731
Well, I think it might have to do with how you're creating your SQL command query.

You should not be dynamically creating your SQL command query by concatenating a string together. You should be using parameters instead. There are a number of reasons why.


First of all, using parameters will help you see where you have made an error in your SQL query.

Secondly, building SQL queries as you are right now leave your application vulnerable to a SQL Injection Attack.

Please check out the article on how to use a database in your program examples on how to use parameters for your SQL commands.
Apr 20 '09 #2

maliksleo
100+
P: 115
i think there is some problem of data type of your fields in database. Due to witch your query has concatenation problem.

first of all check your data types of fields and if there is any field having data type of integer then dont use coats for that field in your query for concatenation.
Hope your problem will be solved by this.

maliksleo
Apr 21 '09 #3

P: 25
well thank you for the advice.

i have a few data with different data type. i tried to stored the data into parameters. but still it didn't work.

Expand|Select|Wrap|Line Numbers
  1. Sub OnBtnSendClicked(ByVal s As Object, ByVal e As EventArgs)
  2.  
  3.                 Dim no As Integer
  4.                 Dim nosurat, kategori, tujuan, hal, deskripsi As String
  5.                 Dim tgl As Date
  6.                 Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("surat.mdb") & ";"
  7.                 Dim MySQL As String = "INSERT INTO surat " & _
  8.           "(No, NoSurat, TglSurat, kategori, Tujuan, Perihal, Deskripsi) VALUES " & _
  9.           "(@no,@nosurat,@tgl,@kategori,@tujuan,@hal,@deskripsi)"
  10.                 Dim MyConn As New OleDbConnection(strConn)
  11.                 Dim cmd As New OleDbCommand(MySQL, MyConn)
  12.  
  13.  
  14.                 no = txtno.Text
  15.                 nosurat = txtnosrt.Text
  16.                 tgl = txttgl.Text
  17.                 kategori = Txtkategori.Text
  18.                 tujuan = txttujuan.Text
  19.                 hal = txthal.Text
  20.                 deskripsi = txtdeskripsi.Text
  21.  
  22.  
  23.                 MyConn.Open()
  24.                 cmd.ExecuteNonQuery()
  25.                 MyConn.Close()
and still reporting the same error.
here are the detail error message.


Expand|Select|Wrap|Line Numbers
  1. [OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
  2.    System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1003520
  3.    System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +255
  4.    System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +188
  5.    System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
  6.    System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +161
  7.    System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
  8.    ASP.guestbook_aspx.OnBtnSendClicked(Object s, EventArgs e) in D:\ASPwebsite\guestbook.aspx:31
  9.    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
  10.    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
  11.    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
  12.    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
  13.    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
  14.    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
help......
Apr 22 '09 #4

maliksleo
100+
P: 115
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String = "INSERT INTO surat (No, NoSurat, TglSurat, kategori, Tujuan, Perihal, Deskripsi) VALUES (" & txtno.Text & ",'" & txtnosrt.Text & "'," & txttgl.Text & ",'" & Txtkategori.Text & "','" & txttujuan.Text & "','" & txthal.Text & "','" & txtdeskripsi.Text & "')"
Replace your query in your first post with the above mentioned one hope your problem will gone by this.

Else visit http://msdn.microsoft.com/en-us/library/bb208861.aspx link for further details.

maliksleo
Apr 22 '09 #5

Frinavale
Expert Mod 5K+
P: 9,731
@dbdb
That's because you aren't using parameters correctly.
You need to actually add the parameters to the OleDbCommand. You should take a look at how to use OleDbCommand Parameters...it should look something like this:

Expand|Select|Wrap|Line Numbers
  1. Sub OnBtnSendClicked(ByVal s As Object, ByVal e As EventArgs)
  2.  
  3.                 Dim no As Integer
  4.                 Dim nosurat, kategori, tujuan, hal, deskripsi As String
  5.                 Dim tgl As Date
  6.                 Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("surat.mdb") & ";"
  7.                 Dim MySQL As String = "INSERT INTO surat " & _
  8.           "(No, NoSurat, TglSurat, kategori, Tujuan, Perihal, Deskripsi) VALUES " & _
  9.           "(@no,@nosurat,@tgl,@kategori,@tujuan,@hal,@deskripsi)"
  10.                 Dim MyConn As New OleDbConnection(strConn)
  11.                 Dim cmd As New OleDbCommand(MySQL, MyConn)
  12.  
  13.  
  14.                 no = txtno.Text
  15.                 nosurat = txtnosrt.Text
  16.                 tgl = txttgl.Text
  17.                 kategori = Txtkategori.Text
  18.                 tujuan = txttujuan.Text
  19.                 hal = txthal.Text
  20.                 deskripsi = txtdeskripsi.Text
  21.  
  22.                 cmd.Parameters.Add("@no", OleDbType.Integer).Value = no
  23.                 cmd.Parameters.Add("@no", OleDbType.Integer).Value = nosurat
  24.                 cmd.Parmaeters.Add("@tgl", OleDbType.VarChar).Value = tgl
  25.                 cmd.Parmaeters.Add("@kategori", OleDbType.VarChar).Value = kategori
  26.                 cmd.Parmaeters.Add("@tujuan", OleDbType.VarChar).Value = tujuan
  27.                 cmd.Parmaeters.Add("@hal", SqlDbType.VarChar).Value = hal
  28.                 cmd.Parmaeters.Add("@deskripsi ", OleDbType.VarChar).Value = deskripsi
  29.  
  30.                 cmd.ExecuteNonQuery()
  31.                 MyConn.Close()
Apr 22 '09 #6

Frinavale
Expert Mod 5K+
P: 9,731
@maliksleo
Maliksleo,

Creating SQL Statements dynamically as you have suggested leaves the application open to SQL Insertion attack. See the link I mentioned posted before for more information.
Apr 22 '09 #7

maliksleo
100+
P: 115
@Frinavale
I think you miss judge my query its written in vb code and with out putting double qoates its unable to pass the values to the databse from vb code.
I think you are talking about the SQL query where only single qoates are necessary. I read your injection attack you talk about the removal of double quotes but with the removal of double quotes no value will be inserted.
If i am wrong then please clear my views by your post. Thanks

maliksleo
Apr 23 '09 #8

Frinavale
Expert Mod 5K+
P: 9,731
Using parameters in .NET lets you pass the values without having to use any quotes at all. When you use parameters (the Command.Parameters property), .NET indicates that these values should be treated as literals only.

:)
Apr 23 '09 #9

maliksleo
100+
P: 115
@Frinavale
i got your point but i must say that you have to give atleast one example for illustration in your Insertion attack topic. Any how its realy helpful for me that by the virtue of this post i studied this SQL-injection and found that its realy terrible for your data. Ihope this link will help others as well.
Thanks Frinny

maliksleo
Apr 24 '09 #10

P: 25
thank you guys, i already found the way to run my query,

here is the code, hope it could inspired somebody who just learn Asp.Net like me.

Expand|Select|Wrap|Line Numbers
  1. Dim no As Integer
  2.                 Dim nosurat, kategori, tujuan, hal, deskripsi As String
  3.                 Dim tgl As Date
  4.                 Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("surat.mdb") & ";"
  5.                 Dim MySQL As String = "INSERT INTO surat " & _
  6.           "([no], NoSurat,[tglsurat], kategori, Tujuan, Perihal, Deskripsi) VALUES " & _
  7.           "(@no, @nosurat,@tgl, @kategori,@tujuan,@hal,@deskirpsi)"
  8.                 Dim MyConn As New OleDbConnection(strConn)
  9.                 Dim cmd As New OleDbCommand(MySQL, MyConn)
  10.  
  11.  
  12.                 no = txtno.Text
  13.                 nosurat = txtnosrt.Text
  14.                 tgl = txttgl.Text
  15.                 kategori = Txtkategori.Text
  16.                 tujuan = txttujuan.Text
  17.                 hal = txthal.Text
  18.                 deskripsi = txtdeskripsi.Text
  19.  
  20.  
  21.                 cmd.Parameters.Add("@no", OleDbType.Integer).Value = no
  22.                 cmd.Parameters.Add("@nosurat", OleDbType.VarChar).Value = nosurat
  23.                 cmd.Parameters.Add("@tgl", OleDbType.Date).Value = tgl.ToString
  24.                 cmd.Parameters.Add("@kategori", OleDbType.VarChar).Value = kategori
  25.                 cmd.Parameters.Add("@tujuan", OleDbType.VarChar).Value = tujuan
  26.                 cmd.Parameters.Add("@hal", OleDbType.VarChar).Value = hal
  27.                 cmd.Parameters.Add("@deskripsi ", OleDbType.VarChar).Value = deskripsi
  28.  
  29.  
  30.                 MyConn.Open()
  31.                 cmd.ExecuteNonQuery()
  32.                 MyConn.Close()
thank you.
Apr 28 '09 #11

P: 17
Hi I have a similar error (Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.), not sure what my problem is...please help. I am trying to insert data into a database using a stored procedure, please the the C# code below and the stored procedure:
Expand|Select|Wrap|Line Numbers
  1. USE [Intranet]
  2. GO
  3. /****** Object:  StoredProcedure [Intranet].[Add_advert]    Script Date: 04/29/2009 11:31:33 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author:
  10. -- Create date: 16 April 2009
  11. -- This stored procedure add a new advert
  12. --into theIntranet.classifieds_adverts table
  13.  
  14. -- =============================================
  15. ALTER PROCEDURE [Intranet].[Add_advert]
  16. (
  17. @Title varchar(255),  
  18. @Expiry_date datetime,
  19. @Description text,
  20. @Added_by varchar(50),
  21. @Email_address varchar (50)
  22. )
  23. AS
  24.  
  25. insert into Intranet.classifieds_adverts (Title, Expiry_date, Description, Added_by, Email_address)
  26. values (@Title, @Expiry_date, @Description, @Added_by, @Email_address)
  27.  
  28.  
  29. Intranet.Classifieds_adverts table
  30. Ad_Id (pk, int, not null), Title (varchar(255), not null), Expiry_date (datetime, not null), Description (text, not null), Added_by (varchar(50), not null) , Load_date(datetime, not null) this is generated by the system, Telephone_number(varchar (50) null), Email_address (varcahr(50), null)
Expand|Select|Wrap|Line Numbers
  1. private void Submit_Click(object sender, System.EventArgs e)
  2.         {
  3.         SqlCommand cmd =  new SqlCommand("Add_advert", new
  4.                 SqlConnection(ConfigurationSettings.AppSettings["ConnString"]));
  5.                 cmd.CommandType = CommandType.StoredProcedure;
  6.         cmd.Connection.Open();
  7.         cmd.Parameters.Add("@Title",SqlDbType.VarChar);
  8.         cmd.Parameters["@Title"].Value = txtTitle;
  9.                 cmd.Parameters.Add("@Expiry_date",SqlDbType.datetime);
  10.         cmd.Parameters["@Expiry_date"].Value = txtTitle;
  11.         cmd.Parameters.Add("@Description",SqlDbType.Text );
  12.         cmd.Parameters["@Description"].Value = txtDescr;
  13.         cmd.Parameters.Add("@Added_by",SqlDbType.VarChar );
  14.         cmd.Parameters["@Added_by"].Value = txtName;
  15.         cmd.Parameters.Add("@Email_address",SqlDbType.VarChar );
  16.         cmd.Parameters["@Description"].Value = txtEmail;
  17.  
  18.         cmd.ExecuteNonQuery();
  19.         cmd.Connection.Close();
  20.  
  21.         }
the error is on the line cmd.ExecuteNonQuery();

please help
Apr 29 '09 #12

maliksleo
100+
P: 115
Expand|Select|Wrap|Line Numbers
  1. cmd.Parameters.Add("@Expiry_date",SqlDbType.dateti me);
  2.  
  3. "check the spell"
  4. dateti me);
  5.  
check the above mentioned code i think you have a spelling mistake not a coding mistake

maliksleo
Apr 29 '09 #13

P: 17
thanks maliksleo, i have correct the typo and it now looks like(below) this but I'm still getting the error, not sure what am I doing wrong.


Expand|Select|Wrap|Line Numbers
  1. private void Submit_Click(object sender, System.EventArgs e)
  2.         {
  3.         SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
  4.         SqlCommand cmd =  new SqlCommand("Add_advert", con);
  5.         cmd.CommandType = CommandType.StoredProcedure;
  6.         con.Open();
  7.         cmd.Parameters.Add("@Title",SqlDbType.VarChar);
  8.         cmd.Parameters["@Title"].Value = txtTitle;
  9.         cmd.Parameters.Add("@Expiry_date",SqlDbType.DateTime);
  10.         cmd.Parameters["@Expiry_date"].Value = txtExpiryDATE;
  11.         cmd.Parameters.Add("@Description",SqlDbType.Text );
  12.         cmd.Parameters["@Description"].Value = txtDescr;
  13.         cmd.Parameters.Add("@Added_by",SqlDbType.VarChar );
  14.         cmd.Parameters["@Added_by"].Value = txtName;
  15.         cmd.Parameters.Add("@Email_address",SqlDbType.VarChar );
  16.         cmd.Parameters["@Email_address"].Value = txtEmail;
  17.  
  18.  
  19.         cmd.ExecuteNonQuery();
  20.         con.Close();
  21.  
  22.  
  23.         }
Apr 29 '09 #14

maliksleo
100+
P: 115
buddy plz check your spellings i think you still have the mistakes in your expiry_date and email_address lines.
maliksleo
Apr 29 '09 #15

P: 17
Sorry to be a pain, I've checked and I have @Expiry_date and @Email_address, I can't find the spelling mistakes in the new version of my code, please help.
Apr 30 '09 #16

P: 25
i don't know it could be run well or not. why don't you try just like i did.

i add the braket among the date field. like this :

Expand|Select|Wrap|Line Numbers
  1. insert into Intranet.classifieds_adverts (Title, [Expiry_date], Description, Added_by, Email_address) 
  2. values (@Title, @Expiry_date, @Description, @Added_by, @Email_address) 
wish it could be help.
coz my problem solve by that.
Apr 30 '09 #17

100+
P: 106
@zizi2
if txtTitle, txtDescr,txtName,txtEmail are text fields then use like
Expand|Select|Wrap|Line Numbers
  1. cmd.Parameters["@Email_address"].Value = txtEmail.Text;
Apr 30 '09 #18

P: 17
Thanks hammayun, it worked I needed to added the ".txt" and DateTime.Parse for the ExpiryDATE.

Thanks everyone for all your help.
May 2 '09 #19

Post your reply

Sign in to post your reply or Sign up for a free account.