insert failed in sql

Posted on 2009-12-18
Last Modified: 2013-11-07
Hi Experts,
I am trying to insert one line in my sql table.Actually the data that I want to insert is the problem.
The column datatype is ntext.
The data I want to insert in the column is a sql query.
Following is the data(the sql statement) is the actual data that i want to insert in the column.

SELECT DISTINCT PTS.PrResourceId As Resource_id,
(SR.First_Name+ ' ' + SR.Last_Name) AS Resource_Name,
  Niku.z_task_name(PBS.slice_date,PTS.PrResourceId) AS TASK_NAME,
(SELECT Last_Name + ', ' + First_Name From  cmn_sec_users Where ID=SR.MANAGER_Id)Resource_Manager,
 ( DATENAME(month,'2/7/2008') + ',' + CONVERT( VARCHAR(20),DATEPART(yyyy,'2/7/2008')))Month_Name,
 FROM Niku.PrTimesheet PTS, Niku.PrTimeentry PE

How can I achieve this?
Question by:johny_bravo1
    LVL 10

    Expert Comment

    From a .NET application?
    Create a SqlCommand with CommandText like

    INSERT INTO myTable (sqlText) values (@sqltext)

    @sqltext is a parameter
    So you need to add a parameter to your sqlCommand and put the sql-string that you want to save in the value-property of that parameter.

    Then execute the SqlCommand to add the row in de database.
    LVL 33

    Expert Comment

    by:Todd Gerbert
    I believe you can just escape the single quotation marks, by adding an extra single quote.

    string safeSqlQuery = sqlQuery.Replace("'", "''");
    Dim safeSqlQuery As String = sqlQuery.Replace("'", "''")
    LVL 8

    Author Comment

     I am using a insert statement like
    Dim str As String = "Insert into Posts (Title, Message, CategoryID, UserName) VALUES ('" & title & "','" & msg & "','" & catId & "','" & uName & "')"
            '        Dim stringAdd As String = "Insert into Posts (Title,Message, CategoryID, UserName) values ('" & title & "',)"

            Catch ex As Exception
    End Try

    Even if I escape the single quotation marks and the data to table as you say.
    Can I display the data as it is (I mean in the format that user was supplying).
    LVL 10

    Accepted Solution

    You really should consider the change to use a parameter. Then you no longer need to worry about quotes, keywords, etc that couls be in de data that you want to insert.  I'm pretty sure your SqlHelper class has some overloads that allow to add parameters.

    Dim str As String = "Insert into Posts (Title, Message, CategoryID, UserName) VALUES ('" & title & "','" & msg & "','" & catId & "','" & uName & "')"

    And: do NOT catch exceptions with an empty Catch-block.  This way you'll never know if an error occurs and what the error msg is.
    LVL 10

    Assisted Solution

    Sorry, I forgot to modify the SQL statement when I saw the empty catch block and starting commenting on that.

    Dim str As String = "Insert into Posts (Title, Message, CategoryID, UserName) VALUES ('" & title & "',@msg,'" & catId & "','" & uName & "')"

    DAL.SqlHelper.ExecuteNonQuery(str, new SqlParameter("@msg", msg))

    And, while your doing that, you might consider modifying the other values to use parameters as well.
    It's usually faster, it's more secure (avoids SQL injection), and you don't need to warry about doubling quotes, verifying decimal symbols (if you have a . as decimal symbol), date formats, ...

    LVL 7

    Expert Comment


    Before inserting your desire value/string in table first find and replace the " ' " with " '' " single quot with double single quot.

    LVL 8

    Author Comment

    I am trying my hands on ur suggestions.will update you soon
    LVL 8

    Author Closing Comment

    Thanks for the valuable sharing of knowledge.
    It works fine :)

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now