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
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Help with updating and inserting

Author  Topic 

CB2000
Starting Member

4 Posts

Posted - 2015-04-07 : 11:03:56
I need some help getting a stored procedure constructed that will be used to handle cases where a user may either update existing data or insert new data.

Specifically I'm working on an application that is a basic form builder and will consists of fields and textboxes. Users may add new fields and textboxes to a form or remove them. They may also decide to later change the text of an existing field.

Here is what I currently have. Which is not working as I'd like and I understand the "like" is the problem, but I'm not sure how to properly construct the query.

With what I currently have if a field is named "Address1" and "Address2" and the user wants to edit the fields to be "Address" and "Address1" both fields will end up being renamed to "Address1".

Here is my stored procedure currently.


IF NOT EXISTS (SELECT WidgetText FROM FormBuilderFormFields WHERE FormId = @formid AND WidgetText like @widgetText + '%')
BEGIN
INSERT INTO FormBuilderFormFields (FormId, [Required], SortOrder, CreateDate, CreatedBy, WidgetId, WidgetText, WidgetVals, WidgetInputType)
VALUES (@formid, @required, @sortOrder, @createDate, @uid, @widgetId, @widgetText, @widgetVals, @widgetInputType)
END
ELSE
BEGIN
UPDATE FormBuilderFormFields SET
[Required] = @required,
SortOrder = @sortOrder,
CreateDate = @createDate,
CreatedBy = @uid,
WidgetId = @widgetId,
WidgetText = @widgetText,
WidgetVals = @widgetVals,
WidgetInputType = @widgetInputType
WHERE FieldId = (SELECT FieldId FROM FormBuilderFormFields WHERE WidgetText = (SELECT WidgetText FROM FormBuilderFormFields WHERE FormId = @formid AND WidgetText like @widgetText + '%'))-- AND FormId = @formid)
END


I'd greatly appreciate some help with this.

Thanks!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-07 : 12:09:11
I am guessing that each widget on your forms have a specific text and id? if this is correct, you might have the UI pass in the form id and widget id along with the text value widget text. you could use a merge statement as well. If not even close, shed some more light by explaining a bit more information.
Go to Top of Page

CB2000
Starting Member

4 Posts

Posted - 2015-04-07 : 12:30:23
quote:
Originally posted by MichaelJSQL

I am guessing that each widget on your forms have a specific text and id? if this is correct, you might have the UI pass in the form id and widget id along with the text value widget text. you could use a merge statement as well. If not even close, shed some more light by explaining a bit more information.



Thanks for the reply; however, I am already passing the FormId and WidgetText. It wouldn't help in this case to pass the WidgetId, because it is the ID of the widget from the widgets table. This table contains entries such as id:1 checkbox, id:2 dropdown, id:3 radiobutton, etc.

Here is a sample from the FormFields table.

FieldId FormId Required SortOrder CreateDate CreatedBy DisabledDate DisabledBy WidgetId WidgetText WidgetVals WidgetInputType
113 8E332641-7548-40EB-AC27-83ED48B730C5 0 1 2015-04-07 09:55:00 115192 NULL NULL 9 First Name NULL character
114 8E332641-7548-40EB-AC27-83ED48B730C5 0 2 2015-04-07 09:55:00 115192 NULL NULL 9 Last Name NULL character
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-07 : 12:46:02
I gather what you are saying is the widget id represents a control type and not a specific control on the form? Perhaps you might want to augment your design so each version of the control has its own unique ID unless that is what Field Id represents - in which case pass that and you won't need the like clause. or is there still more to this?
Go to Top of Page

CB2000
Starting Member

4 Posts

Posted - 2015-04-07 : 13:00:49
quote:
Originally posted by MichaelJSQL

I gather what you are saying is the widget id represents a control type and not a specific control on the form? Perhaps you might want to augment your design so each version of the control has its own unique ID unless that is what Field Id represents - in which case pass that and you won't need the like clause. or is there still more to this?



You got the gist of it. The only thing is that the FieldId is the auto-incremented database key, so I can't pass the FieldId in from the UI. This is why I used a subquery to get the database row to update. The problem is my 'like' matches any widgetText that contains a value like the parameter instead of only the current widget that needs to be updated.
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-07 : 13:43:04
I see your issue then. You could add a parameter: @oldwidgettext with a default of '' and do an exact match

IF NOT EXISTS (SELECT WidgetText FROM FormBuilderFormFields WHERE FormId = @formid AND WidgetText =@OldwidgetText)
BEGIN
INSERT INTO FormBuilderFormFields (FormId, [Required], SortOrder, CreateDate, CreatedBy, WidgetId, WidgetText, WidgetVals, WidgetInputType)
VALUES (@formid, @required, @sortOrder, @createDate, @uid, @widgetId, @widgetText, @widgetVals, @widgetInputType)
END
ELSE
BEGIN
UPDATE FormBuilderFormFields SET
[Required] = @required,
SortOrder = @sortOrder,
CreateDate = @createDate,
CreatedBy = @uid,
WidgetId = @widgetId,
WidgetText = @widgetText,
WidgetVals = @widgetVals,
WidgetInputType = @widgetInputType
WHERE FieldId = (SELECT FieldId FROM FormBuilderFormFields WHERE WidgetText = (SELECT WidgetText FROM FormBuilderFormFields WHERE FormId = @formid AND WidgetText = @oldwidgetText ))-- AND FormId = @formid)
END

This would just require the UI to send the old value along with the new value.

Is that an option?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-08 : 04:18:56
If I have understood this correctly you are using the Form Field's Label as the key, and that can be renamed by the user such that, during renaming, duplicates may occur.

We use a similar type of form building for our applications. I would suggest that you ought to have a unique ID for each form field, which will not change. You could use an IDENTITY or a GUID or even an INT where you maintain the "Next available number" yourself.

We have both IDENTITY and GUID. The IDENTITY is safe within the database, but we also have DEV and TEST database versions, and other clients using similar applications where we want to merge / move / copy Forms from one place to another, and we use the GUID to synchronise those and adjust the IDENTITY (on INSERT of new items, "imported" from another database) to something that will be unique withint the target database.
Go to Top of Page

CB2000
Starting Member

4 Posts

Posted - 2015-04-08 : 11:35:32
Thanks for all of your help. After discussing how we were handling this, we decided to take a much simpler approach and that ends up working well for us.
Go to Top of Page
   

- Advertisement -