Hi all, me again, back to torture you all with another stupid question about .net!!

I have a database driven app which I am trying to create and I have am searching the db for a value, which I intend to return and use in another query.

The criteria for search A is taken from a drop down list, passed into a SQL like so:

Private Sub search_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles search.Click

       Dim comptype As String = ddCompType.SelectedItem.Text

       Dim comp_code As String

       comp_code = get_comp_code(comptype)

end sub

Function get_comp_code(ByVal comptype)

        Dim str_sql_get_comp_code As String
        str_sql_get_comp_code = "SELECT CODES.CDE_CMN_CLCTN_CD FROM CODES WHERE (((CODES.CDE_BUS_VALUE_TXT)='" & comptype & "'));"
        Dim comp_code As String ' this is the value which will be returned
        Dim dbconn As New System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;" & "User ID=Admin;" & "Data Source=C:\Inetpub\wwwroot\CompModel\Database\Dev\C ompModel.mdb")

        dbconn.Open()
        Dim comp_type_ds As System.Data.OleDb.OleDbDataReader
        Dim get_comp_code_cmd As System.Data.OleDb.OleDbCommand
        get_comp_code_cmd = New System.Data.OleDb.OleDbCommand(str_sql_get_comp_co de, dbconn)
        comp_type_ds = get_comp_code_cmd.ExecuteReader()
        comp_code = comp_type_ds.Item(0)
        dbconn.Close()

        Return comp_code

End Function

OK, now for the problem: This function will always find only 1 of 3 possible values in the database each time it is run.
In the line - comp_code = comp_type_ds.Item(0) - i cant seem to get the info out of the data reader and pass it into the variable comp_code. I want to extract this single value from the datareader and put it into comp_code and return it to the main function so i can move it into another function.I am getting the following error: No data exists for the row/column. I have also tried setting the ordinal to 1 as well, but get the same error.

I have tried various types of getting this info out such as comp_type.read which returned a value of true - not what i wanted.

If anyone could shed some light or point me in the right direction with this one, id appreciate it,

thanks very much

Morris

Hi Morris,

Are you sure the DataReader returns a record? If comptype (or comp_code or get_comp_code or comp_type_ds or whatever, I got very confused by your variables naming scheme. I think I mean comptype ;) ) is an empty string, the DataReader will not contain records.

Oh, wait. I think it's the Read problem..... When you open a Reader, you have to use its Read method first to advance to the first record:

comp_type_ds = get_comp_code_cmd.ExecuteReader()
If comp_type_ds.read() Then
  comp_code = comp_type_ds.Item(0)
End If
comp_type_ds.Close()
dbconn.Close()

To return a .NET datatype, use GetInt32 or GetString on the Reader instead.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

sorted!! excellent,

thank for your help - sorry about the naming conventions, its not very conventional!

Morris

If you are only looking to get a single value from the database, you could use the ExecuteScalar() method. Instead of this:

comp_type_ds = get_comp_code_cmd.ExecuteReader()
If comp_type_ds.read() Then
  comp_code = comp_type_ds.Item(0)
End If

You just do this:

comp_code = get_comp_code_cmd.ExecuteScalar()

You'll need to cast the result for your datatype.

Peter
------------------------------------------------------
Work smarter, not harder.

Hi Peter,

thanks for helping me out again!

I tried the ExecuteScalar()command when I was struggling with this and my code didnt seem to like it. However, usually when my code doesnt like something, it is more to do with me and my implementation of the the code than anything else!!

One more thing - I thought ExecuteScalar() was used in situations where values didnt need to be returned such as insert and delete transactions on a database?

Everyday is a school day as they say I guess!

Thanks again,

Morris

The intent of ExecuteScalar is to return a scalar (single) value.

ExecuteNonQuery() is the one you are thinking of. That returns just the number of rows affected by the query (for a SqlClient command at least) but no actual results. Use that for an insert or update query.

Peter
------------------------------------------------------
Work smarter, not harder.

Thank you for pointing that out - you just saved me making another post asking for help reagrding why an update\inseert\delete query wouldnt work!!

It could all have been very embarassing

thanks again for all your help :)