|
I have sql statement in access database to find records -3 fields ID, name, and Date.
i use
SELECT ID,COUNT(name)AS CNT
FROM Table1
WHERE (((Table1.Date)=[Enter Date]
GROUP BY ID
HAVING COUNT(name)>1
now I want to find records < 1 - not sure of how to start to find these-any ideas-
There are supposed to be 30 records each week added - sometimes people add too many records ( the statement above helps- but if they did not add record- do not know how to figure that)
Thank you,
|
|
|
|
|
Because it's a single table, you are a little restricted in how you can do this...
One way is to use the following:
SELECT ID
FROM Table1
WHERE ID not in(
SELECT ID
FROM Table1
WHERE ((Table1.Date)=[Enter Date]
GROUP BY ID
HAVING COUNT(name)>1
)
Naturally this assumes that your existing query is returning the details you are after... Note also that your original query is only returning duplicates... If you want to return records where there is a single entry, your original query needs:
HAVING COUNT(name) >= 1
|
|
|
|
|
|
I work with a database (SQL Server 2000) with thousands of stored procedures. In order to group them so that they are easier to manage, we have made use of numbered stored procedure groups (e.g., "SomeSPGroup;1", "SomeSPGroup;2", and so on). That's better than nothing, but still it's not so great. We have one SP group with hundreds of SP's in it. What I would really like is some way to group them into a namespace and nest further namespaces in that namespace. I can sort of do this now by naming them with dot notation, but then it becomes somewhat clumsy to use them. For example, you have to type out the full name and SQL Server Management Studio provides no recognition that these SP's are related, so they are all just listed next to the other SP's in the tree view, rather than giving them their own node that can be expanded. For example, I could name two SP's like so:
[OrderProcessing.CreateOrder]
[OrderProcessing.DeleteOrder]
Whenever I want to execute those, I have to use the full names:
EXEC [OrderProcessing.CreateOrder] @param1, @param2
And the list of SP's in SSMS shows them side by side with other SP's, like this:
...
MakeShipment
[OrderProcessing.CreateOrder]
[OrderProcessing.DeleteOrder]
ProcessPayment
...
What I would like to see is a hierarchy:
...
MakeShipment
-OrderProcessing <-- This node can expand and collapse.
CreateOrder
DeleteOrder
ProcessPayment
...
I suppose I could create my own tool or perhaps make a plugin that will do this for me, but there is still no support for namespaces in the actual SQL code (i.e., I have to use the whole name when executing it).
What I would like to hear is any thoughts you have on this. Do you know of any database provider that currently has a concept of namespaces? Have you header of Microsoft planning support for this in a future version of SQL Server? Does it already exist in SQL Server and I've just missed it somehow? Do you have alternative methods for managing large groups of stored procedures (and, no, prefixes are not an appealing option to me)?
|
|
|
|
|
A Schema is somewhat equivalent to a namespace in this context.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Interesting. From what I can see, the only advantage a schema has over naming an SP with dots in it is that the square brackets are not required around the full name (e.g., I can say dbo.SomeSP instead of [dbo.SomeSP]). Would be nice if schemas could be nested, could be contextualized (e.g., using dbo; would remove the need to prefix with that schema in that scope), and had tooling support (e.g., if SSMS showed schemas in the tree view and allowed them to be collapsed/expanded to show the objects in them). I suppose they also have other advantages (applying permissions to the schema gives the same permissions to the objects it contains?), but none organize SP's in the manner I'm thinking of. Thanks for the info though.
|
|
|
|
|
I can see a requirement for a custom SQL UI. We also make extensive use of stored proc and I find scrolling through 500+ procs irritating in the extreme. And if you miss name one it can be a royal PITA trying to find the bloody thing.
Go annoy Red-Gate, they make some excellent tools and may well be interested in the idea.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Once more we can't connect to the database. We get the age old message:
Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'HKRAM\Mark Hardenbergh'. HKRAM is the name of my computer. Mark Hardenbergh is, obviously, me.
Which means to me there is something wrong with my user name. Anybody know what's wrong with my user name? Do I have another name? Where do I find my other name. I understand that I could use sa for my name, but that doesn't work either.
Here is the code written on the default2.aspx.vb page, the code behind page.
Imports System.Data.SqlClient
Partial Class Default2
Inherits System.Web.UI.Page
Protected Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim cn As New SqlConnection("C:\DOCUMENTS AND SETTINGS\MARK HARDENBERGH\MY DOCUMENTS\VISUAL STUDIO 2008\WEBSITES\WEBSITE5\APP_DATA\VANILLA.MDF;user id=HKRAM\Mark Hardenbergh")
Dim cmd As New SqlCommand
With cmd
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "mhhtrial"
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
No doubt everyone can recognize what I am trying to do. I am trying to perform the simple taks of running a stored procedure. It is in a database called vanilla.mdb. And, yes, it exist there. However I can't get to square 1 because the fool thing won't let me connect to vanilla.mdb.
As you can imagine, I have a gridview that finds a table in vanilla.mdb and displays it beautifully. When I do gridviews and have the connection string turning up in web.config, there is no problem.
The connection string that is in the web.config file is:
<add name="VanillaConnectionString" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Vanilla.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
It certainly would be nice, since this connection string obviously works, if I could use it in the vb in the code behind page. But, so far, no forum wants to explain that, much less any book I have bought.
Can anyone straighten out this Dim cn as New SqlConnection....... out so I can move along?
|
|
|
|
|
Your user name is the smae as the account name with which you login to your machine with, not necessary your full name.
Also, when posting code here make sure to format it by using pre tags.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
What, pray tell, are pre tags?
Do I use them on every line of code or just at the start and at the end of code?
|
|
|
|
|
|
mhh112 wrote: Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
The error really tells you the problem - Cannot open user default database which means your user is not set up correctly in SQL Server. Go in and see what your default database is set to, or change your connection string to specify the database you want to use.Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ok, here is a silly one.
I have a ms access database that i want to manipulate through a datagridview in my VB .net app.
I have defined the appropriate field in the database as DATE/TIME, because i will make date filtering on the records through sql in my app.
Now when i add any record through my app, i always get a date format like 17/2/2010 0:00:00 in the datagridview, obiously because this is the format of the access database which is databound to the grid.
I DO NOT want the time part.
Is this doable or i am stuck with it ?
Thanks
|
|
|
|
|
Johnkokk wrote: i always get a date format like 17/2/2010 0:00:00 in the datagridview, obiously because this is the format of the access database which is databound to the grid.
Go to the offending table and open it in the design-view. Click on the DateTime-field and go to the properties. There's a Format property that controls how the data of this particular column is formatted.I are Troll 
|
|
|
|
|
I would assign the responsibilities to the right parties: let the database hold the data and not worry about formatting, and let the application take care of the formatting; in this case, tell the DGV what format is to be used.Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
+5, and goodmorning;
You're right. The native grid in Access 2007 doesn't support that, but if it's a VB.NET application than he's using a DataGridView - and not a grid on a native Access-form  I are Troll
|
|
|
|
|
Good morning Sir to you too.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
I've already tried setting the formating in the database field.
It doesn't work
|
|
|
|
|
you can solve it by two ways.
1. Using the date column datatype's format property in Access database.(various formats like General Date, Long Date, Medium Date, Short Date, Long Time, Medium Time, Short Time)
2. using the DataFormatString property of bound column in gridview. (examples: DataFormatString="{0:MM-dd-yyyy}", DataFormatString="{0:MMM dd, yyyy}", DataFormatString="{0:ddd MM, yyyy}", etc )
|
|
|
|
|
<b>2. using the DataFormatString property of bound column in gridview. (examples: DataFormatString="{0:MM-dd-yyyy}", DataFormatString="{0:MMM dd, yyyy}", DataFormatString="{0:ddd MM, yyyy}", etc )</b>
That worked, thanks
|
|
|
|
|
Hi,
you can set the formatting specification for each individual column of a DataGridView. Here is a C# example:
dgv.Columns[1].DefaultCellStyle.Format="dd MMM yyyy";
which works fine as long as the field bound to that column is a DateTime (and not a string!)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
Hi guys,
i'm kinda new in using MSSQL. I'm using MSSQL Server 2005. I've created the primary key on a field that auto generates. However, i need to disallow the same name inputed again. I think i might have to write a t-sql statement on the table. But i really don't know how to go about it.
Pls help. How can i achieve this?He who goes for revenge must first dig two graves.
|
|
|
|
|
Creamboy wrote: However, i need to disallow the same name inputed again.
That would be a Unique Constraint, somewhat similar to this example;
ALTER TABLE [MyTable]
ADD CONSTRAINT [UniqueConstraintName]
UNIQUE (ColumnValue) I are Troll 
|
|
|
|
|
Hello everyone,
I have a MS SQL Server 2005 Express Edition and I want to restrict Login access to the database to only one specific domain account. How can I do this any ideas? because when I went to try and create a windows login and specified the domain account that I want it worked ok, but when then I tried to Login into the database again with a different domain account to test it still let me in. Is there some particular setting that I don't know about that anyone can help me with?
Thank you very much.
|
|
|
|
|
How to retrieve data from two different database with single query
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
