DataColumn Expression Error with LookUp

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Dear all,

I have error " There is no row at position 0" at DataColumn Expression.

Can any one help me please??

First Table :-

VB.NET:
Public Function GetCustomerDetail(ByVal CustomerID As String) As DataTable
Dim techConnection As OleDbConnection = New OleDbConnection(strConn)

Dim techDataAdapter As OleDbDataAdapter

Dim dtCustomerDetail As DataTable

Try

Dim strSQL As String = _

"SELECT * FROM Customers WHERE CustomerID = @CustomerID"


Dim techCommand As New OleDbCommand(strSQL, techConnection)

techCommand.Parameters.Add("@CustomerID", OleDbType.VarChar, 20, "CustomerID").Value = CustomerID

techDataAdapter = New OleDbDataAdapter(techCommand)

Dim techCommandBuilder As New OleDbCommandBuilder(techDataAdapter)

dtCustomerDetail = New DataTable

techDataAdapter.Fill(dtCustomerDetail)

Catch EX As OleDbException

MsgBox(EX.Message)

End Try

Return dtCustomerDetail

End Function

Second table where I have error is:-
VB.NET:
Public Function TempCustomer(ByVal DataTableName As String) As DataTable

dt = New DataTable(DataTableName)

dt.Columns.Add(New DataColumn("ItemNo", GetType(Integer)))

dt.Columns.Add(New DataColumn("CustomerID", GetType(String)))

Dim dtCustomerDetail As DataTable = GetCustomerDetail("CustomerID")

Dim dcCustomerName As DataColumn

dcCustomerName = New DataColumn("CustomerName", GetType(String))

dcCustomerName.Expression = dtCustomerDetail.Rows(0)(1) + " " + dtCustomerDetail.Rows(0)(2) ' First Name + Second Name

dt.Columns.Add(dcCustomerName)

Return dt

End Function

Error appears at above expression.

This table is bound with a DataGrid.

Any help is most appreciated.

Thanks in advance
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,059
Programming Experience
10+
Umm.. yeah. What do you think will happen on this line:

dcCustomerName.Expression = dtCustomerDetail.Rows(0)(1) + " " + dtCustomerDetail.Rows(0)(2) ' First Name + Second Name


if there is no row at position 0. I dont really know how to make this more clear than the error message, but I can try the following re-phrases: youre attempting to access a row that doesnt exist. There is no row at position zero. The rows collection has zero entries. There is nothing in the datatable. It is empty.

Expressions should usually be set at design time, not run-time
 

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Thank you very much cjard,

So, I should use "IsNull" to Evaluate? I tried IsNull But it is not giving error or fetching data (I don't know whether my syntax is correct:confused:).

How could we assign values from 1st DataTable into the Second DataTable Expression column? If anybody know the right syntax please let me know.

Your help is most appreciated.

Thanks in advance
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,059
Programming Experience
10+
Thank you very much cjard,

So, I should use "IsNull" to Evaluate?

No, you should form your expression properly in the first place! Expression is a calculated property, but youre setting it to a constant valuye. I dont know why. If you want first name and last name then your expression is:

"[FirstName] & ' ' & [LastName]"

at DESIGN TIME, NOT IN CODE



How could we assign values from 1st DataTable into the Second DataTable Expression column?
I dont think they are meant to work like that.. DataTables dont interact, except in parent/child relationships
 

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Thank you very much cjard,

My problem is not Concating two columns. the problem is LookUp with expression.(if I enter CustomerID into TempCustomer DataTable then CustomerName column is filled automatically with first & last name)

So, there is no Lookup with DataColumn Expression? Or otherwise
can I put this code into my TempCustomer DataTable? (please Don't Lough it is not arranged).

VB.NET:
Public Function TempCustomer(ByVal DataTableName As String) As DataTable

dt = New DataTable(DataTableName)
Dim strSQL As String = _

"SELECT * FROM Customers WHERE CustomerID = @CustomerID"

Dim techCommand As New OleDbCommand(strSQL, techConnection)


techDataAdapter = New OleDbDataAdapter(techCommand)

Dim techCommandBuilder As New OleDbCommandBuilder(techDataAdapter)

dt.Columns.Add(New DataColumn("ItemNo", GetType(Integer)))

dt.Columns.Add(New DataColumn("CustomerID", GetType(String)))

techCommand.Parameters.Add("@CustomerID", OleDbType.VarChar, 20, "CustomerID").Value = CustomerID

Dim techCommandBuilder As New OleDbCommandBuilder(techDataAdapter)



Please give your valued suggestion or if....if ... don't mind give me sample code pleeees??:p
 
Last edited:

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,059
Programming Experience
10+
Thank you very much cjard,

My problem is not Concating two columns. the problem is LookUp with expression.(if I enter CustomerID into TempCustomer DataTable then CustomerName column is filled automatically with first & last name)

So, there is no Lookup with DataColumn Expression?
Not in the way youre expecting to use it. Expression is used to calculate a value based on other values of the same row or related rows. All values must be available

Or otherwise
can I put this code into my TempCustomer DataTable?
Please give your valued suggestion or if....if ... don't mind give me sample code pleeees??:p

I dont work with .NET 1.1 anymore, but the DW1 link in my signature may be of assistance. I suggest you read up about displaying related data on a windows form, but given that it became so much easier and better thought out in .NET 2, I cant really give good advice here. Upgrading (1.1 is ~5 years old now!) would be a good thing.
 

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Thank you very much cjard,

I will try to code it and post it if I succeeded for the benefit of others who use older version.

thanks for your advise for upgrade. I will try my best to upgrade to VS2005 in near future.
 
Top Bottom