Question How to find INTEGER type column on a datagrid?

black zero

Well-known member
Joined
Jan 15, 2009
Messages
53
Programming Experience
Beginner
Might be a little bit hard to explain,

I have this code run on form load event:

VB.NET:
Try
            Module1.OpenCon2()
            da = New SqlDataAdapter("select * from kelancaran", Module1.xcon)
            da.Fill(ds, "edit")
            da.Dispose()

            For i As Integer = 0 To ds.Tables("edit").Columns.Count - 1
                cbospr.Items.Add(ds.Tables("edit").Columns(i).ToString)
            Next
            cbospr.SelectedIndex = 0

            dv = ds.Tables("edit").DefaultView
            DgCrTransaksi.DataSource = dv
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

That will load all records inside kelancaran table into a dgcrtransaksi AND column titles on cbospr. This is so far, working fine.

And there's a second code like this:

Try
If txtFilter.Text <> "" Then
dv.RowFilter = cbospr.SelectedItem & " like '%" & txtFilter.Text & "%'"
Else
dv.RowFilter = ""
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try

I put the second code on a textbox_change event (please see image below).

However, that code isn't working for all data types. If the column is integer, it would produce this error 'Cannot perform 'Like' operation on System.Int32 and System.String.'. But, it does work fine on VarChar type column.

FYI, here's my form screenshot:

kelancaranedit.JPG

=.= Sorry, I am stuck here... Help, please?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
You can't compare numbers character by character, so of course LIKE doesn't work. The simple solution is to simply not use LIKE if the column contains Integers. You already know which columns those are, don't you?
 

black zero

Well-known member
Joined
Jan 15, 2009
Messages
53
Programming Experience
Beginner
Yes, I do know.

So, I don't use 'LIKE', what sql statement should I use then?

*bows* My sql ignorance, don't blame it please.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
This is not actually SQL. You're setting the RowFilter property of a DataView. If you'd read the documentation for that property it would have directed you to the topic for the DataColumn.Expression, which describes the entire syntax that is supported by such properties. What you really need, like so many people, is to read the relevant documentation first whenever you have a question. More often than not it will provide the answer.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
You'll find the .NET documentation to be the best that Microsoft have created thus far. I suggest you get acquainted ASAP. The index function of the local install should be your first port of call in the vats majority of cases.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,557
Location
Norway
Programming Experience
10+
cjard said:
If ...DataType.Name = "System.String" Then
Why use type string, adding another possibility for human error?
VB.NET:
If ...DataType = GetType(String) Then
For this alternative you get compiler help in assuring you are comparing a correct and valid type.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
Why use type string, adding another possibility for human error?
VB.NET:
If ...DataType = GetType(String) Then
For this alternative you get compiler help in assuring you are comparing a correct and valid type.
VB.NET:
If ...DataType [B][U]Is[/U][/B] GetType(String) Then
That said, is it really necessary to check the data type of the columns? This is not intended to be a generic solution but rather applies to a single table only. You know exactly which columns are which so you can simply use their indexes.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Why use type string, adding another possibility for human error?
Heh, because I'm a C# guy and in the 5 minutes I had this morning I just could not figure out the code needed for VB. C# uses:

column.DataType == typeof(string)


VB, as I now learn, uses Is GetType(type)

I opened a VB project and tried some combinations of Is, =, GetType, Type.GetType, instance.GetType() and TypeOf but I couldnt find one (other than what I posted) that wouldn't draw a wiggly line, sorry.

It's a consequence of C# and VB assigning different meanings to IS and TYPEOF. Thanks to both you and jmc for the correction(s)..
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
VB.NET:
If ...DataType [B][U]Is[/U][/B] GetType(String) Then
That said, is it really necessary to check the data type of the columns? This is not intended to be a generic solution but rather applies to a single table only. You know exactly which columns are which so you can simply use their indexes.

Yeah, but that's a pain in the backside, because if you know that indexes 1,3,7,10,13 and 14 are int else string then hard coding the indexes (or the column names) requires lots of IFs..
Checking the type is a much better solution IMHO, because it can cope with reordering, new columns and is pastable elsewhere
 

black zero

Well-known member
Joined
Jan 15, 2009
Messages
53
Programming Experience
Beginner
>.< I don't understand what you guys are talking about :confused:, so here goes...

I get it..

Youre filtering, and your combo has the names of the columns.. but you need to do a check:

VB.NET:
If ds.Tables("edit").Columns(cbospr.SelectedItem).DataType.Name = "System.String" Then
  'use LIKE
Else
  'use equals sign
End If

I use this, and sure it works great. However, please see screenshot for funny thing:

How come that there's two exact same result on my datagrid??????
 

Attachments

  • kelancaranedit2.JPG
    kelancaranedit2.JPG
    90.6 KB · Views: 29

black zero

Well-known member
Joined
Jan 15, 2009
Messages
53
Programming Experience
Beginner
No no no, I checked my database and I am sure there's only one transaction with id '1'.

I forgot to tell you all:

Private Sub loadcaridg()
Try
Module1.OpenCon2()
da = New SqlDataAdapter("select * from kelancaran", Module1.xcon)
da.Fill(ds, "edit")
da.Dispose()

For i As Integer = 0 To ds.Tables("edit").Columns.Count - 1
cbospr.Items.Add(ds.Tables("edit").Columns(i).ToString)
Next
cbospr.SelectedIndex = 0

dv = ds.Tables("edit").DefaultView
DgTransaction.DataSource = dv
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

I have this code, and is loaded on form load event. So this code loads all transaction and bind it to datagrid. However, I also use bindingsource to bind the table to the very same datagrid. I use the binding to retrieve the transaction id based from datestart and dateend (see screenshot above, retrieve code below). These codes below were from jmcilhinney.

Private Sub timestart_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles timestart.ValueChanged
KelancaranEditBindingSource.Filter = String.Format("transaction_date >= #{0:M/dd/yyyy}# AND transaction_date <= #{1:M/dd/yyyy}#", Me.timestart.Value, Me.timeend.Value)
End Sub

Private Sub timeend_ValueChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles timeend.ValueChanged
KelancaranEditBindingSource.Filter = String.Format("transaction_date >= #{0:M/dd/yyyy}# AND transaction_date <= #{1:M/dd/yyyy}#", Me.timestart.Value, Me.timeend.Value)
End Sub

In short, two data sources that bind themselves to the same grid. Is that the problem?
 
Top Bottom