|
(Scott,)
Thanks for the help. I didnt use the real field names when i posted becuase i wasnt at my development PC. However ive adapted the code and got it part working, as follows:
SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, <br />
FROM tbl_Subforums a<br />
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum<br />
GROUP BY a.forumTitle, a.lastPost, a.lastUser
However i need to include a count from a third table, i figured it should look something like:
SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, COUNT(c.threadTitle) AS threads<br />
FROM tbl_Subforums a<br />
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum<br />
INNER JOIN tbl_topics c ON b.subforum = c.subforum<br />
GROUP BY a.forumTitle, a.lastPost, a.lastUser
Unfortunately this doesnt work. Could you please take a look and let me know where i am going wrong. The fields above are all correctly named and the c = b relationships are also correct.
The error i get within access is regarding the Syntaxt, so it would appear my formatting is wrong.
Thanks for any help you can offer!
munklefish
|
|
|
|
|
That looks like it would work in SQL Server, but Access might require paranthesis like:
munklefish wrote: SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, COUNT(c.threadTitle) AS threads
FROM ((tbl_Subforums a
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum)
INNER JOIN tbl_topics c ON b.subforum = c.subforum)
GROUP BY a.forumTitle, a.lastPost, a.lastUser
Scott
|
|
|
|
|
Thanks Scott!
That did the trick!
Muchos gracias!
munklefish
|
|
|
|
|
Good day everyone,
Please help if you're interested in.
I've got an Access database containing fitness club member profile. I need to make a login session for my webpage. How can I validate user name and password with the ones in database?
I don't have Microsoft SQl Server, so can I do it in VS.Net 2005?
How should I start?
Thanks so much
eric
|
|
|
|
|
|
I am having difficulty setting up my listbox to point to a SQL table (technicians table). I created a dataset that contains the table, but am unable to find enough help sources to create the code to make it work. I have created a Windows form to update a database, which displays the data from a (workorders table). I would like to have one of the fields as a picklist of names to allow the user to change the existing name and re-assign it to another from the list. I know this is basic stuff, but I am new to VB.NET.
Thank you,
LWhite
|
|
|
|
|
How will you take values for an insert statement
To insert the table values some values from one table and some values from open xml this will carried in a Stored Procedure
Help Please
Thanx
-
|
|
|
|
|
I am developing asp.net1.1 application
I have hug number of records in DB( SQLServer ) & want to show in a datagrid
There are thousands of records which need to transfer to client & client can sort & search
What is the best way to handle such type of situation
I am also using Ajax , when I use Ajax to call a function it lock the page until the all data is transfer to client , that’s not good
Did I use xml file to send on client & from xml file show data to client
or if there is any good solution plz....
I will be really thankful
Irfan
|
|
|
|
|
|
Hi,
I have one dataset, that contain one table now how can i retrieve one column in that table can you send me all possible approachments
|
|
|
|
|
if u confirm only one table means
ds.dt[tablename].columnname
-
|
|
|
|
|
Do you really mean 'one column', or do you mean 'one value from a particular row'?
If you really mean 'one column', then:
DataColumn column = dataSet.Tables[string tableName].Columns[columnName]<br />
or<br />
DataColumn column = dataSet.Tables[int tableIndex].Columns[int columnIndex]
If, however, you want the value stored for a particular column in a particular row in a table, then you need to index both the row and the column to get it:
object value = dataSet.Tables[string tableName].Rows[int rowIndex][string columnName (,DataRowVersion rowVersion)]
for example (the first method returns the current value in the described row's value list):
public object GetValueInRow(<br />
DataSet ds, <br />
int tableIndex, <br />
int rowIndex, <br />
string columnName)<br />
{<br />
return GetValueInRow(ds, tableIndex, rowIndex, columnName, DataRowVersion.Current);<br />
}<br />
<br />
public object GetValueInRow(<br />
DataSet ds, <br />
int tableIndex, <br />
int rowIndex, <br />
string columnName, <br />
DataRowVersion rowVersion)<br />
{<br />
return ds.Tables[tableIndex].Rows[rowIndex][columnName, rowVersion]<br />
}
Good luck.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-- modified at 2:16 Monday 22nd May, 2006
|
|
|
|
|
Good day everyone,
Please help if you're interested in.
I need to make a login session for my webpage. How can I validate user name and password with the ones in database?
How should I start?
Thanks so much
eric
|
|
|
|
|
What type of database are you talking about?
|
|
|
|
|
eric_tran wrote: How should I start?
See this "How To" document on MSDN: How To Use Forms Authentication With SQL Server 2000[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Hi,
I have written a very simple application using VB6 and ADO that connects to a MySQL database. I want to be able to distribute the program to somebody who will not install DCOM (on windows XP), however I understand that DCOM is one of the file dependencies for MDAC to function.
At the moment the program references the 'Microsoft ActiveX Data Objects 2.8' library.
Here is the code I am using to connect to the database :
<code>Set gCnn = New ADODB.Connection
gCnn.ConnectionTimeout = 10
gCnn.CommandTimeout = 400
gCnn.CursorLocation = adUseClient
gCnn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
"user=" & txtUser & _
";password=" & txtPassword & _
";database=" & txtDatabase & _
";server=" & txtServer & _
";option=" & (1 + 2 + 8 + 32 + 2048 + 16384)</code>
To be more specific when the program is run on the machine without DCOM a message box is displayed with the message "-2147467259 Unspecified error".
I am curious to know if there is any way to make ADO work without first installing DCOM, or alternatively is there another method that I could use instead?
Thanks in advance for any help,
Mark
|
|
|
|
|
Hello community.
I have a problem and cannot solve it:
How can I measure the query execution time from my C# application?
I need to get a number or a time class to my app. from my database server, but
not measure the time when I got the informations from db but the internal time in db server. So I will display something like "Execution time: 0.0015 ms" in Sybase Interacive SQL.
I'm using Adatptive Server Anywhere v8 and v9
thanx lot
|
|
|
|
|
I have three tables
1) AssignProjectUser(ProjectID,UserID)
2) Task(ID,ProjectID)
3) AssingnTaskUser(UserID,TaskID)
I will create Trigers that after inserting a assignProjectUser
I will select all the task who have the ProjectID inserted in AssignProjectUser and insert list of AssignTaskUser that have the userID same assignProjectUser and TaskID that i have selected
Exemple:
suppose that I have
TASK:
1 1
2 1
3 2
when I insert a assignProjectUser (1,15)
the number of task for this Project is 1 and 2
so the trigers will add
(1,15)
(2,15)
I hope that you understand me
and really need help as soon
thanks
DADAX
|
|
|
|
|
CREATE TRIGGER trg_AssignProjectUser ON [dbo].[AssignProjectUser]
FOR INSERT
AS
INSERT INTO AssignTaskUser (UserID,TaskID)
SELECT inserted.userid,task.id
FROM task INNER JOIN inserted on task.projectid = inserted.projectid
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
|
OK, I think this must be possible, but my head is melting and running out my ears, any help appreciated. I have table structure like this:
Table: Statement
Columns: ID (PK)
Name
TypeCode
PeriodId
Table: Heading
Columns: ID (PK)
StatementID
Name
Table: Line
Columns: ID (PK)
HeaderId
Name
These three tables make up a hierarchal 'tree' of data for a statment (structure simplified for this example). Each statement has a period represented by an id, and there will be a number of statement types for each period. What i need to do is copy each statement for a period and all of it's children to a new period.
After creating the new statements in STATEMENT table (simple), I've been able to do this:
INSERT INTO Heading (STATEMENTID, NAME)
SELECT SNEW.ID,H.NAME
FROM HEADING H
INNER JOIN STATEMENT S
ON H.StatementID = S.ID
LEFT OUTER JOIN STATEMENT SNEW
ON S.TYPECODE = SNEW.TYPECODE
AND SNEW.PeriodID = @NewID
WHERE S.PeriodID = @PrevId
Which inserts all the heading children appropriately. I can't seem to get the join syntax correct though to go down and do the same for the lines, closest i've got is this:
SELECT HNew.ID,L.NAME,
FROM LINE L
INNER JOIN HEADING H
ON L.HEADINGID = H.ID
INNER JOIN STATEMENT S
ON H.StatementID = s.ID
LEFT OUTER JOIN STATEMENT SNEW
ON S.TYPECODE = SNEW.TYPECODE AND SNEW.PeriodID = @NewId
LEFT OUTER JOIN HEADING HNew
ON HNew.StatementID = SNEW.ID
WHERE F.PeriodId = @OldId
This returns too many records. I know my join has gone wrong somewhere, but i can't see it... 
any help would be appreciated.
|
|
|
|
|
What are some of the rows in the STATEMENT table?
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I spent some time on this today, and i don't think the kind of query i want is possible with the current table structure, but thanks for looking...
|
|
|
|
|
Hi all,
I need a sql statement that returns all my Items and Categories from a database. I was thinking of using an inner join statement
SELECT tblCategories.CategoryID, tblCategories.CategoryName, tblCategoryItems.ItemID, tblCategoryItems.Item
FROM tblCategories INNER JOIN
tblNews ON tblCategories.CategoryID = tblCategoryItems.CategoryID
But this returned the category more then once.
This is how my current database looks like.
SQL table tblCategories have fiels (CategoryID, CategoryName)
SQL table tblCategoryItems have fields (ItemID, CategoryID, Item, Price, SellDate, Supplier Approved)
tblCategoryItems.CategoryID = tblCategories.CategoryID
I want to bind these to a datalist, with repeatcolumns=2.
Also each Item has to go under the Category. I must not display the same category more then once. For say there is 5 items for the Frozen Food category, I must display the Frozen Food category, and under that the 5 items.
Need something like this:
Row 1, Column 1 "Frozen Food" with Items "Mince, Chicken"
Row 1, Column 2 "Vegatables" with Items "Carrots, Brocolli"
Row 2, Column 1 "Fruit" with Items "Apples, Peaches, Bananas"
Row 2, Column 2 "Dairy" with Items "Milk, Cheese"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
