|
Try:
select z.*, (z.Bassalary/(z.days*z.Whrs)) as onehour
from (
select a.empid, b.empName, c.Whrs, c.srg,
case when srg=0 then @Outsunday else @Insunday end as days,
c.Bassalary,
sum(AppWHours) as TWhrs,Sum(AppOtHours) as TOThrs
from tblMonthHourDetails a, tblemployeemaster b, tblsalarydetails c
where a.empid=b.empid
and a.empid=c.empid
and b.projectid=@projectid
and datepart(mm,[SDate])=@months
and datepart(yy,[SDate])=@Years
group by a.empid, b.empName, c.Whrs, c.srg, c.Bassalary
) z
order by z.empid This moves most of the source calculations into an inline view (the funny select statement within the main from-clause). You can then use the "z.days" value that you have calculated.
Regards
Andy
|
|
|
|
|
I have a table tbUsers and I selected a row now it has three fields. I want value of first field in txtbox1, second field value in textbox2 and third field value in textbox3. where textbox1, textbox2 and textbox3 are the textboxes in my ASP page.
How can I do that.
Thanks,
|
|
|
|
|
1. create a connection object
2. create a recordset object
3. check whether is BOF/EOF
4. if not assign textboxes with value from recordset
Ex. connecting to SQL Server & Fetching data
<%
Dim rs, mycon, query
set rs=server.CreateObject ("adodb.recordset")
set mycon=server.CreateObject("adodb.connection")
mycon.Open "provider=sqloledb.1; uid=sa; pwd=sa; initial catalog=mydb; data source=myserver"
query="SELECT * FROM tbusers WHERE .....
rs.Open query, mycon, adOpenDynamic
if not rs.EOF and not rs.BOF then
<statements for="" assigning="" values="" to="" textboxes="">
end if
rs.Close
%>
Regards
KP
|
|
|
|
|
Hello
I'm looking for an O/R-Mapper which should be compatible with any/most databases through the OleDb-Provider.
All known an testet ORMs are only available for most common databases such as MSSQL, Access, Oracle, SQLite, MySQL, PostgreSQL, ...
But I need an ORM which is compatible with Paradox and other databases. (Jet/OleDb)
It's enough if the ORM only provide features such as SELECT, INSERT, UPDATE, DELETE and may transactions. Because Paradox doesn't support more.
Or can you give me a hint for problem below?
We're are rewriting an application (now in C++ VLC/OWL) in C# (.NET 2.0). The problem we have is that the new application must support MSSQL as well as Paradox. (Paradox and MSSQL support are Must-Haves)
What kind of ORM would you advise for our problem? Or something different?
Thank you
Danny Meier
|
|
|
|
|
NHibernate works as does DevExpress Persistent Objects.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hello again!
I'm rather new to what SQL means and does (many new "stored procedures"-words for me lately).
So, just the other day i ran into a problem. I designed a program which connects to a SQL Server 2000 Database. And it works just fine on my computer. Still, one of my friends wanted to test the application. I gave him the database files and registered them with SQL Server 2000 (attached to server). He also had VC# Express Edition installed (i think this is the cause and an important clue). When running the .exe, while trying to connect to the databases, he receives an error message which says that there is no connection available for SQL SERVER 2005:
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server( ).
Then, i tried the other way around. I gave him the code of my application, made him run it... and... another error: Cannot open database requested in login 'Consulting'. Login fails. Login failed for user 'GABRIELA\gabi'. I understand that there are users... and i can't synchronize them... but, how do i allow my database to be accesed on any computer with SQL Server 2000 installed on it?
the connection string in the program is:
<br />
<connectionStrings><br />
<add name="cristina_atestat.Properties.Settings.Consulting_coConnectionString"<br />
connectionString="Data Source=(local);Initial Catalog=Consulting;Integrated Security=True;Trusted_Connection=yes;"<br />
providerName="System.Data.SqlClient" /><br />
</connectionStrings><br />
<br />
Thanks a lot in advance,
Catalin David.
|
|
|
|
|
So are you (development machine) using sql server 2000 or 2005?
Nathan Lindley
|
|
|
|
|
i am using sql 2000. my friend has sql 2000 installed. the problem is that (local) points to sql 2005. and i think that is because of vc# express edition which connects mainly to sql 2005. but i want sql 2000. sorry for the confusion.
|
|
|
|
|
Okay then yes, the express edition is whats causing the problem. Id assume he probably installed it when installing Visual Studio 2k5. I actually ran into this problem a few months back, and the solution for me was to uninstall visual studio completely, and reinstall and make sure not to install vc express (custom install if i recall correctly). Id assume you were already on this path so i'm not sure how much help im providing but lmk if you still are pondering a decision.
Nathan Lindley
|
|
|
|
|
oki... will try this. again. just got visual studio 2005 (complete, not express) ... so... will install that, i hope. wish me luck, fingers crossed!
thanks a lot!
|
|
|
|
|
good luck, make sure to COMPLETELY uninstall visual studio.
Nathan Lindley
|
|
|
|
|
completely means anything else than start->control panel->add/remove programs-> uninstall visual c# express edition?
|
|
|
|
|
The reason i wrote that was because, i cant for the life of me think of it now tho, I think you have to clear some registry values. Just try the uninstall via the control panel and if it doesn't work check out this blog:
http://blogs.msdn.com/astebner/articles/574618.aspx
When I had your problem this blog was a life saver.
Good luck.
Nathan Lindley
|
|
|
|
|
Why not attach the db to the instance of SQL 2005?
I suspect whether you are using C# Express or Professional will not makes all that much difference.
Learning is not a spectator sport. - D. Blocher
|
|
|
|
|
Sorry for the late answer, but was not in town for a day. The thing is that my friend doesn't have an instance of sql 2005 installed on the system. plus, i think the connection string would change (sql 2005 express accepts only files to be accessed as far as i remember; i've worked only a little with 2005 because i had some conflicts on my pc so i had to reinstall all the system and...).
Thanks a lot. If professional doesn't solve it, i'm gonna take all the database to 2005.
Thanks again,
Catalin David.
|
|
|
|
|
I've read through a few articles on the issues that a machine running XP will have when accessing a server on a 2k or 2k3 machine, but have yet to find the solution to my problem. Here goes...
The issue is that when someone running a machine with XP on it, tries to access a Crystal Report,(which queries a db hosted via sql server on a machine with win2k), they often run into access issues. The access (in the crystal report) is set to use integrated security so the users are not required to use a un/pw. This happens after new installations (almost every time) and occurs randomly throughout the day. Currently, when the user encounters this error, we are just having them log off / log on and this takes care of the problem. So again to sum it all up, users are being asked for their credentials (although access is set to integrated security) when trying to access a crystal report.
Nathan Lindley
|
|
|
|
|
hi all....
plz let me know how to retrieve the column name of a given table.....without content(data),just only column names
not like select * from sometable and sp_help sometable
Plz help me,,thanqs in advance
|
|
|
|
|
declare @tblname varchar(100)
set @tblname = 'mytable'
select * from syscolumns where id = (select id from sysobjects where name = @tblname)
Regards
KP
|
|
|
|
|
actually i want to bind the column names with a grid on the webpage...here i don't want any content...it giving some information along with the columns...anyway thanqs for ur kind reply MR krish
|
|
|
|
|
while retrieving data to grid use select query which returns no rows
like SELECT * FROM tblA WHERE 1 = 2
this should work (I've not tried this.)
Regards
KP
|
|
|
|
|
Hi
I have to import flat files in a sql server database which are of the format:
Date: 2 apr 07
Input file: input1.test
Output file: input2.test
table1_column1 table1_column2 ....
123 456
table2_column1 table2_column2.......
26678 23334
the file is tab delimited. i have to read the date from the header and read only the first table.
is it possible to do it with IS?
I have a parsing routine in c# that extracts the required data but it is very slow. Iam looking for some efficient solutions
|
|
|
|
|
Hi i will give month and year...,
based on that want to find number of days in a month and number of sundays in that month...,
Its possible in sqlquery???,
If its possible plz send me the query how to find the number of days and number of sundays in a given month.....,
Magi
|
|
|
|
|
query for getting number of sundays in a month.
declare @mth varchar(4), @yr varchar(4), @dt datetime
set @mth = '4'
set @yr = '2007'
set @dt = convert(datetime, '01-' + @mth + '-' + @yr, 103)
-- to get first sunday of the month
select cast(case when ((7 - datepart(dw, @dt)) + 2) = 8 then 1 else ((7 - datepart(dw, @dt)) + 2) end as varchar(5)) + '-' + @mth + '-' + @yr
-- to get number of sundays
select case when (21 + (case when ((7 - datepart(dw, @dt)) + 2) = 8 then 1 else ((7 - datepart(dw, @dt)) + 2) end)) between 24 and 28 then 4 else 5 end
Regards
KP
|
|
|
|
|
Can u provide me with an example of a date routine that will return the begin and ending period for a week number. For example, if week 14 for 2007 was provided, the routine would return starting date 08/04/2007 and ending week 14/04/2007.
Input Provided - week number (1 - 53), and Year(cccc)
Output - week begin date and week end date
Revathi Raj
|
|
|
|
|
check this out. hope the smae you are looking for
declare @wk int
set @wk = 15
select dateadd(dd, @wk * 7 - datepart(dw, '01-jan-2006'), '01-Jan-2006')
this gives begining date of week. Adding 7 days returns end date
Regards
KP
|
|
|
|