Solved

Show search results in datagrid

Posted on 2012-03-10
55
200 Views
Last Modified: 2012-05-01
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
0
Comment
Question by:SimonPrice33
  • 28
  • 23
  • 4
55 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37704996
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705008
setthe datasource of the datagrid to the datatable, e.g
Me.LicenseGrid.DataSource = LincenseTable
0
 

Author Comment

by:SimonPrice33
ID: 37705017
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705037
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
 

Author Comment

by:SimonPrice33
ID: 37705042
returns nothing in either at the moment.
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705050
So is it an issue with constructing an SQL statement in VB.net?
0
 

Author Comment

by:SimonPrice33
ID: 37705061
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705066
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
 

Author Comment

by:SimonPrice33
ID: 37705068
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
 

Author Comment

by:SimonPrice33
ID: 37705091
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705093
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
 

Author Comment

by:SimonPrice33
ID: 37705125
Keyword not supported: 'select * from dbo.licences where username'.

this is the response I am getting from the app

any suggestions
0
 
LVL 33

Expert Comment

by:Norie
ID: 37705133
Simon

What 'app'?

What code do you already have?

I assumed you didn't have any.
0
 

Author Comment

by:SimonPrice33
ID: 37705155
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705180
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
 

Author Comment

by:SimonPrice33
ID: 37705185
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705187
What where clause?

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

Open in new window

0
 

Author Comment

by:SimonPrice33
ID: 37705189
the one i just tried to do was select * from swlicenses where version = '7'
0
 

Author Comment

by:SimonPrice33
ID: 37705193
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705201
Does the query work in SQL Server when yout try it manualy?

Is the table actually populated with data?
0
 

Author Comment

by:SimonPrice33
ID: 37705209
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705224
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
 

Author Comment

by:SimonPrice33
ID: 37705226
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705237
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
 

Author Comment

by:SimonPrice33
ID: 37705241
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705259
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
 

Author Comment

by:SimonPrice33
ID: 37705263
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 33

Expert Comment

by:Norie
ID: 37705268
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
 

Author Comment

by:SimonPrice33
ID: 37705309
nope :'(

back to the drawing board...

would it be easier to write a for each look and bind some fields?
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705320
try LINQ
0
 
LVL 33

Expert Comment

by:Norie
ID: 37705413
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
 

Author Comment

by:SimonPrice33
ID: 37705426
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705447
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
 

Author Comment

by:SimonPrice33
ID: 37705452
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705457
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
 

Author Comment

by:SimonPrice33
ID: 37705464
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705468
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
 

Author Comment

by:SimonPrice33
ID: 37705471
yea, i have 2 records with simon in there, have also tried with search terms like %Simon% too and still nothing coming up..
0
 
LVL 33

Expert Comment

by:Norie
ID: 37705500
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705514
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
 

Author Comment

by:SimonPrice33
ID: 37705523
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705529
What's the exact SQL you tried?
0
 

Author Comment

by:SimonPrice33
ID: 37705531
select * from swlicenses where (username like '%Simon%')

before when i was trying wildcard searches i had %'Simon'%
0
 
LVL 33

Expert Comment

by:Norie
ID: 37705544
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
 

Author Comment

by:SimonPrice33
ID: 37705548
i havent tried a loop yet, i dont know how i would write it..
0
 
LVL 33

Expert Comment

by:Norie
ID: 37705553
I posted the code a couple of posts back.
0
 

Author Comment

by:SimonPrice33
ID: 37705555
didnt see that sorry..
0
 

Author Comment

by:SimonPrice33
ID: 37705571
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
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37705576
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
 

Author Comment

by:SimonPrice33
ID: 37705582
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
 
LVL 33

Expert Comment

by:Norie
ID: 37705595
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
 

Author Comment

by:SimonPrice33
ID: 37705601
i thought this earlier but am i missing the sqldatareader?
0
 
LVL 33

Expert Comment

by:Norie
ID: 37705614
That code on it's own works, so I don't think you need a datareader.
0
 

Author Comment

by:SimonPrice33
ID: 37705657
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
 

Author Closing Comment

by:SimonPrice33
ID: 37914103
still couldnt get it to work but thanks anyway :)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now