Update DataTable

[tqmd1]

I have followin codes

Dim dt As DataTable
        Dim str As New SqlClient.SqlCommand("select code,name from employees", con)
        da = New SqlClient.SqlDataAdapter(str)
        dt = New DataTable
        da.Fill(dt)
        dt.Columns.Add("city")

        Dim cmd As New SqlClient.SqlCommand("UPDATE dt SET city=(SELECT top 1 city FROM master WHERE master.code=dt.code)", con)
        cmd.ExecuteNonQuery()

it says

Invalid object name 'dt'.

How to update datatable with master table?

Please help

[kvprajapati]

Is that your thread?

http://www.daniweb.com/forums/thread256234.html

[apegram]

You're not going to join your in-memory datatable to an actual database table with a SQL statement.

What you want to do is (pseudocode only, have work to do) is something like

'pseudocode
For each row of data in your datatable

   Get value of "code"
   Construct SQL statement to extract top city for that code
       -> SELECT top 1 city FROM master WHERE master.code = [the code here]
   Use SqlCommand.ExecuteScalar() to retrieve that value
   Store value in the "city" field of the row

Next row

[tqmd1]

You're not going to join your in-memory datatable to an actual database table with a SQL statement.

What you want to do is (pseudocode only, have work to do) is something like

'pseudocode
For each row of data in your datatable

   Get value of "code"
   Construct SQL statement to extract top city for that code
       -> SELECT top 1 city FROM master WHERE master.code = [the code here]
   Use SqlCommand.ExecuteScalar() to retrieve that value
   Store value in the "city" field of the row

Next row

Your idea is so great. I really need that you said but could not write code in the light of your words

Could you plesae wirte for me

[apegram]

Try something like this modification. Replace lines 8 and 9 in your code in the original post with this and try it, modify it as necessary (like if "code" is a string and not a number, get rid of Int32.Parse() and change SqlDbType.Int to SqlDbType.VarChar).

Dim cmd As New SqlCommand("SELECT top 1 city FROM master WHERE master.code=@code", con)
        con.Open()

        For Each dr As DataRow In dt.Rows
            cmd.Parameters.Clear()
            Dim param As New SqlParameter("@code", SqlDbType.Int)
            param.Value = Int32.Parse(dr("code").ToString())
            cmd.Parameters.Add(param)
            Dim o As Object = cmd.ExecuteScalar()
            If Not (o.GetType() Is GetType(DBNull)) Then
                dr("city") = o.ToString()
            End If
        Next

        con.Close()
        cmd.Dispose()

I hope it works.

[tqmd1]

Try something like this modification. Replace lines 8 and 9 in your code in the original post with this and try it, modify it as necessary (like if "code" is a string and not a number, get rid of Int32.Parse() and change SqlDbType.Int to SqlDbType.VarChar).

Dim cmd As New SqlCommand("SELECT top 1 city FROM master WHERE master.code=@code", con)
        con.Open()

        For Each dr As DataRow In dt.Rows
            cmd.Parameters.Clear()
            Dim param As New SqlParameter("@code", SqlDbType.Int)
            param.Value = Int32.Parse(dr("code").ToString())
            cmd.Parameters.Add(param)
            Dim o As Object = cmd.ExecuteScalar()
            If Not (o.GetType() Is GetType(DBNull)) Then
                dr("city") = o.ToString()
            End If
        Next

        con.Close()
        cmd.Dispose()

I hope it works.

Dear Sir,

Finally I have following codes

str = "SELECT RANK() OVER(ORDER BY acccode) AS sno,count(acccode) as sno,acccode,COUNT(Date) AS vou_no, SUM(Qty) AS Qty, SUM(Qty*weight) AS weight  FROM gpass where Date between '" & Me.dtp_first.Text & "' and  '" & Me.dtp_last.Text & "' GROUP BY acccode"
            dt = GetTable(str)
            dt.Columns.Add("tittle")


            Dim cmd As New SqlCommand("SELECT top 1 desc1 FROM master WHERE master.code=@code", con)

            For Each dr As DataRow In dt.Rows
                cmd.Parameters.Clear()
                Dim param As New SqlParameter("@code", SqlDbType.Int)
                param.Value = Int32.Parse(dr("acccode").ToString())
                cmd.Parameters.Add(param)
                Dim o As Object = cmd.ExecuteScalar()
                If Not (o.GetType() Is GetType(DBNull)) Then
                    dr("tittle") = o.ToString()
                End If
            Next

on this line

If Not (o.GetType() Is GetType(DBNull)) Then

has this error message
Object reference not set to an instance of an object.

how to get rid of it

[apegram]

If Not (o Is Nothing) Then
                If Not (o.GetType() Is GetType(DBNull)) Then
                    dr("city") = o.ToString()
                End If
            End If

Try that.

[tqmd1]

If Not (o Is Nothing) Then
                If Not (o.GetType() Is GetType(DBNull)) Then
                    dr("city") = o.ToString()
                End If
            End If

Try that.

Thanks for helping
Now everything is ok