[RESOLVED] column value not binding to combobox text property

[gwboolean]

Yes, I have discussed this before. However, I have been looking at it and still cannot figure out what is causing the problem. Here is what I am seeing.

The combobox, cboType displays a specific value from a specific column on a table using the following code:

 cboType.DataBindings.Add("Text",MasterBase.listTable, "colType")

The value that should be displayed from colType is, "Document". The value actually displayed was from colFilePath and was

]C:\Development\FileCabinet\DocMaster\MB100000024\Rev 00

I then changed the binding code for colChangePath.

 cboType.DataBindings.Add("Text", MasterBase.listTable, "colChangePath")

Instead of a value being displayed, i got the following error.



I tried this again, using a different binding approach

cboType.Text = MasterBase.listTable.Rows(0).Item(X).ToString

I varied X from 4 to 7. While I never received the error, I found that when the value for X=6 (colType) was used, the actual value displayed was for X=7 (colOwner). The value for colType could never be made to display.

Now at this point, I can think of a couple more tests that I can run, but I don't think it will get me any closer to understanding what is causing this phenomenon. Nor does the message telling me that it can't bind to the datamember really help either. I can see that it cannot do that, but I cannot see why.

At this point I believe I have pretty thoroughly checked the code, but have still been unable to find a coding mistake that would cause this set of events. Besides some coding error, what might cause this to happen?

[.paul.]

You don’t bind dgvs that way. You set a datasource for the combo column. You then set a datasource for the entire grid…
I’m sure jmcilhinney has explained to you before. If you check the links in his signature, I believe he has a tutorial on binding dgvs.

[jmcilhinney]

You don’t bind dgvs that way.

I don't think this has anything to do with a DataGridView. I think it's just a ComboBox control bound to a column in a DataTable.

[jmcilhinney]

How EXACTLY have you populated the ComboBox in the first place? Please explain exactly what data you're working with and how it relates. Generally speaking, you would bind one table to the drop-down list by setting the DisplayMember, ValueMember and DataSource. If you are then using discrete controls to edit a record from a different table and using that ComboBox to select a value for one field of that record, you would bind directly to the SelectedValue property.

The error message you posted means that the table you're binding to has no column with that name. You may think it does but it doesn't when the binding is done or that error would not occur.

BTW, when you say that you used a different binding approach, what you actually used a was a different approach without binding.

[gwboolean]

The combobox is populated from a lookup table using the following code;

    Public Sub GetTypeComboBox()
        cboType.Items.Clear()
        MasterBase.MasterBaseQuery("SELECT colType From lkpDoc")
        If RecordCount > 0 Then
            For Each r As DataRow In MasterBase.ListDataSet.Tables(0).Rows
                cboType.Items.Add(r("colType"))
            Next
        End If
    End Sub

    Private Sub GetDocument(ByVal SystemID As String)
#Region "Parameters"
        MasterBase.AddParam("@recno", SystemID)
#End Region
#Region "Connect load docMaster Query"
        Try
            Dim RecordCount = 0
            MasterBase.Exception = ""
            MyError = "Failed to find the selected document."
            MasterBase.MasterBaseQuery("SELECT colSystemID,colRev,colName,colDetail,colFilePath,colChangePath " &
                                       "colType,colOwner,colWhere,colActive,colObsolete " &
                                       "FROM sitDocMaster " &
                                       "WHERE colSystemID = @recno")
            MyRev = MasterBase.listTable.Rows(0).Item(1).ToString
            MyDocument = MasterBase.listTable.Rows(0).Item(0).ToString + "." + MasterBase.listTable.Rows(0).Item(1).ToString + "." + "Draft"
        Catch ex As Exception
            MasterBase.Exception = ex.Message
            MsgBox(ex.Message + vbCrLf + MyError)
            Me.Close()
        End Try
#End Region
    End Sub

As you stated, the lookup table is bound to the drop down list and the text property is bound to a column of another table.

The error message you posted means that the table you're binding to has no column with that name. You may think it does but it doesn't when the binding is done or that error would not occur.

I get that. But the table absolutely does have a column with that name and that column holds data. As far as I can tell, there is nothing wrong with the table/database, but if the problem is coming from that end I wouldn't even know how to look for that. I do know that the column being used from both tables have the same name, colType, but I am pretty sure that should not pose a problem anywhere.

[jmcilhinney]

That last code snippet wouldn't work as it is because there is a comma missing between "colChangePath" and "colType". Either that's not your actual code or it's not doing what you think it is. I would have expected an exception to be thrown when executing that query. This is an example of why that sort of string concatenation is bad. It makes code harder to read and mistakes like that easier to make. You should be using a multiline String literal or an XML literal.

[jmcilhinney]

