bindingsource and column names

[besmart2000]

Hi
i have a form with bindingnavigator , bindingsource , dataset and tableadaptor generated in vs2005.
I created textboxes bound to bindingsource
I want to get the base table column names and datatype for all textboxes in the form
and access DataTable object from within binding source
pls , can you provide me with code to do this
thanks

 

[cjard]

youre making life way too hard work for yourself. read the dw2 link in my signature

 

[besmart2000]

Re:

Hi cjard
Really , i want to create query by example by Generatin filter conditions based on the data the user enter in the textbox controls in the WindoForm like (field1 = value1 and field2 = 'value2'), so i need like that service that i described in my post.
I hope that you can direct me to the solution , and i did not find my answer in dw2 link.
best regards,

 

[cjard]

i had a spec once to search any field of any form in my app.. i built parameterised sqls dynamically and populated them.. if you come up with another solution, let me know!

you dont really care about the textboxes binding.. just look through the bound columns to see what the user is searching for..

            For i As Integer = 0 To tabl.Columns.Count - 1
                If tabl.Rows(0).Item(i) Is Nothing OrElse TypeOf (tabl.Rows(0).Item(i)) Is System.DBNull OrElse tabl.Rows(0).Item(i).ToString().Trim() = "" Then Continue For

                numOfWhereClauses += 1
                If tabl.Columns.Item(i).DataType.ToString() = "System.String" Then
                    searchCommand.CommandText += String.Format(" AND {0} LIKE :{0} ", tabl.Columns.Item(i).ColumnName)
                Else
                    searchCommand.CommandText += String.Format(" AND {0} = :{0} ", tabl.Columns(i).ColumnName)
                End If
                searchCommand.Parameters.AddWithValue(tabl.Columns.Item(i).ColumnName, tabl.Rows(0).Item(i))

            Next i

 

[besmart2000]

Re:

Hi Cjard
Good Idea
I have used your code and generated the following expression:

"AND CategoryID = :CategoryID AND CategoryName LIKE :CategoryName AND Description LIKE escription"

I can avoid first "AND" , but how i can substitute the variables :CategoryID , :CategoryName , description with their corresponding values in the bindingsource.

When i try to generate that expressin , if i focused on CategoryID (which is a primary key) i must enter a value, but really i want to let it blank (null) to be out of search criteria, but i can not exit field. So how can i let the bindindingSource relaxed for all PK constraints.
I used :
tabl.DataSet.EnforceConstraints = False , but it has no effect.

I use the same controls which is bound to the BindingSource for entering Search Criteria.

the statement:
searchCommand.Parameters.AddWithValue(tabl.Columns.Item(i).ColumnName, tabl.Rows(0).Item(i)) has no effect in generating code
best regards,

 

[cjard]

what i should also have mentioned is that this is used with a query that is particular per form:

on my PersonDetails form, I have a SQL fragment in the db:

SELECT * FROM personForm_searchView WHERE 1=1


now I add to that the fragment that I build:

SELECT * FROM personForm_searchView WHERE 1=1 AND CategoryID = :CategoryID AND CategoryName LIKE :CategoryName AND Description LIKE escription


Now you can see why the first AND exists, because putting WHERE 1=1 means I can use AND.... this makes the code cleaner because no extra code is needed to avoid the first AND


Now I run that query! The searchCommand.Parameters.AddWithValue(tabl.Columns.Item(i).ColumnName, tabl.Rows(0).Item(i)) puts what the user has typed, into the query parameters which are
:CategoryID
:CategoryName
escription

My db is oracle by the way. It uses parameters like this. SQLServer uses @parameters and Access uses ? for parameters (order of addition is important in access)

So there is my search.. its safe, because nothing the user can type will break the string (unlike old string concat to build dynamic queries)


What I then do is run the query via a dataadapter and read the results into a datagrid. personForm_searchView is a View in the database that selects only some things from some tables, related to the person.

