|
Hi,
I am adding two columns in "payments" table in database. Column A & B. These two are added and updated in Column "Total Payment". The code I wrote is below, its not updating the sum of A & B in column "Total Payment":
cmd = New SqlCommand("Select * FROM payments", con)
If con.State = ConnectionState.Closed Then con.Open()
myDA = New SqlDataAdapter(cmd)
Dim myDataFinalPaySet As New DataSet()
myDA.Fill(myDataFinalPaySet, "payments")
Dim TotalPaymentValue As Double
Dim TaxablePayment As Double
For Each dr As DataRow In myDataFinalPaySet.Tables(0).Rows
Dim ValA As Double = dr("A")
Dim ValB As Double = dr("B")
TotalPaymentValue = ValA + ValB
If con.State = ConnectionState.Closed Then con.Open()
cmd = New SqlCommand("UPDATE payments SET [Total Payment]= '" & TotalPaymentValue & "'", con)
cmd.ExecuteNonQuery()
cmd = New SqlCommand("Select * FROM payments", con)
myDA = New SqlDataAdapter(cmd)
myDA.Fill(myDataFinalPaySet, "payments")
DataGridPayment.Update()
con.Close()
Next
Can Someone help me onthis?
|
|
|
|
|
What are you trying to do here? You are reading an entire table, Looping through the rows and updating the entire table with the sum. This is absurd, since it will update the TotalPayments column of all rows with the sum of the last row. I believe what you are trying to acheive can be done using just one statment:
cmd = New SqlCommand("UPDATE payments SET [Total Payment]= Payment1 + Payment2", con)
cmd.ExecuteNonQuery()
|
|
|
|
|
Yeah, You are right. I will try Your solution. Thanks.
|
|
|
|
|
My column names are "A" and "B". Can I directly use ur command:
cmd = New SqlCommand("UPDATE payments SET [Total Payment]= A + B", con)
cmd.ExecuteNonQuery()
|
|
|
|
|
|
And why update the total payment column, afterward, instead of right away during the insert?
My advice is free, and you may get what you paid for.
|
|
|
|
|
During Insert, I dont have values for TotalPayment Column. I am calculating at later stage and then finally updating the TotalPayment Column.
|
|
|
|
|
Well obviously not, but supposedly you do have the values of column A and B.
So when you do the insert INSERT INTO YourTable (A, B) Values(100, 150) you might as well do INSERT INTO YourTable (A, B, TotalPayment) Values(100, 150, 100 + 150) .
Or is the TotalPayment column supposed to hold the total sum of all rows?
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi there,
I have the following code which takes a table from SQL does some calculations on it and adds a couple of columns...
I'm having issues with sorting the data (descending) on Column "N" from Row 4 to row 50000 (the end of the document) the code I think should work appears to reorder the columns by the entries in row 4.
Any advice gratefully received....
<pre>
Dim SQlQuery2 As String = "select * from growth where Name <> '[Files]'AND InitialSize <> 0 order by FullPath"
Dim SQLCommand2 As New SqlCommand(SQlQuery2, myConn)
Dim SQlReader2 As SqlDataReader = SQLCommand2.ExecuteReader
Dim R2 As Integer = 3
While SQlReader2.Read
R2 = R2 + 1
osheet.Range("I" & R2).Value = SQlReader2.GetValue(0).ToString
osheet.Range("I" & R2).BorderAround(8)
osheet.Range("J" & R2).Value = SQlReader2.GetValue(1).ToString
osheet.Range("J" & R2).BorderAround(8)
osheet.Range("K" & R2).Value = SQlReader2.GetValue(2).ToString
osheet.Range("K" & R2).BorderAround(8)
osheet.Range("L" & R2).Value = SQlReader2.GetValue(3).ToString
osheet.Range("L" & R2).BorderAround(8)
End While
SQlReader2.Close()
SQlReader2 = Nothing
Dim D1 As Integer = 4
Do Until D1 = R2 + 1
osheet.Range("M" & D1).Formula = "=(K" & D1 & ")-(L" & D1 & ")"
osheet.Range("M" & D1).BorderAround(8)
osheet.Range("N" & D1).Formula = "=((M" & D1 & ")/(L" & D1 & "))*100"
osheet.Range("N" & D1).BorderAround(8)
D1 = D1 + 1
Loop
osheet.Range("I4:N50000").Sort(Key1:=osheet.Range("N4:N50000"))
</pre>
|
|
|
|
|
I am not sure, but considering that you are telling excel to do just that in the last line of your code, what is it that you wanted to happen?
My advice is free, and you may get what you paid for.
|
|
|
|
|
ok, what "should" happen is that the range of values from I4 to N50000 should be sorted in descending order by the numerical values in Column N, in this case from N4 to N50000 (As N1, N2 and N3 have text in them...)
osheet.Range("I4:N50000").Sort(Key1:=osheet.Range("N4:N50000"))
I thought that this would do that, but it clearly doesn't but I don't see what I've done wrong. Can you help??
|
|
|
|
|
I'm sorry, but you are not entirely making sense to me...
nhsal69 wrote: should be sorted in descending order by the numerical values in Column N
nhsal69 wrote: N1, N2 and N3 have text in them
Which one is it, text or numbers, or both?
Never mind, sorry, I didn't quite read it right.
If this doesn't do what you want it do to, then what is it doing, if anything?
My advice is free, and you may get what you paid for.
modified on Thursday, November 12, 2009 6:32 AM
|
|
|
|
|
N1, N2 and N3 have text in them
[quote]
Which one is it, text or numbers, or both?
[/quote]
It's both the first 3 rows contain text which should be ignored (the headers), the rest of the rows in the column contain numbers which need to be sorted in descending order.
This process should sort columns I, J, K, L, M by the numerical values in N from largest value in column N to the smallest
What the code:
osheet.Range("I4:N50000").Sort(Key1:=osheet.Range("N4:N50000"))
Appears to do, is sort the columns I, J, K, L, M, N in ascending order by the value in each column in row 4.
So for example the contents of column I move to column J because I4 has a larger value in it than J4
Does this now make sense to you??
|
|
|
|
|
So if I understand you correctly, excel is now sorting the range by column instead of rows.
If that is what is happening I think you'll have to play around with how the ranges are determined in that last sentence.
My advice is free, and you may get what you paid for.
|
|
|
|
|
I got this to work for me (Excel 2007):
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer = 1
Dim tmpConn As New SqlConnection(connStrDBHERFI)
Dim tmpComm As SqlCommand = Nothing
Dim tmpRdr As SqlDataReader = Nothing
Try
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
Me.Cursor = Cursors.WaitCursor
If Not tmpConn.State = ConnectionState.Open Then tmpConn.Open()
tmpComm = New SqlCommand("Select cardcode, cardname from ocrd where 'y' not in (FrozenFor, ValidFor)", tmpConn)
tmpRdr = tmpComm.ExecuteReader(CommandBehavior.Default)
If tmpRdr.HasRows Then
Do While tmpRdr.Read
xlWorkSheet.Cells(i, 1) = tmpRdr.Item("CardCode")
xlWorkSheet.Cells(i, 2) = tmpRdr.Item("CardName")
i += 1
Loop
With xlWorkSheet.Range("A1", "B" & i)
'.BorderAround()'
.Sort(Key1:=.Columns(2), Order1:=Excel.XlSortOrder.xlAscending, _
Key2:=.Columns(1), Order2:=Excel.XlSortOrder.xlAscending, _
Orientation:=Excel.XlSortOrientation.xlSortColumns, _
Header:=Excel.XlYesNoGuess.xlNo, _
SortMethod:=Excel.XlSortMethod.xlPinYin, _
DataOption1:=Excel.XlSortDataOption.xlSortNormal, _
DataOption2:=Excel.XlSortDataOption.xlSortNormal, _
DataOption3:=Excel.XlSortDataOption.xlSortNormal)
End With
End If
tmpRdr.Close()
tmpConn.Close()
tmpConn.Dispose()
xlWorkSheet.SaveAs("c:\Temp\test.xlsx")
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
Catch ex As Exception
MsgBox(ex.ToString)
Finally
Me.Cursor = Cursors.Default
End Try
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
My advice is free, and you may get what you paid for.
|
|
|
|
|
I'll thank you because now that you've done his work for him, he's got better things to do.
|
|
|
|
|
Thanks...
Fortunately, this was something I have use for myself, and I too got it almost entirely the old fashioned Google-and-Paste way.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Just logged in again...
Ta for that.... I'm just using the
"With xlWorkSheet.Range("A1", "B" & i).Sort(Key1:=.Columns(2), Order1:=Excel.XlSortOrder.xlAscending"
with my information:
osheet.Range("I4", "N10").Sort(Key1:=osheet.Columns("Current Size"), Order1:=Excel.XlSortOrder.xlAscending)
But unfortunatly get the following error:
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
when compiling my code..
If I try the following line (substituting "Current Size" to the first cell of the sort column "N4":
osheet.Range("I4", "N10").Sort(Key1:=osheet.Columns("N4"), Order1:=Excel.XlSortOrder.xlAscending)
I get the following error
Exception from HRESULT: 0x800A03EC
Have you any idea why?? Is this a VB error, an Excel one or a formatting one??
Thanks in advance....
|
|
|
|
|
To be perfectly honest, creating excel sheets using interop is an absolute development nightmare. For one, if the user does not have excel installed, your application will not work. The error you described is one of many similar ones I have seen when I tried to do it with interop (that's what is throwing the error). The problem is, the syntax changes with each different version of excel, so even if you finally get it to work, you can start over as soon as MickeySoft decides to market a new Office version.
Personally, I have decided to cut my losses, and I have rebuilt the whole thing using xml. That means I basically build up an xml file in a string and in the end I save the string to an excel file. I can now create excel files containing autofilters, all kinds of formatting, frozen panes, and more, and the resulting file can be opened in any excel version from 97 to 2007.
In other words, I warmly recommend using my approach, and saving yourself a big headache.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hello Experts,
How can i change the process name while the process is running?
Thanks
If you can think then I Can.
|
|
|
|
|
It is not possible to change the name of the currently executing process. Process names are defined by the name file which was loaded into the operating system for execution at the time execution began. You cannot change this at a later point. This is true for C#, managed and native code.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
Ok,
I am working on writing a simple VU Meter in VB.NET (Visual Studio 2008) just for fun and to see if I can. From what I've been able to find online, I need to use DirectSound to do this but I can only find examples on how to play audio in VB.NET using DirectSound. Does anyone know of some tutorials for capturing sound in VB.NET using DirectSound that would help me out?
|
|
|
|
|
Okay, the thing is you need to 'break in' to the filters windows uses to play the sound. To get a clue of what's going on download GraphEdit. Then render the media file (audio) and see what happends!
Graphedit will show you a file source, a decoding filter and a hardware device to send the audio stream to. If you want to create a VU meter, you need to disconnect the line between the filter and your hardware device, and 'insert' your own which fires events about the audio level (input for your VU). And all that is done in code
I recommend taking a look at the DirectShowLib project which has loads of DirectX functions available in .NET. Then try to create a graph in code and do exactly the same as you would do it in GraphEdit, only then in code.
Good luck!
|
|
|
|
|
Why do I feel like I bit off more than I can chew for right now?
Wasn't to hard to write my own Stop Watch but I haven't actually programmed since C++ in college and that was in the 2000-2001 school year.
|
|
|
|
|
Erhm, you may be right... Developing a stopwatch requires less experience than a VU meter. You may want to search the web for a codec (filter) which throws events for you which you can catch... Then you'll leave the fancy develoment stuff to someone else ;
|
|
|
|
|