|
I know nothing about Q&A but a lot of the 90s databases were based around DBase formats and there was a whole family of drivers for reading various dbase files. The most comprehensive was DBase IV and the foxpro drivers.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the good suggestion - after I read it I tried both formats, but to no avail.
Thanks again.
|
|
|
|
|
I need to secure only one particular database in SQL Server so I could access it only with certain predefined username/password. So, I am login in SQL Server as windows authenticated or sa and see all databases with details, but, for one database I want to see her in list but cannot access further without user/pass.
|
|
|
|
|
sa owns the server so you cannot exclude sa from the database. You then need to delete all other credentials from the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm experiencing the same problem and I need help.
|
|
|
|
|
You can't "lock" the database with a password. The only way to "protect" your database-model is to keep it on your own server and to provide a web-service to access it.
The owner of the server will be considered the owner of the data, and you can't lock the owner out.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I have 2 columns (Account Number & State) with follwing set of records in my table
Acc No State
1001 WA
1002 NY
1002 NY
1002 NY
1003 CA
1003 CA
1001 CA
1002 NY
I want to pull such Account Numbers that has multiple states associated with that account. Looking at the existing records, output should be as shown below
1001
1002
What query should I write to get this result set?
Regards,
Vipul Mehta
|
|
|
|
|
Got this resolved
Create Table T1 (Col1 int, Col2 varchar(50))
Insert into T1 Select 1001,'WA'
Insert into T1 Select 1001,'CA'
Insert into T1 Select 1002,'WA'
Insert into T1 Select 1001,'WA'
Select Col1 From
(
Select *,ROW_NUMBER()Over(partition by Col1 Order by Col1) RN
From (Select Col1,Col2 From T1
Group by Col1,Col2)T1
)A Where RN>1
Regards,
Vipul Mehta
|
|
|
|
|
Hi,
An alternative way to retrieve the data would be...
SELECT [Acc No] FROM table
GROUP BY State
HAVING COUNT(State) > 1
Thanks,
|
|
|
|
|
That causes error message 8120 in Microsoft SQL server (similar message in MS Access):
Column [Acc No] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
|
|
|
|
|
Yeah, slight typo there did it on the quick, will have to read it properly next time!
SELECT [Acc No] FROM table
GROUP BY [Acc No], State
HAVING COUNT(State) > 1
Sorry about that
|
|
|
|
|
Create Table T1 (Col1 int, Col2 varchar(50))
Insert into T1 Select 1001,'WA'
Insert into T1 Select 1002,'NY'
Insert into T1 Select 1002,'NY'
Insert into T1 Select 1002,'NY'
Insert into T1 Select 1003,'CA'
Insert into T1 Select 1003,'CA'
Insert into T1 Select 1001,'CA'
Insert into T1 Select 1002,'NY'
select distinct(col1) from t1 group by col1,col2 having count(col1)<2
output will come one record.
because state only one record have multi state
|
|
|
|
|
Jammo gave this exact same answer 2 hours earlier and it was a lot more understandable. Try using the formatting tools for placing code, your answer will be much more readable!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Arun's answer is clearly different from Jammo's. And Jammo's SQL statement fails with an error message.
|
|
|
|
|
My bad - I did not look close enough at the answer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Nice answer. +5
can I suggest that you try to format your code so that it is easier to read?
i.e.
SELECT DISTINCT(col1)
FROM t1
GROUP BY col1,col2
HAVING COUNT(col1) < 2
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Why do you expect "1002" to be among the output? Only "NY" is associated with "1002", though 4 times. But you said "multiple states".
|
|
|
|
|
select [Acc No],count(*) from table group by [Acc No] having count(*)>1
|
|
|
|
|
SELECT DISTINCT A.Acc_No,count(*)
FROM [Test].[dbo].[Table_2] A
JOIN [Test].[dbo].[Table_2] B
on A.Emp_state = B.Emp_state
group by A.Acc_No having COUNT(*)>1
|
|
|
|
|
Hi all, I'm trying to insert info from text boxes into an Access database using a stored procedure and it seems to some what work. The only problem is values from the text boxes are not entered into the database, instead the parameter names are inserted into the database. The following are how I named, set, and added all my parameters:
param = new OleDbParameter("@myvalue", value);
oCommand.Parameters.Add(param);
My stored procedure takes the following form:
INSERT INTO Table( field1,field2)
VALUES ('@value1', '@value2');
I have also tried naming, setting, and adding all my parameters using the below method which gave the same exact result as the above method:
param = new OleDbParameter();
param.ParameterName="@myvalue";
param.Value=value;
oCommand.Parameters.Add(param);
Please point out what the problem is if you can spot it, thanks.
modified 3-Aug-12 16:23pm.
|
|
|
|
|
INSERT INTO Table( field1,field2)
VALUES (@value1, @value2);
You don't need the quotes or they'll be interpreted as literal strings.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Hi Mark, thanks for replying I've already tried using no quote, single and double quotes without much luck. However, I've just found out that since I'm using Access, I have to use square brackets. My problem is now solved.
modified 3-Aug-12 17:31pm.
|
|
|
|
|
Really? How bizarre. Years and years since I used it and I don't recall having to do that. Oh well, relearn something, err, old, every day...
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
mark merrens wrote: Really? How bizarre.
VALUE is a reserved keyword; it would be like having a variable called "SELECT".
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
ASPnoob wrote: VALUES ('@value1', '@value2'); ... param.ParameterName="@myvalue";
As mentioned, remove the apostrophes.
The names have to match as well.
|
|
|
|