Show search results in datagrid

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.

setthe datasource of the datagrid to the datatable, e.g
Me.LicenseGrid.DataSource = LincenseTable

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...

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?

returns nothing in either at the moment.

So is it an issue with constructing an SQL statement in VB.net?

yes... probably didnt make myself too clear in my original question for which i apoligise..

been trying to learn this since 8am this morning..

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 +"';"

Select allOpen in new window

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

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?

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)

Select allOpen 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.

Keyword not supported: 'select * from dbo.licences where username'.

this is the response I am getting from the app

any suggestions

Simon

What 'app'?

What code do you already have?

I assumed you didn't have any.

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...

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?

That now pulls the information through but it not filtering\searching on the specific where clause...

BTW, all this help is very much appreciated :)

What where clause?

There isn't one here.:)

Dim sqlSelect As String = "select * from swlicenses"

Select allOpen in new window

the one i just tried to do was select * from swlicenses where version = '7'

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()

Does the query work in SQL Server when yout try it manualy?

Is the table actually populated with data?

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...

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.

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....

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.

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...

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.

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...

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

Select allOpen in new window

nope :'(

back to the drawing board...

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

try LINQ

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.

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???

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?

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 :)

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.

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...

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?

yea, i have 2 records with simon in there, have also tried with search terms like %Simon% too and still nothing coming up..

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.

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

Select allOpen 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.

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....

What's the exact SQL you tried?

select * from swlicenses where (username like '%Simon%')

before when i was trying wildcard searches i had %'Simon'%

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 %.

i havent tried a loop yet, i dont know how i would write it..

I posted the code a couple of posts back.

didnt see that sorry..

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

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

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?

i thought this earlier but am i missing the sqldatareader?

That code on it's own works, so I don't think you need a datareader.

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...

still couldnt get it to work but thanks anyway :)