Click here to Skip to main content
15,887,267 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hiii,

i have written query :===
SQL
INSERT INTO ContentSentences (ContentID,SentenceID,OrderNo) SELECT ContentID,SentenceID FROM Contents,Sentences WHERE ContentText LIKE '" + dt1.Rows[j]["SentenceText1"].ToString() + "' AND SentenceText LIKE '" + dt1.Rows[j]["SentenceText1"].ToString() + "',tb";


in which '" + dt1.Rows[j]["SentenceText1"].ToString() + "'is value come from datatable you can assume this value as some text.and i want to insert variable's value into table that name is order number.

for getting ordernumber i have write the code
C#
string tb = ((TextBox)GridView1.Rows[index].FindControl("txt1")).Text;


but my query is not working.
Posted
Updated 3-Mar-15 0:06am
v3
Comments
Suvendu Shekhar Giri 3-Mar-15 7:18am    
What is not working? Is it giving any error?

First of all, few notes:
1) If you want to use record set returnef by SELECT statement, you need to define relationship between Contents and Sentences tables.
SQL
SELECT c.ContentId, s.SentenceId
FROM Contents AS c INNER JOIN Sentences AS s ON c.PK = s.FK

where c.PK means Primary Key for Contents table and s.FK means Foreign Key form Sentences table. In other words, if there is no relationship between tables, the cartesian result set[^] is produced (CROOS JOIN).
For further information, please see: Visual Representation of SQL Joins[^]

2) The number of input and output fields must be equal!
SQL
INSERT INTO ContentSentences (ContentID,SentenceID,OrderNo)
SELECT ContentID, SentenceID, OrderNo
FROM ...


3) The data type must be the same!
If data type of ContentId and SentenceID is numeric, then the values returned by SELECT statement must be the same data type!

4) you can't build queries this way!
The proper way is to use command together with paramaters. Build the query this way:
SQL:
C#
string qry = string.Concat("INSERT INTO ContentSentences (ContentID, SentenceID, OrderNo) ",
       "SELECT ContentID, SentenceID, ", tb , " AS OrderNo ",
       "FROM Contents ... Sentences ON ...",
       "WHERE ContentText LIKE '@Param1' AND SentenceText LIKE '@Param2'");


OleDb:
C#
string qry = string.Concat("INSERT INTO ContentSentences (ContentID, SentenceID, OrderNo) ",
       "SELECT ContentID, SentenceID, ", tb , " AS OrderNo ",
       "FROM Contents ... Sentences ON ...",
       "WHERE ContentText LIKE '?' AND SentenceText LIKE '?'");


Because you did not provide enough information about your database provider, i can't show you sample code.
SqlCommand.Parameters Property[^]
OleDbCommand.Parameters Property[^]
 
Share this answer
 
Comments
vatsaldesai 3-Mar-15 23:47pm    
table: contents(contentid ident,contenttext nvarchar(500))
sentences(sentenceid ident,sentencetext nvarchar(500))
contentsentences(contentid int(pK),sentence id int(pk),orderno int)
contentsentences table has relationship with pk_fk with content table & same as sentence table.
vatsaldesai 3-Mar-15 23:49pm    
i didn't get this Line :"FROM Contents ... Sentences ON ...", can you please try to describe it.
Maciej Los 4-Mar-15 1:43am    
I've done it in a 1. point.
vatsaldesai 4-Mar-15 1:46am    
means....???
Maciej Los 4-Mar-15 1:47am    
Have a look at 1. note!
Try this my friend:
SQL
INSERT INTO ContentSentences (ContentID,SentenceID,OrderNo) 
SELECT ContentID,SentenceID,"+tb+" FROM Contents,Sentences WHERE ContentText LIKE '" + dt1.Rows[j]["SentenceText1"].ToString() + "' AND SentenceText LIKE '" + dt1.Rows[j]["SentenceText1"].ToString() + "'";
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900