INSERT INTO and SELECT statement..pls help

Question

INSERT INTO and SELECT statement..pls help

ITSSQL

Newbie

Points: 2
More actions
July 12, 2006 at 4:38 am

#91140

Hi
I am developing a UserControl, I save this user control code to the database, which will be loaded dynamically to the Page.
In my user control code consist of following lines:
<asp:SqlDataSource ID="GetDataFromDatabase" runat="server" ConnectionString="<%$ ConnectionStrings:sConnectionString %>"
SelectCommand="SELECT field_defaultvalue FROM cp_productfields WHERE field_product_id='ProductID' AND field_type='DropDownList' AND field_Control_ID='ddVCAuflag'"
ProviderName="<%$ ConnectionStrings:sConnectionString.ProviderName %>"></asp:SqlDataSource>
At this moment, I am manually puting this data to data
base using "INSERT INTO" statement.
But I get following error:
Incorrect syntax near 'ProductID'.
This is because single quotation mark around ProductID (field_product_id='ProductID').
If I change this single quotation to double quotation (e.g. field_id="ProductID") then this works okay.
But in that scenario, SELECT statement fails while execution.
Can anyone please let me know how can I achieve both successfully?
Thanks in advance.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I'm guessing that the connection your UserControl uses and the connection you use to execute the INSERT INTO statement have different values for QUOTED_IDENTIFIER. Have a look at the SET QUOTED_IDENTIFIER topic in Books Online and see if that helps.

John

Ian Yates SSCoach Points: 19738 More actions · Answer 2

And preferably not rely on it being set one way or the other.

Quoted identifier being ON means that you can type "My Tricky Col Name" rather than using the standard [My Tricky Col Name].

When inserting strings that contain single quotes you'll need to escape the quotes by replacing the single quote character (') with two single quote characters ('' - not a single double quote character though!).

The better way is to use a stored proc and pass your string as a parameter - then you can have all sorts of characters in the string.

ITSSQL Newbie Points: 2 More actions · Answer 3

Thanks for replies. Ian your suggestion is absolutely right. I did try the same way and it worked out.

Ian Yates SSCoach Points: 19738 More actions · Answer 4

No problem - the stored proc parameter method is also much safer as it helps to avoid SQL injection attacks