Get Fields from Data Set

[JesseH]

First, let me apologize for the level of the question. I have tried desperately to solve it on my own without success. I am teaching myself VBDotNet. The problem is accessing the fields in the ODBC file. The name of the field I am trying to access is IDESC the file is IIML01. I believe that the select statement is correct because I checked it out in MS Access using a pass thru query. All I am trying to do is move the contents of IDESC to Me.txtDesc.Text

Can someone give me the construct to move the contents of IDESC to Me.txtDesc. I rather not use field numbers because they are too rigid.

In any case, thanks for the help.

OdbcDataAdapter1.SelectCommand.CommandText = _
"select IDESC from BPCSFV60.IIML01 where " & _
"BPCSFV60.IIML01.IProd = '" & Trim(txtPartNo.Text) & "' "
DataSet11.EnforceConstraints = False
OdbcDataAdapter1.Fill(DataSet11)
Dim IIMData As DataTable = DataSet11.Tables("IIML01")


Me.txtDesc.Text = Convert.ToString(IIMData.Columns(1))<=======Problem

 

[vis781]

If it is just one row you are retrieving then....

Me.txtDesc.Text = Convert.ToString(IIMData.Rows(0).item(1))

If it is more than one row then you'll need to specify the row the data is present in.
On reading this again, if you want all the rows to the textbox then you'll need to do something like this...

dim Dr as system.data.datrow
Dim a as integer = IIMData.rows.count-1
Dim str(a) as string
For I as integer = 0 to a
str(i) = convert.tostring(dr(i).item(1))
next
me.txtdesc.lines = str

 

[JesseH]

Error I am now receiving

The response is only suppose to be one record(row). I even removed the where clause and tried it. I tried row 0 and 1 and got the error below.

This is my first attempt to access ODBC data. Idea, enter part number and read the record relating to that part number using SQL, then capture the Part description.

Thanks for your help.


An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll
Additional information: There is no row at position 1.

 

[TechGnome]

I'd like to go one step further and note that like all arrays, the fields are 0-based. That means the first field is .Item(0), not .Item(1).

Since I've only worked with SQL Server, I can't make any further comments on the code. I would recommend however, accessing the fields by name and not position.

-tg

 

[JesseH]

Dear Tech,

Access the fields by name was my original intent. Accessing the fields by number does not lend itself to the changes that inevitably will occur.

 

[vis781]

Ok just having another look. Firstly you havent added the table to the dataset so it's not surprising there's no row at position 0

Change To This...

OdbcDataAdapter1.SelectCommand.CommandText = _
"select IDESC from BPCSFV60.IIML01 where " & _
"BPCSFV60.IIML01.IProd = '" & Trim(txtPartNo.Text) & "' "
DataSet11.EnforceConstraints = False
Dim IIMData As New DataTable("IIML01") 
dataset11.tables.add(IIMDATA)
OdbcDataAdapter1.Fill(DataSet11.tables(0))
Me.txtDesc.Text = Convert.ToString(Dataset11.tables(0).rows(0).item(1))

Give that a try and let me know how it goes.

 

[JesseH]

I tried it. I do not get a error but I get a blank description. I know that the part has a description because I can see it on the AS400(datasource).

 

[vis781]

Is the column that you are trying to retrieve data from the first column in your table, if not what is it?

 

[JesseH]

It is the first. On the SQL, it is the only field I am trying to access.

Thanks for your effort.

 

[vis781]

In that case change this....

Me.txtDesc.Text = Convert.ToString(Dataset11.tables(0).rows(0).item(1))

to this...

Me.txtDesc.Text = Convert.ToString(Dataset11.tables(0).rows(0).item(0))

 

[JesseH]

I going to change tactics, I must have set something up wrong. Does anybody have an example of using ODBC to enter a value into a text box and use that value to read a file and populate one other text box with the information from the file?

 

[DavidT_macktool]

JesseH, I think you are really close. I would use your select statement as it is. I believe you said it works alright.
So what you have done is used the value in the textbox to query the dataset based on that value - in this case - the Part Number. Once the query has done it's job you should have the record that corresponds to that part number, right? I think the simple solution is to bind the description textbox to the description field of the dataTable.

something like:
Me.txtDesc.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet11, "IIML01.PartDescription"))

Is your intent to not have the textbox bound to a specific field?

 

[JesseH]

It worked. Thanks very much.

Now, I can continue building complexity into the equation.