Question 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

 

[demausdauth]

dt is an invalid object to the SqlServer since it doesn't exist there. It only exists in your program.

It is not clear if you are trying update your SQL table or if you just want to select "code,name, and city" from your SQL tables.

If you just want to select the code,name and city from your tables you could use a join. Give this a try.

SELECT E.code,E.name, M.city FROM employees AS E LEFT JOIN Master AS M ON M.code = E.code

Dim dt As DataTable
        Dim str As New SqlClient.SqlCommand("SELECT E.code,E.name, M.city FROM employees AS E LEFT JOIN Master AS M ON M.code = E.code", con)
        da = New SqlClient.SqlDataAdapter(str)
        dt = New DataTable
        da.Fill(dt)

 

[tqmd1]

Dear Sir,

In the light of you your codes i modify my codes as

 Dim dt As DataTable
        Dim str As New SqlClient.SqlCommand("SELECT RANK() OVER(ORDER BY g.acccode) AS sno,count(g.acccode) as sno, g.acccode,COUNT(g.Date) AS vno, SUM(g.Qty) AS Qty, SUM(g.Qty*g.weight) AS weight  FROM gpass where Date between '" & Me.dtp_first.Text & "' and  '" & Me.dtp_last.Text & "' GROUP BY g.acccode AS g LEFT JOIN Master AS M ON M.code = g.acccode", con)
        da = New SqlClient.SqlDataAdapter(str)
        dt = New DataTable
        da.Fill(dt)

Now i has error message as
Incorrect syntax near the keyword 'AS'.

the codes has two tables
gpass
master

Please modify

 

[demausdauth]

Dear Sir,

In the light of you your codes i modify my codes as

 Dim dt As DataTable
        Dim str As New SqlClient.SqlCommand("SELECT RANK() [COLOR="Red"]OVER(ORDER BY g.acccode) AS sno,count(g.acccode) as sno[/COLOR], g.acccode,COUNT(g.Date) AS vno, SUM(g.Qty) AS Qty, SUM(g.Qty*g.weight) AS weight  FROM gpass where Date between '" & Me.dtp_first.Text & "' and  '" & Me.dtp_last.Text & "' GROUP BY g.acccode AS g LEFT JOIN Master AS M ON M.code = g.acccode", con)
        da = New SqlClient.SqlDataAdapter(str)
        dt = New DataTable
        da.Fill(dt)

I am by no means a SQL guru, but the first thing that comes to mind is that in your SQL statement there are 2 columns named the same.

 

[cjard]

Read the DW3 link in my signature for information on how to do your data access properly, and these problems will disappear.Start with the Creating a Simple Data App tutorial