Inserting a single record

The sample macro, although rather long, is really quite simple. The lengthy parts create dialog boxes and reformat the user-input values. In simplified pseudo-code, the macro resolves to:

	define dialog box
	display dialog box
	if user has pressed cancel then
		stop
	else
		reformat fields, adding quotes or converting to NULL
		build SQL statement
		execute SQL statement
	endif

The Insert Record dialog box

//---------------------------------------------------------
// Sample macro to demonstrate inserting a single
// record into an ODBC data table.
// © Geoff Lane 1997
//---------------------------------------------------------

  //Define the INSERT RECORD dialog box
DIALOGDEFINE(
	Dialog: "InsertDialog";
	Left: 10; Top: 10; Width: 250; Height: 230;
	Style: OK!|Cancel!;
	Caption: "Insert Record")
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "FirstNameLabel";
	Top: 11; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "First Name:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "FirstNameBox";
	Top: 10; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: FirstName; LimitText: 16)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "LastNameLabel";
	Top: 26; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Last Name:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "LastNameBox";
	Top: 25; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: LastName; LimitText: 16)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "StreetLabel";
	Top: 41; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Street:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "StreetBox";
	Top: 40; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: Street; LimitText: 32)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "Address1Label";
	Top: 56; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Address 1:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "Address1Box";
	Top: 55; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: Address1; LimitText: 32)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "Address2Label";
	Top: 71; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Address 2:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "Address2Box";
	Top: 70; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: Address2; LimitText: 32)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "CityLabel";
	Top: 86; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "City:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "CityBox";
	Top: 85; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: City; LimitText: 32)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "CountyLabel";
	Top: 101; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "County:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "CountyBox";
	Top: 100; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: County; LimitText: 20)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "PostCodeLabel";
	Top: 116; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Post Code:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "PostCodeBox";
	Top: 115; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: PostCode; LimitText: 16)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "CountryLabel";
	Top: 131; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Country:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "CountryBox";
	Top: 130; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: Country; LimitText: 32)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "BusTelLabel";
	Top: 146; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Business Tel:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "BusTelBox";
	Top: 145; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: BusTel; LimitText: 16)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "HomeTelLabel";
	Top: 161; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Home Tel:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "HomeTelBox";
	Top: 160; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: HomeTel; LimitText: 16)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "FaxLabel";
	Top: 176; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "Fax:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "FaxBox";
	Top: 175; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: Fax; LimitText: 16)
DIALOGADDTEXT(
	Dialog: "InsertDialog"; Control: "emailLabel";
	Top: 191; Left: 10; Width: 50; Height: 13;
	Style: Right!;
	Text: "email:")
DIALOGADDEDITBOX(
	Dialog: "InsertDialog"; Control: "emailBox";
	Top: 190; Left: 65; Width: 150; Height: 13;
	Style: Left!|NoTabs!|NoWPChar!;
	MacroVar: email; LimitText: 64)

 // Define the ERROR dialog box
DIALOGDEFINE(
	Dialog: "ErrorMessage";
	Left: 45; Top: 175; Width: 150; Height: 50;
	Style: OK!;
	Caption: "Error")
DIALOGADDTEXT(
	Dialog: "ErrorMessage"; Control: "ErrorText";
	Top: 10; Left: 10; Width: 130; Height: 13;
	Style: Center!;
	Text: "Last Name must have a value")


 //Display the record entry dialog box
LABEL(START)
DIALOGSHOW("InsertDialog")

IF( MacroDialogResult = 2 )  // Cancel pressed
	DIALOGDESTROY("InsertDialog")
	DIALOGDESTROY("ErrorMessage")
	QUIT 
ENDIF

// Check that LastName is not null, if it is then
// display an error and re-show dialog box.
IF(StrLen(LastName)=0)
	DIALOGSHOW("ErrorMessage")
	GO(START)
ENDIF

 //--------------------------------------------
 // Build the INSERT INTO SQL statement
 // Note that empty strings should be inserted
 // as null values
 //--------------------------------------------
 // Add single quotes to each side of the strings
 // or replace with NULL if empty
IF (StrLen(FirstName) = 0) 
	FirstName := "NULL" 
ELSE 
	FirstName := "'" + FirstName + "'"
ENDIF
LastName := "'" + LastName + "'"
IF (StrLen(Street) = 0)
	Street := "NULL"
ELSE
	Street := "'" + Street + "'"
ENDIF
IF (StrLen(Address1) = 0)
	Address1 := "NULL"
ELSE
	Address1 := "'" + Address1 + "'"
ENDIF
IF (StrLen(Address2) = 0)
	Address2 := "NULL"
ELSE
	Address2 := "'" + Address2 + "'"
ENDIF
IF (StrLen(City) = 0)
	City := "NULL"
ELSE
	City := "'" + City + "'"
ENDIF
IF (StrLen(County) = 0)
	County := "NULL"
ELSE
	County := "'" + County + "'"
ENDIF
IF (StrLen(PostCode) = 0)
	PostCode := "NULL"
ELSE
	PostCode := "'" + PostCode + "'"
ENDIF
IF (StrLen(Country) = 0)
	Country := "NULL"
ELSE
	Country := "'" + Country + "'"
ENDIF
IF (StrLen(BusTel) = 0)
	BusTel := "NULL"
ELSE
	BusTel := "'" + BusTel + "'"
ENDIF
IF (StrLen(HomeTel) = 0)
	HomeTel := "NULL"
ELSE
	HomeTel := "'" + HomeTel + "'"
ENDIF
IF (StrLen(Fax) = 0)
	Fax := "NULL"
ELSE
	Fax := "'" + Fax + "'"
ENDIF
IF (StrLen(Email) = 0)
	Email := "NULL"
ELSE
	Email := "'" + Email + "'"
ENDIF

MyQuery := "INSERT INTO Contact (" +
	"FirstName, LastName, Street, Address1, Address2, " +
	"City, County, PostCode, Country, BusinessTelNo, " +
	"HomeTelNo, FaxNo, email) VALUES ("
MyQuery := MyQuery +  FirstName + ", " +
	LastName + ", " + Street + ", " +
	Address1 + ", " + Address2 + ", " +
	City + ", " + County + ", " +
	PostCode + ", " + Country + ", " +
	BusTel + ", " + HomeTel + ", " +
	Fax + ", " + email + ")"

  //Now process as in the basic macro
ImportSetSource (SourceType: ODBC!)
ImportSetDestination (DestinationType: WPTable!)
ImportSetIncludeNames (State: On!)
ImportSetDataSource (Source: "MyData")
ImportSetSQLQuery (SQLQuery: MyQuery)
ImportDoImport()

DIALOGDESTROY("InsertDialog")
DIALOGDESTROY("ErrorMessage")

[Return to Macros Page]