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()

Leave a Reply

Your email address will not be published. Required fields are marked *