Visual Basic

Re: Dataview .RowFilter = "PartNo = '123' AND Ave(Cost)" gives error

22-Feb-06 21:25

Hi

I'm trying to use a dataview rowfilter to get the Max, Min, and Ave Order Values for a particular PartNo. (I cannot change the base SQL query, since depending on user input, I need to loop though anything from 10 to 1000 rows, and for each row, get the min, max and average - Too many queries over a network??). For this reason, I pull the whole table via SQL, and am trying to use rowfilters to get the desired values for each row

Dataview.RowFilter = "PartNo = '123' AND Ave(Cost)" gives an error similar to this: Cannot perform AND operation on Boolean and Double. I assume the Boolean part is 'PartNo = '123' and the Double would be the Average Cost returned which does make sense.

Any ideas how I could do this??

Basically I want to implement the following SQL using a rowfilter:
SELECT Ave(Cost) FROM PurchaseOrders WHERE PartNo = '123'

Thanks in advance!!

-- modified at 6:01 Thursday 23rd February, 2006

Ritesh123422-Feb-06 23:02

Ritesh1234

22-Feb-06 23:02

Hi,

 Dim dtItemCost As New DataTable("Cost")<br />
<br />
      dtItemCost.Columns.Add(New DataColumn("PartNo", GetType(String)))<br />
      dtItemCost.Columns.Add(New DataColumn("Cost", GetType(Double)))<br />
<br />
      Dim drNewItemCost As DataRow<br />
<br />
      drNewItemCost = dtItemCost.NewRow<br />
      drNewItemCost.Item("PartNo") = "123"<br />
      drNewItemCost.Item("Cost") = 10<br />
      dtItemCost.Rows.Add(drNewItemCost)<br />
<br />
      drNewItemCost = dtItemCost.NewRow<br />
      drNewItemCost.Item("PartNo") = "100"<br />
      drNewItemCost.Item("Cost") = 100<br />
      dtItemCost.Rows.Add(drNewItemCost)<br />
<br />
      drNewItemCost = dtItemCost.NewRow<br />
      drNewItemCost.Item("PartNo") = "123"<br />
      drNewItemCost.Item("Cost") = 30<br />
      dtItemCost.Rows.Add(drNewItemCost)<br />
<br />
      drNewItemCost = dtItemCost.NewRow<br />
      drNewItemCost.Item("PartNo") = "100"<br />
      drNewItemCost.Item("Cost") = 105<br />
      dtItemCost.Rows.Add(drNewItemCost)<br />
<br />
      Dim dblAvgCost As Double<br />
<br />
      dblAvgCost = dtItemCost.Compute("Avg(Cost)", " PartNo = '123'")

I hope this will help u...:->

Regards,
Ritesh

Re: Dataview .RowFilter = "PartNo = '123' AND Ave(Cost)" gives error

RichardBerry22-Feb-06 23:42

22-Feb-06 23:42

Thank you so much Ritesh

Saved me a lot of time!!

Ritesh1234 wrote:
Dim dblAvgCost As Double

dblAvgCost = dtItemCost.Compute("Avg(Cost)", " PartNo = '123'")

I did have to first check that there is actually at least one Item specified by the filter otherwise it throws an exception. (I assume if there are no rows, the Average function would be dividing by Zero - hence an error)

Thanks again - really helpfull

Cheers

Richard

What am I missing here?

Darshon22-Feb-06 20:47

Darshon

22-Feb-06 20:47

Hello all,

This is my project: I've made a TreeView that displays a folder and all sub folders within it. I also have an AfterSelect sub (Shown below) that looks through the selected directory and displays any files in a ListView. All this works.

This is my problem: Although I have an imagelist that is connected to the ListView control, it doesn't show the pictures beside the items when they are listed. All I have is a blank space where the picture should be. And when I try to assign an image to an item through the designer it just ignors them. Probably cause everything I'm doing is through the code view not the designer view. The only thing I can think of is that I'm forgetting to code something to tell the listview to show the images. Can anyone help me out?

This is the only code I've written that touches the listview so far.

Private Sub tvwExplorer_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles tvwExplorer.AfterSelect<br />
        'Get reference to the selected node<br />
        Dim dirInfo As DirectoryInfo = New DirectoryInfo(e.Node.FullPath)<br />
        'Clear all the items in the listview<br />
        lvwExplorer.Items.Clear()<br />
        'Check if the Directory exist or not<br />
        If (dirInfo.Exists) Then<br />
            'Get reference to all the files<br />
            Dim fileInfos As FileInfo() = dirInfo.GetFiles()<br />
            'Add all the files to the ListView<br />
            Dim info As FileInfo<br />
            For Each info In fileInfos<br />
                Dim item As ListViewItem = New ListViewItem<br />
                item = lvwExplorer.Items.Add(info.Name)<br />
                item.SubItems.Add(info.LastAccessTime.ToString())<br />
            Next<br />
        End If<br />
    End Sub

