Advanced SELECT for a newbie

I have a table full of Latitudes, Longitudes, address, customername, etc. , I need to grab some input(Latitude, Longitude, range) from the user. So now I have a source lat, long(user) and destination lat, long(rows in dbase). I need to take the 2 points and compute a distance from the user given lat, long to every lat, long in the database and check that distance againt the range given from the user. If the distance is below the range, I need to put that row into a temp table and return the temp table at the end of the stored proc.

As of right now I am completely lost and need some guidance.

I would also like to be able to add the computed distance to a table. Here is the function and stored procedure i have so far...

ALTER PROCEDURE [dbo].[sp_getDistance]

@.srcLat numeric(18,6),
@.srcLong numeric(18,6),
@.range int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM dbo.PL_CustomerGeoCode cg
WHERE dbo.fn_computeDistance(@.srcLat, cg.geocodeLat, @.srcLong, cg.geocodeLong) < @.range

END

CREATE FUNCTION fn_computeDistance
(
-- Add the parameters for the function here
@.lat1 numeric(18,6),
@.lat2 numeric(18,6),
@.long1 numeric(18,6),
@.long2 numeric(18,6)
)
RETURNS numeric(18,6)
AS
BEGIN
-- Declare the return variable here
DECLARE @.dist numeric(18,6)

IF ((@.lat1 = @.lat2) AND (@.long1 = @.long2))
SELECT @.dist = 0.0
ELSE
IF (((sin(@.lat1)*sin(@.lat2))+(cos(@.lat1)*cos(@.lat2)*cos(@.long1-@.long2)))) > 1.0
SELECT @.dist = 3963.1*acos(1.0)
ELSE
SELECT @.dist = 3963.1*acos((sin(@.lat1)*sin(@.lat2))+(cos(@.lat1)*cos(@.lat2)*cos(@.long1-@.long2)))

-- Return the result of the function
RETURN @.dist

Thanks,

Kyle

What's the problem you're facing? If you want to add a computed column for the distance to the table, you can use something like:

ALTER TABLE dbo.PL_CustomerGeoCode ADD ComputedDistance AS dbo.fn_computeDistance(@.srcLat, cg.geocodeLat, @.srcLong, cg.geocodeLong)

ADODB.Connection Insert into sql server table

Hi Experts,

Im trying to insert a record from Excel [Sheet2$] from Range (A2) to Range (E2) into a table on MS SQL server:
But I get the following error:

Error-2147217900(The INSERT INTO statement contains the following unknown filed name:F1).

Here is the ADODB.Connection:

Sub DB_con1()


Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
On Error GoTo test_Error

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Book1.xls;" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=titan;Database=dev;" & _
"UID=sa;PWD=welcome1@.].abk_import " & _
"Select * FROM [Sheet2$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

On Error GoTo 0
Exit Sub

test_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"

End Sub

Thanks in advance for any help.

Regards,

AbrahamHi Abraham - Welcome to the forum :D

How does the below SQL change do you?

strSQL = "Insert INTO MyTable (ColA, ColB, ColC, ColD, ColE) " & _
"SELECT * " & _
"FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=D:\Book1.xls;', 'SELECT * FROM [Sheet1$A1:E2]')"

EDIT - I've just reread your code. Your server is Excel. This code assumes your server is SQL Server. You would need to change your connection string to:
cn.Open "odbc;Driver={SQL Server};" & _
"Server=titan;Database=dev;" & _
"UID=sa;PWD=welcome1@."

HTH|||Hi HTH,

Thanks for the quick respond and solution!

My problem was that I was selecting a wrong Worksheet

Here is the code that I used and is functional:
Sub DB_con1()


Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
On Error GoTo test_Error

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Book1.xls;" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=mydbserver;Database=DEV;" & _
"UID=sa;PWD=Welcome1@.].abk_import " & _
"Select * FROM [Sheet1$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

On Error GoTo 0
Exit Sub

test_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"

End Sub
'''

Reagrads,

Abrahim