SQL Count function inside an insert command
I want to set the value of one of the fields with a number that is derived from a count function. But How do I do that?
StringBuilder insertCommand = new StringBuilder();
insertCommand.Append("INSERT INTO Threads(UsersID,TopicsID,Date,ThreadTitle,ThreadParagraph,ThreadClosed,Views,Repli开发者_StackOverflow中文版es,PageNumber)");
insertCommand.Append("VALUES(@uniqueIdentifier,@TopicsID,GETDATE(),@questionTitle,@questionParagraph,0,0,0,@pageNumber)");
To set the page number parameter i want to do something like this:
sqlCommand.Parameters.Add("@subTopic", SqlDbType.Int);
sqlCommand.Parameters["@subTopic"].Value = "Count(ThreadID)/20";
I want to input a number that is divisible by 20 into PageNumber field in table Threads. Note: The number should be a whole number.. so instead of returning 10/20=0.5, it should return 0.
You can use a query insert, like this:
INSERT INTO Threads (UsersID,TopicsID,Date,ThreadTitle,ThreadParagraph,
ThreadClosed,Views,Replies,PageNumber)
SELECT @uniqueIdentifier,@TopicsID,GETDATE(),@questionTitle,
@questionParagraph,0,0,0,FLOOR(Count(ThreadID)/20)
FROM table
Assuming the subtopic
parameter name was a typo and you actually meant pagenumber
. You never know though!
精彩评论