Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Apr 27, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 String
Dim strColumns as String
Dim strValues as String
strSQL = "INSERT INTO [TableName] "
' start SQL statement
strColumns = "(" 
' hold column names
strValues = "VALUES(" 
' holds corresponding column values

For 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
					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
					strValues = strValues & "Null, "
				End If
		End Select
	End If

'remove last comma and space
strColumns = Left$(strColumns, Len(strColumns) - 2)
strValues = Left$(strValues, Len(strValues) - 2)
'add last parentheses
strColumns = strColumns & ") "
strValues = strValues & ")"
'combine strings
strSQL = strSQL & strColumns & strValues
Blake Thomas
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date