custom validator - problem returning bit/boolean in DAL 2

[NuJoizey]

I have a custom validator which does some server side validation:

Protected Sub cvDuplicateName_ServerValidate(ByVal source As Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) Handles cvDuplicateName.ServerValidate
        Dim strLegalNameNew As Object = Me.txtLegalNameNew.Text
        Dim qta As New QueriesTableAdapter
        Dim objResult As Object = qta.udf_ExistsDuplicateCompanyName(strLegalNameNew)
        'Dim bool As Boolean = qta.udf_ExistsDuplicateCompanyName(strLegalNameNew)
       ' Dim int As Integer = qta.udf_ExistsDuplicateCompanyName(strLegalNameNew)


        If objResult = True Then
            args.IsValid = "false"
        Else
            args.IsValid = "true"
        End If

    End Sub

The problem is no matter what I do, objResult always is returning false and I can't figure out why. In my XSD file, the qta.udf_ExistsDuplicateCompanyName(strLegalNameNew) points to a stored prodedure that returns a bit. When I test the procedure in the XSD file interface, it shows that it returns Type Object with the value of either True or False as it should.

But when I step through the code in VS debugger, objResult does not return the right value (always false). I tried makeing objResult an int and a boolean, and also changed the corresponding data return type in my stored procedure, but it didn't work..

ok...I call UNCLE already.....

what gives here?

 

[jmeckley]

it may be returning false because that is the default value and your trying to implicitly convert an object to a boolean. I know vb is forgiving/lazy about this, but it could be the problem. try this instead.

args.IsValid = !(bool)qta.udf_ExistsDuplicateCompanyName(strLegalNameNew);

vb might look like this

args.IsValid = Not qta.udf_ExistsDuplicateCompanyName(strLegalNameNew)

if at all possible I would have your QTA members return strongly typed values, instead of object.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[NuJoizey]

hi jason. i think i'm going to have to have you put on my company's payroll as a consultant. I really appreciate all the times you've gone out of your way to try and help me.


OK, I see what you are driving at i think. The problem is with typing, and that is what I had suspected all along, but I can't seem to figure out how to get the types to agree.

i tried what you have said yet it still doesn't work right, and I notice now that the expression:

qta.udf_ExistsDuplicateCompanyName(strLegalNameNew)

evaluates to Nothing. I do not understand why it validates to Nothing here, but if i right click the expression in my QueriesTableAdapter GUI, choose "Preview data" and enter in my parameter manually and then click the "Preview" button, it works fine. it shows

Results:
----------
Type: Value:
Object True

I *am* supposed to be "returning a single value" in my DAL configuration, as opposed to "returning no value" - right?

 

[jmeckley]

