[2005] Using a textbox for a WHERE condition

[spnz]

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.

 Private Sub FillDataGrids()

        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 daOrders As New SqlClient.SqlDataAdapter("SELECT * from tblOrders WHERE condition = '" & txtCustomerID.Text & "'", 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"




        With grdQuotes

            '  .Columns("QuoteID").HeaderText = "Quote ID"
            ' .Columns("DateOfQuote").HeaderText = "Date Of Quote"

        End With

    End Sub

When I try run the above code I get an error message on the line

        daOrders.Fill(ds, "tblOrders")

The error tells me SQLExpception was unhandled. Invalid column name 'condition'


Can anyone help.

Thanks
Shane


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

[Shaggy Hiker]

First off, put a try...catch block around that whole chunk of code that deals with the DB. You can never be certain that something bizarre hasn't happened to your database, and a Try...Catch costs nothing if no exceptions are raised, so it is both cheap and safer.

Also, catch exceptions of type System.Data.SQLException (or maybe it is Data.SQL.SQLException, something like that anyways). When you see what the error message is, you'll likely get somewhat more information about the error, though with SQL errors, they may not be all that much more informative.

Second, have you confirmed that you have all of those columns, and that they are spelled correctly?

Third, it is somewhat risky to take the text directly from a textbox and put it into a query. A malicious person with a little bit of knowledge could screw up your DB quite nicely. Never tried it myself, but in the code you have, I believe that somebody entering something like:

', DROP tblQuotes

into your textbox, would end your suffering pretty well.

[spnz]

Hi there


Ok so I managed to get rid of the error message but now no data is been displayed on the datagridviews...

Can anyone suggest what I need to add to my code to get the datagrids displaying the information that I want?

My updated code.

Imports System.Data
Imports System.Data.SqlClient
 
Public Class frmCustomers
    Inherits System.Windows.Forms.Form
 
    
 
 
 
 
    Dim objConnection As SqlConnection = New _
        SqlConnection("server= nx6130;database=actionglass;user id=sa;Trusted_Connection=True;")
    Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter( _
        "SELECT tblCustomers.CustomerName,ContactName,PhoneNumber,FaxNumber,MobNumber,EmailAddress,Address1,Address2,Address3,Address4,DAddress1,DAddress2,DAddress3,DAddress4,Comments,CustomerID " & _
        "FROM tblCustomers " & _
        "ORDER BY CustomerName", objConnection)
 
    Dim objDataSet As DataSet
    Dim objDataView As DataView
 
 
 
Private Sub frmCustomers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Me.WindowState = FormWindowState.Maximized
 
        FillDataSetAndView()
        BindFields()
        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 * FROM tblQuotes WHERE tblquotes.CustomerID = '" & txtCustomerID.Text & "'", conn)
        Dim daOrders As New SqlClient.SqlDataAdapter("SELECT * FROM tblOrders WHERE tblOrders.CustomerID = '" & txtCustomerID.Text & "'", conn)
        Dim daPayments As New SqlClient.SqlDataAdapter("SELECT * FROM tblPayments WHERE tblPayments.CustomerID = '" & txtCustomerID.Text & "'", 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"
 
 
 
 
        With grdQuotes
 
            '  .Columns("QuoteID").HeaderText = "Quote ID"
            ' .Columns("DateOfQuote").HeaderText = "Date Of Quote"
 
        End With
 
    End Sub

Thanks for the advise on entering data directly into a query. I was not aware of that. (Yes im fresh from over from access)

Thanks

[Shaggy Hiker]

I really have only used datagrids in a fairly basic way, but despite it being basic, it was similar to how you have done it.

The first thing I would do would be to confirm that the tables in the dataset actually have data. Offhand, I can't think of the correct property, but there must be something like a Rows collection of the Tables collection of the dataset. Have yu confirmed that you are getting data into the dataset?

[TokersBall_CDXX]

it appears you're not calling .Databind
which binds the data to the datagrid... perhaps you should start your search there

[Shaggy Hiker]

Hmmm, I've never used databind, it certainly isn't necessary in 2003, and I wouldn't expect it to be necessary in 2005.

The only difference I have between the way I fill a datagrid, and the way shown here is this:

dg1.Enabled = False
dg1.DataSource = ds.Tables(0)
dg1.Enabled = True


I notice that I am specifying a table, rather than just a dataset, even though there is only one table in the dataset. You may try that.

The other difference is that enable and disable thing. I wrote this about a year ago, and I currently can't remember what that does. Try it out, maybe it's important.

[jmcilhinney]

DataBind is a method of the WebForms DataGrid. This is obviously a WinForms app from the use of things like "Me.WindowState" so that is not the issue.

You say that no data is displayed in the grids. That suggests to me that no data is being returned by your queries. You can test this by checking the return value of Fill, which is the number rows returned by the query. Try this:

MessageBox.Show(daOrders.Fill(ds, "tblOrders").ToString())
        MessageBox.Show(daQuotes.Fill(ds, "tblQuotes").ToString())
        MessageBox.Show(daPayments.Fill(ds, "tblPayments").ToString())

to see if you're getting any data. My guess is that your CustomerID column contains numbers. You're putting single quotes around the values in your SQL code, which makes them text values, so they can never match. Remove the single quotes and the values will be interpreted as numbers.

Also, you absolutely should NOT use string concatenation to build SQL statements. You should be using parameters for a number of reasons. I've given examples too many times lately so do a search for my user name and "Parameters.Add" to find them.