For the record, the first thing I would have done was place a breakpoint on the line that calls DataBindings.Add and actually examined the DataTable being bound to see exactly what columns it contains and exactly what rows. You are assuming it contains what it should contain but it obviously doesn't or it would be working as you expect. That means that you need to actually look and see what it does contain. Once you know that, it will likely become clear why it's different to what you expect.

[gwboolean]

That last code snippet wouldn't work as it is because there is a comma missing between "colChangePath" and "colType".

I would have responded to this sooner, but I needed to schedule my cataract surgery. I really mean that. That comma was the problem and I have been looking at this over and over again for quite a long time. Actually, even now, I can just barely see it with my glasses on. I was unaware that my eyes had gotten that bad.

I really do appreciate the catch.

For the record, the first thing I would have done was place a breakpoint on the line that calls DataBindings.

While that was not the first thing, or even the 10th, or 20th, thing I did, I did do that. I just do not read the information that well yet.

[jmcilhinney]

Actually, even now, I can just barely see it with my glasses on. I was unaware that my eyes had gotten that bad.

You're lucky I could. No cataracts but I've been having trouble getting new glasses so I'm still using a pair that are not really good enough any more.

As I said, using concatenation like that makes code harder to read and thus more error-prone. This seems especially pertinent to you. I have long used XML literals and, more recently, multiline string literals when writing inline SQL as it's much clearer. I suspect that, even if it was blurry, you'd have noticed the different pattern without the extra noise of quotes and ampersands.

I'm also curious what was actually happening. Like I said, I would have expected that SQL to generate a syntax error and thus for the query to fail. Was that the case?

[techgnome]

There's a space at the end of colChangePath so he effectively aliased colChangePath into colType which wouldn't produce a syntax error.

,colChangePath " &
"colType,

-tg

[jmcilhinney]

There's a space at the end of colChangePath so he effectively aliased colChangePath into colType which wouldn't produce a syntax error.

,colChangePath " &
"colType,

-tg

Ah, I always use AS to alias columns so that didn't actually occur to me. That certainly explains why colType contained unexpected data and colChangePath didn't exist.

[techgnome]

Yeah, I always use AS for this very reason... makes it crystal clear that I'm aliasing something. Except tables ... I'm consistently inconsistent about using AS when aliasing a table ... shrug... not sure why, but thinking back on it, it seems I'm not consistent about it. But once I alias a table, I alias all tables, no questions.


-tg

[jmcilhinney]

I'm the same, i.e. As for columns but not for tables. I think it stems from the fact that aliasing a column is fine because I want a specific name while aliasing tables is generally done for brevity. I usually use initials for tables in joins to avoid using long names over and over.

[gwboolean]

You're lucky I could. No cataracts but I've been having trouble getting new glasses so I'm still using a pair that are not really good enough any more.

Lucky is in the eyes of the beholder. And my eyes aren't beholding anything very well these days. I really did schedule an appointment to get my eyes dealt with.

I always use AS to alias columns

Can you tell me a little bit about AS and aliasing. I don't believe that I am familiar with that. I definitely need a better way to do queries that do not rely so heavily on proofing and visual capabilities. This isn't the first, or even the 10th time I have not been able to see something that is there, or not there.

[techgnome]

It's a way of referencing something other than its full name.

Example... I've dealt with tables that had names like this: tblAccountingJournalLineItemApplication ... now try joining that to another table: tblAccountingJournalAccountName .... fuggedit.

so you alias them:L

Select LI.Id, LI.SomeRediculousNameHere as SimpleName
from tblAccountingJournalLineItemApplication LI
inner join tblAccountingJournalAccountName AN on LI.aField = AN.AlsoAField

Here I aliased the two tables and one field. Now in code, I get Id and SimpleName as the fields returned.

This also comes in handy when you use aggrgates...

Select ID, Count(FieldX) as FldXCount
from someTable
Group By AnotherField

Now the count field has a name that can be used in the code.

As for a "better" way... it's subjective ...
Consistency is the key.
Example, unless I have a LOT of fields, I put one on each line:

Select LI.Id, LI.SomeRediculousNameHere as SimpleName
from tblAccountingJournalLineItemApplication LI
inner join tblAccountingJournalAccountName AN on LI.aField = AN.AlsoAField

becomes

Select 
    LI.Id, 
    LI.SomeRediculousNameHere as SimpleName
from tblAccountingJournalLineItemApplication LI
inner join tblAccountingJournalAccountName AN on LI.aField = AN.AlsoAField

I like htis because I can then comment out fields so I can concentrate on other fields (like why am I getting 10 rows, when I'm expecting 2? ... )or if I want to alias some, it's easier to do when they're on one row each. Now, once I've got it working, then everything goes out the window and shop standards kick in...


It's just like any code... consistency is the key

-tg