Filter records

[RobinTheHood]

Hi all

I previously developed in Access and have recently decided to move over to SQL server back-end and vb2008 front-end.

My Everest of problems which I can’t solve is the how to filter records by a user selected parameter. However, if the parameter is null then all records should be shown.

In Access I could do this quit easily within a query, based on table TblOffice, by using

(((TblOffice.Site)=[Forms]![Main Menu]![CmbSite])) OR ((([Forms]![Main Menu]![CmbSite]) Is Null))

Currently, as before, I have my combo box ‘CmbSite’ on my form ‘MainMenu’, and a table named TblOffice that contains a field ‘Site’

As all records, that’ll be accesses from various forms within the application, will be based on this filter it’s best to have the records filtered at the server side rather then dragging everything down to a typed dataset.

1st question
Am I right in thinking this filter should be held as an SQL View which subsequent tables in datasets are related to?

2nd question
I’m not sure if SQL allows pointing directly to an object on a form, so the biggy for me is what’s the equivalent code to (((TblOffice.Site)=[Forms]![Main Menu]![CmbSite])) OR ((([Forms]![Main Menu]![CmbSite]) Is Null)) a small step by small step idiot proof guide would be the icing on the cake for me.

I realise that maybe I’m asking for a little too much here but any thoughtfull pointers to the right direction would also be great start.

Many thanks
RobinTheHood

[samir_ibrahim]

For your first question, and it is my own opinion, I prefer that you create a view on the SQL Server side, and call this view whenever you need it. (at least less coding)

For your second question regarding filter. That is a big question and cannot be answered directly unless you shrink it :)

It depend on how you connect to the SQL Server using (Dataset (design or coding), SQLClient.Command Object, or LINQ)

So what do use?

about your question if the query can be combined with the form control as you type it, AFAIK the answer is no, but you could combine the form control + query in the query string, for ex:

_sql = "Select * from mytable where field1 = " + text1.text

hth

[RobinTheHood]

Hi samir_ibrahim,

Thanks for replying.

I'm not entirely sure how to break the 2nd question down into smaller parts.

I'm using typed Datasets and the connection, from my application settings, is;

Name = TESTDBSQLConnectionString1

Type = Connection String

Scope = Application

Value =
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

Is this the correct way to connect? I'm assuming at deployment time all I'll have to do is change the value setting.

In relation to
_sql = "Select * from mytable where field1 = " + text1.text
How would I handle the issue of selecting all records if text1.text was blank (Null).

Kind regards

[samir_ibrahim]

I am still confused of what is your requirement and especially from mentioning "typed DataSet" (maybe it is me)

Any way, this is (my preferable way) how to connect to SQL Server,
' VS 2010 + MSSQL 2008

Dim _cnn As New SqlClient.SqlConnection("Data Source=server_name;Initial Catalog=database_name;User Id=user_name;Password=password;")

' Establiosh connection
_cnn.Open()
' Test if the connection is ok
Debug.Print(_cnn.State)

' We will use this to make loop though the result that will came after executing the query
Dim _dr As SqlClient.SqlDataReader

Dim _cmd As New SqlClient.SqlCommand

      
_cmd.Connection = _cnn
_cmd.CommandText = "select * from mytable where field1 = " & TextBox1.Text

_dr = _cmd.ExecuteReader '<-- Execute the query
While _dr.Read '<-- loop through the result
    Debug.Print(_dr("field1"))
End While

Now the above example does not check if the textbox is empty or null, to cover that you have multiple option.
1- create SP (store prpcedure) at the sql server and handle the null there http://www.eggheadcafe.com/conversation.aspx?messageid=33535029&threadid=33535026
CREATE PROCEDURE
2- assign a default value for the parameter in case of empty

3- check the Null by If

If TextBox1.Text = Nothing Then
    _cmd.CommandText = "select * from mytable "
Else
    _cmd.CommandText = "select * from mytable where field1 = " & TextBox1.Text
End If

Finally, if the above connection string for sql does not work on sql2005, get the connection string from here
Connection Strings

[RobinTheHood]

Many Thanks samir_ibrahim,

I'm new to VS, SQL Server, vb2008 .... so I don't think it's you, just my inability to translate my problem, and get my head around connections strings. In Access it was simpler I just linked my table to the back-end (once) and away I went without worry. I know this disconnected method is going to get me later as at some stage (I recon 12 or so months down the line) I'll be deploying my app on a network, but I'll tackle that issue when I have a better understanding.

Currently every time I create a dataset in VS I'm prompted for a connection string. I just click 'use current setting' which seem to do the trick.

I'm going to play around with your suggestion later today and will hopefully get to where I'm heading, although I think it's going to be a very curvy path.

Many thanks for your unselfish assistance and time.

[samir_ibrahim]

You are very welcome :)

I suggest that you use the DataSet in design time in the current time. It is hard to do work with VB.Net alone, how about working with SQL Server as well, and both of them are new to you.

I know SQL Server before I get with VB.Net so that makes things a little easier because I know what I want except I need to know how to do it in vb.net instead of VFP.

Press: View >> server explorer
Press: Data >> show data sources

in the server explorer you can create the connection to the sql server (or any db backend)

in the data sources you can create Dataset depending on the connection you created in the server explorer.

Currently every time I create a dataset in VS I'm prompted for a connection string

I guess that is related to how you create the the connection.

Can you post step by step how you are creating the DataSet?