[RESOLVED] What is the correct syntax for INSERT INTO using VB.Net and MS Access?

[oprache]

Hi,
I am using VisualStudio for WebExpress and VB.NET and my data is in an MS Access 2010 database (.accdb)
I connect using OLEDb (that part works well).
I have been trying (so far in vain) to understand how to construct an INSERT INTO query that can use parameters instead of literal values.
The MSDN info on this usage does not exist (at least I cannot find it)
Below is the code that fails for syntax error. It is part of an ASP.NET application I am developing.
sPN is defined as a string

strQuery = "INSERT INTO PartNumbers (PartNum) VALUES (" '" & sPN & "')"
Using con As OleDbConnection = New OleDbConnection(constring)
Using cmd As OleDbCommand = New OleDbCommand(strQuery, con)
cmd.CommandType = CommandType.Text
con.Open()
cmd.ExecuteNonQuery()
End Using
con.Close()
End Using
The result is a Syntax Error in the INSERT statement.

I have verified the connection part is ok with other actions (running a stored MS Access query for example)
I do not understand why Visual Studio puts a space between the first double quote and the next single quote. Obviously there is a reason but no luck trying to find any information on the net about that.
I tried VALUES (sPN)
I tried VALUES (sPN);
I tired VALUES (" '" & sPN & "'") having read text must be enclosed by single quotes
No luck
I would appreciate any help there understanding what the rules are and where they are documented.
Thanks in advance
Olivier

[jmcilhinney]

First things first, please use the appropriate formatting tags when posting code snippets so that they are easily readable.

As for the question, as far as i can see, the code you have posted wouldn't even compile so the SQL couldn't even be executed to produce that exception. It's generally helpful if you post the actual code you're using.

As we have to guess what the issue might be, the simple answer is to not use string concatenation when inserting values into SQL code. It's a great way to introduce all sorts of issues. To learn why and how to use parameters, follow the Blog link in my signature and check out my post on Parameters In ADO.NET. That will show you how you should have been doing it with string concatenation but also why that's a bad idea and what the correct alternative is.

[si_the_geek]

Actually it would compile... but part of it would be displayed in green:

strQuery = "INSERT INTO PartNumbers (PartNum) VALUES (" '" & sPN & "')"

I do not understand why Visual Studio puts a space between the first double quote and the next single quote. Obviously there is a reason but no luck trying to find any information on the net about that.

That is because the ' character is used to indicate a comment (a note for yourself, which VB ignores). The space (and colour) are being added to alert you to it not being code.

The cause of it being a comment (rather than text) is because you ended the string just before. If you remove the extra double-quote, it might work:

strQuery = "INSERT INTO PartNumbers (PartNum) VALUES ('" & sPN & "')"

I say might work, because there are lots of potential problems with using string concatenation for this kind of thing - and the suggestion of parameters is the best way (and easiest) to avoid them all.

If the data you are concatenating (in this case sPN) comes from a user (or anything you didn't type), you should consider parameters to be essential.

[oprache]

Thank you both for the quick response.
I apologize for not displaying the code right: I confess I do not yet know how to do that.
The blog is fantastic, thanks for the link and I could not agree more with using parameters.
That will save me for figuring out why the & is required (those famous rules I was asking about).
I got my sql statement to work once I figured out the ' comes before the " and the & (and reverse after the text parameter) but I will switch to the parameters right away.
The code I listed does compile and it is only when using it from a form that the exception occurs, but that is soon to be history.
Thank you again
Regards,
Olivier