How do I get the OleDbDataReader to read an empty cell?

[haroldjclements]

Hello All,

 

I have created an application that pulls data from a database using an OleDbDataReader. It used a wile loop to collect the data from each line. Now this works fine until it comes across an empty cell in the database.

I have caught the exception “No value for one or more required parameters”.

 

How do I get the OleDbDataReader to read an empty cell?

 

Thanks for your help,

Harold Clements

[loyal]

There are several tricks to over this exception

 

1-> use the simple ToString() to collect your data as string value

 

Exmaple >>

 

cn.Open()

Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader

While rdr.Read()

Me.lsbUseName.Items.Add(rdr("userName")).ToString()

End While

rdr.Close()

cn.Close()

 

 

Or another way to do it

 

Dim strSQL As String

Dim cnn As New OleDb.OleDbConnection(DATAPATH)

Try

cnn.Open()

strSQL = "SELECT AcYear FROM ACYears"

Dim cmd As New OleDb.OleDbCommand(strSQL, cnn)

Dim rdr As OleDb.OleDbDataReader = _

cmd.ExecuteReader(CommandBehavior.CloseConnection)

Dim ACYear As String = rdr.GetOrdinal("AcYear")

Do While rdr.Read()

CBoxYear.Items.Add(rdr("ACYear").ToString())

Loop

rdr.Close()

cmd.Dispose()

Catch err As Exception

MessageBox.Show(err.Message)

End Try

 

 

another solution is to use IS NOT NULL T-SQL

 

by saying that if the current return value is not null

return it otherwise forget it

 

Example >> see the last condition if Grade is Not Null pull the data otherwise skip it

 

strSQL1 = "SELECT Grade FROM DetailsRegistration " & _

"WHERE CourseDescriptions ='" & Course & _

"' AND StudentID ='" & txtID.Text & "' AND Grade IS NOT NULL "

 

Or another way to do it

 

By using the isNull function

 

search data while loading it if any is null

then replace it with UNKNOWNVALUE or any other you desired ...

( check_expression , replacement_value )

 

ISNULL(MYCOLUMN,UNKNOWNVALUE)

 

GOOD LUCK

[haroldjclements]

Thanks for your comprehensive response, it is very much appreciated. I will give it a try.

 

Thanks again,

Harold Clements

[haroldjclements]

How would I use the ToString() method on the following line (in J#).

 

The line that is causing the problem is:

 custPN = myDataReader.GetString(2);

 

Basically my problem is as above.