how to update data using nest reader???

[kank]

I would like to update data matched from one sql command but can't how to do?

Private Sub btnCompare_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCompare.Click
        sqlconn.Open()

        SQLCmd = sqlconn.CreateCommand
        SQLCmd.CommandText = "select emp_id,section,department,position from tblPayroll where type in ('IND','MGR') and emp_id not in (select emp_id from tblPayroll_prev where type in ('IND','MGR') and month_year in(select max(month_year) from tblPayroll_prev where type in ('IND','MGR')))"

        rdr = SQLCmd.ExecuteReader()
        Dim sec, dep, pos As String
        Dim emp As Boolean = False
        Dim ItemList As New ArrayList()
        Dim manyArrayList As New ArrayList()
        While rdr.Read()
            sec = rdr.GetString(1)
            dep = rdr.GetString(2)
            pos = rdr.GetString(3)
            ItemList.Add(sec)
            ItemList.Add(dep)
            ItemList.Add(pos)
            manyArrayList.Add(ItemList)
        End While
        rdr.Close()

        Dim sqlString As String
        sqlString = "select section,department,position,employed from tblNew_IDL "
        SQLCmd = sqlconn.CreateCommand
        SQLCmd.CommandText = sqlString

        DataGridView1.DataSource = Nothing
        da = New SqlDataAdapter(sqlString, sqlconn)
        commandBuilder = New SqlCommandBuilder(da)
        ds = New DataSet()
        da.Fill(ds, "Employ")


        rdr = SQLCmd.ExecuteReader()
        While rdr.Read()

            sec = rdr.GetString(0)
            dep = rdr.GetString(1)
            pos = rdr.GetString(2)
            emp = rdr.GetBoolean(3)


            For i = 0 To ItemList.Count - 1
                If i + 2 <= ItemList.Count - 1 Then
                    If String.Equals(Trim(ItemList.Item(i)), Trim(sec)) And String.Equals(Trim(ItemList.Item(i + 1)), Trim(dep)) And String.Equals(Trim(ItemList.Item(i + 2)), Trim(pos)) Then
                        MessageBox.Show("Equal")
                        emp = True
                        dt = ds.Tables("Employ")
                        da.Update(dt)

                    End If
                End If
            Next
        End While
        rdr.Close()
    
        sqlconn.Close()
    End Sub

From the code, when matched found it alerts "equal", then I would like to change emp to True and then update the change back to Database. But my code doesn't work. :-(

Pls kindly help.
Thanks alot.

 

[kank]

I try to use nest reader but still can't work :-(

rdr = SQLCmd.ExecuteReader()
        While rdr.Read()

            sec = rdr.GetString(0)
            dep = rdr.GetString(1)
            pos = rdr.GetString(2)
            emp = rdr.GetBoolean(3)


            For i = 0 To ItemList.Count - 1
                If i + 2 <= ItemList.Count - 1 Then
                    If String.Equals(Trim(ItemList.Item(i)), Trim(sec)) And String.Equals(Trim(ItemList.Item(i + 1)), Trim(dep)) And String.Equals(Trim(ItemList.Item(i + 2)), Trim(pos)) Then
                        'sqlconn.Close()
                        Dim updateStr As String
                        'sqlconn.Open()
                        updateStr = "update tblNew_IDL set employed = 'True' where section = '" & sec & "' and department = '" & dep & "' and position = '" & pos & "'"
                        SQLCmd = New SqlCommand(updateStr, sqlconn)
                        SQLCmd = sqlconn.CreateCommand
                        SQLCmd.CommandText = updateStr
                        SQLCmd.ExecuteNonQuery()

                    End If
                End If
            Next
        End While
        rdr.Close()

 

[Menthos]

Firstly, look into dropping Try...Catch blocks around your SQL code - you'll find it a lot easier to trap the exceptions that get raised and from there be able to see where the problem lies.

Looking at your second attempt, you need to get away from using concatenated strings for your query... take a look at John McIlhinney's Blog post on using parametized queries it's a better, more secure way of passing your query.

Secondly, you're not opening the sqlconn connection, which ExecuteNonQuery() requires.

 

[kank]

thanks a lot, i got it.