When the user double clicks the row in the grid(it opened in a new form) I use a delegate to pass back the row they clicked, to the original form. The form then knows which rows are the PK, and performs a FILL of its datatables, based on those

 

[besmart2000]

Re

Hi cjard
Thanks for your help
I tried to apply your logic , and i got error:
"Must declare the variable '@CategoryID'."
I use sql express 2005
I uploaded my project , perhabs you may fix my bug
best regards,

 

[besmart2000]

Hi Cjard
Thank you very much
I had resolved the bug , and it is woooooorking as you explained.
Thank you again.
Just , small problem, when i focus in the textbox (in my case CategoryID ) which is a primary key and left it null , i can't leave it, so i have to enter a value.
How i can let these controls relaxed
best regards,

 

[cjard]

mmmh.. ensure you have set EnforceCOnstraints on your dataset, to false.. and I mean in code, of e.g. Form Load, not in design...

If you still get problems, let me know.

 

[cjard]

Setting CausesValidation = false on the textbox will allow you to leave it.. BUT the data typed into the fields wont be transported into the datatable, butcause its not a valid primary key. Either set the value to -1 using custom validation, or look through the bindingsource.list rather than the datatable rows, to get your search terms:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] tabl [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable()[/SIZE]
[SIZE=2]tabl = [/SIZE][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][SIZE=2](CategoriesBindingSource.DataSource.Categories, DataTable)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] tabl.Columns.Count - 1[/SIZE]

better:

Dim drv as DataRowView = CategoriesBindingSource.List(0)
[SIZE=2]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] drv.Row.Table.Columns.Count[/SIZE]
[SIZE=2]If drv(i).GetType = "System.String" Then sqlCommand += string.format("AND {0} LIKE @{0}"...[/SIZE]

[/SIZE]

 

[cjard]

i never encountered this i think because i only ever worked with fields that were primary key strings, not autoincrementing ints

 

[besmart2000]

Hi Cjard
I Set CausesValidation = false on the textbox and allow me to leave it
I began to test , and discovered strange results
The parameters that is passed in the query string is the original value of the row , not the value that is i typed in the textbox?!
I mean that it seem that bindingsource control is not aware of the (new) vlaues , but reterive the original value.

a) causeValidation = flase , let Bindingsource don't care with the new value , and restore the original value!!, so i must let it true

b) changing values of textbox with PK fields , let Bindingsource don't care with the new value , and restore the original value!! ( because i let causeValidation = flase)

I think the generated TableAdaptor class may set some properties out of my seen.

So i must get values from textbox controls , not from bindingsource.

I think i must change the logic and iterate over textbox controls ( not BindingSource) , especially if the bindingsource has fields which have not corresonding bound textbox controls, will appear in the sql expression.
Please advice me, i am confused.
Best regards,

 

[cjard]

I forgot to mention, your code doesnt call BindingSource.EndEdit() before it tries to look though the list...

Ive edited my code sample above to include an example of such:

[B]CategoriesBindingSource.EndEdit()[/B]
Dim drv as DataRowView = CategoriesBindingSource.List(0)
 
[SIZE=2][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] drv.Row.Table.Columns.Count[/SIZE]
[SIZE=2]If drv(i).GetType = "System.String" Then sqlCommand += string.format("AND {0} LIKE @{0}"...[/SIZE][/SIZE]

 

[besmart2000]

Hi CJard
i set : categoryID.CauseValidation = flase
i used at start of search:
CategoriesBindingSource.EndEdit()

I entered the following values:
i let categoryID blank
CategoryName = "dd"
CategoryDescription ="kk"

i expect the result:
CategoryName = @CategoryName and CategoryDescription =@CategoryDescription
but
I got the sql expression
CategoryID= 5 and CategoryName = @CategoryName and CategoryDescription =@CategoryDescription

using MessageBox.Show(drv(i).ToString()) ==> displayed the following values:
5 ( although it is blank)
dd
kk
What is the wrong??
Execuse me: can you modify my code that i send before , and attach the new version.

best regards,

 

[cjard]

For example