|
|
Hi in sqlserver stored procedure i am doing some calculations...,
See the below procedure:
select a.empid,b.empName,c.Whrs,c.srg,case when srg=0 then @Outsunday else @Insunday end as days,c.Bassalary,
(Bassalary/(days*Whrs)) as onehour,
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
In that above procedure using case condition i am getting one column "days" like...,
That days value i want to use to calculate for onehour column,
if i give there days(highlighted in above procedure) value its telling error msg like invalid column days like...,
but i want to use that value there how to use...,
Plz any one solve my probs.....,
Magi
|
|
|
|
|
You can do a nested query, but this is probably better:
case when srg=0 then (Bassalary/(@Outsunday*Whrs)) else (Bassalary/(@Insunday *Whrs))end as onehour
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Thank u for ur reply...,
But i did like this:
Bassalary/((case when srg=0 then @Outsunday else @Insunday end)*Whrs) as onehour
Any way once again thanks for ur reply
Magi
|
|
|
|
|
Stored VB.Net procedures consisting of many subroutines and a few functions
working with one SQL Server 2005 datbase. The problem is as follows:
Sub A receives a table name, a query name, and a directory path name.Using
Dim RecordTypeSet As SqlDataReader
Dim RecordTypeConnection As New SqlConnection
Dim RecordTypeCommand As New SqlCommand I open a reader
from the sql located at directory path name & query name
and While RecordTypeSet.Read()
I create a record which I want to store in the table name provided.
In order to do this I call a SUB B with the record and the Table name.
Sub B builds an insert query and using
Dim SummaryConnection As SqlConnection
Dim SummaryCommand As SqlCommand
Dim SummaryReader As SqlDataReader
SummaryConnection = New SqlConnection("context connection = true")
SummaryConnection.Open()
SummaryCommand = New SqlCommand(sql, SummaryConnection)
SummaryReader = SummaryCommand.ExecuteReader()
errors out on SummaryConnection.Open()
end sub B
wend
rkj
|
|
|
|
|
reykentj wrote: SummaryConnection = New SqlConnection("context connection = true")
What's the error?? Why does everyone leave out the error description when they post "I have a problem!"?
Does the method that this error occurs in have the SqlProcedure attribute on it?
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
Sub_A
Dim sql As String = ""
Dim rdr1 As SqlDataReader
Dim conn1 As New SqlConnection
Dim cmd1 As New SqlCommand
sql = ReadAllText from textfile
conn1 = New SqlConnection("context connection = true")
conn1.Open()
cmd1 = New SqlCommand(Sql, conn1)
rdr1 = cmd1.ExecuteReader()
While rdr1.Read()
build record REC_A
Sub_B (REC_A)
SUB_B
Dim sql As String = ""
Dim rdr2 As SqlDataReader
Dim conn2 As New SqlConnection
Dim cmd2 As New SqlCommand
Builds SQl Query to insert record into some table
conn2 = New SqlConnection("context connection = true")
conn2.Open() ABORTS HERE
eND sUB_b
wend
End Sun_A
With SqlProcedure Attribute I get a deployment error on SUB_A
because it has a Byref Class instantiation to return data to the caller
rkj
|
|
|
|
|
This is utterly useless. Copy and Paste the code in a post. The "Sub_A" crap leaves out details that are required to see what's going on.
You also didn't answer my question. Does the "sub" have an SqlProcedure attribute? Copying and pasting the code probably would have showed that! And, the EXACT error message would help more, not your interpretation of it.
The "context connection = true" only works inside an SqlProcedure attributed function. Without it, you may as well use a seperate connection using a normal connection string.
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
I did not originally have the sql server attribute declared
Your first question prompted me to insert that and try again.
When I did, I got a deployment error on the byref use. I am currently
trying to find another attribute to solve that problem. Here is the code
<microsoft.sqlserver.server.sqlprocedure()> _
Public Shared Sub sbBuildRecordTypeProfitnLossTable(ByVal Account_Summary_Name As String, _
ByVal RecordTypeQueryName As String, _
ByVal DirectoryPath As String, _
ByVal Run_Select As Long, _
ByVal Department As String, _
ByVal Record_Type As Single, _
ByVal record_count As Long, _
ByVal Pdate As Date, _
ByRef PL_Return As Profit_Loss_Return)
Dim Wrec As New Profit_Loss_Summary_Record
Dim ProfitnLossRec As New Profit_Loss_Summary_Record
Dim wSalesRec As New Profit_Loss_Summary_Record
Dim TotMonthly_Item As Decimal = 0
Dim TotYearly_Item As Decimal = 0
Dim result As Long = 0
Dim SalesAccountNo As Long = 0
Dim AccountStor As Long = 0
Dim StrSalesAccountNo As String = ""
Dim CGAccountNo As Long = 0
Dim Branch_Name As String = ""
Dim ReturnValues As New Profit_Loss_Return
Dim wdepartment As String = ""
Dim sql As String = ""
Dim RecordTypeSet As SqlDataReader
Dim RecordTypeConnection As New SqlConnection
Dim RecordTypeCommand As New SqlCommand
On Error GoTo Err_sbBuildRecordTypeProfitnLossTable
sql = File.ReadAllText(DirectoryPath & RecordTypeQueryName)
RecordTypeConnection = New SqlConnection("context connection = true")
RecordTypeConnection.Open()
RecordTypeCommand = New SqlCommand(Sql, RecordTypeConnection)
RecordTypeSet = RecordTypeCommand.ExecuteReader()
While RecordTypeSet.Read()
AccountStor = RecordTypeSet!AccountNo
If AccountStor = 53410 Then
record_count = record_count
End If
sbBuildSummaryProfitnLossTableRec(RecordTypeSet, Record_Type, Department, Pdate, ProfitnLossRec)
If Record_Type = 2 Then
CGAccountNo = ProfitnLossRec.AccountNo
SalesAccountNo = CGAccountNo - 10000
StrSalesAccountNo = SalesAccountNo
sbGetSummaryRec(Account_Summary_Name, SalesAccountNo, wSalesRec)
If wSalesRec.AccountNo <> StrSalesAccountNo Then
wSalesRec = ProfitnLossRec
wSalesRec.AccountNo = SalesAccountNo
wSalesRec.Monthly_item = 0
wSalesRec.Yearly_item = 0
wSalesRec.Record_Type = 1
wSalesRec.Record_Type_Name = "Sales"
wSalesRec.Description = Wrec.Description
record_count = record_count + 1
sbUpdateSummaryProfitnLossTableRecord(Account_Summary_Name, wSalesRec, record_count)
Else
End If
End If
TotMonthly_Item = TotMonthly_Item + ProfitnLossRec.Monthly_item
TotYearly_Item = TotYearly_Item + ProfitnLossRec.Yearly_item
record_count = record_count + 1
If ProfitnLossRec.AccountNo = 53410 Then
record_count = record_count
End If
If Run_Select = 3 Then
ProfitnLossRec.Branch_Name = ""
End If
sbUpdateSummaryProfitnLossTableRecord(Account_Summary_Name, ProfitnLossRec, record_count)
End While
Branch_Name = ProfitnLossRec.Branch_Name
RecordTypeSet.Close()
RecordTypeCommand.Dispose()
RecordTypeConnection.Close()
ReturnValues.record_count = record_count
ReturnValues.Monthly_Total = TotMonthly_Item
ReturnValues.Yearly_total = TotYearly_Item
ReturnValues.Branch_Name = Branch_Name
PL_Return = ReturnValues
Exit_sbBuildRecordTypeProfitnLossTable:
Exit Sub
<microsoft.sqlserver.server.sqlprocedure()> _
Public Shared Sub sbUpdateSummaryProfitnLossTableRecord(ByVal ProfitnLossName As String, _
ByVal Wrec As Profit_Loss_Summary_Record, _
ByVal record_count As Long)
Dim sql As String
Dim crlf As String = ""
Dim Quote As String = ""
Dim SummaryProfitConnection As SqlConnection
Dim SummaryProfitCommand As SqlCommand
Dim SummaryProfitReader As SqlDataReader
On Error GoTo err_sbUpdateSummaryProfitnLossTableRecord
If fnProfitNLossNotZeroRecord(Wrec) Then
crlf = Chr(13) & Chr(10)
Quote = Chr(39)
sql = ""
sql = sql & "Insert INTO " & ProfitnLossName & crlf
sql = sql & "(" & crlf
sql = sql & " Record_Index" & crlf
sql = sql & ", Record_Type" & crlf
sql = sql & ", Record_Type_Name" & crlf
sql = sql & ", Branch_Name, Department" & crlf
sql = sql & ", AccountNo" & crlf
sql = sql & ", Description" & crlf
sql = sql & ", Monthly_Item" & crlf
sql = sql & ", MonthlyPercent" & crlf
sql = sql & ", Yearly_Item" & crlf
sql = sql & ", YearlyPercent" & crlf
sql = sql & ", LastEffectiveDate" & crlf
sql = sql & ", Asofdate" & crlf
sql = sql & ")" & crlf
sql = sql & "Values ( " & crlf
sql = sql & record_count & crlf
sql = sql & "," & Quote & Wrec.Record_Type & Quote & crlf
sql = sql & "," & Quote & Wrec.Record_Type_Name & Quote & crlf
sql = sql & "," & Quote & Wrec.Branch_Name & Quote & crlf
sql = sql & "," & Quote & Wrec.Department & Quote & crlf
sql = sql & "," & Quote & Wrec.AccountNo & Quote & crlf
sql = sql & "," & Quote & Wrec.Description & Quote & crlf
sql = sql & "," & Quote & Wrec.Monthly_item & Quote & crlf
sql = sql & "," & Quote & Wrec.MonthlyPercent & Quote & crlf
sql = sql & "," & Quote & Wrec.Yearly_item & Quote & crlf
sql = sql & "," & Quote & Wrec.YearlyPercent & Quote & crlf
sql = sql & "," & Quote & Wrec.LastEffectiveDate & Quote & crlf
sql = sql & "," & Quote & Wrec.AsofDate & Quote & crlf
sql = sql & ");" & crlf
End If
SummaryProfitConnection = New SqlConnection("context connection = true")
SummaryProfitConnection.Open() "ABORTED HERE without SQl Attribute. Cannot get here with it!!"
SummaryProfitCommand = New SqlCommand(Sql, SummaryProfitConnection)
SummaryProfitReader = SummaryProfitCommand.ExecuteReader()
SummaryProfitCommand.Dispose()
SummaryProfitConnection.Close()
exit_sbUpdateSummaryProfitnLossTableRecord:
Exit Sub
err_sbUpdateSummaryProfitnLossTableRecord:
fnHandleError("sbUpdateSummaryProfitnLossTableRecord")
Resume exit_sbUpdateSummaryProfitnLossTableRecord
End Sub
rkj
|
|
|
|
|
reykentj wrote: "ABORTED HERE without SQl Attribute. Cannot get here with it!!"
It "aborted" with what error message?!! Do you actually want help? This is the third time you've been asked for the error message. Why are you being so evasive?
I'd love to help, but I'm not going to read through the code looking for the cause of an error until I know what error I'm supposed to be looking for.
|
|
|
|
|
Running the code last provided
"Error The context connection is already in use. occurred in Module: sbUpdateSummaryProfitnLossTableRecord"
"Error 5 occurred in Module: sbUpdateSummaryProfitnLossTableRecord" was obtained from my error handling
I captured the following output.
A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll
A first chance exception of type 'System.IO.FileNotFoundException' occurred in Clrtest
I think I am trying to use MARS in this environment. Is this even possible?
rkj
-- modified at 0:33 Sunday 15th April, 2007
|
|
|
|
|
Hello,
[VS 2005]
I have created a user control that has a tab control, text boxes, combo boxes. I have a typed dataset with some datatables and tableAdapters for doing the inserts and the delete, and updates.
I build the control and when I want to use this control in another project.
1) open a new windows form project
2) Create a new tab on the tool box
3) Choose items
4) Browse to the dll in the release folder.
My question when I add the dll it also adds the typed dataset, table adapters. I have done something wrong as I think it should be the control itself and not the table adapters and the dataset. As when I click ok with the above selected it add them to my toolbox.
Can anyone explain about this, and if I have done done something wrong.
Many thanks for your help,
Steve
|
|
|
|
|
How to get or to collect all objects or controls in a form using vb.net? For example of controls are Textbox, Combobox and check box.
|
|
|
|
|
Every form has a Controls collection. This is where you'll find all the controls on the form. There's a little bit of a "gotcha" though. Controls can have Control collections of their own, so you can end up having controls inside other controls, like RadioButtons in a GroupBox or Panel.
You can enumerate the controls like this:
For Each c As Control In Me.Controls
If TypeOf c Is Panel Then
Dim tb As Panel = DirectCast(c, Panel)
' do whatever you want with the Panel you found
End If
Next
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
I'm trying to create password protected zip file from my vb.net application. But could not Is there anyway or component (especially free) which creates password protected zip files.
Thanks.
|
|
|
|
|
Dim ZipFile As ChilkatZip2<br />
<br />
Private Sub Command1_Click()<br />
<br />
' Get the zip file name<br />
CommonDialog1.ShowOpen<br />
CommonDialog1.CancelError = True<br />
SourceFile.Text = CommonDialog1.FileName<br />
<br />
End Sub<br />
<br />
Private Sub Command2_Click()<br />
<br />
' Create a ChilkatZip object<br />
Set ZipFile = New ChilkatZip2<br />
ZipFile.UnlockComponent UnlockCode.Text<br />
ZipFile.NewZip OutputZip.Text<br />
<br />
' To create a WinZip-compatible password-protected Zip, simply<br />
' set the PasswordProtect property to 1, and set a password.<br />
' To unzip, WinZip will prompt for this password.<br />
ZipFile.PasswordProtect = 1<br />
ZipFile.SetPassword "secret"<br />
<br />
' Add the disk file to the Zip object<br />
ZipFile.AppendFiles SourceFile.Text, 0<br />
<br />
' See if anything was added.<br />
If (ZipFile.NumEntries = 0) Then<br />
ZipStatus.Caption = "Error, no files added to the Zip archive."<br />
Set ZipFile = Nothing<br />
Exit Sub<br />
End If<br />
<br />
' Compress and write to disk.<br />
success = ZipFile.WriteZip()<br />
If (success = 0) Then<br />
MsgBox ZipFile.LastErrorText<br />
Exit Sub<br />
End If<br />
<br />
' How much compression occured?<br />
Dim e As ChilkatZipEntry2<br />
Set e = ZipFile.GetEntryByIndex(0)<br />
If (Not (e Is Nothing)) Then<br />
origSize = e.UncompressedLength<br />
compressedSize = e.CompressedLength<br />
ZipStatus.Caption = "Success." + vbCrLf + Str(origSize) + " bytes compressed to " + Str(compressedSize) + " bytes"<br />
Set e = Nothing<br />
End If<br />
<br />
ZipFile.CloseZip<br />
<br />
Set ZipFile = Nothing<br />
<br />
End Sub<br />
<br />
Private Sub Form_Load()<br />
OutputZip.Text = CurDir$ + "\outputFile.zip"<br />
<br />
End Sub<br />
Regards,
Satips.
|
|
|
|
|
The code is nice and all, but where's he going to get the ChilkatZip2 class library from?
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
You can use the #ZipLib to do this. Find it here[^].
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
hi to alll
i need to blink the label in vb.net
plz help !!!!!!!!!!!
thanks in Advance;)
|
|
|
|
|
devkranth wrote: Very Urgent
No, it's not. Very Urgent is having to pee very badly. Hence, the icon for the Pee Pee Dance, ![Jig | [Dance]](https://codeproject.freetls.fastly.net/script/Forums/Images/jig.gif) .
devkranth wrote: i need to blink the label in vb.net
You'll have to create a custom label control for this, easily done by inheriting from the existing Label class. You'll need to add a Timer to your control, and a couple of properties and methods so you can set the blink rate and start and stop the blinking. Then it's just a matter of making the foreground color match the background color and changing it back to what it's supposed to be on every timer tick.
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
Use a timer to set the Visible property of the label to either true or false.
Regards,
Thomas Stockwell
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Visit my homepage Oracle Studios[ ^]
|
|
|
|
|
I have a specified printer that only can print paper with 78mm of width. So how to resize the width of report to fix in that printer.
Help me please or send solution to my email address:
Thanhtc249@yahoo.com.vn
thanks.
|
|
|
|
|
I am using the following code to list the accounts of my operating system.
Currently i have just two accounts namely
1.Administator
2.Guest
But it shows 6 accounts.
can i filter the query to make it to show the actual accounts.
Dim oQuery As New Management.ObjectQuery("select * from <br />
Win32_Account WHERE SIDType = 1")<br />
Dim osearch As New ManagementObjectSearcher(oQuery)<br />
Dim ocollection As ManagementObjectCollection = osearch.Get<br />
Dim oresult As ManagementObject<br />
For Each oresult In ocollection<br />
TextBox1.Text = oresult("Name").ToString & " "<br />
Next
Thank You
Pankaj
|
|
|
|
|
I think it is showing the real accounts, with the `hidden` accounts, such as the help and support account, if you have VMWare installed, __vmware_user__ and other things.
You could try:
For Each oresult In ocollection
Dim oname As String = oresult("Name").ToString
If Not oname.StartsWith("SUPPORT") Then
If oname <> "__vmware_user__" Then
If oname <> "ASPNET" Then
If oname <> "HelpAssistant" Then
textBox1.Text = oname & " "
End If
End If
End If
End If
I don't know if this'll work very well.
|
|
|
|
|
hey there,
i am working in an organisation that uses foxPro for its DB needs. We are trying to convert it to SQL. how do i do that? also is there a possibility to develop a VB (not .Net) appln that uses foxpro as its back end? If there is please give me a sample of that code or where i could find it. Please some help me out with some insights.
signing off is preci
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
