Nulls from msaccess to Vb.Net

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
I've written the following and found that the ISNULL function is not
Click to expand...
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:
 
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
 
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
 
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 said:
Bill,
I'm running a repeater on this code with an html table. I'm not using a
Click to expand...
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'><%#
Click to expand...
DataBinder.Eval(Container.DataItem said:
When the repeater is bound there is no border drawn to the html table. It
Click to expand...
look incomplete. Maybe I can write javascript and if so what would it be?
 
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.
 
FYI, in Access this is the NZ function...


Bryan said:
I've written the following and found that the ISNULL function is not
Click to expand...
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:
 
Bryan:

What's happening now? An exception or it just doesn't do what you expect?
If an exception,what is it?
Bryan said:
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,
Click to expand...
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'
Click to expand...
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" said:
About the user... thanks for the concern... I'm just looking to make this
Click to expand...
work in a small envirnoment and move to a bigger environment once everything
is said and done. Thanks for the help.
 
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top