[dba-SQLServer] SQL Server 2000 - User Defined Functions - CallingFrom Visual Basic 6.0

If I understand this correctly, then you are trying to execute your UDF as
if it were a sproc. That won`t work.
Try some variant instead...
SELECT * FROM myUDF WHERE parm1 = etc.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Hartland
Sent: October 13, 2005 8:31 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SQL Server 2000 - User Defined Functions -
CallingFrom Visual Basic 6.0

To all,

I have just created my very first UDF in SQL Server 2000 basically I
want to use it to return a print address to a string variable in Visual
Basic 6.0, my function is as below:

CREATE FUNCTION [gng_create_PrintAddress] 
	(
		@strLine1 	[nvarchar](255),
		@strLine2	[nvarchar](255),
		@strLine3	[nvarchar](255),
		@strLine4	[nvarchar](255),
		@strLine5	[nvarchar](255),
		@strLine6	[nvarchar](255),
		@strPC		[nvarchar](255)
	
)  
RETURNS 
		[nvarchar](2000)
AS  
BEGIN 
	RETURN ISNULL(@strLine1+CHAR(13),'')	+
		   ISNULL(@strLine2+CHAR(13),'')	+
		   ISNULL(@strLine3+CHAR(13),'')	+
		   ISNULL(@strLine4+CHAR(13),'')	+
		   ISNULL(@strLine5+CHAR(13),'')	+
		   ISNULL(@strLine6+CHAR(13),'')	+
		   ISNULL(@strPC+CHAR(13),'')
END

In VB6 I have a data environment with a SQLConn connection, and if I
want to execute a stored procedure to return a recordset I do something
like the following:

Dim rsData As ADODB.Recordset
Set rsData = DEGNG.SQLConn.Execute("[SP Name]") and give it the
parameters if required.....

So I thought I could have something like below:

Dim strAdd As String
strAdd = DEGNG.SQLConn.Execute("gng_create_PrintAddress '" & Line1 & "',
'" & Line2 & "','" & Line3 & "', '" & Line4 & "', '" & Line5 & "', '" &
Line6 & "', '" & PCode & "'")

And then strAdd would contain the print address, but it give me an error
saying: Type Mismatch.
can anyone point me in the right direction here ?

Thanks in advance for any help on this, as I would really like to start
learning more about UDF's and using them instead of modules in VB6




 


PAUL HARTLAND
Database Designer/Programmer
paul.hartland at orridge.co.uk
DDI - 01922 472031
Mobile - 07730 523179


_______________________________________________

*  This message is confidential.
*  This email, its content and any files transmitted with it are intended
solely for the addressee and may be legally privileged and/or confidential.
*  Access by any other party is unauthorised without the express written
permission of the sender.
*  If you have received this email in error you may not copy or use the
contents, attachments or information in any way and any review, use,
dissemination, forwarding, disclosure, alteration, printing of this
information is strictly prohibited. Please destroy it and notify the sender
via return e-mail.
*  This email has been prepared using information believed by Paul Hartland
to be reliable and accurate, but the company makes no warranty as to
accuracy or completeness. In particular the author does not accept
responsibility for changes made to this email after it was sent.
*  Any opinions expressed in this document are those of the author and do
not necessarily reflect the opinions of the company or its affiliates.

The Orridge web site can be found at:
http://www.orridge.co.uk


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com