Hi,

I am trying to run the following code in ASP.NET, using SqlDataAdapter to fetch a specific row and then update the data in that row.
But when I run the code, it returns no row in line 20, and no error occured.

Then I try to debug. I found that after I removed LINE 17 "da.FillSchema(osTable, SchemaType.Source)", the dataAdapter correctly returned a row that I wanted to. But I cannot update the row data after removing da.FillSchema, because an error "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand" will occur.

So what can I do to do select and update in dataAdapter? I'm using ASP.NET 2.0, SQL SERVER 2005 EXPRESS SP2, .net framework 3.5 SP1.

Thanks,
Ronald

1    Using dbconn = New SqlConnection(ConfigurationManager.ConnectionStrings("shop").ConnectionString)
2            dbconn.Open()
3        
4            Dim tran As SqlTransaction = dbconn.BeginTransaction
5            Dim osTable As New DataTable()
6            Dim SalaryID As New Guid(Request.Form("g"))
7    		
8            Using da As New SqlDataAdapter("select * from Employment WHERE id= @id", dbconn)
9                da.SelectCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier)
10               da.SelectCommand.Parameters("@id").Value = SalaryID
11               da.SelectCommand.Transaction = tran
12               da.SelectCommand.Prepare()
13      
14               Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
15               builder.ConflictOption = ConflictOption.OverwriteChanges
16      
17               da.FillSchema(osTable, SchemaType.Source)
18               da.Fill(osTable)
19      
20               If osTable.Rows.Count <= 0 Then Response.Write("NO DATA") Else Response.Write(osTable.Rows(0).Item(0))
21           End Using
22   End Using
23   

No one can answer my question?

Hi ronaldun,

Please try this code for selecting and updating:

Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString 
Dim sql As String = "select * from test" 

Using conn As New SqlConnection(connStr) 
    Dim cmd As New SqlCommand(sql, conn) 
    Dim da As New SqlDataAdapter(cmd) 
    
    ' Select the rows 
    Dim ds As New DataSet() 
    conn.Open() 
    da.Fill(ds) 
    
    ' Update the row 
    ds.Tables(0).Rows(0)("name") = "who" 
    
    ' Update the changes back into database using SqlCommandBuilder 
    Dim scb As New SqlCommandBuilder(da) 
    da.UpdateCommand = scb.GetUpdateCommand() 
    da.Update(dt) 
End Using 

If there's any problem, please let me know.

Thanks. 

Hi Wencui,

I found that when I removed the code "da.SelectCommand.Prepare()", it worked as my expected.

But I don't know what's wrong with the Prepare() function till now. Is this a BUG???...

Anyway, thanks for your solution.

Ronald

Hi ronaldun,

Based on my experience, the reason for calling Prepare method is to precompile command. In this case, it'll run faster if you use the same command for multiple times. Especially it's a stored procedure( you don't change it ). But I don't think it's suitable for your scenario.

Thanks.

Hi Wencui,

You're right. But I think even though I put the Prepare() method to the code, it should work as normal (Because MS didn't say I can't use in this situation).

Someone may execute the same sql command many times using my method.

Anyway, thanks for your hints.

Ronald