Show search results in datagrid

Hi

i am currenty trying to show search results in a datagrid from a SQL table.

table and database are both called licenses

I am trying to show the results for assigned user, but for the life of me cant figure out how to get the items into the data grid.

Any and all assistance gratefully received.

Thanks

Simon
SimonPrice33Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieData ProcessorCommented:
Simon

How did you get the search 'results'?

If you've used a query then you should be able to bind the query to the datagrid just as you would bind a table.
0
nepaluzCommented:
setthe datasource of the datagrid to the datatable, e.g
Me.LicenseGrid.DataSource = LincenseTable
0
SimonPrice33Author Commented:
In SQL Server I am using

Select * From licenses Where username = 'Simon'

howver returning nothing, when using simple select * from licenses return 4 results, 3 Simon, 1 Catherine

SQL is far from my strong point.... as you can probably see...
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

NorieData ProcessorCommented:
Simon

Do you mean the query is returning nothing when you run it in SQL Server?

Or does it return nothing when you try it in VB.NET?
0
SimonPrice33Author Commented:
returns nothing in either at the moment.
0
nepaluzCommented:
So is it an issue with constructing an SQL statement in VB.net?
0
SimonPrice33Author Commented:
yes... probably didnt make myself too clear in my original question for which i apoligise..

been trying to learn this since 8am this morning..
0
NorieData ProcessorCommented:
There's nothing you need to change about that SQL statement, unless you name for the criteria is from a variable.

Something like this would do.
strName = "Simon"

strSQL = "SELECT * FROM licences WHERE username = '" + strName +"';"

Open in new window

0
SimonPrice33Author Commented:
the criteria will be coming from a variable, but the issue im having is getting the search results to populate a data grid in VS 2010
0
SimonPrice33Author Commented:
Try
            Me.LicensesTableAdapter.Fill(Me.LicensesDataSet.licenses)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

this brings up the entire table of results but i want to be able to refine these down to certain criteria such as username, machinename, manufacturer application and a few more...

any idea how i would do this?
0
NorieData ProcessorCommented:
Well this is how I did it.

        Dim strDB = "<databasename>"
        Dim strSRV = "<servername>"

        Dim strConn As String = "Data Source=" + strSRV + ";Initial Catalog =" + strDB + ";Integrated Security=True"

        Dim strUser = "Simon"

        Dim strSQL As String = "Select * From dbo.licences WHERE username= '" + strUser + "'"
        Dim conn As New SqlConnection(strConn)

        Dim adp As New SqlDataAdapter(strSQL, conn)

        Dim ds As New DataSet()

        conn.Open()

        adp.Fill(ds)

        conn.Close()

        DataGridView1.DataSource = ds.Tables(0)

Open in new window

Replace <servername> and <databasename> with your server and database.

Obviously the username is hardcoded in this example but it could come from a combobox, textbox, wherever.
0
SimonPrice33Author Commented:
Keyword not supported: 'select * from dbo.licences where username'.

this is the response I am getting from the app

any suggestions
0
NorieData ProcessorCommented:
Simon

What 'app'?

What code do you already have?

I assumed you didn't have any.
0
SimonPrice33Author Commented:
I didnt \ dont have any code, but I am trying to pull information out into a datagrid for an app i am building...

what I have now from your code is;


    Dim connstring As String = "server=simon-pc;database=licenses;trusted_connection=yes;"

    Dim sqlconn As New SqlConnection
    Dim sqlcmd As New SqlCommand

Dim sqlSelect As String = "select * from swlicenses"

        Dim adpt As New SqlDataAdapter(sqlSelect, connstring)
        Dim ds As New DataSet

        sqlconn.ConnectionString = connstring
        sqlconn.Open()
        sqlcmd.Connection = sqlconn
        sqlcmd.CommandText = "select * from swlicenses"
        adpt.Fill(ds)

        sqlcmd.ExecuteNonQuery()
        sqlconn.Close()

        resultsDG.DataSource = ds.Tables

upon searching nothing comes back although there are records in the app and all lines dissapear...
0
NorieData ProcessorCommented:
You need to specify which table from the dataset you want to display in the datagrid, try ds.Tables(0) instead of just ds.Tables.

PS Why do you have the command part?
0
SimonPrice33Author Commented:
That now pulls the information through but it not filtering\searching on the specific where clause...

BTW, all this help is very much appreciated :)
0
NorieData ProcessorCommented:
What where clause?

There isn't one here.:)
Dim sqlSelect As String = "select * from swlicenses"

Open in new window

0
SimonPrice33Author Commented:
the one i just tried to do was select * from swlicenses where version = '7'
0
SimonPrice33Author Commented:
but then none of my where clauses are returning anything..

this is how my database was created, would it be anything to do with the table constructs?

