Jerry
Fri Feb 20 08:29:02 CST 2004
This is a multi-part message in MIME format.
------=_NextPart_000_000C_01C3F793.FA6370E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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=3DSomeUser;password=3Dpassword;data source=3DMYSERVER")
Dim cmdFiles As New SqlCommand()
Dim sFileName As String
sFileName =3D 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 =3D "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 =3D Scope_Identity())"
.CommandType =3D CommandType.Text
.Connection =3D 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 =3D sqlInsert
daFiles.Fill(dsFiles, "FD_Files")
Dim dr As DataRow =3D dsFiles.Tables(0).NewRow
Dim dFileSize As Double =3D 1234567 / 1024 / 1024
If dFileSize < 0.01 Then dFileSize =3D 0.01
dr("FileName") =3D "TestFileName"
dr("ExpireTime") =3D Now.AddHours(24)
dr("PostedBy") =3D "JerryCa"
dr("HashName") =3D sFileName
dr("FileSize") =3D Format(dFileSize, "N") & " MB"
dr("Description") =3D "Test Description"
dsFiles.Tables(0).Rows.Add(dr)
daFiles.Update(dsFiles, "FD_Files")
dsFiles.AcceptChanges()
Dim i As Int16
For i =3D 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 [MVP C#]" <miha at rthand com> wrote in message =
news:O0Cwhl59DHA.1128@tk2msftngp13.phx.gbl...
> Hi Jerry,
>=20
> Scope_identity() is the right one.
> About your problem: what do you mean that you get the whole table?
> What is your scenario?
> --=20
> Miha Markic [MVP C#] - RightHand .NET consulting & software =
development
> miha at rthand com
> www.rthand.com
>=20
> "Jerry Camel" <rlrcstr@msn.com> wrote in message
> news:%23taobN09DHA.1816@TK2MSFTNGP12.phx.gbl...
> > 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 =3D
> @@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 =3D 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
> >
> >
>=20
>
------=_NextPart_000_000C_01C3F793.FA6370E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff background=3D"">
<DIV><FONT face=3DArial size=3D2>I've got everythign stripped down to =
just the=20
basics - taking my lead from a kb article. All I'm doing at this =
point is=20
adding a single record to a table and trying to get the identity =
(FileID) value=20
back so I can add recs to a related table. Here's my =
code:<BR><BR><FONT=20
face=3D"Courier =
New">--------------------------------------------------------------------=
--</FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Courier New">Dim sqlCnxn =
As New=20
SqlConnection("user id=3DSomeUser;password=3Dpassword;data =
source=3DMYSERVER")<BR>Dim=20
cmdFiles As New SqlCommand()<BR></DIV></FONT></FONT>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Courier New">Dim =
sFileName As=20
String<BR>sFileName =3D Now.Millisecond & Now.Second & =
Now.Minute &=20
Now.Hour & Now.Year & Now.Month & =
Now.Day<BR></FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Courier New">Dim daFiles =
As New=20
SqlDataAdapter("SELECT FileID, FileName, ExpireTime, PostedBy, HashName, =
FileSize, Description FROM FD_Files", sqlCnxn)<BR></FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Courier New">Dim dsFiles =
As New=20
DataSet()<BR>Dim sqlInsert As New SqlCommand()<BR></FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Courier New">With=20
sqlInsert<BR> .CommandText =3D "INSERT INTO FD_Files =
(FileName,=20
ExpireTime, PostedBy, HashName, FileSize, Description) " &=20
_<BR> &n=
bsp; "VALUES=20
(@FileName, @ExpireTime, @PostedBy, @HashName, @FileSize, @Description); =
" &=20
_<BR> &n=
bsp; =20
"SELECT FileID, FileName, ExpireTime, PostedBy, HashName, FileSize, =
Description=20
" &=20
_<BR> &n=
bsp; =20
"FROM FD_Files WHERE (FileID =3D Scope_Identity())"<BR> =
.CommandType =3D=20
CommandType.Text<BR> .Connection =3D sqlCnxn<BR> =
.Parameters.Add(New SqlParameter("@FileName", SqlDbType.VarChar, 300,=20
"FileName"))<BR> .Parameters.Add(New =
SqlParameter("@ExpireTime",=20
SqlDbType.DateTime, 4, "ExpireTime"))<BR> =
.Parameters.Add(New=20
SqlParameter("@PostedBy", SqlDbType.Char, 25, =
"PostedBy"))<BR> =20
.Parameters.Add(New SqlParameter("@HashName", SqlDbType.VarChar, 25,=20
"HashName"))<BR> .Parameters.Add(New =
SqlParameter("@FileSize",=20
SqlDbType.VarChar, 10, "FileSize"))<BR> .Parameters.Add(New=20
SqlParameter("@Description", SqlDbType.VarChar, 512, =
"Description"))<BR>End=20
With</FONT></FONT></DIV><FONT face=3DArial size=3D2><FONT =
face=3D"Courier New">
<DIV><FONT face=3DArial></FONT><FONT face=3DArial></FONT><FONT=20
face=3DArial></FONT><BR>daFiles.InsertCommand =3D =
sqlInsert<BR>daFiles.Fill(dsFiles,=20
"FD_Files")<BR></DIV>
<DIV>Dim dr As DataRow =3D dsFiles.Tables(0).NewRow<BR>Dim dFileSize As =
Double =3D=20
1234567 / 1024 / 1024<BR>If dFileSize < 0.01 Then dFileSize =3D =
0.01<BR></DIV>
<DIV>dr("FileName") =3D "TestFileName"<BR>dr("ExpireTime") =3D=20
Now.AddHours(24)<BR>dr("PostedBy") =3D "JerryCa"<BR>dr("HashName") =3D=20
sFileName<BR>dr("FileSize") =3D Format(dFileSize, "N") & "=20
MB"<BR>dr("Description") =3D "Test Description"<BR></DIV>
<DIV>dsFiles.Tables(0).Rows.Add(dr)<BR>daFiles.Update(dsFiles,=20
"FD_Files")<BR>dsFiles.AcceptChanges()<BR></DIV>
<DIV>Dim i As Int16<BR> For i =3D 0 To=20
dsFiles.Tables("FD_Files").Rows.Count - 1<BR> With=20
dsFiles.Tables("FD_Files")<BR> =20
Debug.WriteLine("FileID: " & .Rows(i)(0).ToString)<BR> =20
Debug.WriteLine("FileName: " &=20
.Rows(i)(1).ToString)<BR> =
Debug.WriteLine("ExpireTime:=20
" & .Rows(i)(2).ToString)<BR> End With<BR>Next i</DIV>
<DIV>--------------------------------------------------------------------=
--</DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial>Now, I assumed that the SELECT attached to the =
INSERT=20
would cause the updated dataset to contain only the new record because =
of the=20
WHERE clause. But the updated ds always has the full table. =
How am I=20
supposed to know which is the new record? Is it safe to assume =
that the=20
last record in the ds is the new one? According th the KB artice I =
got=20
this code from, I should see "The new record", not all records. =
What am I=20
missing? Thanks!</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>Jerry</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial></FONT></FONT></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>"Miha Markic [MVP C#]" <miha at =
rthand com>=20
wrote in message news:O0Cwhl59DHA.1128@tk2msftngp13.phx.gbl...<BR>> =
Hi=20
Jerry,<BR>> <BR>> Scope_identity() is the right one.<BR>> About =
your=20
problem: what do you mean that you get the whole table?<BR>> What is =
your=20
scenario?<BR>> -- <BR>> Miha Markic [MVP C#] - RightHand .NET =
consulting=20
& software development<BR>> miha at rthand com<BR>>=20
www.rthand.com<BR>> <BR>> "Jerry Camel" <rlrcstr@msn.com> =
wrote in=20
message<BR>> news:%23taobN09DHA.1816@TK2MSFTNGP12.phx.gbl...<BR>> =
>=20
This has been addressed several times and I've read several articles =
and<BR>>=20
> posts and I still can't get this to work. (This is VB =
.NET)<BR>>=20
><BR>> > My understanding is that if I append a select =
statement to=20
my<BR>> DataAdapter's<BR>> > Insert statement that I should be =
able to=20
get an updated resultset. I've<BR>> > got this:<BR>> =
><BR>>=20
> INSERT INTO dbo.FD_Files (FileName, ExpireTime, PostedBy, =
HashName,<BR>>=20
> FileSize, Description) VALUES (@FileName, @ExpireTime, =
@PostedBy,<BR>>=20
@HashName,<BR>> > @FileSize, @Description); SELECT FileID, =
FileName,=20
ExpireTime, PostedBy,<BR>> > HashName, FileSize, Description FROM=20
dbo.FD_Files WHERE FileID =3D<BR>> @@IDENTITY<BR>> ><BR>> =
> I've=20
also tried using SCOPE_IDENTITY() - reagrdless, I always get the<BR>> =
whole<BR>> > table returned.<BR>> ><BR>> > I need to =
know the=20
identity of the record I just added and I keep getting<BR>> a<BR>> =
>=20
whole table. It's making me crazy.<BR>> ><BR>> > I've =
even=20
tried setting the adapter's select command to SELECT FileID from<BR>> =
>=20
FD_Files WHERE FileID =3D SCOPE_IDENTITY() and re-filling the =
dataset. =20
I<BR>> get<BR>> > the whole damned table. =
Why!!!!????<BR>>=20
><BR>> > What am I missing? I've tried using the built in =
wizard=20
which comes up<BR>> with<BR>> > an insert statement just like =
the one=20
above and I still end up with the<BR>> > whole table after I=20
update.<BR>> ><BR>> > Can anyone shed some light on =
this? =20
Thanks.<BR>> ><BR>> > Jerry<BR>> ><BR>> =
><BR>>=20
<BR>> </FONT></DIV></BODY></HTML>
------=_NextPart_000_000C_01C3F793.FA6370E0--