what does the stored proc look like? Null is a valid scalar value from the stored proc. VB probally translates null to false. (although they aren't the same).

I would also enable VB Strict Option. this will require you to explicitly define variables and types. it may seem like "more work", but it really helps. plus it could shed some like on this problem.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[NuJoizey]

here is the user defined function that returns bit:

ALTER FUNCTION [dbo].[udf_ExistsDuplicateCompanyName] 

(

-- Add the parameters for the function here

@CompanyName varchar(50)

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @Result bit

-- Add the T-SQL statements to compute the return value here 

-- Manually set it just as a test:

-- SET @Result=1

IF EXISTS

(

SELECT CompanyID 

FROM COMPANY 

WHERE CompanyLegalName=@CompanyName

OR CompanyRefName=@CompanyName

)

SET @Result=1

ELSE

SET @Result=0

RETURN @Result

END

and, don't know if this will help shed a little light, but here is the qta definition code found in the DAL

<DbSource ConnectionRef="MyDatabaseConnectionString (Web.config)" DbObjectName="MyDatabase.dbo.udf_ExistsDuplicateCompanyName" DbObjectType="Function" GenerateShortCommands="True" MethodsParameterType="CLR" Modifier="Public" Name="udf_ExistsDuplicateCompanyName" QueryType="Scalar" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="True">
            <SelectCommand>
              <DbCommand CommandType="StoredProcedure" ModifiedByUser="False">
                <CommandText>dbo.udf_ExistsDuplicateCompanyName</CommandText>
                <Parameters>
                  <Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DbType="Object" Direction="ReturnValue" ParameterName="@RETURN_VALUE" Precision="0" Scale="0" Size="0" SourceColumnNullMapping="False" SourceVersion="Current">
                  </Parameter>
                  <Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@CompanyName" Precision="0" ProviderType="VarChar" Scale="0" Size="50" SourceColumnNullMapping="False" SourceVersion="Current">
                  </Parameter>
                </Parameters>
              </DbCommand>
            </SelectCommand>
          </DbSource>

 

[jmeckley]

<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DbType="[COLOR=red]Object[/color]" Direction="ReturnValue" ParameterName="@RETURN_VALUE" Precision="0" Scale="0" Size="0" SourceColumnNullMapping="False" SourceVersion="Current">

change that to Boolean instead of Object. This may be the problem. if that doesn't fix it change Boolean to Int32 and compare against number.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

you have:

 args.IsValid = "false"
''or
 args.IsValid = "true"

These are string values not boolean, you need:

 args.IsValid = false
''or
 args.IsValid = true

 

[jmeckley]

good catch j here I got him running in circles.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

LOL.. thanks.. I missed that the first go around as well, just happened to catch it this time.

 

[NuJoizey]

no dice guys. just isn't working. I think the problem has something to do with it not returning any actual records, but instead only a value. This is the only one of my DAL functions that does this. If i changed my udf to return some sort of select statement, it would probably work, but that's a hack - there has to be some way to get the CLR to recognize a simple return value, but darned if i know what it is.

 

[jbenson001]

since we don't have any clue as to what is going on here:

Dim objResult As Object = qta.udf_ExistsDuplicateCompanyName(strLegalNameNew)

We can't really help.. you will have to debug that code.

 

[NuJoizey]

since we don't have any clue as to what is going on here:

???? I don't understand ...

it just takes a legal name string like "my company", and searches the Database via a UDF in SQL server (shown above in a previous post).

that UDF returns a bit value of 1 if "my company" exists and a bit value of 0 if it doesn't exist.

The udf works fine in SQL Server Mgmt Studio.

CLR should pass that bit value 1 or 0 into objResult shouldn't it?, but the problem is in CLR, regardless of how the UDF evaluates is always "Nothing".

This leads me to believe there is some problem with the typing somewhere, but changing the type to Boolean still doesn't change the fact that qta.udf_ExistsDuplicateCompanyName(strLegalNameNew) always is Nothing when it should be 0 or 1.

Debugging doesn't seem to tell me anything more than what I've written above. the nearest I can guess is that you can't use a UDF in this way unless it returns a record, and not just a value. I find that hard to believe though.

 

[jbenson001]

I just noticed you are using a table adapter. I would guess that is the problem. There are different types of user defined functions and the types they return. Yours should be a scalar-valued function which means it returns only a singl value. I'm not sure what the table adapters do with it at that point. This is the reason I always suggest to any poster not to use the datasource controls. They are not for anything more complex than simple operations and they cannot be debugged.

 

[jmeckley]

what he means is most/all the real work being done has been defined through xml and wizards, making the code like a black box. you cannot easily debug what's going on.

i didn't think udf's could be called directly from command objects. I work with ORM frameworks, so I do as little business logic on the db as possible. to change this up a bit, i would recommend having the db do what it does best, query. Save the business logic (albeit very simple) for your code.

so have the database return the Id of the customer. then in your logic (business logic, not code behind) return Id > 0. this can also be done with straight sql, so you don't need to clutter the db with procs/functions.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[NuJoizey]

the nearest I can guess is that you can't use a UDF in this way unless it returns a record, and not just a value. I find that hard to believe though.

well, my testing would corroborate this theory. I don't know if this is by design or this could be considered a workaround.

The way I got it to work was to call the UDF from a SPROC that I wrote like so:

CREATE PROCEDURE usp_Falaffel 
-- Add the parameters for the stored procedure here
@CompanyName varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
--	DECLARE @CompanyName varchar(50)
--SET @CompanyName='008'
[COLOR=red]SELECT dbo.udf_ExistsDuplicateCompanyName(@CompanyName) AS ExistsCompanyName [/color]

END
GO

and then in the VB, i have

Protected Sub cvDuplicateName_ServerValidate(ByVal source As Object, ByVal args As 

System.Web.UI.WebControls.ServerValidateEventArgs) Handles cvDuplicateName.ServerValidate
        Dim strLegalNameNew As Object = Me.txtLegalNameNew.Text
        Dim qta As New QueriesTableAdapter

Dim bool As Boolean = qta.usp_Falaffel(strLegalNameNew)

args.IsValid = Not bool

End Sub

It seems having to do that is kind of funny, but I couldn't find another way.

By the way, I love a good falaffel.


jbenson, i just noticed you recommended not using table adapters, so then failing that, what would you recommend?

 

[jmeckley]

jbenson, i just noticed you recommended not using table adapters, so then failing that, what would you recommend?

Nhibernate or ActiveRecord! You could roll your own DAL/ORM, but that's like reinventing the wheel.

My take on this is you are pushing most of your logic into procs (and xml). this can become unmaintainable very quickly. especially when the logic change for some scenarios, but not others.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[NuJoizey]

hmmm.. ok hadn't heard of these before. I understand the idea that you want to separate business logic from data access logic from presentation, but how this is actually accomplshed is still a bit fuzzy in mind mind.

seems according you you guys this issue is raised here in this post.

and I have never really gotten into ORM yet, but I'm sure I will need to be acquainted with these things sooner than later.

 

[jmeckley]

if you have ~$70 and some spare time pick up Domain Driven Design by Eric Evans. The abbreviated version is here, but the book is much more in depth and valuable. check out this series by Jeffery Palmero. The link points to part 3. at the begining of the post there is a link to parts 1 and 2. read sequentially.

the concepts of DDD (domain driven design) vs. CRUD (create, read, update, delete) is not an easy, or simple transition. But the payoffs are huge if you can grasp DDD and begin to implement it into your projects.

Jason Meckley
Programmer
Specialty Bakers, Inc.