Hi Jerry,
Select in insert stataments serves only for refreshing the values such as
identity when you insert new records to database (it is invoked and used
automatically within Update method).
The RowState of row indicates the well state of row.
However, this state is reset after you call AcceptChanges to Unmodified.
HTH,
--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
I've got everythign stripped down to just the basics - taking my lead from a
kb article. All I'm doing at this point is adding a single record to a
table and trying to get the identity (FileID) value back so I can add recs
to a related table. Here's my code:
----------------------------------------------------------------------
Dim sqlCnxn As New SqlConnection("user id=SomeUser;password=password;data
source=MYSERVER")
Dim cmdFiles As New SqlCommand()
Dim sFileName As String
sFileName = Now.Millisecond & Now.Second & Now.Minute & Now.Hour & Now.Year
& Now.Month & Now.Day
Dim daFiles As New SqlDataAdapter("SELECT FileID, FileName, ExpireTime,
PostedBy, HashName, FileSize, Description FROM FD_Files", sqlCnxn)
Dim dsFiles As New DataSet()
Dim sqlInsert As New SqlCommand()
With sqlInsert
.CommandText = "INSERT INTO FD_Files (FileName, ExpireTime, PostedBy,
HashName, FileSize, Description) " & _
"VALUES (@FileName, @ExpireTime, @PostedBy, @HashName,
@FileSize, @Description); " & _
"SELECT FileID, FileName, ExpireTime, PostedBy, HashName,
FileSize, Description " & _
"FROM FD_Files WHERE (FileID = Scope_Identity())"
.CommandType = CommandType.Text
.Connection = sqlCnxn
.Parameters.Add(New SqlParameter("@FileName", SqlDbType.VarChar, 300,
"FileName"))
.Parameters.Add(New SqlParameter("@ExpireTime", SqlDbType.DateTime, 4,
"ExpireTime"))
.Parameters.Add(New SqlParameter("@PostedBy", SqlDbType.Char, 25,
"PostedBy"))
.Parameters.Add(New SqlParameter("@HashName", SqlDbType.VarChar, 25,
"HashName"))
.Parameters.Add(New SqlParameter("@FileSize", SqlDbType.VarChar, 10,
"FileSize"))
.Parameters.Add(New SqlParameter("@Description", SqlDbType.VarChar, 512,
"Description"))
End With
daFiles.InsertCommand = sqlInsert
daFiles.Fill(dsFiles, "FD_Files")
Dim dr As DataRow = dsFiles.Tables(0).NewRow
Dim dFileSize As Double = 1234567 / 1024 / 1024
If dFileSize < 0.01 Then dFileSize = 0.01
dr("FileName") = "TestFileName"
dr("ExpireTime") = Now.AddHours(24)
dr("PostedBy") = "JerryCa"
dr("HashName") = sFileName
dr("FileSize") = Format(dFileSize, "N") & " MB"
dr("Description") = "Test Description"
dsFiles.Tables(0).Rows.Add(dr)
daFiles.Update(dsFiles, "FD_Files")
dsFiles.AcceptChanges()
Dim i As Int16
For i = 0 To dsFiles.Tables("FD_Files").Rows.Count - 1
With dsFiles.Tables("FD_Files")
Debug.WriteLine("FileID: " & .Rows(i)(0).ToString)
Debug.WriteLine("FileName: " & .Rows(i)(1).ToString)
Debug.WriteLine("ExpireTime: " & .Rows(i)(2).ToString)
End With
Next i
----------------------------------------------------------------------
Now, I assumed that the SELECT attached to the INSERT would cause the
updated dataset to contain only the new record because of the WHERE clause.
But the updated ds always has the full table. How am I supposed to know
which is the new record? Is it safe to assume that the last record in the
ds is the new one? According th the KB artice I got this code from, I
should see "The new record", not all records. What am I missing? Thanks!
Jerry
Miha Markic said:
Hi Jerry,
Scope_identity() is the right one.
About your problem: what do you mean that you get the whole table?
What is your scenario?
--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
Jerry Camel said:
This has been addressed several times and I've read several articles and
posts and I still can't get this to work. (This is VB .NET)
My understanding is that if I append a select statement to my DataAdapter's
Insert statement that I should be able to get an updated resultset. I've
got this:
INSERT INTO dbo.FD_Files (FileName, ExpireTime, PostedBy, HashName,
FileSize, Description) VALUES (@FileName, @ExpireTime, @PostedBy, @HashName,
@FileSize, @Description); SELECT FileID, FileName, ExpireTime, PostedBy,
HashName, FileSize, Description FROM dbo.FD_Files WHERE FileID = @@IDENTITY
I've also tried using SCOPE_IDENTITY() - reagrdless, I always get the whole
table returned.
I need to know the identity of the record I just added and I keep
getting
a
whole table. It's making me crazy.
I've even tried setting the adapter's select command to SELECT FileID from
FD_Files WHERE FileID = SCOPE_IDENTITY() and re-filling the dataset.
I
get
the whole damned table. Why!!!!????
What am I missing? I've tried using the built in wizard which comes
up
with
an insert statement just like the one above and I still end up with the
whole table after I update.
Can anyone shed some light on this? Thanks.
Jerry
