Concatenate Issue,.

Question

User1977787504 posted

            If chkPFCI.Checked And chkPFD.Checked Then
                cmd.CommandText = "Delete from FinanceData Delete from FinancePeriod "
            ElseIf chkPFCI.Checked Then
                cmd.CommandText = "Delete from FinancePeriod "
            ElseIf chkPFD.Checked Then
                cmd.CommandText = "Delete from FinanceData "
            End If

            cmd.CommandText = "update SuperAdminSettingsArea set Value=@GP where ID=1 " &
                              "update SuperAdminSettingsArea set Value=@VRL where ID=2 " &
                              "update SuperAdminSettingsArea set Value=@IC where ID=3 " &
                              "update SuperAdminSettingsArea set Value=@RS where ID=5 " &
                              "update SuperAdminSettingsArea set Value=@PVD where ID=6 " &
                              "update SuperAdminSettingsArea set Value=@CR where ID=4 " &
                              "update SuperAdminSettings set Name=@Name, Value=@URL where ID=1 and AreaID=4 "

            cmd.Parameters.AddWithValue("@GP", htData("GroupPermissions"))
            cmd.Parameters.AddWithValue("@VRL", htData("ViewRequestLicenses"))
            cmd.Parameters.AddWithValue("@IC", htData("InsightsCategories"))
            cmd.Parameters.AddWithValue("@RS", htData("ReportSettings"))
            cmd.Parameters.AddWithValue("@CR", htData("CustomReportsLink"))
            cmd.Parameters.AddWithValue("@Name", htData("ReportName"))
            cmd.Parameters.AddWithValue("@URL", htData("ReportURL"))
            cmd.Parameters.AddWithValue("@PVD", htData("PurgeDocumentVersionsLink"))

I have to concatenate 

cmd.CommandText,

Answer 1

User-484054684 posted

If you would like to execute multiple update statements at a time, better to go with stored procedure with transaction commit and rollback.

Answer 2

User-1509636757 posted

I agree with siva. You should create stored procedure and execute all update statement. Check out the code sample. I just assume the parameter types to varchar which you can change on  your requirement:

CREATE PROC usp_Update_SuperAdminSettingsArea
    @GP        VARCHAR(50),
    @VRL    VARCHAR(50),
    @IC        VARCHAR(50),
    @RS        VARCHAR(50),
    @CR        VARCHAR(50),
    @Name    VARCHAR(50),
    @URL    VARCHAR(50),
    @PVD    VARCHAR(50),
    @chkPFCI    BIT,
    @chkPFD        BIT
AS
BEGIN
    IF @chkPFCI = 1 AND @chkPFD = 1
    BEGIN
        Delete from FinanceData
        Delete from FinancePeriod
    END
    Else If chkPFCI = '1'
        Delete from FinancePeriod
    Else IF chkPFD = '1'
        Delete from FinanceData

    update SuperAdminSettingsArea set Value=@GP where ID=1
    update SuperAdminSettingsArea set Value=@VRL where ID=2
    update SuperAdminSettingsArea set Value=@IC where ID=3
    update SuperAdminSettingsArea set Value=@RS where ID=5
    update SuperAdminSettingsArea set Value=@PVD where ID=6
    update SuperAdminSettingsArea set Value=@CR where ID=4
    update SuperAdminSettings set Name=@Name, Value=@URL where ID=1 and AreaID=4
END

and pass the parameters:

            cmd.CommandText = "usp_Update_SuperAdminSettingsArea"

            cmd.Parameters.AddWithValue("@GP", htData("GroupPermissions"))
            cmd.Parameters.AddWithValue("@VRL", htData("ViewRequestLicenses"))
            cmd.Parameters.AddWithValue("@IC", htData("InsightsCategories"))
            cmd.Parameters.AddWithValue("@RS", htData("ReportSettings"))
            cmd.Parameters.AddWithValue("@CR", htData("CustomReportsLink"))
            cmd.Parameters.AddWithValue("@Name", htData("ReportName"))
            cmd.Parameters.AddWithValue("@URL", htData("ReportURL"))
            cmd.Parameters.AddWithValue("@PVD", htData("PurgeDocumentVersionsLink"))
            cmd.Parameters.AddWithValue("@chkPFCI", chkPFCI.Checked)
            cmd.Parameters.AddWithValue("@chkPFD", chkPFD.Checked)

hope it helps./.

Answer 3

User1977787504 posted

BY string builder ?

Answer 4

User-1509636757 posted

BY string builder ?

If you go by using stored procedure, StringBuilder is not required because by using StringBuilder you were creating the CommandText to execute it in database, But instead of that in stored procedure you will just pass the parameters in SqlCommand and the name of the stored procedure.

hope it helps./.

Answer 5

User1977787504 posted

I want to use commandtext, yeah i know that was recommended to use SP ,but here we need to use command text with sql query,

Answer 6

User-1016547394 posted

