SQL statement syntax error

starstarstarstarstarstarstarstarstarstar Rating: 0/5 (0 vote cast) print

strSqlCmd = "INSERT INTO Members (Username, Password, Email, TitleAdd, Surname, Name, Birthday, Race, Homepage, Biography, Location, Occupation) VALUES ('" & strUsername & "','" & strPassword & "','" & strEmail & "','" & strTitle & "','" & strSurname & "','" & strName & "','" & strBirthday & "','" & strRace & "','" & strHomepage & "','" & strBiography & "','" & strLocation & "','" & strOccupation & "')"


 


the above is my SQL statement error. I do not know why is there an error. Can anyone HELP MEE???


(Do note that i did not use ' _' to make a new line but this is directly copy from my vb.net file the continous coding)

 : Hua Rong     Reply  

Replies (14)

profile
KINGSLEY TAGBO

You didn't answer all my questions. What type of database are you using? Can you use a stored procedure on your database instead of a dynamic SQL statement?

Thanks


huh? what is a the difference between stored procedire and dynamic SQL?

: Hua Rong    Reply

profile
KINGSLEY TAGBO

What is the type of database you are using SQL Server, Ms Access, Oracle, MYSQL, etc?


MS access.

: Hua Rong    Reply

profile
KINGSLEY TAGBO

I am not sure about what you mean by the blue underline, however, you can try this System.Diagnostics.Debug.WriteLine(strSqlCmd) It looks like you have an error in your string statement and when you print it out, it may be easier to spot check the dynamic sql string you are trying to create.

Thanks


Can you send the stack trace of the error...it will b great help for me to analyse....


Sure. Thanks alot.

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]

System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object executeResult)

System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object executeResult)

System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object executeResult)

System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Great_China.DbMgmt.ExecuteNonQuery(String cmd) in C:Inetpubhttp://www.kdkeys.net/sql-statement-syntax-error/#link-7179 ChinaDbMgmt.vb:89

Great_China.confirmRegister.submitBtn_Click(Object sender, EventArgs e) in C:Inetpubhttp://www.kdkeys.net/sql-statement-syntax-error/#link-7179 ChinaconfirmRegister.aspx.vb:75

System.Web.UI.WebControls.Button.OnClick(EventArgs e)

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)

System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)

System.Web.UI.Page.ProcessRequestMain()

: Hua Rong    Reply

Hi,

I tried your code it works perfectly for my given input. Whatevr I thought it gives different error...I'm sorry but please provide me your entire code and the database schema of Members table....

and data against which you are testing the code..which causes the above error to occurred..

Its really intersting problem...


Oh my friend helps me solve my problem already, he said it is because i used reserved words, username and password. But anyway thank alot for your effort, you do help me not only in this thread but other threads too! thanks

: Hua Rong    Reply

One thing that will break it for example is someone entering the surname O'Kelly. The single quote messes it up. What you need for each string is Replace(str,"'","''") or more clearly Replace (str," ' ", " ' ' "). This 'escapes' any single quotes in the user entered strings.

: caustin    Reply

profile
KINGSLEY TAGBO

What is the error message and what type of database are you accessing with the SQL statement?

Add the line DEBUG.PRINT(strSqlCmd) immediately after the SQL statement above and before executing it and then post both the error message and the printed strSqlCmd string to this message so that we can more easily diagnose your problem.

Thanks


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.

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

Source Error:

Line 87: Line 88: sqlCmd.CommandText = cmdLine 89: sqlCmd.ExecuteNonQuery()Line 90: Line 91: End Sub

But i got a problem, the DEBUG.PRINT(strSqlCmd) adds a error to the vb.net with the zig-zag blue underline.

thk alot

: Hua Rong    Reply

('" strUsername "','" strPassword "','" strEmail "','" strTitle "','" strSurname "','" strName "','" strBirthday "','" strRace "','" strHomepage "','" strBiography "','" strLocation "','" strOccupation "')"

in the above statement what you have missed is quotes like

("' UserName

it should be

("'" UserName

If you see you have to put the single quote between two double quotes.

Hope this helps you...Let me know


not it does not works because the first inverted double comma that i type will make the rest of the line become green. But thanks for your reply.

: Hua Rong    Reply


Post A Reply

 Questions & Answers