Using a textbox as a WHERE condition

[diverdan]

Hi there

I am sure this is really easy but I have a form that contains a number of textboxes and also a datagrid.

I have a textbox on the form called txtCustomerID which displays the primary key for tblCustomers and also I have another table called tblQuotes which has CustomerID as a FK.

What I would like is for the datagrid to be showing all quotes that relate to the customer.

This is my code.

Can anyone show me what I need to do to enable what im trying to do.

 Me.WindowState = FormWindowState.Maximized
        Dim conn As New SqlClient.SqlConnection("Server = " & "nx6130" & _
                "; Database = ActionGlass; " & _
                "Integrated Security  = sspi;")

        Dim ds As New DataSet

        Dim daQuotes As New SqlClient.SqlDataAdapter("SELECT tblQuotes.QuoteID,DateOfQuote,CustomerID  FROM tblQuotes WHERE tblQuotes.CustomerID = '10' ", conn)
        Dim daPayments As New SqlClient.SqlDataAdapter("SELECT tblPayments.PaymentID FROM tblPayments", conn)
        Dim daOrders As New SqlClient.SqlDataAdapter("SELECT * from tblOrders", conn)

        daOrders.Fill(ds, "tblOrders")
        daQuotes.Fill(ds, "tblQuotes")
        daPayments.Fill(ds, "tblPayments")
        
        grdInvoices.ReadOnly = True
        grdQuotes.ReadOnly = True
        grdPayments.ReadOnly = True
        grdInvoices.AllowUserToAddRows = False
        grdInvoices.DataSource = ds
        grdQuotes.DataSource = ds
        grdPayments.DataSource = ds
        grdInvoices.DataMember = "tblOrders"
        grdQuotes.DataMember = "tblQuotes"
        grdPayments.DataMember = "tblPayments"


    End Sub

Thank you!!!

 

[fpineda101]

Should be something like this...

Dim daOrders As New SqlClient.SqlDataAdapter("SELECT * from tblOrders WHERE condition = [COLOR=red]'[/COLOR]" & textbox.text & "[COLOR=red]'[/COLOR]", conn)

Don't forget the single quotes!

 

[diverdan]

Hi fpineda101

I tried your line of code but it didn't work for me.

I have the aboce code in the forms loading code.

I moved the following 2 lines as they are used to bind data to some textboxes within the form.
I code now looks like this.

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] frmCustomers_Load([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Load

[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].WindowState = FormWindowState.Maximized
FillDataSetAndView()
BindFields()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection([/SIZE][SIZE=2][COLOR=#800000]"Server = "[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"nx6130"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#800000]"; Database = ActionGlass; "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#800000]"Integrated Security = sspi;"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ds [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] daQuotes [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter([/SIZE][SIZE=2][COLOR=#800000]"SELECT tblQuotes.QuoteID,DateOfQuote,CustomerID FROM tblQuotes WHERE tblQuotes.CustomerID = '10' "[/COLOR][/SIZE][SIZE=2], conn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] daOrders [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter([/SIZE][SIZE=2][COLOR=#800000]"SELECT * from tblOrders WHERE condition = '"[/COLOR][/SIZE][SIZE=2] & txtCustomerID.Text & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2], conn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] daPayments [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter([/SIZE][SIZE=2][COLOR=#800000]"SELECT tblPayments.PaymentID FROM tblPayments"[/COLOR][/SIZE][SIZE=2], conn)
[/SIZE][SIZE=2][COLOR=#008000]'Dim daOrders As New SqlClient.SqlDataAdapter("SELECT * from tblOrders", conn)
[/COLOR][/SIZE][SIZE=2]daOrders.Fill(ds, [/SIZE][SIZE=2][COLOR=#800000]"tblOrders"[/COLOR][/SIZE][SIZE=2])
daQuotes.Fill(ds, [/SIZE][SIZE=2][COLOR=#800000]"tblQuotes"[/COLOR][/SIZE][SIZE=2])
daPayments.Fill(ds, [/SIZE][SIZE=2][COLOR=#800000]"tblPayments"[/COLOR][/SIZE][SIZE=2])

grdInvoices.ReadOnly = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]grdQuotes.ReadOnly = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]grdPayments.ReadOnly = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]grdInvoices.AllowUserToAddRows = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]grdInvoices.DataSource = ds
grdQuotes.DataSource = ds
grdPayments.DataSource = ds
grdInvoices.DataMember = [/SIZE][SIZE=2][COLOR=#800000]"tblOrders"
[/COLOR][/SIZE][SIZE=2]grdQuotes.DataMember = [/SIZE][SIZE=2][COLOR=#800000]"tblQuotes"
[/COLOR][/SIZE][SIZE=2]grdPayments.DataMember = [/SIZE][SIZE=2][COLOR=#800000]"tblPayments"
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff] 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

When I try run the code I get an error message that is highlight on this row of code.

[/COLOR]
[COLOR=#0000ff][SIZE=2]daOrders.Fill(ds, [/SIZE][SIZE=2][COLOR=#800000]"tblOrders"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2]

[/SIZE]
I get an error message saying :
SQLExpection was unhandled. Invalid column name 'condition'


Any help would be great.

Thanks
Shane

 

[RTaulbee]

Check that the column "Condition" is in both the database table and the dataset table

 

[diverdan]

Hi RTaulbee,

Thats the strange thing I don't have condition in any of the tables....

 

[RTaulbee]

but you have it in this sql statement:

Dim daOrders AsNew SqlClient.SqlDataAdapter("SELECT * from tblOrders WHERE condition = '" & txtCustomerID.Text & "'", conn)

"Condition" should be the name of the field like "WHERE OrderDate='" & txtcustomerid.text & "'",conn)

 

[diverdan]

Sorry I should have picked that up. Feel a little stupid now.

The form loads up ok now but there are no results within the datagridview.

Thanks for helping me out on this one.

 

[cjard]

Just a quick Q.. you say you have a dataset.. so you made it with the designer.. Did you know there's a whole load of stuff you can do with VS on this one? Here is a link that will show you how to get data onto your forms, tell the computer how it relates to other data,and have all that done automatically:


http://msdn2.microsoft.com/en-US/library/fxsa23t6(VS.80).aspx

look for the section on fetching data into your app, displaying data on wiondows forms, and displaying related data


basically, you tell your dataset about the relation between the datatables..
then you make a form, drop fields on from the parent table, drop fields on from the child table (but from the link under the parent table in data sources window, not from the top level table - read the link for more info) then fill the child and filter the parent table. the relation causes only those order for that customer to be shown.

you should also look into putting your queries into your table adapters, as they operate in a way that mean your database will be more able to cache the plan for them and execute them faster (parameterized) and they will become immune to sql injection attacks

 

[diverdan]

Hi cjard,

I dont have a dataset set up on the application.
I am running vb express and have an SQL 2000 server. I have not been able to connect to an SQL database as I have problems with adding a new datasource as the wizard always creates a wrong connection string as it thinks I only have SQLExpress installed.
How do I make it so it the add datasource wizard can reference SQL2000?