CODEWITHSUNDEEP
asp.netvb.net
Martin
Martin

Reputation: 1

split and update string in database

i have a set of strings in my textbox seperated with a comma each. i want to split them and update their record in db for each of the strings in the split. after running my code it only updates the first string and leave the rest. Here is my code.

Dim serialnumberlist = Split(txtserial.Text, ",").ToList
For Each serial As String In serialnumberlist 
    'update their branches
    Dim constr As String = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString

    Using con As New SqlConnection(constr)
        con.Open()
        Using cmd As New SqlCommand()
            cmd.CommandText = "Update scratchcards set branches=@branches where Pin_Serial=@serial"
            cmd.Parameters.AddWithValue("@branches", ddlbranches.SelectedValue)
            cmd.Parameters.AddWithValue("@serial", serial)
            cmd.Connection = con
            cmd.ExecuteNonQuery()
        End Using
    End Using
Next
Response.Write("<script>alert('Branches allocated successfully!');</script>")

Upvotes: 0

Views: 129

Answers (3)

Pramod
Pramod

Reputation: 96

Please try this way:

Dim serialnumberlist As String() = txtserial.Text.Split(","C)
For Each item As String In serialnumberlist 
    Using con As New SqlConnection("")
        Using cmd As New SqlCommand("Update scratchcards set branches=@branches where Pin_Serial=@serial", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@branches", ddlbranches.SelectedValue)
            cmd.Parameters.AddWithValue("@serial", item )


            cmd.ExecuteNonQuery()
        End Using
    End Using
Next

Upvotes: -1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

There doesn't seem to be anything wrong with your code, apart from a certain inefficiency. Perhaps the input is empty, or contains consecutive separators? You could avoid this confusion by using String.Split(Char(), StringSplitOptions) with StringSplitOptions.RemoveEmptyEntries, which will remove empty entries.

If you suspect that theere may be multiple separators in the string, eg space-comma, you can add more separators in the separators array.

As others have commented, you shouldn't rebuild the connection and command in each iteration. Your code should change to this:

Dim separators() As String = {","}
Dim serialnumberlist = txtserial.Text.Split(separators,StringSplitOptions.RemoveEmptyEntries)
'update their branches
Dim constr As String = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString

Using con As New SqlConnection(constr)
    Using cmd As New SqlCommand()
        cmd.Connection = con
        cmd.CommandText = "Update scratchcards set branches=@branches where Pin_Serial=@serial"
        cmd.Parameters.Add("@branches", SqlDbType.Int)
        cmd.Parameters.Add("@serial", SqlDbType.NVarChar,30)                

        'At the moment of execution
        cmd.Parameters("@branches").Value=ddlbranches.SelectedValue
        con.Open()
        For Each serial As String In serialnumberlist 
            cmd.Parameters("@serial").Value=serial
            cmd.ExecuteNonQuery()
        Next
    End Using
End Using

Response.Write("<script>alert('Branches allocated successfully!');</script>")

Only the parameter value needs to change for each iteration. You don't need to call .ToList() after split as For Each works with any IEnumerable

You can create the command in advance, even store it in a field for reuse. You only need to set a new Connection befor each execution.

UPDATE

From the comments, it seems that the input data contains both a comma and a space, ie PA/3/0000001740, PA/1/0000004583, PA/4/0000000095. To split this, the separator string , should be used, ie:

Dim separators() As String = {", "}

If there is any chance that a column may start withouth the space, the separators array should contain both characters, ie:

Dim separators() As String = {",", " "}

or 

Dim separators() As String = {", ",","}

Performance

Finally, the best performance for this update can be achieved if only one query is performed, ie UPDATE ... WHERE Pin_Serial in (....). This is risky though, as IN expressions can't accept parameters. If the serials were numbers, one could validate the input and just join the keys. With text though, it's possible that unexpected values will create problems.

In this case, the query can be modified to use a table-valued parameter. That's a bit more involved though, and requires creating a table type in the database:

CREATE TYPE dbo.PinTableType AS TABLE ( Pin_Serial nvarchar(50) )

Once this is done though, the loop can become a single query:

UPDATE scratchcards
set branches=@branches
from scratchcards inner join @serialsTable on myTable.Pin_Serial=scratchcards.Pin_Serial

The @serialsTable parameter will have to be of type DataTable. I'll cheat a bit and use MoreLinq and ToDataTable() :

cmd.CommandText = "UPDATE scratchcards
set branches=@branches
from scratchcards inner join @serialsTable on myTable.Pin_Serial=scratchcards.Pin_Serial"
cmd.Parameters.Add("@branches", SqlDbType.Int)    
cmd.Parameters.Add("@serialsTable",SqlDbType.Structured)

'At the moment of execution
cmd.Parameters("@branches").Value =ddlbranches.SelectedValue
Dim table as DataTable = serialnumberlist.ToDataTable()
cmd.Parameters("@serialsTable").Value=table
cmd.ExecuteNonQuery

Upvotes: 3

Trevor_G
Trevor_G

Reputation: 1331

Just as I thought, it's comma space... Either split on ", " or better, use the trim function

Dim serialnumberlist = Split(txtserial.Text, ",").ToList
For Each serial As String In serialnumberlist 
'update their branches
Dim constr As String = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString

Using con As New SqlConnection(constr)
    con.Open()
    Using cmd As New SqlCommand()
      cmd.Connection = con  
      cmd.CommandText = "Update scratchcards set branches=@branches where Pin_Serial=@serial"
      cmd.Parameters.AddWithValue("@branches", ddlbranches.SelectedValue)
      cmd.Paramaters.Add("@Serial", SqlDbType.Text)
      For Each serial As String In serialnumberlist
            cmd.Parameters("@Serial").Value = Trim(serial) '<--------HERE
            cmd.ExecuteNonQuery()
      Next       
   End Using
End Using

Response.Write("<script>alert('Branches allocated successfully!');</script>")

Upvotes: 0

Related Questions