I was wondering how I can create a relational query?

I have the code on how to create a query but I'm not sure how to tell the textbox to list the specified field. Ex. Here's the tables.

tblCustomers
customer_ID Primary Key
customer_Name

tblOrders
order_ID Primary Key
order_Product
customer_ID (Foreign field)

"SELECT FROM tblCustomers LEFT OUTER JOIN tblOrders ON tblCustomers.customer_ID=tblOrders.customer_ID WHERE tblCustomers.customer_ID=" & txtID & "

I don't know how to put it in the text boxes so that they know what field to return info from. I tried tblOrders.customer_ID but that didn't work. Do I have to create a seperate SQL statement inside the original SQL statement? Will this cause any problems?

Comments

  • : I have the code on how to create a query but I'm not sure how to tell the textbox to list the specified field. Ex. Here's the tables.
    :
    : tblCustomers
    : customer_ID Primary Key
    : customer_Name
    :
    : tblOrders
    : order_ID Primary Key
    : order_Product
    : customer_ID (Foreign field)
    :
    : "SELECT FROM tblCustomers LEFT OUTER JOIN tblOrders ON tblCustomers.customer_ID=tblOrders.customer_ID WHERE tblCustomers.customer_ID=" & txtID & "
    :
    : I don't know how to put it in the text boxes so that they know what field to return info from. I tried tblOrders.customer_ID but that didn't work. Do I have to create a seperate SQL statement inside the original SQL statement? Will this cause any problems?

    You can nest select statements in other select statements, but I don't understand why you would need to here. You need to just put the field(s) you want returned between SELECT and FROM. I wonder, though, why you're using an outer join and don't have a many-to-many relationship set up between your tables.
  • : : I have the code on how to create a query but I'm not sure how to tell the textbox to list the specified field. Ex. Here's the tables.
    : :
    : : tblCustomers
    : : customer_ID Primary Key
    : : customer_Name
    : :
    : : tblOrders
    : : order_ID Primary Key
    : : order_Product
    : : customer_ID (Foreign field)
    : :
    : : "SELECT FROM tblCustomers LEFT OUTER JOIN tblOrders ON tblCustomers.customer_ID=tblOrders.customer_ID WHERE tblCustomers.customer_ID=" & txtID & "
    : :
    : : I don't know how to put it in the text boxes so that they know what field to return info from. I tried tblOrders.customer_ID but that didn't work. Do I have to create a seperate SQL statement inside the original SQL statement? Will this cause any problems?
    :
    : You can nest select statements in other select statements, but I don't understand why you would need to here. You need to just put the field(s) you want returned between SELECT and FROM. I wonder, though, why you're using an outer join and don't have a many-to-many relationship set up between your tables.
    :
    I can change it. I was searching through my MSDN and the help file I found on this used an outer join. I just wanted to see if it would work so I tested it out. I can switch it to an inner join. My main concern is how can I set it to display the output to the textbox this way.
  • Oh, I think I get it. All you want to do is display the contents of a specific field in a textbox? Easy breezy.

    txtText.Text = Format(rstADORecordset.Fields("FIELDNAME").Value)

    There is also a "shorthand" notation for recordset fields:

    txtText.Text = Format(rstADORecordset!FIELDNAME)

    The Format() function is to convert NULL values to blank strings so you don't get invalid use of null errors. You can also append a nullstring to your field instead of using the Format() function:

    txtText.Text = rstADORecordset!FIELDNAME & vbNullString

    You can set FIELDNAME to any of the fields returned by your SQL statement.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories