Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

dataviewGrid in vb.net

Posted on 2016-07-28
8
46 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
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.

 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 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