Use Tag Property in SQL Statements

When building SQL statements, use the Tag property to hold the Field Name and Data Format. I use a naming convention of str, int, dat, and so on to determine the datatype; the rest of the tag holds the field name in the database?such as strCompanyName, datStartDate, or intQuantity. This routine iterates through the controls on the form and determines whether the control has a tag (you should tag only the controls that hold data). The routine then checks the type of control?Textbox or MaskedEditBox?to determine whether it should use the Text or FormattedText property. If the control has a value, it builds a string consistent with the datatype. Otherwise, it adds a Null value. After running through the controls, it combines the strings. You can then use the resulting SQL statement to execute an Insert operation into the database:

 Dim strSQL as StringDim strColumns as StringDim strValues as StringstrSQL = "INSERT INTO [TableName] "' start SQL statementstrColumns = "(" ' hold column namesstrValues = "VALUES(" ' holds corresponding column valuesFor Each ctrl In frmSite.Controls' iterate thru controls	If Len(ctrl.Tag) > 0  Then	' if no tag, do not use		strColumns = strColumns & Mid(ctrl.Tag, _			4) & ", "   ' add column name		Select Case TypeName(ctrl)		' find control type			Case "TextBox", "ComboBox"				If Len(ctrl.Text) > 0  Then					Select Case Left(ctrl.Tag) 					' find datatype, whether to 					' include single quotes or not						Case "str"						   strValues = strValues & _						     "'" & ctrl.Text & "', "						Case "int"						   strValues = strValues & _						     ctrl.Text & ", "					End Select				Else					strValues = strValues & "Null, "				End If			Case "MaskEdBox"				If Len(ctrl.Text) > 0  Then					Select Case Left$(ctrl.Tag, 3)  					' find data type, whether to 					' include single quotes or not						Case "dat", "phn", "ipa"						  strValues = strValues & _						    "'" & ctrl.FormattedText _						    & "', "						Case "int"						  strValues = strValues & _						    ctrl.FormattedText _						    & ", "					End Select				Else					strValues = strValues & "Null, "				End If		End Select	End IfNext'remove last comma and spacestrColumns = Left$(strColumns, Len(strColumns) - 2)strValues = Left$(strValues, Len(strValues) - 2)'add last parenthesesstrColumns = strColumns & ") "strValues = strValues & ")"'combine stringsstrSQL = strSQL & strColumns & strValues
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: