Solved

dataviewGrid in vb.net

Posted on 2016-07-28
8
34 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 69

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
 

Author Comment

by:PeterBaileyUk
ID: 41734689
now the interface
ee.JPG
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 69

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now