[.nethelp]

hi all,

i am trying to do a search in datagrid using variables i made a search text box here is the code

  Dim srch As Integer
        Dim coltotal As Short
        coltotal = DataGridView1.Columns.Count - 2
        For srch = 0 To coltotal
            Dim item As String = DataGridView1.Columns(srch).HeaderText

            Dim mysql As String = "Select * from [" & DBCreaTor.CmbDB.Text & "].[dbo].[" & cmbtbl.Text & "] where [" & item & "] like '%" & TextBox1.Text.Trim & "%'"

            Dim dt3 As New DataTable

            adp = New SqlDataAdapter(mysql, con)

            adp.Fill(dt3)


            DataGridView1.DataSource = dt3

next

the search is not showing anything though on sql management studio it returns result i dont know what is it that am not knowing or adding please help

[CharlieMay]

So if you put a breakpoint where you build the string, what does the string contain?

[.nethelp]

CharlieMay, on 28 September 2012 - 01:56 PM, said:

So if you put a breakpoint where you build the string, what does the string contain?

hey charlieMay long time, first thank you for replying
well it will show
Select * from [May].[dbo].[Try now] where [Product Number] like '%type%'
when i run it in sql it will show a result

[CharlieMay]

It appears that you have the ability to do this against multiple databases (ie, May) How is your connection string handling this?

[.nethelp]

my con string is

"Data Source=Sec-pc\sqlexpress;database=May;integrated security=SSPI;"

[CharlieMay]

hmmm, then the only other thing I can think to check is that if Sec-pc is the machine you tested your query on.

In other words, I've switched from local to a remote machine and when I ran a query locally it worked but didn't in code because the remote database didn't have that item. Is the database you ran that query on the same one you are connecting to in your connection string.

EDIT:
Wait, you're in a loop, is it possible that the last column information doesn't exist because that is the only thing that will show up in that dgv due to you creating everything new with each iteration of the loop.

This post has been edited by CharlieMay: 28 September 2012 - 02:40 PM

[.nethelp]

yes it is all on same pc and same db, but i am thinking maybe the string is not filling the datatable ( dt3) correctly i.e lets say it finds the search then in the next ( NEXT) it fills another dt3 which has no result let me show you the pic of the table just for testing with random everything

CharlieMay, on 28 September 2012 - 02:37 PM, said:

hmmm, then the only other thing I can think to check is that if Sec-pc is the machine you tested your query on.

In other words, I've switched from local to a remote machine and when I ran a query locally it worked but didn't in code because the remote database didn't have that item. Is the database you ran that query on the same one you are connecting to in your connection string.

EDIT:
Wait, you're in a loop, is it possible that the last column information doesn't exist because that is the only thing that will show up in that dgv due to you creating everything new with each iteration of the loop.

exactly that is exactly what i was thinking of but i have no other way to do it i dont know, i need to use the loop to get all column headers to be dynamic

[CharlieMay]

re-read my last post. And on top of that, you're looping through columns in datagridview1 but then changing that by setting its datasource to dt3. Are you sure you're wanting the same DGV for both?

[.nethelp]

yes, when i press view table the dgv is DataGridView1.DataSource = dt so i see the table on datagridview then when i build my string ( search in sql ) lets say find word try i put the result on another dt which is dt3 thats all

[.nethelp]

now i got what you mean yes you are right on the first next the dt changes so it willl search in an empty dt or in a result dt Mmmm but i dont know how to fix it

[.nethelp]

sorry i cant open my eyes anymore its 1:25 am sleepy i will check your post later today and see what can i do and again thank you so much for you efforts and hope we can get it to work

Thank you again

[CharlieMay]

OK, I think you are better off building a string for your statement that will search all columns.

This is a bit tricky but here is an example based off your DGV1 columns.

Dim mysql As String = "Select * from [demo] where " 'This is the base or your sql statement up to the Where (of course you need to change it to your string, I was basing it off a table I have
For srch = 1 To DataGridView1.Columns.Count - 1 'starting at column 1 to the end (I skipped column 0 and I'll explain why as it may be an issue for you)
  Dim item As String = DataGridView1.Columns(srch).HeaderText 'Get the header of the column
  If srch = 1 Then 'If its the first column then we don't need or
     mysql &= "[" & item & "] = '" & srchtext & "' "
  Else
     'the second column puts Or between them
     mysql &= "Or [" & item & "] = '" & srchtext & "' "
  End If

next
Now if we ran this against your datagridview1 we would get a string (mysql) of:

Select * From [demo] where [Product Number] = 'try' OR [Date] = 'try' OR [Book Type] = 'try'

That's all the columns I could see but you get the gist.

So this will return multiple rows if one of the columns = 'try' (you would use your LIKE)
then your dt3 would be set as the datasource after the loop is finished and that grid should be cleared and reset to your new datasource of dt3.
PROBLEMS:
1. DataTypes, a number isn't surrounded with ' ' so a field of numeric type in the database would have to be handled, which means you will have to be able to determine its type when building the statement. My first column was an ID (numeric field) which would cause a datatype mismatch error.
2. This can get very hard to manage

This post has been edited by CharlieMay: 28 September 2012 - 03:33 PM

[.nethelp]

Hey Thank you, yes i can see why you dont want the column of product ID it will throw an exception coz it is an int but the datetime also throws the same conversion.
maybe i should only restrict the search to bookName or booktype only and not let them search for anything in the sql.

coz i dont think it will work this way though so many programs you can throw any search and it will give you results but as you said ( maybe when you do use a search they have specific fields you search in and thus they know the datatypes and make the query to fit them)

so what you think please advise.
and thank you again really

[CharlieMay]

Or instead of limiting your search, you can get the valuetype of the column and base building your string off of that.

DataGridView1.Columns(srch).ValueType.ToString

This should give you the type of data the column holds so that you can build your string based off of it.

[.nethelp]

i tried building the string using like not = and it worked it didnt throw any exception what do you think ? is it good to use?
by the way getting the valuetype is amazing i loved it it can be used in so many things