locked
DataColumn Expression Error with LookUp RRS feed

  • Question

  • Dear all,

     

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

     

    Can any one help me please??

     

    First Table :-

     

    Code Snippet

    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:-

     

    Code Snippet

    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

    Thursday, May 17, 2007 2:28 PM

Answers

  • You're trying to access a row that doesn't exist, possibly because there was not data returned.

    I see you're trying to fill the datatable, then create a new column with the complete customer name from the populated info.

    You could avoid having to do this, and rid yourself of the error if you call the name through the SQL Query.

    Change Your Query to

     

     "SELECT *, Firstname + ' ' + Lastname as CustomerName  FROM Customers WHERE CustomerID = @CustomerID"

     

    That will select all the columns from your table and add a column called CustomerName that already has the First and Last Names.

    Than just add a Column to your datatable similar the the ItemNo and CustomerID columns for CustomerName

     

    Hope this helps

     

     

     

    Thursday, May 17, 2007 3:13 PM

All replies

  • You're trying to access a row that doesn't exist, possibly because there was not data returned.

    I see you're trying to fill the datatable, then create a new column with the complete customer name from the populated info.

    You could avoid having to do this, and rid yourself of the error if you call the name through the SQL Query.

    Change Your Query to

     

     "SELECT *, Firstname + ' ' + Lastname as CustomerName  FROM Customers WHERE CustomerID = @CustomerID"

     

    That will select all the columns from your table and add a column called CustomerName that already has the First and Last Names.

    Than just add a Column to your datatable similar the the ItemNo and CustomerID columns for CustomerName

     

    Hope this helps

     

     

     

    Thursday, May 17, 2007 3:13 PM
  • Thanks for your reply stlarmon ,

     

    So, Should I put this

     

     "SELECT *, Firstname + ' ' + Lastname as CustomerName  FROM Customers WHERE CustomerID = @CustomerID"

     

    into expression of TempCustomer DataTable?

     

    How could I implement this statement into the expression of TempCustomer DataTable?

     

    thanks in advance.

     

     

    Monday, May 21, 2007 5:11 AM
  • That should go in your strSql variable

     

    Dim strSql as string =  "SELECT *, Firstname + ' ' + Lastname as CustomerName  FROM Customers WHERE CustomerID = @CustomerID"

    Monday, May 21, 2007 2:55 PM
  • Actually, if you really wanted to make it easier... you could get rid of the TempCustomer Function and get the info you need from the GetCustomerDetail.  The example below would return a table containing the only three items you want in the datatable, and then you wouldn't have to format it through the TempCustomer Function.

     

    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 ItemNo, CustomerID, Firstname + ' ' + Lastname as CustomerName  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

    Monday, May 21, 2007 3:11 PM
  • Thank you very much stlarmon,

     

    The TempCustomer DataTable is bound with DataGrid. Actually What I want is  when we enter CustomerID into (CustomerID Column of DataGrid or DataTable) CustomerName should be fetched into CustomerName Column of DataGrid(i mean DataTable). For this We should set DataColumn Expression for CustomerName Column of TempCustomer DataTable.

     

    That is why I wanted put reference of GetCustomerDetail DataTable into TempCustomer DataTable Expression.

     

    Is there any way to sort out this problem please?

     

    Thanks for giving your valuable time.

     

     

    Tuesday, May 22, 2007 5:59 AM