|
I haven't written a UDF or a while, and I don't like that design, but can you use a UNION?
SELECT * FROM Emp WHERE @Param=1
UNION ALL
SELECT * FROM Pffices WHERE @Param=0
|
|
|
|
|
|
No this isn't possible with either an inline or multi statement TVF (unless the 2 tables are union compatible?)
so the above reply is only workable solution, if tables are union compatible.
|
|
|
|
|
This is not a valid use for a function, a stored proc maybe but not a function. There is no way that emp and offices are going to be union compatible, if they are then why are they in different tables. You need to rethink your design.
Select * from anytable
This is frowned upon, explicitly declare the fields.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear ALl,
i have a record set lets say: set rs = db.openrecordset ("SELECT Name, Location FROM tb1")
I need to add a new field "salary" to rs, so i used : rs.fields.append "salary" , adinteger
salary is of integer datatype
This is not working, it is giving me Typemismatch Error and it highlights "salary", i also googled, all says the same method to append, but still not working .....
AM not sure whats the error, or why , Any one can guide how to append this coloumn to the recordset ??? any work around ??? 
Note: I want to fill the values of this "salary" field with numbers from calculations.
Regards,
0 will always beats the 1.
modified on Monday, February 7, 2011 5:48 AM
|
|
|
|
|
scorp_scorp wrote: any work around ???
How about adding a dummy-column in the select-statement? Something like below;
set rs = db.openrecordset ("SELECT Name, Location, 0 As [Salary] FROM tb1")You could then overwrite the values in the new column as required 
I are Troll 
|
|
|
|
|
Greaaaat, thanks alot, this worked excellent but hence i got another problem:
when now i want to fill this feild (after creation), am doing:
With rs
.AddNew
.Fields("salary") = x
.Update
End With
where x is an integer from some where, i have the following error: Field cannot be updated .
i also used :
rs.Edit
rs("salary").Value = x
rs.Update
also same error
Any hint why this error showing up ??? is it the right way to fill in this field ?? i think it is 
0 will always beats the 1.
modified on Monday, February 7, 2011 11:36 PM
|
|
|
|
|
scorp_scorp wrote: Any hint why this error showing up ???
I don't know, would have to look it up. My guess is that the recordset-object is readonly.
What are you trying to achieve, and in what language? If you need to update some records in a database, you'd best execute the Sql against the database-server 
I are Troll 
|
|
|
|
|
Thaks eddy, i found that recordsets are read only by default ... and also are not modifiable if sql depends on two tables of one-to-many relation or invlove sumation of rows ...
Also, if above conditions are not true, then a recordset to be modifiable, i think attribs should be set like:
set st = db.openrecordset("SQL",2,0,3)
or
set st = db.openrecordset("SQL",adOpenDynamic,adUseNone, adLockOptimistic)
0 will always beats the 1.
|
|
|
|
|
hi everyone,
i have sql server db with a table which contains a field for a workplace and the number of people employed in a specific month and year separated by gender.
it kinda looks like :
workplace month year male female
Microsoft feb 2011 5 1
Google feb 2011 6 2
Intel feb 2011 7 3
Microsoft march 2011 8 4
obviously with different actual data.
is there a way to concatenate the year and month columns and pivoting the data so it'll look something like :
workplace date male female date male female
Microsoft feb 2011 5 1 march 2011 8 4
Google feb 2011 6 2
Intel feb 2011 7 3
sorry if that was a bit too much, I'll be happy to explain myself more clearly if needed.
|
|
|
|
|
Select columnA + columnB from someTable
This concatenates the column vakues. Have you tried using this in your pivot query?
|
|
|
|
|
There are some excellent articles [^]around on that subject.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: There are some excellent articles
Shameless plug, excellent.  5 for that.
And it is a very useful article.
|
|
|
|
|
Hey,
Sorry for the late response, I wasn't around a computer.
While article you added really shed some light about the topic, I still have a small problem.
the article showed how to concatenate to columns into one and then pivot the column, what I want to do is to pivot them both.
So if i refer to the article, instead of having a column for "1996-F" and one for "1996-M" I want two columns, one for "M" and one for "F" and "1996" wrapping them.
something like:
1996
M F
6 5
|
|
|
|
|
Now you are trying to put UI/presentation logic into the database operation. Adding the additional row with the year is not a database operation it needs to be done in your application.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dears,
I have SQL Server table which includes contains follwing columns
File Name Telephone
MemeberName_TelephoneNumber_IDNUmber ,TelephoneNumber
I need to add one or more column named ID Number and get the value from column Filename.what is the query to get the result?
please help me
|
|
|
|
|
Not entirely clear what you are asking but this might set you in the right direction:
declare @Id int
set @Id = 999
select FileName from [table] where IDNumber = @Id
Hoep that helps.
"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
|
|
|
|
|
Howdy!
I'm creating an application to keep track of people at an event. The app will run on a Windows 7 laptop, and have no network connectivity (I believe that means it's single tier?). The app is going to be written in C# 2008 Express, with the database in SQL Server 2008 Express.
Here's the security concerns for the app:
About 8 people (door volunteers) will use the app in a night, to register dancers when they arrive.
Every half hour, the volunteer changes.
The volunteers are highly computer challenged (or must be assumed to be so).
I don't want a windows login for each volunteer.
The app will prompt for the volunteer to "log in" to the app, as their shift starts.
I'm considering a single windows user for the whole app. The volunteer app "login" will just be a record of whose at the door at a giving time.
Here's my question:
Is the best way to set up the database security to create the app user in windows, or to use SQL Authentication from within the program? What considerations indicate the best choice?
Thanks,
Jess
|
|
|
|
|
That all sounds reasonable.
A concern I see is the amount of time spent changing users. If there's a line of dancers waiting to be checked in and out, you may not want to wait for Windows to log out and back in and restart the app. So I would have users as a feature of the application. A supervisor-type user can add and remove users. And a user would log into the app by menu or drop-down -- you may or may not ask for a password.
So, I would have a User table which contains the details of each user. The ID of the user can then be attached to the various actions (check-in, check-out) taken within the system.
|
|
|
|
|
Do NOT use windows logon within the database security. Create a "functional" id in sql server with a usedid and password, have the application use this ID as the credentials to connect to the database.
Manage the user security within the application rather than use windows/sql security. As Piebal suggested have a used table with credentials that can be managed from within your application by an administrator (you presumably)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Q. Why do you need each volunteer to be separately logged in? Do you need to keep track of who let in whom?
Assuming you're not too concerned about someone trying to steal or knacker the database then the type of security doesn't really matter - integrated or not that is.
You could do something as simple as asking them to type in their name (selection from a drop down could be risky - what if someone volunteers in another user's place?) and then log that in the database.
Disable the entry of the name on a login/logout button and bob's your uncle.
___________________________________________
.\\axxx
(That's an 'M')
|
|
|
|
|
How can I update my table every hour?please help me
|
|
|
|
|
You question is a bit open-ended, but I'll try to point you in the right direction.
If you are logged in as "sa" in SQL server Management Studio, look under "SQL Server Agent" and you will find "Jobs". Here you can create a job to execute with a Daily Frequency of every hour.
Does this help?
|
|
|
|
|
Thanks but I'm not logged in as "sa". please explain more about job?
I see it in sql server agent but I didn't work it.I should update table data every hour
|
|
|
|
|
If you don't have sa access, then I would write a windows command line utility (no GUI) which can be scheduled in the Windows Task Schedule to run every hour to update your table.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
