Insert new controls into a form via SQL INSERT INTO

jeremyll
jeremyll used Ask the Experts™
on
I wish to insert a checkbox control into a form via sql INSERT INTO. I’m using MS Sql Server Management Studio 2008.

I know I need to use a sql statement something like below.

INSERT INTO [M1_TJ].[dbo].[FormDefinitions]
           ([xaoFormID]
           ,[xaoControlName]
           ,[xaoClassID]
           ,[xaoType]
           ,[xaoProperties]
           ,[xaoCode])
     VALUES
           (<xaoFormID, char(75),>
           ,<xaoControlName, char(70),>
           ,<xaoClassID, char(35),>
           ,<xaoType, numeric(1,0),>
           ,<xaoProperties, text,>
           ,<xaoCode, text,>)

I would like to insert the following values below into M1_TJ dataset

xaoFormID: PART-FSIP*
xaoControlName: CHKLINTPOT                                                            
xaoClassID: M1.M1CHECKBOX                      
xaoType: 2
xaoProperties: Top = 1640 Left = 877 Height = 44 Width = 96 DataSource = "M1DataControl" Caption = "Lint Pot"
xaoCode: Function CHKLINTPOT_Change() Call UpdateSummary()   End Function

*For xaoFormID above, I would like to insert all the other values into xaoFormID where xaoFormID like ‘%PART-FSIP%’

My concern is the xaoCode field. When I go into design studio to edit the code, there are line breaks which adhere to VBscript coding conventions.

To check if your solution is getting proper results, I would like to first fun a Select and check the data and if it is ok, run the update or insert statement.

Thanks heaps! J
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
You can do something like:

create table a (field1 varchar(2000))
insert into a values 
('
into M1_TJ dataset

xaoFormID: PART-FSIP*
xaoControlName: CHKLINTPOT                                                            
xaoClassID: M1.M1CHECKBOX                      
xaoType: 2
xaoProperties: Top = 1640 Left = 877 Height = 44 Width = 96 DataSource = "M1DataControl" Caption = "Lint Pot" 
xaoCode: Function CHKLINTPOT_Change() Call UpdateSummary()   End Function

*For xaoFormID above, I would like to insert all the other values into xaoFormID where xaoFormID like ‘%PART-FSIP%’
')

select * from a

Open in new window

Senior Developer
Commented:
Your concerns about the values in [xaoCode] can be avoided, when you use hard-coded line breaks.


mfG
--> stefan <--
SELECT  'test' + CHAR(13) + CHAR(10) + 'test' ;

SELECT  REPLACE('Function|CHKLINTPOT_Change()|Call UpdateSummary()|End Function', '|', CHAR(13) + CHAR(10)) ;

Open in new window

Author

Commented:
Your help is much appreciated.

I executed the attached code but it gave me the following message box errors when I tried to open up the form

Errors
1.      The following error has occurred in program frmDesignProps.AddProp:
a.      Object variable or With block variable not set: Property = DataSource
b.      Object variable or With block variable not set: Property = DataFormat
c.      Object variable or With block variable not set: Property = DataMember
d.      Object variable or With block variable not set: Property = DataSource
2.      The following error has occurred in program frmDesignForm_Activate: Invalid Procedure call or argument.
3.      Unknown type for property: ‘Top – value 1600 Left = 880 Height = 40 Width = 104 DataSource = “XBDataControl” Caption = “Lint Pot”


INSERT INTO [M1_TJ].[dbo].[FormDefinitions]
           ([xaoFormID]
           ,[xaoControlName]
           ,[xaoClassID]
           ,[xaoType]
           ,[xaoProperties]
           ,[xaoCode])
     VALUES
           --(<xaoFormID, char(75),>
           --,<xaoControlName, char(70),>
           --,<xaoClassID, char(35),>
           --,<xaoType, numeric(1,0),>
           --,<xaoProperties, text,>
           --,<xaoCode, text,>)
           ('PART-FCYCMONS09-REV-A','CHKLINTPOT','M1.M1CHECKBOX',2,convert(varchar(max),'Top = 1600 Left = 880 Height = 40 Width = 104 DataSource = "M1DataControl" Caption = "Lint Pot"'),convert(varchar(max),'Function CHKLINTPOT_Change() Call UpdateSummary()   End Function'))

Open in new window

Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Those errors don't have anything to do with SQL. They are VB/ VBA errors. You should post the VBA code that is generating the error then request for this to be added to a VB / VBA group.

Author

Commented:
INSERT INTO FormDefinitions (xaoFormID , xaoControlName, xaoClassID, xaoType, xaoProperties, xaoCode)
SELECT '3ASDF', xaoControlName, xaoClassID, xaoType, xaoProperties, xaoCode
FROM FormDefinitions
WHERE xaoFormID like 'whatever'

above solved it
Top Expert 2012

Commented:
Amazing.  T-SQL code resolves a VBA error. Hmmm
I guess the VBA is somehow generated from the SQL.
In actual fact, ste5an answered the original question so he should get the points. No one could have answered the second question.

Author

Commented:
Solved part of the problem

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial