aspn.net | Common SQL Codes
DirectCast(fv_equ.FindControl("lblNoteLabel"), Label).Visible = False
SQL Insertion (with select scope ID)
Dim objcon As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnectionString")
Dim con As New SqlConnection(objcon.ToString)
Dim str As [String] = "INSERT INTO [TableName] ([FieldName1]) VALUES (@FieldName1Value) SELECT SCOPE_IDENTITY()"
Dim cmd As New SqlCommand(str, con)
cmd.Parameters.AddWithValue("@FieldName1Value", "ABC 123")
con.Open()
gid = cmd.ExecuteScalar()
con.Close()
SQL Select
Dim objcon As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnectionString")
Dim con As New SqlConnection(objcon.ToString)
Dim str1 As [String] = "SELECT * FROM [TableName] WHERE Condition = '" & var_myCondition & "'"
Dim cmd1 As New SqlCommand(str1, con)
con.Open()
varGetValue = cmd1.ExecuteScalar()
con.Close()
SQL Reader / fill data
Dim objcon As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnectionString")
Dim con As New SqlConnection(objcon.ToString)
Dim str As [String] = "SELECT * FROM [TableName] WHERE (Field1 = " & var_condition & ")"
Dim cmd As New SqlCommand(str, con)
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then
lbl_first_name.Text = dr("first_name").ToString
End If
con.Close()
For Stored Procedure
Dim objcon As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnectionString")
Dim con As New SqlConnection(objcon.ToString)
con.Open()
Dim cmd As SqlCommand = New SqlCommand("StoredProcedureName", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@var2PassID", "VALUE#")
Using r = cmd.ExecuteReader()
If r.Read() Then
lbl_1.Text = r.GetValue(0)
lbl_2.Text = r.GetValue(1)
lbl_3.Text = r.GetValue(2)
lbl_4.Text = r.GetValue(13)
End If
End Using
con.Close()