|
First off, I really don't know much about vbScript, so hopefully what I have thrown together won't look too awful.
I am trying to come up with a way to label parcels in ArcGIS, and I am about ready to throw my computer out the window. I have to use vbScript to create the labels by connecting to a database pulling the data from multiple tables, assembling a text string and assigning it to a polygon. Unfortunately the vbScript engine in ArcGIS doesn't support joins, so I came up with a way to create one query, get the data I need from one table and stuff it into an array (if necessary) for the second query. Unfortunately, I am getting bombarded with errors when I try to do the second query, and I have scoured search engines for a solution with no luck at all. Everything I found tells me how to do one query, but doesn't address the problem of running multiple queries. Any help that I can be given would be greatly appreciated. Everything works fine until I try to do the second query. I'm not sure what the procedure is to do another query, or if it's even possible.
Here is what I have written so far, including the portions that don't work (they are commented out).
Thanks a lot in advance!
Function FindLabel ([FEATURE_ID])
Dim strPrclQry, strLseQry, strinfo, LeaseNum, aLeaseList, LabelCount, FinalLabel, sLeaseList
'form query string
strPrclQry = "SELECT LEASE_NAME FROM TRACTS WHERE TRACTS.FEATURE_ID = '"&[FEATURE_ID]&"' ORDER BY LEASE_NAME ASC"
Dim ADOConn
set ADOConn = CreateObject("ADODB.Connection")
Dim rsPrcl
set rsPrcl = CreateObject("ADODB.Recordset")
ADOConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=.\DART_PROJECTS.mdb"
ADOConn.CursorLocation = 3
rsPrcl.Open strPrclQry, ADOConn, 3, 1, 1
'if no record is found, return empty string
Select Case rsPrcl.RecordCount
Case -1, 0
strInfo = " "
Case 1
'reading only the first record
LeaseNum = rsPrcl.Fields("LEASE_NAME").Value
strInfo = LeaseNum + strInfo
aLeaseList = strInfo
Case Else
for I = 1 to rsPrcl.RecordCount
if I <> 1 then
strInfo = " " + strInfo
End If
LeaseNum = rsPrcl.Fields("LEASE_NAME").Value
strInfo = LeaseNum + strInfo
rsPrcl.Movenext
Next
aLeaseList = Split(strInfo)
End Select
'closing connections
rsPrcl.Close
Set rsPrcl = Nothing
'put arrays into labels
If IsArray(aLeaseList) <> 0 then
LabelCount = 0
For Each sLeaseList In aLeaseList
if LabelCount <> 0 then
FinalLabel = vbNewLine + FinalLabel
End If
LabelCount = 1
FinalLabel = sLeaseList + FinalLabel
Next
Erase aLeaseList
'If there is only one lease in the parcel
Else
'form query string
' strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID = '"&aLeaseList&"'"
' Dim rsLse
' set rsLse = CreateObject("ADODB.Recordset")
' rsLse.Open strLseQuery, ADOConn, 3, 1, 1
'building text string if there is a name
' If rsLse.Fields("L_NAME").Value <> "" then
' FinalLabel = rsLse.Fields("L_NAME").Value
' if IsNull(rsLse.Fields("F_NAME1") = "False" then
' strLabelInfo = rsLse.Fields("L_NAME")&", "&LEFT(rsLseFields("F_NAME1"), 1)
' End If
' if IsNull(rsLse.Fields("F_NAME2") = "False" then
' strLabelInfo = strLabelInfo + "/" & LEFT(rsLse.Fields("F_NAME2"), 1)
' End If
' if IsNull(rsLse.Fields("ADD_INFO") = "False" then
' strLabelInfo = strLabelInfo & " " & rsLse.Fields("ADD_INFO")
' End If
' End If
FinalLabel = aLeaseList
End If
'closing connections
'rsLse.Close
'Set rsLse = Nothing
ADOConn.Close
Set ADOConn = Nothing
FindLabel = FinalLabel
End Function
|
|
|
|
|
Try releasing your ADO.Recordset - you are using it twice in your code without a release - or create two record sets.
|
|
|
|
|
I would say that the following line looks iffy:
strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID = '"&aLeaseList&"'"
If LeaseNum values are numbers then your aLeaseList should be comma-separated, then use:
strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID IN ("&aLeaseList&")"
If that doesn't work then post the value of your strLseQry variable to this thread.
|
|
|
|
|
Thanks for the quick responses, I've made some real progress and have gotten the queries to work!
|
|
|
|
|
Don´t know exactely if this will help, but you do not necessarily must use "JOIN" to get data out of several tables. You can define the connection between two or more tables in the "Where" clause. In your case it would look something like this:
SELECT TRACTS.LEASE_NAME, LEASES.L_NAME, LEASES.F_NAME1, LEASES.F_NAME2 FROM TRACTS, LEASES
WHERE TRACTS.LEASE_NAME=LEASES.L_NAME AND TRACTS.FEATURE_ID = '"&[FEATURE_ID]&"'
ORDER BY LEASE_NAME ASC"
You will receive 1 recordset with all needed data.
There is one little blemish within that kind of SQL. To connect several tables by a "JOIN" statement works quicker than only using the "Where" clause because "JOIN" is an optimized procedure. But in Your case that doesn´t matter!
|
|
|
|
|
hi. I'm trying to delete a row by specifying the actual row number. When I call all columns from one table it does work but when I already multiselect them I get an error: Msg 4405, Level 16, State 1, Line 1
View or function 'Records ORDERED BY rowID' is not updatable because the modification affects multiple base tables.
This one works:
WITH [Records ORDERED BY rowID] AS
(SELECT row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,*)
DELETE FROM [Records ORDERED BY rowID] WHERE rowID=1
This one gets the error:
WITH [Records ORDERED BY rowID] AS
(SELECT row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,
Records.[Officer ID],
DriverInfo.[Plate Number],
DriverInfo.[License Number],
DriverInfo.[Conduction Number],
DriverInfo.[Vehicle Category],
DriverInfo.[Vehicle Type],
DriverInfo.[Vehicle Brand],
DriverInfo.[Last Name],
DriverInfo.[First Name],
DriverInfo.[Middle Name],
DriverInfo.[Birth Date],
DriverInfo.[Registered Address],
DriverInfo.[Address' City Code],
Records.[Violation Commited],
Violations.[Violation Code],
Records.[Street Name],
Records.[City of Apprehension],
Cities.[City Code],
Violations.[Fine Amount],
Records.[Date/Time Apprehended]
FROM Violations,DriverInfo,Records,Cities
WHERE Records.[Plate Number] like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Violations.Violations
and Records.[City of Apprehension] like Cities.City)
DELETE FROM [Records ORDERED BY rowID] WHERE rowID=1
|
|
|
|
|
Because you are pulling information from multiple tables, it can not figure out which row number you are using
Try changing to this for example:
(SELECT Records.[Plate Number], row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,
|
|
|
|
|
I see. How can I use this when calling multiple tables then? I need to invoke multiple tables because I only want to delete the ones that satisfy that multiselect query. If I just use one table. The other rows that are not supposed to be included in my deletion will be deleted. Like when I want to delete row 1 in the condition of the multiselect I created, and when I only specify one table, the row 1 that it will be reading is the row 1 of that table not the multiselected table. Got any suggestions? 
|
|
|
|
|
Is it this what you are looking for?
WITH ORDERED_RECORDS AS(
SELECT
row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID
,Records.[Officer ID]
,DriverInfo.[Plate Number]
,DriverInfo.[License Number]
,DriverInfo.[Conduction Number]
,DriverInfo.[Vehicle Category]
,DriverInfo.[Vehicle Type]
,DriverInfo.[Vehicle Brand]
,DriverInfo.[Last Name]
,DriverInfo.[First Name]
,DriverInfo.[Middle Name]
,DriverInfo.[Birth Date]
,DriverInfo.[Registered Address]
,DriverInfo.[Address' City Code]
,Records.[Violation Commited]
,Violations.[Violation Code]
,Records.[Street Name]
,Records.[City of Apprehension]
,Cities.[City Code]
,Violations.[Fine Amount]
,Records.[Date/Time Apprehended]
FROM Violations
,DriverInfo
,Records
,Cities
WHERE Records.[Plate Number] like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Violations.Violations
and Records.[City of Apprehension] like Cities.City
)
DELETE FROM Records
WHERE Records.[Plate Number]
IN (
SELECT [Plate Number]
FROM ORDERED_RECORDS
WHERE rowID=1
)
|
|
|
|
|
Oh gosh. Thanks! I GOT IT WORKING FINALLY!!!! THANK YOU SOOO SOOO MUCH!!!!!! 
modified on Thursday, July 17, 2008 12:38 PM
|
|
|
|
|
Hi all,
I want to fill a grid (no hirarchical) where to combine customer data and the last selling item of each customer in a row (getting only the last sellings depending on sells_date).
My idea was
SELECT C.id,C.customerdata,S.id,S.sellsdata FROM dbo.customer AS C LEFT OUTER JOIN (SELECT TOP 1 id,sellsdata FROM dbo.sells ORDER BY sells_date DESC) AS S ON C.id=S.id
Whith this string I get only one selling item and not one item for each.
How does the correct SQL-String look likes ?
tnx in advance
Frank
|
|
|
|
|
SELECT C.id,C.customerdata,S.id,Selldata = (Select TOP 1 S.sellsdata FROM dbo.sells S Where C.id=S.id ORDER BY sells_date DESC) FROM dbo.customer AS C
Don't know why you want to return C.id and S.id since they should be the same anyhow.
|
|
|
|
|
Thank you for your answer,
you are right S.id is useless.
I played with your solution but on this way I only can get one item from the sells table ( = sellsdata). What's to do when I nead all the items from sells table row ?
tnx
Frank
|
|
|
|
|
fracalifa wrote: What's to do when I nead all the items from sells table row ?
Then you can go back to using a join to do so.
|
|
|
|
|
This was my first suggestion, but then I'm already at the initial position of the problem !
more ideas ?
|
|
|
|
|
I'm confused.
Originally from what I understand, your question was, how do you get the data such that you get all the rows of the first table, combined with 1 row of sell table for each row of the first table.
Now you say you want all of the rows from sell? What exactly do you want? Give me examples please.
|
|
|
|
|
Your first understanding is right.
I need for all customers their last sold product. But the last product information consits of 4 items. So the solution grid should look like that
ID-----Name----Address------> LastSoldItem----ProductNr.----ProductPrice----LastSellingDate
1111--Miller--1.st Avn----------Ship-----------00112233--------1000,00-----------05/09/2008
1123--Riven---Hallstreet--------Car------------00055564--------2000,00-----------07/08/2008
etc.
Every customer has sold many products and I want to see only the last sold product consisting of 4 items.
The tables are related by the customer-ID.
With your suggested solution I only get one item of the last sold product. (and not all the 4 I need)
I hope this clears your confusion....
tnx
frank
|
|
|
|
|
fracalifa wrote: Every customer has sold many products and I want to see only the last sold product consisting of 4 items.
fracalifa wrote: With your suggested solution I only get one item of the last sold product. (and not all the 4 I need)
You mean 4 columns from the sells table?
|
|
|
|
|
A Wong wrote: You mean 4 columns from the sells table?
If that's the case then:
SELECT C.id,C.customerdata,S.id,S.LastSoldItem, S.ProductNr, S.ProductPrice, S.LastSellingDate FROM dbo.customer AS C
LEFT OUTER JOIN
(SELECT s1.id,s1.LastSoldItem,s1.ProductNr, s1.ProductPrice, s1
.LastSellingDate FROM dbo.sells s1
where s1.LastSellingDate = (select max(s2.LastSellingDate) from dbo.sells s2 where s1.id = s2.id))
AS S ON C.id=S.id
|
|
|
|
|
You should ALWAYS provide the actual fields for each table to avoid misunderstandings.
This should solve the problem...
;WITH LatestSells AS (
SELECT s1.*
FROM dbo.Sells AS s1
INNER JOIN (
SELECT DISTINCT Customer_ID
, ( SELECT TOP 1 Sell_ID
FROM dbo.Sells
WHERE Customer_ID = sx.Customer_ID
ORDER BY Sell_Date DESC
) AS Sell_ID
FROM dbo.Sells AS sx
) AS s2 ON s1.Customer_ID = s2.Customer_ID AND s1.Sell_ID = s2.Sell_ID
)
SELECT c.*, s.*
FROM dbo.Customers AS c
LEFT OUTER JOIN LatestSells AS s
ON c.Customer_ID = s.Customer_ID
|
|
|
|
|
CREATE TABLE #Customers (
Customer_ID INT,
Customer_Name NVARCHAR(50)
) ;
INSERT INTO #Customers (Customer_ID, Customer_Name)
SELECT 1, 'John Smith' ;
INSERT INTO #Customers (Customer_ID, Customer_Name)
SELECT 2, 'Kate Taylor' ;
CREATE TABLE #Purchases (
Customer_ID INT,
Purchase_ID INT,
Product_Name NVARCHAR(50),
Quantity DECIMAL(8,2),
Sell_Date DATETIME
) ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 1, 1, 'Seagate HDD' , 4 , '2008-07-16 08:45' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 2, 2, 'Maxtor HDD' , 2 , '2008-07-16 08:47' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 1, 3, 'Mouse' , 2 , '2008-07-16 08:52' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 2, 4, 'LCD' , 1 , '2008-07-16 09:15' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 1, 5, 'Keyboard' , 7 , '2008-07-16 09:25' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 1, 6, 'KVM' , 2 , '2008-07-16 10:30' ;
;WITH LatestSells AS (
SELECT s1.*
FROM #Purchases AS s1
INNER JOIN (
SELECT DISTINCT Customer_ID
, ( SELECT TOP 1 Purchase_ID
FROM #Purchases
WHERE Customer_ID = sx.Customer_ID
ORDER BY Sell_Date DESC
) AS Purchase_ID
FROM #Purchases AS sx
) AS s2
ON s1.Customer_ID = s2.Customer_ID AND s1.Purchase_ID = s2.Purchase_ID
)
SELECT c.*, s.*
FROM #Customers AS c
LEFT OUTER JOIN LatestSells AS s
ON c.Customer_ID = s.Customer_ID ;
DROP TABLE #Customers ;
DROP TABLE #Purchases ;
|
|
|
|
|
Thank you,
that's it.
Great
|
|
|
|
|
Hello i am trying to sync my data from my PDA(pocket pc) to my server which is located remotely.
So any one can help me how to right simple application with only one field on db like name only using vb2005-8 and sql server as data base that will sync remotely.
then i can change other to my project which is having many tables.
Nassoro Ally.
|
|
|
|
|
Hi 2 All
I developed a desktop application using Microsoft Visual Studio .NET 2003 (VB.NET) with Microsoft SQL Server 2000 with no service pack installed.
I deployed this application on network (Windows XP on all computers clients as well as server), running fine. I mean accessing and showing data from server machine (i installed connectivity only on the client machine, not client and server tools).
The problem which i am facing is that reports (used built in crystal reports) are not visible on clients machines, when i access reports on server machine it is fine, but when i access reports from client machine a dialoge box appear which demands
Server
database
user name
password
Even if i provide all above informations the report is not visible.
Please help me that how can i view reports on client machine ...
thanks in advance ...
bye
Regards,
Arfan Qadir
|
|
|
|
|
Hi,
I have a simple method that checks if a sheet name is present in the excel file or not. The code is simple and works fine when the sheet name doesn't contain any spaces:
private bool validateExcelSheetByName(string name, OleDbConnection connection) {
bool result = true;
try {
if (connection.State == ConnectionState.Closed)
connection.Open();
var command = new OleDbCommand(string.Format("select * from [{0}]", name), connection);
command.ExecuteNonQuery();
}
catch {
result = false;
if (connection.State == ConnectionState.Open)
connection.Close();
}
return result;
}
In case I have any space in the sheet name (for example 'My Sheet'), I get an exception saying:
Invalid bracketing of name 'My Sheet$'.
I store the sheet name in a variable:
private const string sheetName = "My Sheet$";
How can I fix this code so it will work when I have spaces in the sheet name?
Thank you!
Andrei
------------
Croitoriu Andrei
andrei.croitoriu@gmail.com
http://www.stud.usv.ro/~acroitoriu
http://spaces.msn.com/acroitoriu/
"No complexity beyond what is necessary"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
