Creating query based on multiple combo boxes

[Shrimpersfan]

Hi there.

 

I have a form with 3 combo boxes on, each with a random number of items in them.

 

What I am trying to do is to let the user select items from the combo boxes and then generate the sql query based on what they had selected, i would then want to display the results of this query in a datagrid view.

 

I am fairly sure i will need to use a sproc to pass parameters for the fields i will need from the app to the database but i am pretty much a vb novice and have only recently started using sql connections through it.

 

Any help would be greatly appreciated.

[irsprint84]

I know how to do it but building new IT suite at the moment so will get back to you later, PM me (After work)

[sahmeepee]

Are you making a windows application in VB or a web-based asp/asp.net app in vbscript/vb.net?

[Shrimpersfan]

I am making a windows app in vb.net and am using ADO.net for my sql connections.

[irsprint84]

oh.... I only know how to do it with asp.net with VB.net

[Jay]

Try something like the below and bind to a gridview/datagrid.

 

(from memory, so untested)

 

Function getFoo() As DataSet

 

Dim ds As New DataSet

 

Dim oConn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("Foo_Connection").ConnectionString)

 

Dim oComm As New SqlCommand

With oComm

.Connection = oConn

.CommandText = "FooDB.dbo.sp_Get_My_Foo"

.CommandType = CommandType.StoredProcedure

End With

 

oComm.Parameters.Add("@Param1", Data.SqlDbType.NVarChar).Value = sParam1

oComm.Parameters.Add("@Param2", Data.SqlDbType.NVarChar).Value = sParam2

oComm.Parameters.Add("@Param3", Data.SqlDbType.NVarChar).Value = sParam3

 

Dim DA As New SqlDataAdapter(oComm)

Try

DA.Fill(ds)

Catch ex As Exception

Throw

Return Nothing

Finally

If Not (oConn.State = ConnectionState.Closed) Then oConn.Dispose()

End Try

 

Return ds

End Function

[Shrimpersfan]

Thanks Jay.

 

I can see how this may work. How would i get the results of the sproc to appear in a datagrid view?

[Pashers]

Thanks Jay.

 

I can see how this may work. How would i get the results of the sproc to appear in a datagrid view?

 

I think the datagrid view can do a .requery?

[Jay]

GridView.DataSource = getFoo()

GridView.DataBind()

 

 

Normally I would pop the above in some sort of function I can call whenever I need to refresh the contents of the gridview. That way I can either:

 

1) Implement some custom paging/sorting/searching etc using parameters to only present the results I want back from the database.

 

2) I can use the built in (dot net 2) paging/sorting/searching functions without going back to the database at all at the cost of a larger initial query (entire results set) .

 

With both appropaches you have to get the data back from the database once its been changed. (particularly in a multi-user system)