Howdy, Stranger!

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

Categories

I was wondering how I can create a relational query?

HGeneAnthonyHGeneAnthony Member Posts: 92
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

  • infidelinfidel Member Posts: 2,900
    : 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.
  • HGeneAnthonyHGeneAnthony Member Posts: 92
    : : 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.
  • infidelinfidel Member Posts: 2,900
    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.
  • Chris BrownChris Brown USAMember Posts: 4,624 ✭✭

    __ { http://forcoder.org } free video tutorials and ebooks about ( Ruby Perl Java C++ Go Swift Objective-C MATLAB R PL/SQL Python Visual Basic PHP Scratch Visual Basic .NET JavaScript Delphi C Assembly C# ML D FoxPro Prolog Lisp Erlang VBScript COBOL ABAP Clojure Hack Scheme F# Kotlin LabVIEW Ada Logo Fortran Bash Awk Transact-SQL Apex Lua Alice Scala Crystal Julia SAS Dart Rust ) _______

Sign In or Register to comment.