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
Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes