Nz not working in VB.NET

[Ashlewis]

VB.net, Microsoft visual studio, ACCESS 2007

Hi,

Im finding the maximum value in a column I have using this sql statment:

SELECT Nz(Max(TblLeague.rank),0) AS MaxRank 
FROM TblLeague 
WHERE (((TblLeague.sportID)=[sID]));
 

As you can see im using a parameter sID, this just means im looking for the maximum value in that sport.
Im also using the Nz command because when there were no entries for a particular sID a NULL value was being returned as the max value, and i needed this value to actually be 0.

This worked perfectly when the query was run within access.

When i used the below code in VB i got an error:
Undefined function 'Nz' in expression

Dim MaxRank As Integer
            Dim DataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/Database.mdb"))
 
            Dim RankCommand As New OleDbCommand("QryMaxRank")
            RankCommand.CommandType = Data.CommandType.StoredProcedure
            RankCommand.Connection = DataConn
            RankCommand.Parameters.Add(New OleDbParameter("sID", DDLSports.SelectedValue))
 
            DataConn.Open()
            Dim RankDBReader As OleDbDataReader = RankCommand.ExecuteReader()
            MaxRank = RankDBReader("MaxRank")
 
            RankDBReader.Close()
 

Any ideas very welcome, if theres someway to not eliminate the null in sql and deal with it on the vb side of things such as IF null dont do the operation, i would welcome that too.

Thanks

[shweta123]

Hi,

The another way to not allow the Null values in the database columns is to set the Required field by going into the Design properties of that table. This way you can avoid the Null values at the first place.

VB.net, Microsoft visual studio, ACCESS 2007

Hi,

Im finding the maximum value in a column I have using this sql statment:

SELECT Nz(Max(TblLeague.rank),0) AS MaxRank 
FROM TblLeague 
WHERE (((TblLeague.sportID)=[sID]));
 

As you can see im using a parameter sID, this just means im looking for the maximum value in that sport.
Im also using the Nz command because when there were no entries for a particular sID a NULL value was being returned as the max value, and i needed this value to actually be 0.

This worked perfectly when the query was run within access.

When i used the below code in VB i got an error:
Undefined function 'Nz' in expression

Dim MaxRank As Integer
            Dim DataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/Database.mdb"))
 
            Dim RankCommand As New OleDbCommand("QryMaxRank")
            RankCommand.CommandType = Data.CommandType.StoredProcedure
            RankCommand.Connection = DataConn
            RankCommand.Parameters.Add(New OleDbParameter("sID", DDLSports.SelectedValue))
 
            DataConn.Open()
            Dim RankDBReader As OleDbDataReader = RankCommand.ExecuteReader()
            MaxRank = RankDBReader("MaxRank")
 
            RankDBReader.Close()
 

Any ideas very welcome, if theres someway to not eliminate the null in sql and deal with it on the vb side of things such as IF null dont do the operation, i would welcome that too.

Thanks

[balabaster]

Hi,

The another way to not allow the Null values in the database columns is to set the Required field by going into the Design properties of that table. This way you can avoid the Null values at the first place.

Dim ReturnValue As <DataType> = IIf(Not DataReaderObject("FieldName") = System.DBNull.Value, <TrueValue>, <FalseValue>)

You can obviously substitute TrueValue and FalseValue for whatever data type your data needs to be:

Dim MyVal As Integer = IIf(Not DataReader1("Field1") = System.DBNull.Value, DataReader1("Field1"), 0)

In the event that there's data in Field1 of the DataReader object, it will put that data into MyVal. In the event that the field holds a null value, it will put 0 into MyVal.