Solved

dataviewGrid in vb.net

Posted on 2016-07-28
8
51 Views
Last Modified: 2016-07-30
I have a form see attached, the user makes a selection and i would like the dataview grid to display different data based on the choice made. I have made a number of stored procedures but i dont know how to change the binding to the different sp's.

my grid is named DataGridViewStringDescAll
0
Comment
Question by:PeterBaileyUk
  • 5
  • 2
8 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41734482
as found on http://www.vbforums.com/showthread.php?632123-Filling-a-datagridview-with-the-help-of-a-stored-procedure-variable:
Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand("sproc name here", connection),
      adapter As New SqlDataAdapter(command)
    command.CommandType = CommandType.StoredProcedure
 
    'Add parameters, e.g.
    command.Parameters.AddWithValue("@ParentID", parentID)
 
    Dim table As New DataTable
 
    'Get the data.
    adapter.Fill(table)
 
    'Display the data.
    Me.DataGridView1.DataSource = table
End Using

Open in new window

0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41734485
You did not attach anything, but to bind a DatagridView to a different source, you could do this:

Using con As New SQLConnection(YourConnectionInfoHere)
  con.Open
    Using cmd As New SQLComman
      cmd.connection = con
       Using dt As New Datatable
          dt.Load cmd.ExecuteReader
          MyDGV.Datasource = dt
       End Using
    End Using
End Using
0
 

Author Comment

by:PeterBaileyUk
ID: 41734688
What I found was that I had so many adaptors created that the whole thing fell apart, am I right in thinking that if I create one dataset, which has all the available stored procedures that then I can call which ever stored procedure i want but with only the one dataset.

Ive attached a screen shot where it all fell apart, i think its because i added to mant data sources which here the same thing.

Ive gone back to the start.

you can see the empty grid now with the option box.

thats why i didnt post code as it was more advisory at the moment.
barry.JPG
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PeterBaileyUk
ID: 41734689
now the interface
ee.JPG
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 41734727
TimeOutExpired is because your query takes too long to return (default is max 30 seconds). Can you optimize your query?

or you can try setting no limit:
cmd.CommandTimeout = 0

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41735496
Ok so that's great, that I never knew. So using your 41734482 I can have one grid and call params to different stored procedures as required.
0
 

Author Comment

by:PeterBaileyUk
ID: 41735509
ok I am going in little steps, I have 3 functions but it looks like your function 41734482 can take place of all these 3 but i am not 100% sure how to put in the logic to supply the correct parameter based on the option chosen as you can see in my functions its determining the parameter values in the first if statements of each 3

do I need to put in each of these the  cmd.CommandTimeout = 0 or does that go somewhere else globally.

    Public Function GetBulkDataStringsSuperVehCat() As DataTable
        Dim StrProcName As String
        Dim StrVehSupCat As String

        If OpShortDescCarsLcvOthersAll.Checked = True Then
            StrVehSupCat = "CarLcvOthers"
        End If

        If OpShortDescBikesQuadsAll.Checked = True Then
            StrVehSupCat = "BikesQuads"
        End If

        dtWords = New DataTable
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString


        StrProcName = "usp_GetBulkDescStringsSuper"


        Using conn As New SqlConnection(connectionString)

            'Using cmd As New SqlCommand("usp_GetWords", conn)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                cmd.Parameters.AddWithValue("@VCatSuper", StrVehSupCat)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtWords.Load(reader)
            End Using
        End Using
        Return dtWords
    End Function
    Public Function GetBulkDataStringsVehCat() As DataTable

        Dim StrProcName As String
        Dim StrVehCat As String

        If OpShortDescCarsAll.Checked = True Then
            StrVehCat = "Car"
        End If
        If OpShortDescBikesAll.Checked = True Then
            StrVehCat = "Motorcycle"
        End If
        If OpShortDescLcvAll.Checked = True Then
            StrVehCat = "Lcv"
        End If
        If OpShortDescOthersAll.Checked = True Then
            StrVehCat = "Others"
        End If

        dtWords = New DataTable

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString


        StrProcName = "usp_GetBulkDescStringsVehCat"


        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                'cmd.Parameters.AddWithValue("@Client", StrClient)
                cmd.Parameters.AddWithValue("@VCategory", StrVehCat)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtWords.Load(reader)
            End Using
        End Using
        Return dtWords
    End Function

    Public Function GetBulkDataStrings() As DataTable
        Dim StrProcName As String
        dtWords = New DataTable
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString


        StrProcName = "usp_GetBulkDescStrings"


        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                'cmd.Parameters.AddWithValue("@Client", StrClient)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtWords.Load(reader)
            End Using
        End Using
        Return dtWords
    End Function

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41735686
ive split the points as both responses have fixed the situation.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question