Re: SCOPE_IDENTITY() in c# and no stored procedure...

Ok, let my try doing that and I will let you know.
Thanks,
Trint

"gabe garza" wrote:

> Actually you'd create your stored procedure with Enterprise Manager first.
> Once it's installed in the database you can call it anytime.
>
> The way you have it now in code, that would work once but the second time
> you call your code it would fail cause the stored procedure
> GetParameterIdentity() already exists in the database because it was create
> on your first ExecuteNonQuery() call.
>
>
> Check out this link.
> http://www.gbg-development.com/development.aspx
> This describes how I simplified my process to call and get results from a
> stored procdure. (This works with SELECT/UPDATE/DELETE statements too)
>
> I generate an XML file that has my stored procedure parameter inputs, then I
> use that XML file to create a SQLCommand so that I can use it to get my
> results from a stored procedure.
>
> So I have a T-SQL script that'll generate the XML, then I have C# class
> library that will create the SQL Command, then I set the parameters before
> executing the SQLCommand, then I get my result set. I can get a DataTable or
> a SqlDataReader as my result set.
>
> Check it out. Any questions let me know.
>
>
> "TrintCSD" <TrintCSD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:7D2C9CE6-8ADB-4DC3-AB39-2C9F32334ACC@xxxxxxxxxxxxxxxx
> > Ok.
> > This works, but I'm not sure it's exactly what I'm looking for:
> >
> > scmd.CommandText ="CREATE PROCEDURE GetParameterIdentity " +
> > Environment.NewLine +
> > "@Identity bigint OUT" + Environment.NewLine +
> > "AS " +
> > " INSERT INTO tblTravelDetail(MemberId, " +
> > " Comments, " +
> > " TravelEventId, " +
> > " OrderId, " +
> > " ItemID, " +
> > " PeriodID, " +
> > " CreatedDateTime, " +
> > " Operator) " +
> > "VALUES ('" + deiinsertString2 + "', " +
> > " '" + deiinsertString3 + "', " +
> > " '" + eiinsertString1 + "', " +
> > " '" + deiinsertString5 + "', " +
> > " '" + deiinsertString6 + "', " +
> > " '" + deiinsertString7 + "', " +
> > " '" + deiinsertString8 + "', " +
> > " '" + insertString7 + "') " +
> > "SET @Identity = SCOPE_IDENTITY()";
> >
> > try
> > {
> > scmd.ExecuteNonQuery();
> > }
> > catch(SqlException expSql)
> > {
> > MessageBox.Show(expSql.ToString(), this.Text);
> > frmStatusMessage.Close();
> > return;
> > }
> >
> > }
> > catch(Exception exp)
> > {
> >
> > if (strConn == SQL_CONNECTION_STRING)
> > {
> > strConn = MSDE_CONNECTION_STRING;
> > frmStatusMessage.Show("Connecting to MSDE");
> > }
> > else
> > {
> > frmStatusMessage.Close();
> > MessageBox.Show("you must have SQL " +
> > "or MSDE ", this.Text);
> >
> > Application.Exit();
> >
> > }
> >
> > }
> >
> > }
> >
> > frmStatusMessage.Close();
> >
> > MessageBox.Show("The stored procedures were successfully added to the " +
> > "tsNess database.", this.Text);
> >
> > HasCreatedSprocs = true;
> >
> > ///Start stored procedure here
> > strConn = SQL_CONNECTION_STRING5;
> > SqlConnection scnntsNess1 = new SqlConnection(strConn);
> > SqlCommand scmd1 = new SqlCommand("GetParameterIdentity", scnntsNess1);
> > SqlDataAdapter sda = new SqlDataAdapter(scmd1);
> > scmd1.CommandType = CommandType.StoredProcedure;
> >
> > scmd1.Parameters.Add(new SqlParameter("@Identity",
> > SqlDbType.BigInt)).Direction = ParameterDirection.Output;
> >
> > try
> > {
> > scnntsNess1.Open();
> >
> > scmd1.ExecuteScalar();
> > }
> > catch(SqlException expSql)
> > {
> > MessageBox.Show(expSql.ToString(), this.Text);
> > return;
> > }
> > finally
> > {
> >
> > scnntsNess1.Close();
> > }
> > // Display the results.
> > textBox1.Text = scmd1.Parameters["@Identity"].Value.ToString();
> >
> > Thanks,
> > Trinity
> >
> > "TrintCSD" wrote:
> >
> >> Gabe,
> >> adCmdStoredProc does not work in c#, only seen it working in vb.net.
> >>
> >> "gabe garza" wrote:
> >>
> >> > Start here
> >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vboriIntegratingDataVB.asp
> >> >
> >> > "TrintCSD" <TrintCSD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> > news:271DC363-6FAA-42B2-AE5B-195FA6EF807B@xxxxxxxxxxxxxxxx
> >> > > ok, I do not understand how to get a value into a stored procedure
> >> > > from my
> >> > > c#
> >> > > string, int or whatever datatype. It's much easier for me to do it
> >> > > as you
> >> > > can see in the "VALUES" part of the command with strings. What does
> >> > > the
> >> > > VALUES look like when you create a stored procedure for insert if you
> >> > > don't
> >> > > have the values yet.
> >> > > When you call the stored procedure:
> >> > > catDA.InsertCommand = new SqlCommand("GetParameterIdentity",
> >> > > strConn);
> >> > > catDA.InsertCommand.CommandType = CommandType.StoredProcedure;
> >> > > How do my values from textboxes, calculations and all over the place
> >> > > get
> >> > > in
> >> > > there?
> >> > > From all I've seen, values look like the field names with an @ sign
> >> > > in
> >> > > front
> >> > > of them.
> >> > > An example would probably help.
> >> > > Thanks for any replies.
> >> > > Trint
> >> > >
> >> > > "gabe garza" wrote:
> >> > >
> >> > >>
> >> > >> > string strSQL5 = "INSERT INTO tblTravelDetail(MemberId, " +
> >> > >> > " Comments, " +
> >> > >> > " TravelEventId, " +
> >> > >> > " OrderId, " +
> >> > >> > " ItemID, " +
> >> > >> > " PeriodID, " + //here is where we do the PeriodID lookup
> >> > >> > calculation
> >> > >> > " CreatedDateTime, " +
> >> > >> > " Operator) " +
> >> > >> > "VALUES ('" + deiinsertString2 + "', " +
> >> > >> > " '" + deiinsertString3 + "', " +
> >> > >> > " '" + eiinsertString1 + "', " +
> >> > >> > " '" + deiinsertString5 + "', " +
> >> > >> > " '" + deiinsertString6 + "', " +
> >> > >> > " '" + deiinsertString7 + "', " +
> >> > >> > " '" + deiinsertString8 + "', " +
> >> > >> > " '" + insertString7 + "') "
> >> > >> > cnn5.Execute(strSQL5);
> >> > >>
> >> > >> > strSQL5 = "SELECT SCOPE_IDENTITY()"
> >> > >> > cnn5.Execute(strSQL5);
> >> > >>
> >> > >> Using the same cnn5 connection you can retrieve your
> >> > >> SCOPE_IDENTITY()
> >> > >> from
> >> > >> your previous INSERT.
> >> > >> But it must be the same SqlConnection, don't create another
> >> > >> SqlConnection,
> >> > >> use cnn5 on both Execute()'s.
> >> > >>
> >> > >> I think someone pointed out that you should really be using a
> >> > >> parameterized
> >> > >> sql query or even better, do this in a stored procedure and have the
> >> > >> identity return in an OUTPUT Parameter or as a RETURN from your
> >> > >> stored
> >> > >> procedure.
> >> > >>
> >> > >>
> >> > >> "TrintCSD" <TrintCSD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> > >> news:C13DFFCD-546D-458C-8FDA-C4B391120085@xxxxxxxxxxxxxxxx
> >> > >> > ok, I've been on this for a long time...How can I get the most
> >> > >> > recent
> >> > >> > identity from this insert into a string?(with using this code as
> >> > >> > the
> >> > >> > insert):
> >> > >> >
> >> > >> > string strSQL5 = "INSERT INTO tblTravelDetail(MemberId, " +
> >> > >> > " Comments, " +
> >> > >> > " TravelEventId, " +
> >> > >> > " OrderId, " +
> >> > >> > " ItemID, " +
> >> > >> > " PeriodID, " + //here is where we do the PeriodID lookup
> >> > >> > calculation
> >> > >> > " CreatedDateTime, " +
> >> > >> > " Operator) " +
> >> > >> > "VALUES ('" + deiinsertString2 + "', " +
> >> > >> > " '" + deiinsertString3 + "', " +
> >> > >> > " '" + eiinsertString1 + "', " +
> >> > >> > " '" + deiinsertString5 + "', " +
> >> > >> > " '" + deiinsertString6 + "', " +
> >> > >> > " '" + deiinsertString7 + "', " +
> >> > >> > " '" + deiinsertString8 + "', " +
> >> > >> > " '" + insertString7 + "'); " +
> >> > >> > "SELECT SCOPE_IDENTITY()";
> >> > >> >
> >> > >> > cnn5.Execute(strSQL5, out recordsEffected, 0);
> >> > >> >
> >> > >> > Thanks,
> >> > >> > Trint
> >> > >> >
> >> > >> > --
> >> > >> > Trinity Smith
> >> > >> > c#/vb.Net Developer
> >> > >> > EcoQuest, Intl.
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
>
>
>
.

Relevant Pages

  • Re: SCOPE_IDENTITY() in c# and no stored procedure...
    ... "gabe garza" wrote: ... I do not understand how to get a value into a stored procedure from my ... >>> But it must be the same SqlConnection, ... >>> identity return in an OUTPUT Parameter or as a RETURN from your stored ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Generate an XML file from a stored procedure?!!
    ... "CrystalDBA" wrote in message ... I could run the project and try to figure out where the developers are making the call to the stored procedure and insert a line to writetoxmlfile. ... > Is there a way working with SQL Server that I can generate an xml file. ...
    (microsoft.public.sqlserver.dts)
  • Re: Generate an XML file from a stored procedure?!!
    ... "CrystalDBA" wrote in message ... I could run the project and try to figure out where the developers are making the call to the stored procedure and insert a line to writetoxmlfile. ... > Is there a way working with SQL Server that I can generate an xml file. ...
    (microsoft.public.sqlserver.programming)
  • Re: Generate an XML file from a stored procedure?!!
    ... "CrystalDBA" wrote in message ... I could run the project and try to figure out where the developers are making the call to the stored procedure and insert a line to writetoxmlfile. ... > Is there a way working with SQL Server that I can generate an xml file. ...
    (microsoft.public.sqlserver.xml)
  • Re: How to use content of file for xml input?
    ... Best regards ... XML on the client and pass it to the stored procedure as a parameter. ... actually i'm starting to work with OpenXML on SQL Server 2k. ... how a external xml file can be prepared to use ...
    (microsoft.public.sqlserver.xml)