I want to use commandtext, yeah i know that was recommended to use SP ,but here we need to use command text with sql query,

Maybe you can save your SQL and SqlParameters into Dictionary(Of String, Of SqlParameter()) and then use For Each to loop each of them and dynamically add possibly. However I strongly recommand you doing that by using SP.

Answer 7

User-1509636757 posted

I want to use commandtext, yeah i know that was recommended to use SP ,but here we need to use command text with sql query,

Try adding semicolon after every statement:

If chkPFCI.Checked And chkPFD.Checked Then
            cmd.CommandText = cmd.CommandText & "Delete from FinanceData Delete from FinancePeriod; "
        ElseIf chkPFCI.Checked Then
            cmd.CommandText = cmd.CommandText & "Delete from FinancePeriod; "
        ElseIf chkPFD.Checked Then
            cmd.CommandText = cmd.CommandText & "Delete from FinanceData; "
        End If
        cmd.CommandText = cmd.CommandText & "update SuperAdminSettingsArea set Value=@GP where ID=1; " &
                          "update SuperAdminSettingsArea set Value=@VRL where ID=2; " &
                          "update SuperAdminSettingsArea set Value=@IC where ID=3; " &
                          "update SuperAdminSettingsArea set Value=@RS where ID=5; " &
                          "update SuperAdminSettingsArea set Value=@PVD where ID=6; " &
                          "update SuperAdminSettingsArea set Value=@CR where ID=4; " &
                          "update SuperAdminSettings set Name=@Name, Value=@URL where ID=1 and AreaID=4 "

Reference link: Executing multiple SQL statements as one against SQL Server

hope it helps./.

Answer 8

User1977787504 posted

Dim sbquery As New StringBuilder

If chkPFCI.Checked And chkPFD.Checked Then
            cmd.CommandText = cmd.CommandText & "Delete from FinanceData Delete from FinancePeriod; "
        ElseIf chkPFCI.Checked Then
            cmd.CommandText = cmd.CommandText & "Delete from FinancePeriod; "
        ElseIf chkPFD.Checked Then
            cmd.CommandText = cmd.CommandText & "Delete from FinanceData; "
        End If

If chkCR.Checked Then If txtReportName.Text.Count > 255 Then htData.Add("ReportName", txtReportName.Text.Substring(0, 254)) Else htData.Add("ReportName", txtReportName.Text) End If If txtURL.Text.Count > 255 Then htData.Add("ReportURL", txtURL.Text.Substring(0, 254)) Else htData.Add("ReportURL", txtURL.Text) End If sbquery.Append("update SuperAdminSettingsArea set Value=@GP where ID=1 " & "update SuperAdminSettingsArea set Value=@VRL where ID=2 " & "update SuperAdminSettingsArea set Value=@IC where ID=3 " & "update SuperAdminSettingsArea set Value=@RS where ID=5 " & "update SuperAdminSettingsArea set Value=@PVD where ID=6 " & "update SuperAdminSettingsArea set Value=@CR where ID=4 " & "update SuperAdminSettings set Name=@Name, Value=@URL where ID=1 and AreaID=4 ") cmd.CommandText = sbquery.ToString cmd.Parameters.AddWithValue("@GP", htData("GroupPermissions")) cmd.Parameters.AddWithValue("@VRL", htData("ViewRequestLicenses")) cmd.Parameters.AddWithValue("@IC", htData("InsightsCategories")) cmd.Parameters.AddWithValue("@RS", htData("ReportSettings")) cmd.Parameters.AddWithValue("@CR", htData("CustomReportsLink")) cmd.Parameters.AddWithValue("@Name", htData("ReportName")) cmd.Parameters.AddWithValue("@URL", htData("ReportURL")) cmd.Parameters.AddWithValue("@PVD", htData("PurgeDocumentVersionsLink")) Else sbquery.Append("update SuperAdminSettingsArea set Value=@GP where ID=1 " & "update SuperAdminSettingsArea set Value=@VRL where ID=2 " & "update SuperAdminSettingsArea set Value=@IC where ID=3 " & "update SuperAdminSettingsArea set Value=@RS where ID=5 " & "update SuperAdminSettingsArea set Value=@PVD where ID=6 " & "update SuperAdminSettingsArea set Value=@CR where ID=4 ") cmd.CommandText = sbquery.ToString cmd.Parameters.AddWithValue("@GP", htData("GroupPermissions")) cmd.Parameters.AddWithValue("@VRL", htData("ViewRequestLicenses")) cmd.Parameters.AddWithValue("@IC", htData("InsightsCategories")) cmd.Parameters.AddWithValue("@RS", htData("ReportSettings")) cmd.Parameters.AddWithValue("@CR", htData("CustomReportsLink")) cmd.Parameters.AddWithValue("@PVD", htData("PurgeDocumentVersionsLink")) End If Try SqlHelperInsight.ExecuteCommandNonQuery(cmd)

i did it with string builder without semicolon, because i am using sql 2008, which can work without semicolon.