[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert failed in sql

Posted on 2009-12-18
8
Medium Priority
?
202 Views
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,
 DAY(PBS.slice_date)Rcount,
 ( DATENAME(month,'2/7/2008') + ',' + CONVERT( VARCHAR(20),DATEPART(yyyy,'2/7/2008')))Month_Name,
 ( SELECT NAME FROM NIKU.PRJ_OBS_UNITS WHERE ID=( '5001078' ) )DEPARTMENT_NAME
 FROM Niku.PrTimesheet PTS, Niku.PrTimeentry PE

How can I achieve this?
0
Comment
Question by:johny_bravo1
8 Comments
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 26081141
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.
0
 
LVL 33

Expert Comment

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

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

Author Comment

by:johny_bravo1
ID: 26081982
joriszwaenepoel:
 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 & "',)"
        Try
            DAL.SqlHelper.ExecuteNonQuery(str)

        Catch ex As Exception
End Try

tgerbert:
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).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Accepted Solution

by:
joriszwaenepoel earned 2000 total points
ID: 26082029
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.
0
 
LVL 10

Assisted Solution

by:joriszwaenepoel
joriszwaenepoel earned 2000 total points
ID: 26082058
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, ...


0
 
LVL 7

Expert Comment

by:aplusexpert
ID: 26086025
Hi

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

Thanks.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 26086871
I am trying my hands on ur suggestions.will update you soon
0
 
LVL 8

Author Closing Comment

by:johny_bravo1
ID: 31667772
Thanks for the valuable sharing of knowledge.
It works fine :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
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 …
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

872 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