Solved

queries ?

Posted on 2015-02-16
7
83 Views
Last Modified: 2015-02-17
vb.net, vs2012
why won't this query work?

SELECT        CustomerID, Address1, Address2, CellPhone, City, County, CountyCode, DateOfBirth, DriversLicense, EmailAddress, Fax, FirstName, HomePhone, IsEmployee, Notes, PayrollDeduction, SocialSecurity,
                         WorkPhone, Zip, Do_Not_Call, IsBusiness, IsCurrent, BaseAddressID, ActiveDate, LastName, MiddleInitial, State, DriversLicenseState, LastWriteCount, DriversLicenseExpiration,
                             (SELECT        stock_num
                               FROM            stock
                               WHERE        customers.customerID = stock.PurchasedFrom) AS stkNo
FROM            Customers

I get an error in the Select command???
0
Comment
Question by:MBHEY131
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 40613403
Try

SELECT       A. CustomerID, a.Address1, a.Address2, a.CellPhone, a.City, a.County, a.CountyCode, a.DateOfBirth, a.DriversLicense, a.EmailAddress, a.Fax, a.FirstName, a.HomePhone, a.IsEmployee, a.Notes, a.PayrollDeduction, a.SocialSecurity, 
                         A.WorkPhone, a.Zip, a.Do_Not_Call, a.IsBusiness, a.IsCurrent, a.BaseAddressID, a.ActiveDate, a.LastName, a.MiddleInitial, a.State, a.DriversLicenseState, a.LastWriteCount, a.DriversLicenseExpiration, b.stock_num as stkNO
                          
FROM            Customers a join stock b on customers.customerID = stock.PurchasedFrom

Open in new window

0
 

Author Comment

by:MBHEY131
ID: 40614797
I know about JOIN - However I am trying to avoid using it because the UPDATE, DELETE, INSERT etc. won't be automatically generated in the TABLE ADAPTER WIZARD of vb.net in visual studio 2012 as I understand it, although I don't understand why vb.net of vs2012 won't accept the SQL as I have written, It seems to adhere to all of the SQL rules as I understand them.
0
 
LVL 77

Expert Comment

by:arnold
ID: 40614826
I am not sure what you are saying.  If you know what you need to run, why be limited by what a tool built to assist one in a less complicated situation can or can not do?

what is the concern?

I think the result set of your query versus the join query will include entries where stkNO is null While the other will return only those entries that had a stkNO.

Missed the reference to vb,...
You might want to reorder the where clause. customers.customerID is unknown in the context of the table stock, try stock.PurchasedFrom=customers.customerID.

Though I am uncertain how customers.customerID is bound/limited what is the basis of the evaluation of select stock_num from stocks where stocks.PurchasedFrom=Customers.customer.ID
 
In the join it is limited on a per row basis by the customer.customerID such that it has a singular value.

run explain on your query and see what SQL reports on it.

What is the exact error that you get?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:MBHEY131
ID: 40614889
run explain on your query and see what SQL reports on it.

How would I do that???

=====================================================
What is the exact error that you get?

Error in SELECT clause: expression near 'SELECT'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Unable to parse query text.
======================================================

I am not sure what you are saying.  If you know what you need to run, why be limited by what a tool built to assist one in a less complicated situation can or can not do?

 what is the concern?
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
What to you mean "Be Limited to"? No Believe me I do not want to be limited anymore than I already am.
My thought-be it right or wrong was that it was less complicated to let Vb.net and vs2012 do a lot of the work regarding, UPDATE, DELETE etc. as opposed to writing these SQL's manually. And to do that we could not use the JOIN clause on the SQL, as is my limited understanding. And the above query should suffice as I understand
==========================================================================================
I think the result set of your query versus the join query will include entries where stkNO is null While the other will return only those entries that had a stkNO.
+++++++++++++++++++++++++++++++++++++++++++++
If that is true, that's all the more reason to use My query as I do not need the "stkNo" null values at this point in my Project, but it could be a benefit in the long run and I did not know that.
====================================================================================
You might want to reorder the where clause. customers.customerID is unknown in the context of the table stock, try stock.PurchasedFrom=customers.customerID.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Get the same error.
===========================================================================
0
 
LVL 77

Expert Comment

by:arnold
ID: 40614927
You said that the reason you want this query versus using the join option is because of table updater wizard.

99.99% of your columns are from the customers table while one is from the stock table.

Depending on your database design what would an update/delete be if not only to the stock table or to the customers table.

I think you misinterpreted, your query if it worked would return stkno as null while the join will only return customer's who had purchased at least one stock.


Using SSMS (SQL Server management suite) can you run your query without an error?
Can you run the query within your code without the (select ) as stkNO?

The other option is prepend all your customers column with customers. and and stock columns with stock and see whether that resolves your issue.
0
 

Author Comment

by:MBHEY131
ID: 40615133
You said that the reason you want this query versus using the join option is because of table updater wizard.

 99.99% of your columns are from the customers table while one is from the stock table.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
That's because it states that you can use only return only 1 column in a sub-query - as I understand.
I need plenty more, but I can't get even 1 to return on the query. I was planning to add some more sub-queries in the future if I could get this to work.
==========================================================================================
I think you misinterpreted, your query if it worked would return stkno as null while the join will only return customer's who had purchased at least one stock.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
No - that's what I understood - my query has all the records and while I don't need them now, it would be beneficial in the future.
========================================================================================
Using SSMS (SQL Server management suite) can you run your query without an error?
 Can you run the query within your code without the (select ) as stkNO?
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Well Ok - more software to buy? vs2012 should take care of this in my humble opinion. it's SQL code is it not - I was unaware, again - that certain software does not accept all SQL code.
========================================================================
The other option is prepend all your customers column with customers. and and stock columns with stock and see whether that resolves your issue.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
tried that - same
==========================================================
could you please share about and possibly give an example of the EXPLAIN command you eluded to earlier.
==========================================================
Depending on your database design what would an update/delete be if not only to the stock table or to the customers table.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
not sure what your asking here - but there's plenty of reasons to update customer info and stock info simultaneously.
open RO's on sold units that are returned, etc.
==========================================
is this not allowed in vb.net?
0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 40615191
You have a local sql server installed, SSMS is a component of that you can get SSMSE (e stands for express)  from MS

for SQL 2012 http://www.microsoft.com/en-us/download/details.aspx?id=29062

for SQL 2005 http://www.microsoft.com/en-us/download/details.aspx?id=8961

Often the higher versioned ones can access the older versions. i.e. get 2012 and you should be able to access sql 2005, 2008 and 2012.

Lets try this, for the purposes of getting just this data out, will the join work? If so, use it.

deal with the other query as needed to address it.

It could be as simple as picking the wrong tool to use.

once you have ssms/e installed.  you would connect to the database in question, open a new query window. make sure you are using the correct database (use databasename)
then enter the
paste your query, and looking on the ssmse there is a tab/control that "include actual execution plan"
This when the query is run will display what resources were used for the query with the data,
there is a similar looking button that is labeled as "display estimated execution plan"

You could run two queries. select to get the customers. Then run the select for the stock and combine the data in your application.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Mysqli Query 5 52
Clean text to insert in database 9 65
paypal ipn to mysql 3 40
mysql database, schema and table creation 13 27
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question