|
Hi All
New to .Net even newer to SQL.
I need to count a column in a table and add up number of times a value appears.
e.g.
Cat
Cat
Cat
Dog
Dog
Cow
Cat = 3
Dog = 2
Cow = 1
I am writing in VB.Net a reporting tool to query the DB. With this query I want to list each unique value (Cat,Dog,Cow) in a ListView with the corresponding value next to it.
I hope this makes sense....
Thanks
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
You can use GROUP BY to group your result based on the animal, and use COUNT to get the number of animals of the same type, e.g.:
SELECT animal, COUNT(*)<br />
FROM animaltable<br />
GROUP BY animal
It will return something like:
Cat 1
Dog 2
Cow 3
Edbert
Sydney, Australia
"A day without sunshine is like, you know, night."
|
|
|
|
|
Try this
suppose table name is 'table' and column name is 'animal'
select animal,count(animal) from table group by animal
hope yours problem will be solved.
|
|
|
|
|
I need to run a SQL statement in a C# Windows Forms program using SQLDataFillAdapter. I am binding a SQL statement to a DataGrid. I would like to give the user the ability to click a button to kill the SQL statement if it runs to long. I can't figure out how to do it.
If I run the SQL in a separate Thread, the SQL Thread never blocks (i.e. never returns control back to the Main Thread) in order to allow a button click. So this doesn't seem to be the solution.
Has anyone accomplished what I am trying to do or have any advice?
Thanks,
Mark Mokris
|
|
|
|
|
Hi Everybody,
I have read that ADO.net uses disconnected architecture.i.e it gets disconnected from database and gets connected only while updating and retrieving data. If it is true then y do we require to close connection using cn.close.
2) and what is the difference between ASP.net with VB.net and ASP.net with c#. Why is the later one so popular.
Thanking you and waiting for some kind replies.
Bye
Amit
|
|
|
|
|
Amit R wrote: I have read that ADO.net uses disconnected architecture.i.e it gets disconnected from database and gets connected only while updating and retrieving data. If it is true then y do we require to close connection using cn.close.
You don't require to, but it is good practice in my opinion.
Amit R wrote: and what is the difference between ASP.net with VB.net and ASP.net with c#. Why is the later one so popular.
Your second question is off topic for this forum, but anyway.... ASP.NET has no differences between C# and VB.NET. It is the same framework regardless of language. You could write it in Fujitsu COBOL (as it now targets the .NET Framework) if you really wanted to. The only differences are in the languages themselves and on the whole those are relatively minor.
As to why C# appears to be more popular? I can't say I'd noticed. They seem to be fairly evenly matched to me.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
doubt no 1:it is better to close the database after u can accomplish the work,otherwise it leads to some minor errors.
doubt no 2:vb.net is only for windows applications,example to be programmed for LAN or intranet applications only,whereas asp.net is the web application used to create the best features of internet applications.
and web related tasks.
asp.net having powerful features of web services,validation controls,xml etc..
ASP.NET in C# will having better future because c# is the copy of core java,so c# coding supports platform independent.so better to practice asp.net(c#) in SDK environment rather than IDE environment.
m.s.senthil kumar
|
|
|
|
|
after installing MSDE 2000 and Microsoft SQL Web Data Administrator.
i login into Microsoft SQL Web Data Administrator using Windows Integrated option and created an account for sql login.
when i tried to login using newly created account i got the following error.
Invalid username and/or password, you are using a windows login that is not your own, or server does not exist.
what could be the problem?
my os is Windows XP SP2.
|
|
|
|
|
i have aproblem using LIKE and % with stored procedure.
row:
022533228 ahmed shabana
stored procedure:
CREATE PROCEDURE sel_cust_servtel
(
@servtel char(9)
)
AS
select *
from cust
where serv_tele LIKE @servtel
Query:
exec sel_cust_servtel '%2533228' ------> return nothing
exec sel_cust_servtel '%22533228' ------> return the row
that mean it work only for one character which is not supposed to do.
|
|
|
|
|
Because a char type will space pad the value if it is less than the number of characters specified. So your first query will actually be looking for:
'%2533228 '
The solution is to use a varchar.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
how could i connect my apllication web site i mean with my database engine. I am using SQL Server 2005.i was using express edition and then change it to enterprise edition.it doest connect with my apllication.
|
|
|
|
|
Change the connection string so it points to the new SQL Server.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
sqlConnection1.Open();
listBox1.Items.Clear();
SqlCommand loader = new SqlCommand("SELECT name from employee;", sqlConnection1);
SqlDataReader read = loader.ExecuteReader();
while (read.Read())
{
listBox1.Items.Add(read[0].ToString().Trim());
}
sqlConnection1.Close();
OR This:
sqlConnection1.Open();
listBox1.Items.Clear();
SqlDataAdapter loader = new SqlDataAdapter("SELECT name from employee;", sqlConnection1);
DataSet dataset_for_emp_names = new DataSet();
loader.Fill(dataset_for_emp_names);
for (int i = 0; i < dataset_for_emp_names.Tables[0].Rows.Count; i++)
{
listBox1.Items.Add(dataset_for_emp_names.Tables[0].Rows[i][0].ToString());
}
I know the fastest way to get data from the SQL Server is by a Query but which one is fastest in c# for retriving data the (SQLCommand with SqlDataReader or SQLDataAdapter.Fill())
thanks;)
|
|
|
|
|
Well, it's quite obviously the 1st one.
I wonder how it didn't occurs to you that using an object which will read all employee to fill a complex data structure, then take the first element of this structure is obviously slower than just reading the 1st element, mhh?
|
|
|
|
|
cbhkenshin wrote: I know the fastest way to get data from the SQL Server is by a Query
Ummmm.... Surely that is the only way? Either that, or we have a different idea of what a query is.
cbhkenshin wrote: which one is fastest in c# for retriving data
Strictly speaking, and this is taking pedantry to the extreme, it would take the same amount of time to retrieve the data either way, because the time to retrieve the data is a function of SQL Server and not C#. However, as you are refering to the time it takes for the C# application to accept the data it is receiving then I agree Super Lloyd that it would be the first option.
cbhkenshin wrote: and more apprpriate
Now, you mentioned this in the subject, but not the body. Which is more appropriate depends on your situation. You can take advantage of data binding with the latter method. With the former method you have to do more work yourself.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
hi
i have three tables named A,B,C
having common field srno.
A b c
---- ------- ----
srno srno srno
---- ------- ------
101 101 102
102 105
103
104
105
i wrote a query like : (A.srno) - (B.srno) - (C.srno)
and my ans is :103 and 104
but in a critical situation 102 is again inserted in A.
not assign to B or C.
but didn't display in my query.
plz. help me.
|
|
|
|
|
I don't understand what you are trying to achieve. Could you explain? Also, what is your current SELECT statement?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
sorry, i couldn't explain properly.
i am trying to explain by example :
i have two tables A and B having common field SRNO.
in table A i have values 101,101,101,102,102,103.
in table B i have values 101,102.
i want to write a query from A nad B, which provide me the output : 101,101,102,103.
means :
101,101,101 in A - 101 in B = 101,101 in Output
102,102 in A - 102 in B = 102 in output
103 in A - null = 103 in output
actually i wrote like :
select srno from A where srno not in (select srno from B)
but it eliminate all the instances of srno.
the output is only :103.
but i want as defined above.
could i tried well to make you understand my problem?
thanks![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
-- modified at 5:21 Saturday 11th February, 2006
|
|
|
|
|
hi :
I have a table for transaction that i use for entering each action that has been done in the system as requested from the user, so basically this table will be big in rows and i have about 5 types of transactions so what should i do:
1-split the table into 5 tables each hold only one type of transaction.
2-just make it all in one table as before.
Which is better???
1:for the first option the number of rows will be divided by fifth and the insert and delete time will be faster but the select time will be slower.
2:but the second option will give fast select and slow insert or delete and also a very large number of rows.
please help and thanks for your time and cooperation.
cbhkenshin
|
|
|
|
|
cbhkenshin wrote: but the second option will give fast select and slow insert or delete and also a very large number of rows.
This isn't necessarily correct. It depends on how you are inserting the rows. For instance if they key is sequential (like an IDENTITY or a timestamp) then new rows will be appended to the the end of the table. If the key is on something more random then the rows will be potentially inserted in the middle and other rows around it will be shuffled around. But it will likely only affect one additional data page.
As for deleting, I'd guess it would still be the same speed because the row is only marked as being deleted. It isn't actually removed at the time of delete.
Have you tested your performance hypotheses? Do you know for certain that option 1 has a faster insert than option 2, and vice versa for select?
Personally (and this is without knowing the full story), I'd go for option 2 and then refactor to option 1 if it becomes a problem. Also, you didn't say which is more important, the ability to insert lots of data, or the ability to select lots of data? Which do you do more of? (That will have an influence on your final answer)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi Frenz!
I have a table with 10 Duplicate rows in a SQL Server Database. In that table there is no unique identification column name (eg: SlNo, RowNo... like that) means all rows having a common values and that table does not have any constraints. So I want remove first 9 duplicate rows from that table. How?
Kindly give the suggestions to solve this problem. coz I am learner in making Querys in SQL Server 2000.
wish me all the best.
-- modified at 23:52 Friday 10th February, 2006
|
|
|
|
|
You could try a SELECT with a GROUP BY clause containg every column name in your table, and the output columns having an additional COUNT(*)
For example:
SELECT Column1, Column2, Column3, COUNT(*)
FROM MyTable
GROUP BY Column1, Column2, Column3
ORDER BY DESC COUNT(*)
This will float all the duplicates to the top of the result set. If you have no primary key or unique constraint on any of the columns then you have no way to reliably delete only the duplicates automatically. You will need to take note of the duplicate rows and delete them manually. (Actually, I think there might be, but if you only have 9 duplicates, it would be faster to just do it manually)
I'm curious as to why you don't have a primary key on the table? I've never come across a situation where I'd leave out a primary key.
Does there look like a good candidate for a primary key? It might be a good idea to create one.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi all (again!)
I am new to sql database design, and am in the process of learning it all really quick....Our old POS program was written in a DOS 4G dbms, which is completely different from what I am learning. I have a quesion about database design. I opted to start with a timesheet program that I wrote originally in this other stated ide. I am looking to crate a database for a timesheet. I have thought about this for a while and came up with a few idea's, but would like some input, being new to this and all. I need to store values of the time clocked in and clocked out, in weekly intervals.
Here it goes:
Employee Info Table
Includes employee number, name, password, security level
and then a table for each day of the week,
or a table with a timein and timeout for each week, with of course the employee id,
or create a table at the time the employee first signs in with a timein and time out for each day of the week...each day also needs to be tagged with if the hours are regular work hours, vacation, jury duty, holiday, sick, etc.
Thats basiclly the info I need stored. There is no need for an 'approved by' column, or things of that sort. A pretty basic program that gets the job done.
Any advice/help is greatly appreciated!
Thanx in advance!
|
|
|
|
|
Database design is not an easy topic to just jump into. I could attempt to provide a schema for what you are asking, but it would be a temporary fix at best. Better for you to learn to "fish" as the saying goes. In general, the goal of a database design is to prevent data redundancy and to organize your data into a logical fashion. Much beyond that would require far more than a simple forum post. I would suggest finding yourself a good database design book (I'm afraid I can't suggest one, perhaps another poster will be better able to) and perhaps taking advantage of Google to see what you can see. About.Com Database Design has quite a few interesting looking guides.
After leading off with the above, for your specific problem I would say you're on the right track, an employee table tracking employee specific information. For your timesheet tracking, most decisions would require a more in depth knowledge of the domain (which you have), but to hazard a guess I would say something along the lines of a Timesheet table with EmployeeId, Date, ClockInTime, ClockOutTime, and WorkType. Of course, this all depends on how picky you are about data constraints, but based on what you've said it is what I would suggest. While the idea of separate tables for each day may seem more prudent, you need to think in terms of the data you will have to retrieve. Something as simple as retrieving an employee's total hours for the year would be costly, involving joining eight different tables.
I hope I came across at least reasonably clear, and I wish you the best of luck in learning database design.
|
|
|
|
|
Thanx for the input. Here is the solution I came up with.
Table 1 - Employee Info
Table 2 - Timecard - Employee ID, Time in, Time Out, Date, Work Type
Table 3 - Table Name - Employee ID, Timecard Table name
and for the programming solution, create a table everytime an employee signs in for the first time, and give that table a unique name, store that name in the table name table, along with the Employee ID.
Does that sound feasable?
Thanx again!
Common sense...the least common of the senses used
Jude
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
