|
I have 4 databases named: db1, db2, db3, db4 on the same SQL Server. All 4 databases have the same table Department(ID, Name) but the data is different. For example:
Table ID Name
db1.Department 1 Dept1
db2.Department 1 Dept2
db3.Department 1 Dept3
db4.Department 1 Dept4
I use SQL Query analyzer to execute the code fragment below:
declare @n int
declare @Name nvarchar(256)
set @n = 1
while (@n < 5)
begin
if (@n = 1) use db1
else if (@n = 2) use db2
else if (@n = 3) use db3
else if (@n = 4) use db4
select [Name] from Department where [ID] = 1
set @Name = [Name] from dbo.Department where [ID] = 1
print @Name
set @n = @n + 1
end
The results:
---------------------------------
Dept1
Dept4
---------------------------------
Dept2
Dept4
---------------------------------
Dept3
Dept4
---------------------------------
Dept4
Dept4
But I think the correct result should be
---------------------------------
Dept1
Dept1
---------------------------------
Dept2
Dept2
---------------------------------
Dept3
Dept3
---------------------------------
Dept4
Dept4
The question is: Is there anything wrong from me or from SQL???
|
|
|
|
|
It sounds like your first conditional is falling through to the last case.
Have you tried running it with conditions you know will always be false?
It looks like it should work....
|
|
|
|
|
Below is my query. When I do a count it is counted all location,I want to be have a count after group by executes. How do I do this?
SELECT a.Number, a.Location,count(a.Location)'Number'
FROM Reports_Location
GROUP BY a.Number, a.Location
|
|
|
|
|
Not sure of the meaning. But if you are looking for a count of locations you could try this
select count(*) from (
select unique location from reports_location
)
;
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
Hi all,
I'm a .NET newbie, and just kind of a programming/developing wannabe. I'm moving into C# from a (tiny) background in JavaScript only. I administer a club website in ASP and want to switch to ASP.NET. Here's my conundrum:
I have a membership database (Access) that includes a field for when a record was last updated (UpdDt), and who updated it (UpdLogin). The value in UpdLogin is a member's ID number, correlating to the MbrId field.
I want to show administrators a list of all members that were updated recently, and who updated them. I need to not just show the UpdLogin value, but the actual name of the person who did the update. So I need to (for each record) find the name, within the table I'm already using as the DataSource, and show that as "Updated By".
In ASP, I created 2 recordsets and looped through the first. For every record, I used:
rs2.MoveFirst<br />
rs2.Find("MbrId = "+updLogin)
then (making sure the record existed) grabbed the name.
I think it was inelegant, and probably inefficient, but it worked.
SO! How the heck do I do this in .NET? I'm still working on wrapping my head around DataGrids and DataSets and DataAdapters and DataViews and OhMyGoodnessDoesItEverEnd...
Have I missed some simple SQL solution to this? Do I require a couple of tables in a DataSet, and use some method similar to what I did in ASP?
Please, help a poor newbie in need! 
Best Regards,
Kevin Swanson
http://bcmc.ca[^]
|
|
|
|
|
Why don´t you get all the data in a snigle query ?
Free your mind...
|
|
|
|
|
Sheesh. Query. Thanks, Guillermo.
I went into Access, and designed a query. Of course, I didn't realize you could put more than one copy of the same table into the query builder!
Came out with a SQL statement like this:
SELECT Mbr.MbrId, Mbr.LastName, Mbr.FirstName, Mbr.UpdDt, Mbr_1.LastName, Mbr_1.FirstName<br />
FROM Mbr INNER JOIN Mbr AS Mbr_1 ON Mbr.UpdLogin = Mbr_1.MbrId<br />
ORDER BY Mbr.UpdDt DESC;
Well, yeah!
Very embarassed now. Going to quietly read some more books, and experiment...
Thanks again!
Kevin Swanson
|
|
|
|
|
Hello, all database masters 
I'm currently writing an application in Visual Basic.NET, and I would need to be able to update an ID_STATUS field for an Order table, using a combo box that would take it's value list from a Status table. I have been able to fix that in Access using the multi-columned comboboxes provided there, however I have no clue on how to do this in VB.NET. The Status table has only two columns, ID_STATUS (primary key, relates to "Order"'s ID_STATUS), and STATUS (which is the description for the specific ID). The IDs are numeric, that's how they are stored in the Status and Order tables, and I would like a use to be able to choose the Status from the combo box using the status' Text.
Would anyone have a clue on how to do this?
Matt.
|
|
|
|
|
Hi
I am using VB.net/ASP.NET and SQL Server 2000 for a web application.
I have AccountNo as identity column. I use some dummy records for testing. But, every time I do a Delete from table and Insert Dummy rows, the identity Number of AccountNo starts for (Max(AccountNo)+1) of previously inserted rows. This throws of the AccountNo associated with my Dummy rows and actual table table rows.
How can I reset the IDENTITY to start from 1 without re-creating the table?
Please advice. Thanks in advance.
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
Do a "Truncate Table MyTableName". That will delete all of the records from the table, and resets the seed for the identity column. Note that this will only work if you don't have any tables with foreign keys pointing at your table (you would have to drop the foreign key contraints before doing the truncate).
Hope this helps.
Andy
|
|
|
|
|
Thanks Andy...
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
I'm trying to get a view that contains a sub-query to work in a special way. Right now I'm getting the error message
ORA-01427: single-row subquery returns more than one row when I query the view.
Essentially what I want the subquery to return, is a single string of comma separated values of the multiple rows that exist. Is this possible?
So a table with 3 rows in it and the column that I'm querying has as data
ROWA
ROWB
ROWC
would return the string "ROWA,ROWB,ROWC".
Thanks.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
After some thought, I realized that what I needed was a 'grouping function'. So I wrote my own function that returns a single string of the comma separated data. Works like a charm.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
I have the following query where table1 located on Server1 and rest tables located on different server2.
How do I connect to server2 in order to perform this query?
INSERT Table1
select t.id,sum(p.paymenttotal)
from transactions t,payments p
where t.transactionnum=p.transactionnum
group by t.id
|
|
|
|
|
You have to use the full name of the object if you have objects on different servers. e.g.:
Server1.DatabaseName.dbo.Table1
or
Server2.DatabaseName.dbo.transactions
or
Server2.DatabaseName.dbo.payments
dbo=Database Owner
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I have a query that populates a datagrid. My question is how can i take the results in the datagrid and use them as parameters in an insert query. Essentially I am taking data out of one table and putting it back into another one.
|
|
|
|
|
Try to bind a DataSet into your DataGrid, therefore you can add/update/delete items in your DataSet through the DataGrid.
When you have finished, use a different SQLDataAdapter to insert/update another table.
Have a look at the example below:
ADO.NET: Update a Database from a DataSet
http://samples.gotdotnet.com/quickstart/aspplus/default.aspx?url=%2fquickstart%2fhowto%2fdoc%2fadoplus%2fupdatedatafromdb.aspx
|
|
|
|
|
 I'm using this code to execute an stored procedure:
VARIANT vRecords;
_RecordsetPtr m_pRecordsetPtr;
irecordsaffected=0;
try{
vRecords.vt=VT_I4;
m_pRecordsetPtr=pQDef->Execute(&vRecords, NULL, adCmdText);
irecordsaffected= vRecords.iVal;
}
when I do it for "Insert" statements, the vRecords variable gets the correct value, but I get always 0 if I try an "Update" statement, any idea?.
thanks in advance.
|
|
|
|
|
The problem was that I was using allways the same _CommandPtr (pQDef), and never delete the Params used, this is why I got always 0 records when Update
and 1 record on the first Insert.
now works fine.
David.
|
|
|
|
|
Hi, Currently i need to display information from 1 table. This is the information of the table which contain 3 fields
Logdate ProjCode Hrs
2003-09-11 SBS 3
2003-09-11 asd 9
2003-09-11 asd 9
2003-09-12 dsa 6
2003-09-13 zxc 5
2003-09-13 cxz 2
When i add a new field using
Quey: Select *, (select Sum(Hrs) from Table a where a.Logdate = b.Logdate Group by Logdate)from Table b
Result:
Logdate ProjCode Hrs Sum
2003-09-11 SBS 3 22
2003-09-11 asd 9 22
2003-09-11 asd 9 22
2003-09-12 dsa 6 6
2003-09-13 zxc 5 7
2003-09-13 cxz 2 7
how do i edit my query statement where i can get
Result:
Logdate ProjCode Hrs Sum
2003-09-11 SBS 3 22
2003-09-11 asd 9 null
2003-09-11 asd 9 null
2003-09-12 dsa 6 6
2003-09-13 zxc 5 7
2003-09-13 cxz 2 null
as only display 1 sum in 1 logdate where the rest is null.
|
|
|
|
|
create procedure RepProjHours<br />
as begin<br />
set nocount on<br />
<br />
--Create temporary table with line-number.<br />
create table #temp1 (<br />
LineNo integer identity not null,<br />
LogDate datetime null,<br />
ProjCode varchar(3) null,<br />
Hrs decimal(6,2) null<br />
)<br />
insert into #temp1<br />
select LogDate, ProjCode, Hrs<br />
from MyTimesheetTable<br />
order by LogDate, ProjCode, Hrs desc --Nice ordering?<br />
<br />
--Find the first timesheet entry for each day.<br />
create table #temp2 (<br />
LogDate datetime null,<br />
MinLineNo integer not null,<br />
TotalHrs decimal(6,2) not null<br />
)<br />
insert into #temp2<br />
select LogDate, Min(LineNo), Sum(Hrs)<br />
from #temp1<br />
group by LogDate<br />
<br />
--Join everything together (with subtotal on first line for each date).<br />
select T1.LogDate, T1.ProjCode, T1.Hrs, T2.TotalHrs<br />
from #temp1 T1<br />
left outer join #temp2 T2<br />
on T2.LogDate = T1.LogDate<br />
and T2.MinLineNo = T1.LineNo<br />
order by T1.LineNo<br />
end
Hope this helps. There are several other ways of achieving the same effect).
Andy
|
|
|
|
|
ALTER TABLE tt ALTER COLUMN [cc] [int] identity [(2, 1)] not null
and it failed with below error:
Incorrect syntax near the keyword 'IDENTITY'.
How can I make it work ?
Thanks
|
|
|
|
|
Remove the square brackets around the parentheses after 'IDENTITY'. Then it should work.
|
|
|
|
|
I Removed the square brackets around the parentheses after 'IDENTITY'.
ALTER TABLE tt ALTER COLUMN [cc] [int] identity (2, 1) not null
but it still failed with below error :
Incorrect syntax near the keyword 'IDENTITY'.
why?~
|
|
|
|
|
I have a query that fills a datagrid with a order number, order qty, and shipped qty. After I click a button to fill the grid I want the code to be able to highlight any rows where the order qty isn't equal to the shipped qty.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
