view not running if user submits a blank field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

view not running if user submits a blank field

Author

Topic

barnsley
Starting Member

34 Posts

Posted - 2011-03-11 : 04:57:39

We have a web form which gathers users details (name, tel,email etc).
This web form populates 3 sql tables.

A view finds this information and runs a view.

The problem is this -
The View fails to run if user submits a blank field on the web form.

There are work arounds which we can deploy but are not ideal such as:

• Putting a ‘default value’ on our form inside the box – which would be visible to the user – like :
“please enter your tel number”
But they could delete this and then decide not to enter anything.

• We could make it a compulsory field - but do not really want to restrict users in this way (so would like to avoid this option).

• Or we could insert some hidden spaces (as the default text) which would remain if they left it blank but again, if they entered a number and then deleted it all – it would be removed (so this is not 100% bullet proof).

Any ideas about what is the best way to get around this please?

Perhaps a SQL a job which will detect blank fields and insert something prior to the job above?

Full code below.
CREATE VIEW dbo.vw_mailRegistrations
AS SELECT TOP 100 PERCENT
fred.UserDefinedRows.ModuleId,
fred.UserDefinedData.UserDefinedRowId,
fred.UserDefinedData.FieldValue AS EmailAddress,
UserDefinedData_1.FieldValue AS NINo,
UserDefinedData_2.FieldValue AS Surname,
UserDefinedData_3.FieldValue AS Forename,
UserDefinedData_4.FieldValue AS Telephone,
UserDefinedData_5.FieldValue AS Mobile

FROM fred.UserDefinedRows INNER JOIN
fred.UserDefinedData ON fred.UserDefinedRows.UserDefinedRowId = fred.UserDefinedData.

UserDefinedRowId INNER JOIN
fred.UserDefinedData UserDefinedData_1 ON fred.UserDefinedRows.UserDefinedRowId = UserDefinedData_1.UserDefinedRowId INNER JOIN
fred.UserDefinedData UserDefinedData_2 ON fred.UserDefinedRows.UserDefinedRowId = UserDefinedData_2.UserDefinedRowId INNER JOIN
fred.UserDefinedData UserDefinedData_3 ON fred.UserDefinedRows.UserDefinedRowId = UserDefinedData_3.UserDefinedRowId INNER JOIN
fred.UserDefinedData UserDefinedData_4 ON fred.UserDefinedRows.UserDefinedRowId = UserDefinedData_4.UserDefinedRowId INNER JOIN
fred.UserDefinedData UserDefinedData_5 ON fred.UserDefinedRows.UserDefinedRowId = UserDefinedData_5.UserDefinedRowId

WHERE (fred.UserDefinedRows.ModuleId = 873)
AND (fred.UserDefinedData.UserDefinedFieldId = 90)
AND (UserDefinedData_1.UserDefinedFieldId = 89)
AND (UserDefinedData_2.UserDefinedFieldId = 88)
AND (UserDefinedData_3.UserDefinedFieldId = 45)
AND (UserDefinedData_4.UserDefinedFieldId = 149)
AND (UserDefinedData_5.UserDefinedFieldId = 167)

ORDER BY fred.UserDefinedData.UserDefinedRowId

mark.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-11 : 06:22:35

EAV for the...hm...FAIL! Sorry, just had to go there. This is where the EAV model really bites you in the a$$... http://en.wikipedia.org/wiki/Entity-attribute-value_model

Change all the UserDefinedData-joins to LEFT OUTER JOIN instead.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

barnsley
Starting Member

34 Posts

Posted - 2011-03-14 : 10:04:55

Thanks for the reply.

I'm afraid outer join didn't work.

I think the problem is to do with the form itself:
e.g.
"UserDefinedFieldId = 167)" - which is for the mobile phone (cellphone) field does not get populated in the database if it is left blank by the user.

SO I guess something like this wouldn't work:

"(UserDefinedData_4.UserDefinedFieldId = 149)
AND (UserDefinedData_5.UserDefinedFieldId = 167)"

mark.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-14 : 10:08:53

Hm, did you try to move all the where-conditions to the join instead? Also using LEFT OUTER JOIN...? ->


...
FROM fred.UserDefinedRows 
  LEFT OUTER JOIN fred.UserDefinedData 
    ON fred.UserDefinedRows.UserDefinedRowId = fred.UserDefinedData.
      AND fred.UserDefinedRows.ModuleId = 873
  LEFT OUTER JOIN ...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

barnsley
Starting Member

34 Posts

Posted - 2011-03-15 : 08:59:50

...don't think we can remove the WHERE clause -
becasue we really need to display only the moduleIDs of '873'

- moduleID shows all fields from all our web forms and we only want to find records from one form (which is moduleID 873).

thanks

mark.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources