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?
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
All Courses

From novice to tech pro — start learning today.