Christian Graus22-Feb-06 20:51

22-Feb-06 20:51

Darshon wrote:
Dim item As ListViewItem = New ListViewItem
item = lvwExplorer.Items.Add(info.Name)

This adds a listview item which does not have an image index. You need to build your list view item so that it contains an image index.

Darshon wrote:
Dim item As ListViewItem = New ListViewItem
item = lvwExplorer.Items.Add(info.Name)

This makes no sense. You create a new one, then you throw it away to be replaced by the one returned by Add.

Christian Graus - Microsoft MVP - C++

Darshon23-Feb-06 4:55

Darshon

23-Feb-06 4:55

Thank you Christian Graus for your comments. I'm fairly new at programing with VB could you give me an example of how you would fix this?

Darshon23-Feb-06 6:21

Darshon

23-Feb-06 6:21

Nevermind! Smile | :)

I got it to work. I downloaded an example project from this site and looked at how s/he assigned an imagelist to an listviewitem and I got it to work on mine. Thanks for your help.

Christian Graus23-Feb-06 10:59

23-Feb-06 10:59

No worries - I just got in and online, but I'm glad you found a solution Smile | :)

Christian Graus - Microsoft MVP - C++

what is this mean?

pandapatin22-Feb-06 20:34

pandapatin

22-Feb-06 20:34

Object reference not set to an instance of an object.
Dim MyConn As New SqlConnection(ConfigurationManager.ConnectionStrings("psmConnectionString").ConnectionString)

how to solve it?

Christian Graus22-Feb-06 20:38

22-Feb-06 20:38

Well, at a guess you need to add psmConnectionString to your config file.

Christian Graus - Microsoft MVP - C++

pandapatin22-Feb-06 20:46

pandapatin

22-Feb-06 20:46

i did put it on the config file.this is the line:

appSettings
add key="psmConnectionString" value="Provider=.NET Framework Data Provider for SQL Server:Data Source=HAPPYFAM-B614E1\SQLEXPRESS;Initial Catalog=psm;Integrated Security=True" appSettings

is it rite?

-- modified at 2:49 Thursday 23rd February, 2006

Christian Graus22-Feb-06 20:50

22-Feb-06 20:50

>>Dim MyConn As New SqlConnection(ConfigurationManager.ConnectionStrings("psmConnectionString").ConnectionString)

What makes you think that the psmConnectionString item in the config manager will have a connection string property ? Why is it in the ConnectionStrings collection ? I didn't know there was such a thing, but assuming that there is, I'd assume that you need to do something specific to get your config item to appear there.

Christian Graus - Microsoft MVP - C++

Guffa23-Feb-06 2:32

Guffa

23-Feb-06 2:32

You have put the value in the appSettings section and try to access it in the connectionStrings section. Either move it to the connectionstrings, or access it using the AppSettings collection.

---
b { font-weight: normal; }

pandapatin23-Feb-06 4:22

pandapatin

23-Feb-06 4:22

when i used the conMyData = New SqlConnection(ConfigurationManager.AppSettings("psmConnectionString")) it has an error.the error is The ConnectionString property has not been initialized. the program stop at Myconn.open().

anything can i do??

Guffa23-Feb-06 9:21

Guffa

23-Feb-06 9:21

You have a colon instead of a semicolon between the two first properties in the connection string.

---
b { font-weight: normal; }

Automation of XML

winpoorni22-Feb-06 20:05

winpoorni

22-Feb-06 20:05

Dear All,,

Can U Help me to Generate the XML document from Excel Worksheet by Using Macro and VBA facility...Excel 2003 professional Edition have an option like XML in Data Menu..I have Excel 2003 Standard Edition...

Please Help me to get the Solution

Thanks n Regards,
Ramya.R

Re: Automation of XML

RichardBerry22-Feb-06 21:44

Generation of XML from Excel---Urgent Help Me Pls

22-Feb-06 21:44

Sub SaveAsXML()

ActiveWorkbook.SaveAs Filename:= "C:\Book3.xml", FileFormat:= _
xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

If you are trying to save the workbook that contains the macro, you will get a message about not being able to save the vbproject, but I assume the Macro will not be in the workbook you are saving as XML?

winpoorni23-Feb-06 0:14

winpoorni

23-Feb-06 0:14

Dear Richard Rose | [Rose]

Thanks For your Reply.
Actually that Worksheet is in .xls format.
I need to Write a Macro that Take the Column value from the Excel Sheet and Generate the Xml File From the particular field... Cry | :((

Thanks n Regards,
Ramya.R

-- modified at 6:36 Thursday 23rd February, 2006

Re: Generation of XML from Excel---Urgent Help Me Pls

RichardBerry23-Feb-06 4:17