|
if you have a lot of distinct to put, it is because the sql query is not well written...
you have to set to correct restrictions in the where clause.
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
|
|
|
|
|
Toxcct,
I know that, just asking if there are much simpler and shorter way to do this. Thanks for reply.
denpsia
|
|
|
|
|
Hi i need a query that can manipulate i.e count records on hour basis from a table depending upon time_in field....
|
|
|
|
|
You are going to have to be more specific.
You can do a count with something like this:
SELECT COUNT(*) FROM MyTable
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
How do you write a query that horizontally merges records from different tables? For example, say I have Person, Phone, and Email tables where Phone and Email have foreign key relationships to the Person table, and store 0 to n email and phone records for each person record.
The result query should include the person's name, all email addresses and all phone numbers, and the email addresses and phone numbers should be listed "side by side". To illustrate in CSV format;
--------------------------------------------------------------
LName, FName, EmailAddress, PhoneNumber
Jones, Sue, sjones@nowhere.com, 123-555-0203
Jones, Sue, sj@somewhere.com, 098-555-2382
Jones, Sue, SueJones@AspenState.com, NULL
Smith, Joe, jsmith@nowhere.com, 123-555-0202
Smith, Joe, js@home.com, 098-555-2382
Smith, Joe, NULL, 230-555-3298
Smith, Joe, NULL, 234-555-0982
Washington, Peter, NULL, NULL
...
--------------------------------------------------------------
In this example Sue has more email records than phone records, so the third record in the result set is NULL for the PhoneNumber field. Similarly, Joe has more email records than phone records so rows 6 and 7 have NULL for the EmailAddress field. Finally Peter has no email or phone records so both PhoneNumber and EmailAddress are NULL.
Any ideas? Thanks
|
|
|
|
|
Hi djsdjsdjsdjs,
Have you tried using DataRelations in DataTable or Joining tables? Just a thought.
denpsia
-- modified at 19:33 Wednesday 21st December, 2005
|
|
|
|
|
While this data will go into an ADO.Net Dataset I'm mainly concerned in how to write the SQL query that gets the data into a single row set that I can use to fill a Dataset.
|
|
|
|
|
if you have this schema :
table1 : table2 :
------------- --------------
col1 <-----\ col1
col2 \ col2
\---- col_tab1_id
then you can "merge horizontally" the two tables like this :
SELECT t1.col1,
t1.col2,
t2.col1,
t2.col2
FROM table1 t1,
table2 t2
WHERE (t1.col1 = t2.col_tab1_id);
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
-- modified at 10:19 Thursday 22nd December, 2005
|
|
|
|
|
This query produces the Cartisian product of the Email and Phone tables and doesn't have any relation to the Person table. These kinds of joins produce every possible combination of rows from the joined tables, but I don't want every possible combination.
|
|
|
|
|
but you add the where clause that condition your query as needed !
i did not read the first message entierly.
ok, just change the query, adding this :
... were Table1.id = Table2.tab1_id;
(or something like this)... d'you follow me ?
[edit]
i modified my first answer... i think it is now as you want.
[/edit]
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
-- modified at 10:22 Thursday 22nd December, 2005
|
|
|
|
|
i've created a new database with 2 table in microsoft access 2003 (.mdb)
I've created a query too, embedded in the .mdb file.
Can i use directly this query in ADO.NET avoiding to write SQL comand in c# code?
|
|
|
|
|
Yes u can. You can refer to this query as the store procedure in C# and call at as you are calling a store procedure.
Akif
|
|
|
|
|
Does anyone know how to use just the time portion of a DateTime object when calling an SQL Query (using SQL Server). I have a field in the database that stores only the Time, and want to do a select based on this field, but it forces me to match the Date portion of the field or it will not work. So, as simple example, if wanted to get all records with time of 9:30am, would use a Command something like:
<br />
DateTime timeToGet = new DateTime(1900,1,1,9,30,0);<br />
SqlCommand command = new SqlCommand();<br />
command.CommandText = "Select * from MyTable where MyTime = @TimeParam";<br />
SqlParameter parameter = new SqlParameter();<br />
parameter.ParameterName = "TimeParam";<br />
parameter.SqlDbType = SqlDbType.DateTime;<br />
parameter.Value = timeToGet;<br />
command.Parameters.Add(parameter);<br />
This will work fine as long as Date portion of field in database = 1/1/1900. But, if date portion is something else then will not find record.
Maybe it would work if could pass it a Time-Only object (instead of DateTime object) but not sure if object like that exists, and if did, not sure parameter object could handle it.
If anyone has any ideas, please let me know.
Thanks,
BK
|
|
|
|
|
SELECT * FROM mytable WHERE CONVERT(CHAR(8), mytime, 8) = CONVERT(CHAR(8), @TimeParam, 8)
|
|
|
|
|
Hi..
I want to put a page in asp ( or if not possible I could also use dot.net) to my server that will report about instant number of
> open database connection object number
> open recordset object number
> connected user number (not by counting from global.asa)
> ram and cpu used by iis...
and any usefull data like above..
where to start
karanba
|
|
|
|
|
can i use truted connection with microsoft access database?
that is avoid to specified password and id value in the connection string?
can i use directive with .mdb?
i use c# language and ado.net
|
|
|
|
|
Ofcourse you can use that !
R A M
|
|
|
|
|
string strConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyNothing.MDB;Jet OLEDB:Database Password =MyNothing;"
|
|
|
|
|
sorry i mean connect to a password protected .mdb, but avoid to specified in the connection string the password used.
In SQL server i can do it but in .mdb access?
|
|
|
|
|
Sasuki,
Infact you have to populate the Password screen yourself in Access you can use this
string strConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyNothing.MDB;Jet OLEDB:Database Password =" & Text1.Text & ";"
OR
string strConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyNothing.MDB;Jet OLEDB:Database Password =" & InputBox$("Enter password") & ";"
R A M
|
|
|
|
|
I've recently completed a Pocket PC application in VB.NET that works with connections to a local SQL Server 2000 CE DB and a SQL Server 2000.
I'd like to look at doing the same thing using eMbedded Visual C++ 4.0 and OLE DB for a CE.NET device. While there are plenty of resources for SQL CE I can't find any for SQL Server 2000.
Does anyone know of a good sample for eVC++ that hits both SQL CE and SQL Server? No ActiveSync and no IIS for this solution thus RDA and Merge Repl are of no value.
Any input is much appreciated!
|
|
|
|
|
Can SQL Server views be constructed to take parameters?
Some assembly required.
Code-frog System Architects, Inc.
|
|
|
|
|
The short answer is no. But this link[^] says it much better. 
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
As far as I know you cant pass parameters to a view. As an alternative what you can do is create a user defined function in sql server (2000 and later) where you can pass parameters to the function,do what ever the process or selections you want and then return a table from the function.
Rohitha M.Hewa
Senior IT Executive
Naturub Group of Companies - Sri Lanka
|
|
|
|
|
I am using .NET C# to program a database program. I am modifying a program that already exists, and it creates an "SDF" file on my Pocket PC. Be warned, I am a very big newbie, and I've been trying to read up on this question, but it seems the more I read, the less I am understanding it all.   
I have tried to install and use Microsoft Server 2000 (with Server CE), but I keep getting error messages 1069 and no matter waht I do, it doesn't seem to help.
So I was wondering if I could use a MySQL database for this program?
Furthermore, I have the database (with images/blobs) currently in MS-Access format, which was made on my PC. This is the database I would like to have on my PocketPC (I don't want to be able to edit it or anything. Just show it, with the pictures that go with the ID #.)
Thanks so much everyone. I'm really getting confused about this all.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
