|
Hi this is my probs...,
Its working in query analyzer...,
But its not working, when the values passing from asp.net to stored procedure...,
I am getting error like "Incorrect syntax near '/'...,
Sorry before msg i really confused lot...,
declare @appdate as smalldatetime
declare @noofdays as varchar(50)
declare @month as varchar(50)
declare @year as varchar(50)
set @appdate='8/1/2007'
Set @noofdays=Day(DateAdd(Month, 1, @appdate) - Day(DateAdd(Month, 1,@appdate)))
set @month =datepart(month,@appdate)
set @year=datepart(year,@appdate)
begin
SELECT EM.empId, TC.carddate,SD.Whrs,sd.edate,MHD.Appwhours as AppHours,MHD.AppOtHours as AppOtHours
FROM dbo.tblEmployeeMaster EM INNER JOIN dbo.tblTimeCardmain TC ON EM.empId = TC.empid
INNER JOIN dbo.tblSalaryDetails SD ON EM.empId = SD.EmpID
left outer join dbo.tblMonthHourdetails MHD ON TC.empid=MHD.empid and TC.Carddate=MHD.Sdate
inner join (select empid,max(edate) as edate from tblsalarydetails
where edate <= @month + '/' + @noofdays + '/' + @year group by empid) z on SD.empid=z.empid and SD.edate=z.edate
WHERE TC.carddate = @appdate AND TC.app1 = 1 AND TC.projectid = 104 order by EM.Empid
end
GO
Regards,
Magi
|
|
|
|
|
Why aren't you using a DATETIME type?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Hey there,
I've never liked databases and will probably never do so rather than trying to build my own database connection class I copied one written in VB.Net.
Dont know why but it does not work :-?
Here is the code:
[code]
Public Sub FindAndSelect(ByVal TheTableName As String, ByVal oFindValue As String, ByVal ColName As String, ByVal xColName As String, ByRef xValue As String)
Dim i As Integer
For i = 0 To MyDts.Tables(TheTableName).Rows.Count - 1
If MyDts.Tables(TheTableName).Rows(i).Item(ColName) = oFindValue Then
xValue = MyDts.Tables(TheTableName).Rows(i).Item(xColName)
Exit For
End If
Next
End Sub
[/code]
That looks this way after converting it (at least imho  )
[code]
public static void FindAndSelect(string tableName, string findValue, string colName, string xcolName, string xValue)
{
for(int i = 0; i < MyDts.Tables.Count; i++)
{
if(MyDts.Tables[tableName].Rows[i].Item[colName] = findValue)
{
DataRow row = new DataRow();
xValue = MyDts.Tables[tableName].Rows[i].Item[colName];
DataRowCollection col = new DataRowCollection();
break;
}
}
}
[/code]
But that causes a compiler error in the lines:
if(MyDts.Tables[tableName].Rows[i].Item[colName] = findValue)
&
xValue = MyDts.Tables[tableName].Rows[i].Item[colName];
saying that "System.Data.DataRow does not contain a definition for 'Item'".
I dont understand that since it works in VB.Net and according to the MSDN the DataRow class has a 'Item' member.
http://msdn2.microsoft.com/en-us/library/system.data.datarow_members(VS.71).aspx
Any ideas?
|
|
|
|
|
Drop the .Item and just refer to Rows[i][colName]. This is an indexer property so it doesn't need the Items.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
That works, thank you.
Still I dont understand why the .Item doesn't work.
|
|
|
|
|
The collection is called .Items and not .Item. In order to do this, you would have to do .Items[...].
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
c0rvus wrote: But that causes a compiler error in the lines:
if(MyDts.Tables[tableName].Rows[i].Item[colName] = findValue)
&
xValue = MyDts.Tables[tableName].Rows[i].Item[colName];
bolded code replace with Rows[i][colName]
all what to do is to remove .Item property
I Love SQL
|
|
|
|
|
Hi all,
How to communicate an AJAX and SQL. I want to pass values directly from AJAX to SQL, with out any server side script(like asp,php). Is it possible to do so? If there is an option plz send me the link or plz guide me.
Thanks in advance
Know is Drop, Unknown is Ocean
Know is Drop, Unknown is Ocean
|
|
|
|
|
You would presumably have to use the Classic ADO interface, but:
* There have been security issues in some ADO components (ADODB.Stream) which have led to them being blocked from loading in Internet Explorer;
* It is very unlikely, and very bad practice, for the SQL Server to be exposed to the internet.
|
|
|
|
|
declare @noofdays as varchar(5)
declare @month as varchar(5)
declare @year as varchar(5)
declare @appdate as smalldatetime
set @appdate=8/1/2007
Set @noofdays=Day(DateAdd(Month, 1, '@appdate') - Day(DateAdd(Month, 1,'@appdate')))
set @month =datepart(month,'@appdate')
set @year=datepart(year,'@appdate')
select @noofdays,@month,@year
Hi in this i am getting error like "Syntax error converting datetime from character string."
How to solve that ...,
Plz help me,
Advance thanks...,
Regards,
Magi
|
|
|
|
|
Hi Dear Remove quotation from all Variables
Like this or run this code.
declare @noofdays as varchar(5)
declare @month as varchar(5)
declare @year as varchar(5)
declare @appdate as smalldatetime
set @appdate=8/1/2007
Set @noofdays=Day(DateAdd(Month, 1, @appdate) - Day(DateAdd(Month, 1,@appdate)))
set @month =datepart(month,@appdate)
set @year=datepart(year,@appdate)
select @noofdays,@month,@year
Regard
Sohail
|
|
|
|
|
Hi Dear Remove quotation from all Variables
Like this or run this code.
declare @noofdays as varchar(5)
declare @month as varchar(5)
declare @year as varchar(5)
declare @appdate as smalldatetime
set @appdate=8/1/2007
Set @noofdays=Day(DateAdd(Month, 1, @appdate) - Day(DateAdd(Month, 1,@appdate)))
set @month =datepart(month,@appdate)
set @year=datepart(year,@appdate)
select @noofdays,@month,@year
Regard
Sohail
|
|
|
|
|
Hi plz check it,it wont work...,
Give some different date ...,
It will show the same values and see the year it will give 1900
Regards,
Magi
|
|
|
|
|
Dates to be enclosed with in single quotes
other wise they will be taken as 01/01/1900
Regards
KP
|
|
|
|
|
Solution given to you basing on the code what you have given.
Regards
KP
|
|
|
|
|
Very good.
request you please learn SQL before using not the other way.
your questions shows that you a fresher to SQL
Regards
KP
|
|
|
|
|
What do you expect? You've specified that put quotes around your @appdate entries turning them from variables into constants. Remove them.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Two errors in your code
1. only string constants to be enclosed in quotations. Not variables.
Remove quotations ( this is for Syntax Error)
2. Dates should be enclosed within quotation. (this is for logical error. other wise it takes as 01/01/1900)
Regards
KP
|
|
|
|
|
Ok i need o/p...,
Give me some solution to solve that probs...,
Regards,
Magi
|
|
|
|
|
If i remove the quotes...,
It will give year 1900 and month always 1
Regards,
Magi
|
|
|
|
|
Magi,
you want to send date as a input parameter from your application to stored procedure right!
all you got to do when you create the stored procedure mention the parameter datatype as datetime and when in your code when creating parameter mention the sqldbtype as datetime.
This would solve your problem you need not put any quotes when using the name of the parameter you can directly give @param_name wherever you like in your stored procedure
gauthee
|
|
|
|
|
Hello,
VS 2005
I have find myself getting all my code in a mess about having data populated into control and then having to modify the data in the controls.
I always use typed dataset, binding source.
But I have some questions. I have read that you should always perform operations on the binding source
rather than the control it is bound to.
i.e.
If I have a datagridview and I want loop through some rows based on a certain condition, then I should loop through the rows in the binding source and filter for those rows, rather than looping through the dgv rows. Is this a correct assumption.
I have also read that you should set the datasource property of the control only once.
This is what I have done in the past, and please correct if I am wrong.
If I have a child parent relationship then I sent the child binding source to the parents binding source
in the designer, so that if a user click on a combo box then child data is correctly dislayed in the
datagridview, without writing any code.
In the form event I would have sub routines that would call for the controls to be populated.
<br />
Private Sub FillDataGrid()<br />
Try<br />
Me.OrderDetailsTableAdapter.Fill(Me.DataSet1.OrderDetails) <br />
Me.DataGridView1.DataSource = Me.bsOrderDetails<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
<br />
End Sub
<br />
Private Sub FillCombo()<br />
Try<br />
Me.ComboBox1.DisplayMember = "OrderID"<br />
Me.ComboBox1.ValueMember = "OrderID"<br />
Me.ComboBox1.DataSource = Me.bsOrders<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
End Sub
Normally, if was to add a new order details i would call the FillDataGrid() to load the data again, i
normally do this when I delete and modify.
<br />
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles <br />
<br />
btnSave.Click<br />
Try<br />
Me.bsOrderDetails.EndEdit()<br />
Me.OrderDetailsTableAdapter.Update(Me.DataSet1.OrderDetails)<br />
<br />
Me.FillDataGrid()<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
End Sub
This is just a small example, my applications are always much bigger and contain a lot of modifications
to the data and have a lot of controls to update. However, this means that I am always calling re-filling the setting the datasource each time a add, update, or delete has happend to the data.
Any feedback would be most helpful.
Steve
|
|
|
|
|
SELECT EM.empId, TC.carddate,SD.Whrs,
MHD.Appwhours as AppHours,MHD.AppOtHours as AppOtHours
FROM dbo.tblEmployeeMaster EM INNER JOIN dbo.tblTimeCardmain TC
ON EM.empId = TC.empid INNER JOIN dbo.tblSalaryDetails SD
ON EM.empId = SD.EmpID left outer join dbo.tblMonthHourdetails MHD
ON TC.empid=MHD.empid and TC.Carddate=MHD.Sdate
WHERE TC.carddate = '8/1/2007' AND TC.app1 = 1 AND TC.projectid = 100
order by EM.Empid
Hi this is my query:
For this i am getting o/p like:
Empid carddate whrs AppHours AppOtHours
100 8/1/2007 11 11 3
101 8/1/2007 8 8 6
102 8/1/2007 11 11 3
103 8/1/2007 11 11 3
103 8/1/2007 11 11 3
See that empid=103 have 2 entries in tblsalarydetails...,
So its givng in o/p also 2 values...,
But there i need one o/p that want to be based on the date...,
In my tblsalarydetails i have date column...,
So How to display the whrs based on the date for that employee?,
Plz hlp me...,
Tblsalarydetails table design:
empid whrs edate
100 11 8/1/2007
101 8 7/1/2007
102 11 8/1/2007
103 11 8/1/2007
103 10 2/21/2007
Regards,
Magi
|
|
|
|
|
Use SELECT DISTINCT instead.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi Magi
Try:
SELECT EM.EmpId, TC.CardDate, SD.Whrs,
MHD.Appwhours as AppHours, MHD.AppOtHours as AppOtHours
FROM dbo.tblEmployeeMaster EM
INNER JOIN dbo.tblTimeCardmain TC
ON EM.EmpId = TC.EmpId
INNER JOIN dbo.tblSalaryDetails SD
ON EM.EmpId= SD.EmpId
AND SD.EffectiveDate = (
SELECT Min(A.EffectiveDate)
FROM dbo.tblSalaryDetails A
WHERE A.EmpId = EM.EmpId
AND A.EffectiveDate <= TC.CardDate)
LEFT OUTER JOIN dbo.tblMonthHourdetails MHD
ON TC.EmpId = MHD.EmpId
AND TC.CardDate = MHD.Sdate
WHERE TC.CardDate = '8/1/2007'
AND TC.app1 = 1
AND TC.projectid = 100
ORDER BY EM.EmpIdIt uses a correlated sub-query to find the effective salary record (assuming your date column is named "EffectiveDate").
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
