Nulls from msaccess to Vb.Net

I've written the following and found that the ISNULL function is not working properly. I want to be able to query the Firstname record from an access database and have it return a string of my preference when the Firstname field is null. I found that this is isn't happening. Under transact-sql ISNULL is to have a replacement parameter and find that I receive a wrong parameters error message with my code. I've tried using the nz command for msAccess and I receive a function not found error. Is there a way to return a string for a null value from vb.net querying an access database?. My code is below

Dim x as strin
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Test.MDB"

x = "SELECT Name, ISNULL(FirstName,'John') From MyTable

Dim cmd As New OleDb.OleDbCommand(x, conn
Dim rdr As OleDb.OleDbDataReade

Tr

conn.Open(
rdr = cmd.ExecuteReade

repeaterOrderDetails.DataSource = rd
repeaterOrderDetails.DataBind(

rdr.Close(
conn.Close(

Catch ex As Exceptio
Response.Write(ex.Message
Response.End(
Finall
If conn.State = ConnectionState.Open The
conn.Close(
End I
End Tr

William
Mon Apr 26 20:59:58 CDT 2004

In Access, IsNull returns true or false, it's a different function from
T-Sqls. Off the top of my head, I don't know of the access equivalent or if
there is one, but I'll see if I can find something and post back shortly.
However, using IsNull can cause some problems depending on your update
statement b/c if it comes in as string.empty, and the value in the DB is
Null, you may get a concurrency exception when you try to update... depends
on how your update logic is set up. If you only need the empty string for
User INterface reasons, a grid for instance has a
TableStyles/DataGridColumnStyle and the ColumnStyle has a NullText property
which you can set to an empty string. So everywhere there's a null it will
appear as a blank string. You can also check for null using
IsDbNull(ColumnName) and this will determine if it's null or not so you can
reference it accordingly.

let me see If I can find an access equivalent.

Cheers,

Bill

HTH,

Bill
"Bryan" <anonymous@discussions.microsoft.com> wrote in message
news:F2919522-53C7-4460-8C2B-18FB5AA60FDE@microsoft.com...
> I've written the following and found that the ISNULL function is not
working properly. I want to be able to query the Firstname record from an
access database and have it return a string of my preference when the
Firstname field is null. I found that this is isn't happening. Under
transact-sql ISNULL is to have a replacement parameter and find that I
receive a wrong parameters error message with my code. I've tried using the
nz command for msAccess and I receive a function not found error. Is there
a way to return a string for a null value from vb.net querying an access
database?. My code is below:
>
> Dim x as string
> Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=Test.MDB")
>
> x = "SELECT Name, ISNULL(FirstName,'John') From MyTable"
>
> Dim cmd As New OleDb.OleDbCommand(x, conn)
> Dim rdr As OleDb.OleDbDataReader
>
> Try
>
> conn.Open()
> rdr = cmd.ExecuteReader
>
> repeaterOrderDetails.DataSource = rdr
> repeaterOrderDetails.DataBind()
>
> rdr.Close()
> conn.Close()
>
> Catch ex As Exception
> Response.Write(ex.Message)
> Response.End()
> Finally
> If conn.State = ConnectionState.Open Then
> conn.Close()
> End If
> End Try
>

anonymous
Mon Apr 26 22:11:04 CDT 2004

Bill
I'm running a repeater on this code with an html table. I'm not using a grid so the IsDbNull function will not work for me. The reason why I'm asking to return a different string is that the table is coming back with no borders. I'm trying to return something except null so my table will render properly.. for example this is my code in html when binding on the html side

<td bgcolor='white' class='v8' valign='top' align='left'><%# DataBinder.Eval(Container.DataItem, "Status") %></td

When the repeater is bound there is no border drawn to the html table. It look incomplete. Maybe I can write javascript and if so what would it be

Val
Mon Apr 26 22:37:46 CDT 2004

Hi,

I think in Access there is a function, which call NZ(), which is an analog
for IsNull. Query would look like

SELECT Name, NZ(FirstName,'John') From MyTable

--
Val Mazur
Microsoft MVP


"William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
news:%23WG4Tt$KEHA.2260@TK2MSFTNGP09.phx.gbl...
> In Access, IsNull returns true or false, it's a different function from
> T-Sqls. Off the top of my head, I don't know of the access equivalent or
> if
> there is one, but I'll see if I can find something and post back shortly.
> However, using IsNull can cause some problems depending on your update
> statement b/c if it comes in as string.empty, and the value in the DB is
> Null, you may get a concurrency exception when you try to update...
> depends
> on how your update logic is set up. If you only need the empty string for
> User INterface reasons, a grid for instance has a
> TableStyles/DataGridColumnStyle and the ColumnStyle has a NullText
> property
> which you can set to an empty string. So everywhere there's a null it
> will
> appear as a blank string. You can also check for null using
> IsDbNull(ColumnName) and this will determine if it's null or not so you
> can
> reference it accordingly.
>
> let me see If I can find an access equivalent.
>
> Cheers,
>
> Bill
>
> HTH,
>
> Bill
> "Bryan" <anonymous@discussions.microsoft.com> wrote in message
> news:F2919522-53C7-4460-8C2B-18FB5AA60FDE@microsoft.com...
>> I've written the following and found that the ISNULL function is not
> working properly. I want to be able to query the Firstname record from an
> access database and have it return a string of my preference when the
> Firstname field is null. I found that this is isn't happening. Under
> transact-sql ISNULL is to have a replacement parameter and find that I
> receive a wrong parameters error message with my code. I've tried using
> the
> nz command for msAccess and I receive a function not found error. Is
> there
> a way to return a string for a null value from vb.net querying an access
> database?. My code is below:
>>
>> Dim x as string
>> Dim conn As New
> OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
>> Source=Test.MDB")
>>
>> x = "SELECT Name, ISNULL(FirstName,'John') From MyTable"
>>
>> Dim cmd As New OleDb.OleDbCommand(x, conn)
>> Dim rdr As OleDb.OleDbDataReader
>>
>> Try
>>
>> conn.Open()
>> rdr = cmd.ExecuteReader
>>
>> repeaterOrderDetails.DataSource = rdr
>> repeaterOrderDetails.DataBind()
>>
>> rdr.Close()
>> conn.Close()
>>
>> Catch ex As Exception
>> Response.Write(ex.Message)
>> Response.End()
>> Finally
>> If conn.State = ConnectionState.Open Then
>> conn.Close()
>> End If
>> End Try
>>
>
>

William
Mon Apr 26 22:40:15 CDT 2004

I've looked for a while and I can't find an Access equivalent, but one of
the people I work with uses Access quite a bit and I've sent him an email.
I'll get back to you on it.

You bring up another issue though...for a web app, how many users are you
expecting? I know this is a totally unrelated issue, but Access doesn't
shine in multi-user environments unless the usercount is low. If you are
going to have more than a few users at any given time, MSDE is probably a
better way to go. For one thing it's free. For another it's truly a client
server db. Its security features are much much better (you can use all
stored procedures for instance, and grant only exec permissions to the procs
not giving any users access to datareader or writer directly.. greatly
enhancing security) as is performance, finally, you have the full support of
T-Sql and you can do things like have a warm standby server (which woudl be
free) or have much better backup and recovery. I'm not shirking the
question though, I just figured I'd mention it. I'll keep looking b/c there
has to be a simple equivalent for this.

I'll be in touch.

Bill
"Bryan" <anonymous@discussions.microsoft.com> wrote in message
news:63C82299-DA11-4E66-839A-A33395014221@microsoft.com...
> Bill,
> I'm running a repeater on this code with an html table. I'm not using a
grid so the IsDbNull function will not work for me. The reason why I'm
asking to return a different string is that the table is coming back with no
borders. I'm trying to return something except null so my table will render
properly.. for example this is my code in html when binding on the html
side:
>
> <td bgcolor='white' class='v8' valign='top' align='left'><%#
DataBinder.Eval(Container.DataItem, "Status") %></td>
>
> When the repeater is bound there is no border drawn to the html table. It
look incomplete. Maybe I can write javascript and if so what would it be?
>

William
Mon Apr 26 23:04:40 CDT 2004

Looks like you can use IIF
http://www.experts-exchange.com/Databases/MS_Access/Q_20956813.html
"William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
news:%23yFEWlALEHA.2388@TK2MSFTNGP09.phx.gbl...
> I've looked for a while and I can't find an Access equivalent, but one of
> the people I work with uses Access quite a bit and I've sent him an
email.
> I'll get back to you on it.
>
> You bring up another issue though...for a web app, how many users are you
> expecting? I know this is a totally unrelated issue, but Access doesn't
> shine in multi-user environments unless the usercount is low. If you are
> going to have more than a few users at any given time, MSDE is probably a
> better way to go. For one thing it's free. For another it's truly a
client
> server db. Its security features are much much better (you can use all
> stored procedures for instance, and grant only exec permissions to the
procs
> not giving any users access to datareader or writer directly.. greatly
> enhancing security) as is performance, finally, you have the full support
of
> T-Sql and you can do things like have a warm standby server (which woudl
be
> free) or have much better backup and recovery. I'm not shirking the
> question though, I just figured I'd mention it. I'll keep looking b/c
there
> has to be a simple equivalent for this.
>
> I'll be in touch.
>
> Bill
> "Bryan" <anonymous@discussions.microsoft.com> wrote in message
> news:63C82299-DA11-4E66-839A-A33395014221@microsoft.com...
> > Bill,
> > I'm running a repeater on this code with an html table. I'm not using a
> grid so the IsDbNull function will not work for me. The reason why I'm
> asking to return a different string is that the table is coming back with
no
> borders. I'm trying to return something except null so my table will
render
> properly.. for example this is my code in html when binding on the html
> side:
> >
> > <td bgcolor='white' class='v8' valign='top' align='left'><%#
> DataBinder.Eval(Container.DataItem, "Status") %></td>
> >
> > When the repeater is bound there is no border drawn to the html table.
It
> look incomplete. Maybe I can write javascript and if so what would it be?
> >
>
>

anonymous
Tue Apr 27 01:41:02 CDT 2004

iff won't work... isn't that interesting!!!! Well, here is the solution. I used a wrap function

Set up a function on the code behind

Public Function WrappingNullFunction(ByVal nullDbObject As Object, ByVal objectType As String) As Strin
Select Case objectTyp
Case "Status
If (nullDbObject.Equals(DBNull.Value)) Or (nullDbObject.Equals("")) The
Return "Processing
Els
Return String.Format("{0:d}", nullDbObject
End I
Case "Date
If (nullDbObject.Equals(DBNull.Value)) Or (nullDbObject.Equals("")) The
Return "Pending
Els
Return String.Format("{0:d}", nullDbObject
End I
End Selec
End Functio

'Then on the html script side I bound the function to the table cel

<td bgcolor='white' class='v8' valign='top' align='right'><%#WrappingNullFunction(DataBinder.Eval(Container.DataItem, "DateShipped", "{0:d}"),"Date") %></td><td bgcolor='white' class='v8' valign='top' align='right'><%#WrappingNullFunction(DataBinder.Eval(Container.DataItem, "Status", "{0:d}"),"Status") %></td

About the user... thanks for the concern... I'm just looking to make this work in a small envirnoment and move to a bigger environment once everything is said and done. Thanks for the help.

Patrice
Tue Apr 27 06:09:13 CDT 2004

FYI, in Access this is the NZ function...


"Bryan" <anonymous@discussions.microsoft.com> a écrit dans le message de
news:F2919522-53C7-4460-8C2B-18FB5AA60FDE@microsoft.com...
> I've written the following and found that the ISNULL function is not
working properly. I want to be able to query the Firstname record from an
access database and have it return a string of my preference when the
Firstname field is null. I found that this is isn't happening. Under
transact-sql ISNULL is to have a replacement parameter and find that I
receive a wrong parameters error message with my code. I've tried using the
nz command for msAccess and I receive a function not found error. Is there
a way to return a string for a null value from vb.net querying an access
database?. My code is below:
>
> Dim x as string
> Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=Test.MDB")
>
> x = "SELECT Name, ISNULL(FirstName,'John') From MyTable"
>
> Dim cmd As New OleDb.OleDbCommand(x, conn)
> Dim rdr As OleDb.OleDbDataReader
>
> Try
>
> conn.Open()
> rdr = cmd.ExecuteReader
>
> repeaterOrderDetails.DataSource = rdr
> repeaterOrderDetails.DataBind()
>
> rdr.Close()
> conn.Close()
>
> Catch ex As Exception
> Response.Write(ex.Message)
> Response.End()
> Finally
> If conn.State = ConnectionState.Open Then
> conn.Close()
> End If
> End Try
>

William
Tue Apr 27 08:11:15 CDT 2004

Bryan:

What's happening now? An exception or it just doesn't do what you expect?
If an exception,what is it?
"Bryan" <anonymous@discussions.microsoft.com> wrote in message
news:397C1980-E991-401B-8F1F-754DF1B50D25@microsoft.com...
> iff won't work... isn't that interesting!!!! Well, here is the solution.
I used a wrap function:
>
> Set up a function on the code behind:
>
> Public Function WrappingNullFunction(ByVal nullDbObject As Object,
ByVal objectType As String) As String
> Select Case objectType
> Case "Status"
> If (nullDbObject.Equals(DBNull.Value)) Or
(nullDbObject.Equals("")) Then
> Return "Processing"
> Else
> Return String.Format("{0:d}", nullDbObject)
> End If
> Case "Date"
> If (nullDbObject.Equals(DBNull.Value)) Or
(nullDbObject.Equals("")) Then
> Return "Pending"
> Else
> Return String.Format("{0:d}", nullDbObject)
> End If
> End Select
> End Function
>
> 'Then on the html script side I bound the function to the table cell
>
> <td bgcolor='white' class='v8' valign='top'
align='right'><%#WrappingNullFunction(DataBinder.Eval(Container.DataItem,
"DateShipped", "{0:d}"),"Date") %></td><td bgcolor='white' class='v8'
valign='top'
align='right'><%#WrappingNullFunction(DataBinder.Eval(Container.DataItem,
"Status", "{0:d}"),"Status") %></td>
>
> About the user... thanks for the concern... I'm just looking to make this
work in a small envirnoment and move to a bigger environment once everything
is said and done. Thanks for the help.

Paul
Tue Apr 27 08:43:06 CDT 2004

On Tue, 27 Apr 2004 13:09:13 +0200, "Patrice" <nobody@nowhere.com> wrote:

¤ FYI, in Access this is the NZ function...
¤

Yes, but unfortunately it cannot be used in a query executed outside of the Access application.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)