devxlogo

Use Tag Property in SQL Statements

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
See also  Using Consumer Trust to Help Build Brands On Major Social Platforms
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist