Hi there,
[color=blue]
> fNewsDate = "'" & txtNewsDate.Text & "',"
> fNewsSubBy = "'" & txtNewsSubBy.Text & "',"
> fNewsHdr = "'" & txtNewsHdr.Text & "',"
> fNewsDesc = "'" & txtNewsDesc.Text & "'"
>
> sql = "SET NOCOUNT ON; Insert INTO [News]
> (NewsDate,NewsSubBy,NewsHdr,NewsDesc) Values " & "(" &
> fNewsDate & fNewsSubBy & fNewsHdr & fNewsDesc & ");SELECT
> @@IDENTITY AS myID FROM News;"[/color]
Few points. Never concat an SQL string like that. The code below has
security problems. Always use parameters.
Try this:
sql = "SET NOCOUNT ON; INSERT [News] (NewsDate, ETC ) VALUES (@NewsDate,
@ETC); SET @ID = SELECT @@IDENTITY;";
new command, bla bla
myCommand.Add("@NewDate", SqlDbTypes.DateTime).Value = myDateTime;
myCommand.Add("@ETC", SqlDbTypes.Whatever).Value = ETC;
myCommand.Add("@ID", SqlDbTypes.Int).Direction =
ParameterDirection.Output;
[color=blue]
> conSCGC.Open()
> Try
> cmd.ExecuteScalar()
> NewID = cmd.ExecuteScalar("myID")[/color]
Why call ExecuteScalar() twice? Also, shouldn't you have to cast there?
You DO have OPTION STRICT ON, right? :)
Did this work the way you had it? Try with the output param if not. You
may have to play around a bit (I use stored procedures for inserts, so my
syntax might be off somewhere).
[color=blue]
> Response.Write(NewID)
> Catch
> Response.Write(sql)
> Finally
> conSCGC.Close()[/color]
Forgot to dispose your command (SqlCommand implements IDisposable).
[color=blue]
> End Try[/color]
-mike
MVP
[color=blue]
> End Sub
> ******[color=green]
> >-----Original Message-----
> >Have you added an output parameter and assigned[/color]
> @@IDENTITY to it? I think[color=green]
> >that should do it. Alternatively, you could use[/color]
> ExecuteScalar and get the[color=green]
> >only SELECTed row, @@IDENTITY.
> >-mike
> >MVP
> >
> ><anonymous@discussions.microsoft.com> wrote in message
> >news:047f01c3b07e$9651b190$a301280a@phx.gbl...[color=darkred]
> >> Thanks but I still have a question..I already had the[/color][/color]
> sql[color=green][color=darkred]
> >> part that wasn't the issue but when I click on the[/color][/color]
> button[color=green][color=darkred]
> >> to submit the link how do I assign the newly added[/color][/color]
> record[color=green][color=darkred]
> >> id to a variable so I can then display a confirm page
> >> with a link to the users newly added record page? I
> >> right now am using the executeNonQuery but it just adds
> >> the record and doesn't return any value.
> >>
> >> Thanks
> >>
> >> >-----Original Message-----
> >> >In the command that inserts, after the insert, you can
> >> do SELECT @@IDENTITY
> >> >and that will give you the ID of the last row[/color][/color]
> inserted.[color=green][color=darkred]
> >> >
> >> >-mike
> >> >MVP
> >> >
> >> >"Tony Stoker" <anonymous@discussions.microsoft.com>
> >> wrote in message
> >> >news:0e7e01c3aebf$98f55f70$a501280a@phx.gbl...
> >> >> I have a .Net web app that adds a record to a SQL
> >> >> database. After the user adds their record I want[/color][/color]
> to[color=green][color=darkred]
> >> >> have a link that will link them to their new record!
> >> The
> >> >> recordID is a AutoNumber in the SQL server...
> >> >>
> >> >> How do I return the recordID after I have added the
> >> >> record?
> >> >
> >> >
> >> >.
> >> >[/color]
> >
> >
> >.
> >[/color][/color]
