asked on

Insert new controls into a form via SQL INSERT INTO

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

Om Prakash

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

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jeremyll

ASKER

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

nmcdermaid

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.

jeremyll

ASKER

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

Anthony Perkins

Amazing. T-SQL code resolves a VBA error. Hmmm

nmcdermaid

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.

jeremyll

ASKER

Solved part of the problem