Dim connstring As String

        Dim sqlconn As New SqlConnection()
        Dim sqlcmd As New SqlCommand()

        connstring = "server=" & pcNameTxt.Text & ";database=" & dbNameTxt.Text & ";trusted_connection=yes;"

        sqlconn.ConnectionString = connstring
        sqlconn.Open()
        sqlcmd.Connection = sqlconn
        sqlcmd.CommandText = "create table " & tableTxt.Text & "(Manufacturer nvarchar (255)," & _
                                                                "username nvarchar (255)," & _
                                                                "CI nvarchar (12)," & _
                                                                "AppName nvarchar (255)," & _
                                                                "version nvarchar (5)," & _
                                                                "Assyst_Ref nvarchar (8)," & _
                                                                "PO nvarchar (10)," & _
                                                                "order_date date," & _
                                                                "disk_No nvarchar (5)," & _
                                                                "invoice nvarchar (12)," & _
                                                                "license nvarchar (255)," & _
                                                                "disc_no integer," & _
                                                                "status nvarchar (8)," & _
                                                                "date_logged date," & _
                                                                "date_out date," & _
                                                                "date_in date," & _
                                                                "date_disposed date," & _
                                                                "disposed_by nvarchar (255)," & _
                                                                "site_of_dispose nvarchar (255), " & _
                                                                ")"

        sqlcmd.ExecuteNonQuery()
        sqlconn.Close()
0
NorieData ProcessorCommented:
Does the query work in SQL Server when yout try it manualy?

Is the table actually populated with data?
0
SimonPrice33Author Commented:
for some reason no..

nothing seems to work...

i have been on this for hours now... have scoured the net, read books and it all seems that

select * from swlicenses where version = '7' should return resutls....

if i try select username from swlicenses this returns all the usernames but when i try
select username from swlicenses where username = 'simon' i get nothing...
0
NorieData ProcessorCommented:
Try dbo.swlicences.

By the way, do you still have that command stuff?

I've not checked but perhaps that's interfering in some way.

Actually just checked, makes no difference, still don't know why you have it though.
0
SimonPrice33Author Commented:
it wont run at all without having the sqlcmd.commandtext in there...

i have tried the dbo.swlicenses too...  

i find it really odd that its not working....
0
NorieData ProcessorCommented:
Have you tried just taking the code I posted and plugging in the variable things that need to be changed?

All you really need is the server and database name and the SQL.

Oh, and change the name of the datagrid in the code.
0
SimonPrice33Author Commented:
yup... tried that...

its puzzles me even more though that the select statement isnt working in sql server management studio either...

im trying it completely fresh on a new machine now to see if that has any effect...
0
nepaluzCommented:
When you browse the database, does it contain any records? If so, ensure you have a connection that is open to the database before executing the query.
0
SimonPrice33Author Commented:
i have a connection when i pull the open the form,  and it pulls through all of the records, granted there are only 4 records in there at the moment, however the is data in there and it oes pull it through..

just when i try and filter\search\select the information it returns nothing...
0
NorieData ProcessorCommented:
If you can't get the query to work with criteria there is an alternative, use DataView.

I've not used it much but here's the general idea, hope I've got the syntax right.
   Dim dv As DataView

        dv = New DataView(ds.Tables(0), "username = 'Simon'" ,"username DESC", DataViewRowState.CurrentRows)

        DataGridView1.DataSource = dv

Open in new window

0
SimonPrice33Author Commented:
nope :'(

back to the drawing board...

would it be easier to write a for each look and bind some fields?
0
nepaluzCommented:
try LINQ
0
NorieData ProcessorCommented:
Simon

Nope what?
 
I was originally, before the DataView idea, going to suggest looping through the table and it is possible.

Wasn't sure if that would be an option you would consider though.
0
SimonPrice33Author Commented:
nope, your previous solution didnt work...

i was orignially thinking running a for each loop, but if i did that I have no idea how I would create an array in some combo boxes..

for instance,

if we searched on username and that username has 10 items of software installed, i would want the field for application name to have a drop down with all other software titles assigned to them and when this is selected, of the information.

but with the select statement not even bringing up username statement in the SQL Server Management Studio, how would we get past this???
0
NorieData ProcessorCommented:
How exactly did you try the DataView?

Did you remove the criteria from the query so it returns all tecords?

For the looping I wasn't thinking about comboboxes, I was thinking about looping the rows and checking if the name matched the name you were looking for.

If it did then add the record to the datagrid.

PS How exactly did you try the query in SSMS and what errors, if any where there?
0
SimonPrice33Author Commented:
tried it exactly how you gave me, along with pulling the datagridview in and linking it via the dataconnections and that VS give.

theres no error messages that some out either... just returns nothing

can we have a look at the for each route please :)
0
NorieData ProcessorCommented:
Simon

I am looking at the for each loop, but as far as I can see you still haven't said how you tried the query in SMSS.

By the way what do you mean here?

linking it via the dataconnections and that VS give.
0
SimonPrice33Author Commented:
sorry, right clicked on table, script table as select to,

deleted all the text out and tried select * from swlicenses which returns everything, then select * from swlicenses where username = 'Simon' which returns just the headers and noth rows.

no error messages...
0
NorieData ProcessorCommented:
Stupid question, but there are records in the table with the username Simon?

Did you try any other names or perhaps a wildcard to return all names beginning with S?
0
SimonPrice33Author Commented:
yea, i have 2 records with simon in there, have also tried with search terms like %Simon% too and still nothing coming up..
0
NorieData ProcessorCommented:
Tried another name, another field?

How about trying the Design Query Editor... in SSMS?

You'll find that under Query.

PS Really sorry about the stupid questions.

PPS I've almost got the loop thing going, need to add something for criteria.
0
NorieData ProcessorCommented:
Well here's the loop.

        DataGridView1.ColumnCount = ds.Tables(0).Columns.Count

        Dim x As Integer = 0

        For I = 0 To ds.Tables(0).Rows.Count - 1

            ' case sensitive

            If ds.Tables(0).Rows(I)("username") = "simon" Then

                DataGridView1.Rows.Add()


                For j = 0 To ds.Tables(0).Columns.Count - 1

                    DataGridView1.Rows(x).Cells(j).Value = ds.Tables(0).Rows(I).ItemArray(j)

                Next
                x = x + 1
            End If
        Next

Open in new window

Not pretty but it works.

PS I've got another idea, kind of off topic though - a combobox with a list of usernames.

This can be populated with a simple query and can be used to select the name for the criteria.
0
SimonPrice33Author Commented:
ok... interesting... i can search on integer fields and return the values no problem so it must be it thinks there is padding in the username field.

i have no also got the % in the right place when searching a like which returns results... .

im glad im not going crazy although  maybe at the end of this...

dont apologise about stupid questions, or what you think are stupid questions, im new with SQL..  and have a massive learning curve to get through by tuesday....
0
NorieData ProcessorCommented:
What's the exact SQL you tried?
0
SimonPrice33Author Commented:
select * from swlicenses where (username like '%Simon%')

before when i was trying wildcard searches i had %'Simon'%
0
NorieData ProcessorCommented:
Yeah, sorry about that - just looked back throught the posts.

How about the loop have you tried that?

If you have what did you use in the If statement for wildcards?

You would need to use * instead of %.
0
SimonPrice33Author Commented:
i havent tried a loop yet, i dont know how i would write it..
0
NorieData ProcessorCommented:
I posted the code a couple of posts back.
0
SimonPrice33Author Commented:
didnt see that sorry..
0
SimonPrice33Author Commented:
can y ou send me everything you had to get it to work please as ive just had this pop up

ColumnCount property cannot be set on a data-bound DataGridView control.

thanks

Simon
0
NorieData ProcessorCommented:
Here you are:
        Dim strDB = "<databasename>"
        Dim strSRV = "<servername>"

        Dim strConn As String = "Data Source=" + strSRV + ";Initial Catalog =" + strDB + ";Integrated Security=True"


        Dim strUser = "Simon"

        Dim strSQL As String = "Select * From dbo.licences "
        Dim conn As New SqlConnection(strConn)

        Dim adp As New SqlDataAdapter(strSQL, conn)

        Dim ds As New DataSet()

        conn.Open()


        adp.Fill(ds)

        conn.Close()


        DataGridView1.ColumnCount = ds.Tables(0).Columns.Count

        Dim x As Integer = 0

        For I = 0 To ds.Tables(0).Rows.Count - 1

            ' case sensitive

            If ds.Tables(0).Rows(I)("username") Like "*i*" Then

                DataGridView1.Rows.Add()


                For j = 0 To ds.Tables(0).Columns.Count - 1

                    DataGridView1.Rows(x).Cells(j).Value = ds.Tables(0).Rows(I).ItemArray(j)

                Next
                x = x + 1
            End If
        Next
        'Dim dv As New DataView(ds.Tables(0), "username<>'simon'", "username ASC", DataViewRowState.CurrentRows)

        'DataGridView1.DataSource = dv



    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SimonPrice33Author Commented:
i still get the same issue here

ColumnCount property cannot be set on a data-bound DataGridView control.

from

 DataGridView1.ColumnCount = ds.Tables(0).Columns.Count
0
NorieData ProcessorCommented:
What's the exact code you used?

Also, how did you create the DataGridView?

Is it bound to anything?


Why not try it with a new DataGrid?
0
SimonPrice33Author Commented:
i thought this earlier but am i missing the sqldatareader?
0
NorieData ProcessorCommented:
That code on it's own works, so I don't think you need a datareader.
0
SimonPrice33Author Commented:
unfortunately not for me... and i copies and pasted directly in to my sub...

im going to sleep on it for tonight now i think...

thanks for your help...
0
SimonPrice33Author Commented:
still couldnt get it to work but thanks anyway :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.