[Paul Washburn]

My program pulls a weeks worth of records at a time and filters it into daily columns on a form, the problem is that there are ~80 fields to be filled in this manner and as far as i know each one has to be check for null before it can be filled. Could someone tell me if there is a faster way to do this? Below is my current code for one set of fields.

Thanks in advance

   Private Sub cboDigit_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDigit.SelectedIndexChanged, cboOffice.SelectedIndexChanged, dtEndDate.ValueChanged, dtStartDate.ValueChanged

		If blnLoad = True Then
			If cboDigit.SelectedIndex <> -1 And cboOffice.SelectedIndex <> -1 Then
				Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= \\Bkesl\VOL2\Common\updates\CaseAdmin\CaseAdmin.accdb")
				conn.Open()

				Dim userDetailsAdapter As New OleDbDataAdapter("SELECT * FROM tblCaseAdmin where Digit =" + cboDigit.Text + " AND Office = '" + cboOffice.Text + "' And Day between #" + dtStartDate.Text + "# and #" + dtEndDate.Text + "#", conn)

				Dim userDetailsDataSet As New DataSet
				userDetailsAdapter.Fill(userDetailsDataSet, "Table1")
				userDetailsAdapter.Dispose()
				Dim userDetailsTable As DataTable = userDetailsDataSet.Tables(0)

				conn.Close()

				'Fill Cases Report
				If userDetailsTable.Rows.Count <> 0 Then
					If userDetailsTable.Rows(0).Item(3) Is DBNull.Value Then
						moncasesreport.Text = ""
					Else
						moncasesreport.Text = userDetailsTable.Rows(0).Item(3)
					End If

					If userDetailsTable.Rows(1).Item(3) Is DBNull.Value Then
						tuecasesreport.Text = ""
					Else
						tuecasesreport.Text = userDetailsTable.Rows(1).Item(3)
					End If

					If userDetailsTable.Rows(2).Item(3) Is DBNull.Value Then
						wedcasesreport.Text = ""
					Else
						wedcasesreport.Text = userDetailsTable.Rows(2).Item(3)
					End If

					If userDetailsTable.Rows(3).Item(3) Is DBNull.Value Then
						thurcasesreport.Text = ""
					Else
						thurcasesreport.Text = userDetailsTable.Rows(3).Item(3)
					End If

					If userDetailsTable.Rows(4).Item(3) Is DBNull.Value Then
						fricasesreport.Text = ""
					Else
						fricasesreport.Text = userDetailsTable.Rows(4).Item(3)
					End If
				End If
			End If
		End If
	End Sub

[krum110487]

So, you are running this code EVERY time?

I would recommend to pull ALL of the data at once into a DataSet and manipulate that, or call ALL of it with a SQL call (with the filter) into the dataset all at once.

thats what I did with my program which has like 750,000 fields.

it runs smooth!

[modi123_1]

A few things..

Don't use select *.
When you name your columns in your SQL select statement use an 'ISNULL' to get put the empty string in for you.

Example..
I have columns: col_a and col_b.

I want to select them and replace any nulls with an empty string.

SELECT
ISNULL(col_a, '') as col_a
,ISNULL(col_b, '') as col_b
FROM <table name>

[jens]

@Modi

Could you please elaborate a little on what this does: ISNULL(col_a, '')

Regards
/Jens

[PsychoCoder]

@jens: ISNULL replaces the null value with a specified value that you provide. Like

SELECT ISNULL(@MyColumn,'IsEmpty') FROM MyTable

If @MyColumn is a NULL value IsEmpty (the stirng) will be returned.

Hope that helps

[Paul Washburn]

PsychoCoder, on 24 Mar, 2009 - 08:49 AM, said:

@jens: ISNULL replaces the null value with a specified value that you provide. Like

SELECT ISNULL(@MyColumn,'IsEmpty') FROM MyTable

If @MyColumn is a NULL value IsEmpty (the stirng) will be returned.

Hope that helps

Just to clarify would you need the ISNULL(@MyColumn,'IsEmpty') for each column you are grabbing data from IE

"SELECT ISNULL(ColumnA,'IsEmpty'), ISNULL(ColumnB, 'IsEmpty') FROM MyTable"

[modi123_1]

Yes. Hence why using the 'select *' is a bad habit.

[Paul Washburn]

modi123_1, on 24 Mar, 2009 - 01:07 PM, said:

Yes. Hence why using the 'select *' is a bad habit.

When I try this method I am getting the following error:
OledbException was unhandled: Wrong number of arguments used with function in query expression 'ISNull(CasesReport,'IsEmpty''.

The error is triggered when it attempts to fill the dataset

The backend database is ms access 2008 if that makes a difference, and i double checked the name from the database.

Have i got something wrong in the syntax?

Thanks

	Private Sub cboDigit_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDigit.SelectedIndexChanged, cboOffice.SelectedIndexChanged, dtEndDate.ValueChanged, dtStartDate.ValueChanged

		If blnLoad = True Then
			If cboDigit.SelectedIndex <> -1 And cboOffice.SelectedIndex <> -1 Then
				Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= \\Bkesl\VOL2\Common\updates\CaseAdmin\CaseAdmin.accdb")
				conn.Open()

				Dim userDetailsAdapter As New OleDbDataAdapter("SELECT ISNull(CasesReport,'IsEmpty') FROM tblCaseAdmin where Digit =" + cboDigit.Text + " AND Office = '" + cboOffice.Text + "' And Day between #" + dtStartDate.Text + "# and #" + dtEndDate.Text + "#", conn)

				Dim userDetailsDataSet As New DataSet
				userDetailsAdapter.Fill(userDetailsDataSet, "Table1")
				userDetailsAdapter.Dispose()
				Dim userDetailsTable As DataTable = userDetailsDataSet.Tables(0)

				conn.Close()

				'Fill Cases Report
				If userDetailsTable.Rows.Count <> 0 Then
					moncasesreport.Text = userDetailsTable.Rows(0).Item(3)
					tuecasesreport.Text = userDetailsTable.Rows(1).Item(3)
					wedcasesreport.Text = userDetailsTable.Rows(2).Item(3)
					thurcasesreport.Text = userDetailsTable.Rows(3).Item(3)
					fricasesreport.Text = userDetailsTable.Rows(4).Item(3)

				End If
			End If
		End If
	End Sub

[modi123_1]

http://msdn.microsof...10(SQL.80).aspx

It looks like 'isnull' is being set up right.

What is 'CasesReport' column? You should be returning all your column names needed to fill in the adapter's columns.

[Paul Washburn]

CasesReport is a text column, was attempting the code with one column for ease of testing, i had thought that using a select statement you could select one column at a time?

I tried adding in all of the columns and received the same results, there is one number column and one date column, the remaining columns are all text

[modi123_1]

this is in SQL right? Not something silly like Access, right?

[Paul Washburn]

SQL query to pull data from an Acccess database

[krum110487]

Access is weird with SQL calls...and the older the version of access the weirder it gets...it's hit and miss, it could be an access problem, what version of access are you using?

[Paul Washburn]

Access 2007

[krum110487]

lets see the query, make sure you didn't make a mistake or something.