Display Read-only Data

[The Real Snuffles]

Hello all,

I would like to be able to directly bind data from a query to a datagrid. See I don't need to update or modify this data so I don't believe creating a dataset is necessary.

I originally tried to execute three seperate subs to get the data that I needed, but unfortunately, my code breaks on sub "Top25byOccurrence" and "Top10byAmount".

My error message is a descriptive "Invalide syntax near ")"" and is referring to the MyDataAdapter.Fill(DS, "Top 25") or MyDataAdapter.Fill(DS, "Top 25")

Any help is appreciated

Public Class Form1
    Public MyConnString As String
    Dim ds As DataSet = New DataSet




    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        
        'CnStr ="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf";Integrated Security=True;User Instance=True"
        MyConnString = "Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True;"
        'C:\Documents and Settings\All Users\Documents\EY Global Analytics\
        InsertMyData()
    End Sub

    Public Sub InsertMyData()

        Dim mySelectQuery As String = "Select Name FROM sys.databases Where Name Not Like '%IMPORT%' AND Name Not Like '%DENORM%' AND Name Not Like 'JEAnalysisMaster' AND Name Not Like 'Master' AND Name Not Like 'Model' AND Name Not Like 'MSDB' AND Name Not Like 'tempdb'"
        Dim myConnection As New SqlConnection(myConnString)
        Dim myCommand As New SqlCommand(mySelectQuery, myConnection)

        myConnection.Open()


        Dim myReader As SqlDataReader
        myReader = myCommand.ExecuteReader()

            '' Always call Read before accessing data.

        While myReader.Read()
            cmbDBListing.Items.Add(myReader("Name"))
            End While

            '' always call Close when done reading
            'cmbDBListing.DataSource = myReader
        myReader.Close()
            '' Close the connection when done with it.
        myConnection.Close()



    End Sub 'ReadMyData

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DBtoHit As String
        DBtoHit = cmbDBListing.SelectedItem
        If DBtoHit = "" Then
            'do nothing for now
        Else
            MyConnString = "Data Source=.\SQLEXPRESS;Initial Catalog=" & DBtoHit & ";Integrated Security=True;"
            Dim mySelectQuery As String = " declare @pop numeric(18,2) " & _
            "declare @zstat numeric(18,2) " & _
            "set @pop = (select count(JEEntryKey) from LineItems Where Amount <> 0)" & _
            "set @zstat = 2.57 " & _
            "select sub2.LeadDigits, Sub2.Actual, round(Sub2.Expected*@pop,0) as Expected," & _
               "round((abs((sub2.Actual/@pop)-sub2.Expected))/sqrt((sub2.Expected*(1-sub2.Expected)/@pop)),3)as ZStat," & _
               "round((sub2.expected-(@zstat*sqrt(sub2.expected*(1-sub2.expected)/@pop)))*@pop,0) as LowerBound," & _
               "round((sub2.expected+(@zstat*sqrt(sub2.expected*(1-sub2.expected)/@pop)))*@pop,0) as UpperBound " & _
            "from " & _
             "(select sub1.LeadDigits,sub1.Actual,((log10(sub1.LeadDigits+1))-(log10(sub1.LeadDigits))) as Expected from " & _
              "(select left(abs(Amount)*1000,2)as LeadDigits, count(JEEntryKey) as Actual " & _
               "from LineItems " & _
               "Where AMOUNT <> 0 " & _
               "group by left(abs(Amount)*1000,2) " & _
              ")as sub1 " & _
             ")as sub2 " & _
            "order by sub2.LeadDigits "

            Dim myConnection As New SqlConnection(MyConnString)
            Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
            Dim MyAdapter As New SqlDataAdapter(myCommand)

            myConnection.Open()

            MyAdapter.Fill(ds, "Benford")

            myConnection.Close()

            dgvBenfordTable.DataSource = ds
            dgvBenfordTable.DataMember = "Benford"


            '' Close the connection when done with it.
        End If


    End Sub

    Private Sub btnAnalyzeDigits_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnalyzeDigits.Click
        Top25ByOccurrence()
        'Top10ByAmount()
    End Sub

    Sub Top25ByOccurrence()
        Dim DigitToAnalyze As String = txtLeadingDigits.Text

        Dim DBtoHit As String
        DBtoHit = cmbDBListing.SelectedItem
        If DBtoHit = "" Then
            'do nothing for now
        Else
            MyConnString = "Data Source=.\SQLEXPRESS;Initial Catalog=" & DBtoHit & ";Integrated Security=True;"
            Dim mySelectQuery As String = "Select Top 25 abs(Amount) as [$ Amount], Count(amount) as [Number of Occurrences], Sum(abs(amount)) as [Total $ Amount] From(LineItems)Where Left(abs(Amount*1000),2) ='" & DigitToAnalyze & "' And Amount <> 0 Group By Abs(amount) Order By Count(Amount) DESC;"

            Dim myConnection As New SqlConnection(MyConnString)
            Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
            Dim MyAdapter As New SqlDataAdapter(myCommand)

            myConnection.Open()

            MyAdapter.Fill(ds, "Top25")
            '' Close the connection when done with it.
            myConnection.Close()

            dgvTop25.DataSource = ds
            dgvTop25.DataMember = "Top25"



        End If

    End Sub

    Sub Top10ByAmount()
        Dim DigitToAnalyze As String = txtLeadingDigits.Text
        Dim DBtoHit As String
        DBtoHit = cmbDBListing.SelectedItem
        If DBtoHit = "" Then
            'do nothing for now
        Else
            MyConnString = "Data Source=.\SQLEXPRESS;Initial Catalog=" & DBtoHit & ";Integrated Security=True;"
            Dim mySelectQuery As String = "Select Top 10 Amount, JENumber, GLAccountNumber, JEDescription, EntryDate, EffectiveDate, Period, PreparerID, Source, BusinessUnit From(LineItems Where Left(abs(amount*1000),2)='10' And Amount <> 0 Order By abs(amount*1000) DESC;"
            Dim ds1 As DataSet = New DataSet
            Dim myConnection As New SqlConnection(MyConnString)
            Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
            Dim MyAdapter As New SqlDataAdapter(myCommand)
            Dim ds2 As DataSet = New DataSet
            myConnection.Open()

            MyAdapter.Fill(ds2, "Top10")

            myConnection.Close()

            dgvTop10.DataSource = ds2
            dgvTop10.DataMember = "Top10"


            '' Close the connection when done with it.
        End If

    End Sub

 

[cjard]

I would like to be able to directly bind data from a query to a datagrid. See I don't need to update or modify this data so I don't believe creating a dataset is necessary

There's a logical disconnect in your thought processes; you dont use datasets only when you want writable data, and using datasets doesnt make data writable by default

You should still use datasets: Use datasets whenever you want to downlaod data and show it to the user, maybe edit it and return it. Use readers when you want a one way flow of info that is too huge to read into RAM all at once, e.g. downloading 4 gig of data from a table, for writing to a text file. THEN you would use a reader.

Go the dataset route (Follow the advice in the DW2 link of my signature, section Creating a simple data app) and simply make the data grid read only:

myTableAdapter.Fill(myDataDet.MyDataTable)
myDataGridView.ReadOnly = True 'myDataGridView is already bound to MyDataTable

The readonly property can be set in design mode property grid too