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.ConfigurationMa nager.ConnectionStrings("Foo_Connection").Connecti onString)

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)