|
I am trying to get the questionID from two different Select statements and
then insert into a table one by one.
DECLARE @NewID INT
This one is inserting a new surveyID, Output is SurveyID
insert into survey(title,description, surveystatus,CreatedBy,date )
values('New Survey','New Survey',1,'test',Getdate())
SELECT @NewID = SCOPE_IDENTITY()
Copying the questions with the new surveyID
INSERT SurveyQuestions(surveyid, questions,answertype)
SELECT @NewID ,questions,answertype
FROM SurveyQuestions
WHERE surveyid='81'
The problem is below here. I want to get the value of the questionId of the
first select statement then the value of the questionID of the second select
statement and insert into the table SurveyChoices one by one.
Both the select statements can have 1 or 2 or 3 rows or more but the both the
select statements will have exact number of rows.
select QuestionId from surveyquestions where surveyid=@NewID and answertype
<> 'T'
select QuestionId from surveyquestions where surveyid='81' and answertype <>
'T'
Here i am using the insert statement using the value form First select
statement and second select statement
INSERT Surveychoices(QuestionId,choice)
SELECT questionID,choice((This is the value of the First select statement.)
FROM Surveychoices
WHERE questionid=questionID(This is the value of the second select statement.)
|
|
|
|
|
Hi,
HI,
SELECT @vCount=('select COUNT(['+@v_COLUMN_NAME+']) from '+@DataTable)
print @vCount
This is my stored procedure part to find the count of a column specified.But here i m getting a conversion error
vCount is declared as int
seeism
|
|
|
|
|
Got the answer
create table #countlists (ICount int)
exec ('insert into #countlists(ICount) select COUNT(['+@v_COLUMN_NAME+']) from '+@DataTable+' where '+@v_COLUMN_NAME+' is not null')
set @vCount=(select ICount from #countlists)
drop table #countlists
|
|
|
|
|
How can I use transaction on multiple database? I use SQL Server Compact Edition.
Thanks...
|
|
|
|
|
suppose you will insert some data in a table from two other's table/data base.
Begin Tran
Use DataBase2
insert into Table1(Name,Id,Roll,Address)
select A.Name,A.Id,B.Roll,B.Address from DataBase1..Table2 A inner join DataBase2..Table3 B
on A.Id = B.Id
Commit tran
If You use this query then you will easily transaction (Insert) data from two different data base & tables.
Mrityunjoy Bhattacharjee
modified on Sunday, August 8, 2010 12:06 PM
|
|
|
|
|
Hi guys...
Actually i have a table called products which has 4 products in it say for example pepsi, miranda, coke and fanta. The table has two columns product id and productname as follows:
1 pepsi
2 miranda
3 coke
4 fanta
now i have another table called agent, which has 3 columns such as agentid, region, productid. Now each agentid has a region and productid assigned to it as follows:
1001 DXB 1
1001 AJM 4
1002 ABC 2
1003 XYZ 3
1003 DEF 4 and so on. now in my application i need to make use of a query which gives me a result of all the product name with status for any particular agent id, for example: for agentid 1001 the result should be
productname status
pepsi true
miranda false
coke false
fanta true
the query i tried is :
select a.productname, status = case b.product_id
when 1 then 'True' else 'False'
when 2 then 'True' else 'False'
when 3 then 'True' else 'False'
when 4 then 'True' else 'False' end
from products a, agent b where a.productid = b.productid and agentid ='1001'
but doest work... any help is appreciated...
|
|
|
|
|
select a.productname,
case when b.agentid is null then 'False' else 'True' end AS status
from products a
left outer join agent b on a.productid = b.productid
and b.agentid ='1001'
|
|
|
|
|
Thanx a million buddy.. works like a charm.... thanx alot...
|
|
|
|
|
I converted my 2008 / .NET 2.0 data service to VS2010 with the 4.0 framework.
I can do my normal test: http://localhost:4440/datareflector.svc and I get my wsdl screen.
If I click the link I get all of my WSDL as expected. So nothing is wrong there.
My data structure uses Linq to Sql to connect to he database and extract information.
The applications' service reference has all of the data in it correctly.
When I call the service I get a report that the service terminated.
When I attach my code to the service and enter debug the results totally floor me:
my code pattern is simple:
public things[] GetMyThings( Guid myIdentity )
{
using ( MyContext context = new MyContext( connectionString ) )
{
var mt = from t in context.ThingTable
where t.Identity == myIdentity
select t;
return mt.ToArray();
}
}*
When I walk through this simplistic code, I can confirm that mt ends up populated with the rows and values expected and goes through the return code just fine. At the end of the walk (the bracket marked with the *) when I hit F11 I get the exception
InvalidOperationException ' Attempting to access an object (context) that has been disposed. '
I cannot, for the life of me, figure this one out. About to wipe my machine and do a full reinstall just to see if that cleans up everything. This is an issue no one seems to have an answer for.
Has ANYONE experienced this kind of issue?
|
|
|
|
|
I need to create a secured visual basic 2010 Hotel management system, i need guidelines!
|
|
|
|
|
guidelines...guidelines...hmmm. let me think.
How about only allowing the same number of occupants as the number of sleeping places in the room. Stops those pesky kids allowing their friends to kip on the floor.
Or, how about prompting the user to offer the breakfast package when someone books a room.
No, wait - those aremore suggestions for functionality than guidelines.
Wait, ive got it. Don't use VB, its a horrible blemish on the arse of programming languages. No, still not guidelines, more personal opinion.
What sort of guidelines do you want?
|
|
|
|
|
What do you have against VB.NET? Do you like C#?
|
|
|
|
|
there is always a love hate relationship of VB.net on this forum
As barmey as a sack of badgers
|
|
|
|
|
I think it's more of a hate-hate relationship.
|
|
|
|
|
It was a bad joke. There is of course very little difference between the two.
|
|
|
|
|
oh dont let viaducting hear you say that he'll burn you at the stake 
As barmey as a sack of badgers
|
|
|
|
|
firstly
get the requirements of the application. decide the ways that it is to be used! desktop application or web
As barmey as a sack of badgers
|
|
|
|
|
prog_mafia wrote: i need guidelines
Maybe the ones on top of the forum would be good to begin with.
|
|
|
|
|
Guidelines: Never accept a job you can't handle youself.
Tell your client, you can't do it and step back to a job, that you are more capable of.
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
You need this[^].
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Hello,
i am at the beginning of learning SQL, and i cannot find the solution to my problem:
I have got two tables, WORKCENTER and CALENDAR. WORKCENTER contains columns ID and NR_OF_DAYS, CALENDAR Contains columns DAY and IS_WORKING_DAY.
DAY contains the numeric value of a day (for example 14824 for today, 2010-08-03), and IS_WORKING_DAY is CHAR Type and indicates with 'y/n' if it is a working day or not.
I want for each Work Center to get the day from today + NR_OF_DAYS from table CALENDAR, excluding weekends and holidays.
The result should be like this:
ID MAX_CALENDAR_DAY
1 14833
2 14840
...
Explanation:
Work Center 1 has NR_OF_DAYS = 7, today is 14824, + 7 = 14831, but there is a weekend in between, so + 2 = 14833.
Work Center 2 has NR_OF_DAYS = 12, today is 14824, + 12 = 14836, but there are two weekends in between, so + 4 = 14840.
This is my sql query (Oracle):
SELECT WORKCENTER.ID AS WORKCENTER_ID, MAX_CALENDAR_DAY
FROM WORKCENTER,
(SELECT MAX(DAY) AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
WHERE CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY
)
WHERE ROWNUM <= WORKCENTER.NR_OF_DAYS
)
WHERE WORKCENTER.ID IN ( 1, 2, 6, 7, 9)
Oracle SQL Developer shows an error, WORKCENTER.NR_OF_DAYS is unknown. This is obvious, if inner queries are handled first (is this correct?).
Could you tell me how i have to build this query?
I hope you see what i am trying to do...
Greetings
|
|
|
|
|
In your first sub-select, you have MAX(DAY) as MAX_CALENDAR_DAY and you get that data from a further sub-select. Fine and good.
However, on your where clause, you reference, WORKCENTER.NR_OF_DAYS.
The WORKCENTER table was never referenced.
Tim
|
|
|
|
|
Start with a query telling you CALENDAR.DAY for every NR_OF_DAYS and limit that to the maximum number found in the WORKCENTER table, maybe
SELECT ROWNUM as NR_OF_DAYS, DAY AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
AND CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY
)
WHERE ROWNUM <= (SELECT MAX(NR_OF_DAYS) FROM WORKCENTER WHERE WORKCENTER.ID IN ( 1, 2, 6, 7, 9))
and then INNER JOIN that to the WORKCENTER table ON NR_OF_DAYS.
|
|
|
|
|
This is my query now:
SELECT ID,
DAY
FROM (SELECT ROWNUM as CAL_ROWNUM,
DAY AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
AND CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY)
WHERE ROWNUM <= (SELECT MAX(NR_OF_DAYS)
FROM WORKCENTER
WHERE ID IN (1, 2, 6, 7, 9))
)
INNER JOIN WORKCENTER
ON NR_OF_DAYS = CAL_ROWNUM
WHERE ID IN (1, 2, 6, 7, 9)
And it works, yeah! Thanks guys.
Greetings
|
|
|
|
|
Hi all,
I'm running SQL CE on Wince 5.0, and am finding that about every 40 seconds or so that the whole system freezes then carries on. This happens at precisely the time when the timestamp on the .SDF file changes, so I am assuming that it is freezing when SQL is updating the database file. Is there any way to prevent this or specify when it happens as it's interfering with other operations on the board?
TIA!
Edit - Fixed: on-board Flash is far, far too slow.
modified on Tuesday, August 3, 2010 7:56 AM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
