Solved

dataviewGrid in vb.net

Posted on 2016-07-28
8
30 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

13 Experts available now in Live!

Get 1:1 Help Now