C-Sharp (C#) Group: Re: Understanding stored procedures

Thread: C-Sharp (C#) Group: Re: Understanding stored procedures

Search this list only Search all lists
C-Sharp (C#) Group: Re: Understanding stored procedures
	2006-10-03 12:43:25
Hi! Thanks for answering. I'm getting no error message, and no insert to the sql database. However I get the message as defined in the inner finally statement. I was wondering if the stored procedure could be wrong. Here it is: CREATE PROCEDURE dbo.InsUnameAndPword Username varchar(8) OUTPUT, Password varchar(8) OUTPUT AS SET NOCOUNT ON INSERT INTO tblUsers(Username,Password) VALUES(Username,Password) SET NOCOUNT OFF GO As I'm new to these things I'd be grateful if you could correct me here. I was also wondering if the third parameter, in the C# code, is correct. What I'm aiming for is getting the values of the two textboxes on the form for the cmdIns.Parameters.Add() thing. Thank you Marty Thompson skrev: > What is the error message you are getting? I'm seeing two calls to " > con.Close()" which if I'm not mistaken throws an exception. You should only > have to call it once(within the finally block is a good place). > > On 10/3/06, Krij <gsb58start.no> wrote: > > > > > > Hi! > > > > As part of my school training I'm experimenting with the following: > > > > When trying to insert new username and password into a sql database > > (SQL 2000) through C# code I did the following: > > > > private void btnInsertWithStoredProcedure_Click(object sender, > > System.EventArgs e) > > { > > //Outer exception, nothing succeeded > > try > > { > > SqlConnection con = new SqlConnection > > > > ("server=localhost;database=Users;uid=geir;pwd=geir&quo t;); > > con.Open(); > > > > //Inner exception. ExecuteNonQuery failed > > try > > { > > //Addressing stored procedure > > SqlCommand cmdIns = new > > > > SqlCommand("InsUnameAndPword",con); > > cmdIns.CommandType = > > CommandType.StoredProcedure; > > > > cmdIns.Parameters.Add("Username", > > SqlDbType.VarChar,8,txtUsername.Text.Trim()); > > cmdIns.Parameters.Add("Password", > > SqlDbType.VarChar,8,txtPassword.Text.Trim()); > > > > cmdIns.ExecuteNonQuery(); > > } > > catch(SqlException ex) > > { > > lblInfo.Text = "ExecuteNonQuery > > failed because: n" + > > "n" + > > ex.Message; > > } > > finally > > { > > //Refresh datagrid and blank > > out textboxes on form > > dgUsers.Refresh(); > > txtUsername.Text = ""; > > txtPassword.Text = ""; > > con.Close(); > > lblInfo.Text = "Updated database > > with new user and password!"; > > > > }//End Inner exception > > con.Close(); > > } > > catch(Exception ex) > > { > > lblInfo.Text = "Errormessage: " + > > ex.Message; > > }//End Outer Exception > > > > } > > > > Anybody that could help me out explaining what I miss here? > > > > This is just a simple training case in order to understand the 'rhytm' > > of stored procedures. > > > > I use a sql 2000 database with three fields: > > > > UserID int(4) > > Username varchar(8) > > Password varchar(8) > > > > > > > > > > > ------=_Part_11191_21838794.1159878268494 > Content-Type: text/html; charset=ISO-8859-1 > Content-Transfer-Encoding: quoted-printable > X-Google-AttachSize: 9841 > > What is the error message you are getting?  I'm seeing two calls to &q= > uot;con.Close()" which if I'm not mistaken throws an exception.  = > You should only have to call it once(within the finally block is a good pla= > ce).<br> > <br> > <div><span class=3D"gmail_quote">On 10/3/06, <b class=3D"gmail_sendername">= > Krij</b> <<a href=3D"mailto:gsb58start.no">gsb58start.no</a>> wrote= > :</span> > <blockquote class=3D"gmail_quote" style=3D"PADDING-LEFT: 1ex; MARGIN: 0px 0= > px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid"><br>Hi!<br><br>As part of my sch= > ool training I'm experimenting with the following:<br><br>When trying to in= > sert new username and password into a sql database > <br>(SQL 2000) through C# code I did the following:<br><br>private void btn= > InsertWithStoredProcedure_Click(object sender,<br>System.EventArgs e)<br>&n= > bsp;     &n bsp;     &n bsp= > ;  {<br>    &nbsp ;     &n= > bsp;     &n bsp;      //Ou= > ter exception, nothing succeeded > <br>             &nbsp= > ;     &nbsp ;     try<br> = >               &nb= > sp;     &nb sp;   {<br>   = >               &nb= > sp;     &nb sp;     &nb sp;= >    SqlConnection con =3D new SqlConnection<br><br>("server=3Dlocalho st;database=3DUsers;uid=3Dgeir;pwd=3Dgeir");<br& gt;  = >               &nb= > sp;     &nb sp;     &nb sp;= >    =20 > con.Open();<br><br>  &nbsp ;     &nbsp ;&n= > bsp;     //Inner exception. ExecuteNonQuery failed<br>&= > nbsp;     & nbsp;     & nbs= > p;     &nbs p;     &nbs p;&= > nbsp;     try<br>    &nb sp; &n= > bsp;     &n bsp;     &n bsp= > ;     &nbsp ;      {<br>&n= > bsp;     &n bsp;     &n bsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;     &n bsp;     &n bsp= > ;  //Addressing stored procedure > <br>             &nbsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;     &n bsp;     &n bsp= > ;  SqlCommand cmdIns =3D new<br><br>SqlCommand("InsUnameAndPword &= > quot;,con);<br>   &nbsp ;     &nbsp ;= >               &nb= > sp;     &nb sp;     &nb sp;= >     cmdIns.CommandType =3D CommandType.StoredProcedure;<br><= > br>     & ;nbsp;     & ;nbsp;= >               &nb= > sp;     &nb sp;     &nb sp;= >  =20 > cmdIns.Parameters.Add("Username",SqlDbType.VarChar,8,txtUsername= > .Text.Trim());<br>   &n bsp;     &n b= > sp;     &nb sp;     &nb sp;= >               &nb= > sp;    cmdIns.Parameters.Add("Password",SqlDbType= > .VarChar,8,txtPassword.Text.Trim()); > <br><br>             &= > nbsp;     & nbsp;     & nbs= > p;     &nbs p;     &nbs p;&= > nbsp;  cmdIns.ExecuteNonQuery();<br>  & nbsp;  &nbs= > p;     &nbs p;     &nbs p;&= > nbsp;     & nbsp;      }<b= > r>     & nbsp;     & nbsp;&= > nbsp;     & nbsp;     & nbs= > p;      catch(SqlException ex)<br>  &nbs= > p;     &nbs p;     &nbs p;&= > nbsp;     & nbsp;     & nbs= > p;   {<br>    &nbsp ;    &= > nbsp;     & nbsp;     & nbs= > p;     &nbs p;     &nbs p;&= > nbsp;   =20 > lblInfo.Text =3D "ExecuteNonQuery failed because: n" +<br> = >               &nb= > sp;     &nb sp;     &nb sp;= >               &nb= > sp;     &nb sp;  "n" +<br> &nb= > sp;     &nb sp;     &nb sp;= >               &nb= > sp;     &nb sp;     &nb sp;= >          ex.Message;<br>  &nbsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;     &n bsp;     &n bsp= > ;   } > <br>             &nbsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;      finally<br>    &am p;nbsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;     &n bsp;     &n bsp= > ; {<br>    &nbsp ;     &nbsp ;&n= > bsp;     &n bsp;     &n bsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;  //Refresh datagrid and blank<br>out textboxes on form<br> &= > nbsp;     & nbsp;     & nbs= > p;     &nbs p;     &nbs p;&= > nbsp;     & nbsp;      dgU= > sers.Refresh > ();<br>    &nb sp;     &nb sp;&n= > bsp;     &n bsp;     &n bsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;  txtUsername.Text =3D "";<br>  &nbsp ; &n= > bsp;     &n bsp;     &n bsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;     &n bsp;   txtPassword.Text =3D &= > quot;";<br>   & ;nbsp;     & ;nbsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;     &n bsp;     &n bsp= > ;    con.Close();<br>   &nbs p;  &nb= > sp;     &nb sp;     &nb sp;= >               &nb= > sp;     &nb sp;=20 > lblInfo.Text =3D "Updated database with new user and password!";<= > br><br>    & ;nbsp;     & ;nbsp;&n= > bsp;     &n bsp;     &n bsp= > ;     &nbsp ; }//End Inner exception<br> &nbsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;     &n bsp;     &n bsp= > ;    con.Close();<br>   &nbs p;  &nb= > sp;     &nb sp;     &nb sp;= >     }<br>    &nbsp ;   &nb= > sp;     &nb sp;     &nb sp;= >   catch(Exception ex) > <br>             &nbsp= > ;     &nbsp ;     {<br> &n= > bsp;     &n bsp;     &n bsp= > ;     &nbsp ;     &nbsp ;&n= > bsp;    lblInfo.Text =3D "Errormessage: " + ex.Mes= > sage;<br>    & nbsp;     & nbsp;= >              }//End O= > uter Exception<br><br>   & amp;nbsp;    &nbsp= > ;      }<br><br>Anybody that could help me out exp= > laining what I miss here? > <br><br>This is just a simple training case in order to understand the 'rhy= > tm'<br>of stored procedures.<br><br>I use a sql 2000 database with three fi= > elds:<br><br>   & nbsp;    UserID int(4)<= > br>     & ;nbsp;  Username varchar(8)<br>&= > nbsp;     & nbsp; Password varchar(8) > <br><br><br> > ------=_Part_11191_21838794.1159878268494-- --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "C-Sharp (C#)" group. To post to this group, send email to C_Sharpgooglegroups.com To unsubscribe from this group, send email to C_Sharp-unsubscribegooglegroups.com For more options, visit this group at http://groups. google.com/group/C_Sharp -~----------~----~----~----~------~----~------~--~---

C-Sharp (C#) Group: Re: Understanding stored procedures
	2006-10-03 13:42:46
Your two parameters are declared as Output in the stored procedure. Remove the Output if you are trying to insert. Sincerely, Brad Gies ------------------------------------- NLM Software Southfield, MI, USA ------------------------------------- Life isn't measured by the number of breaths we take, but by the moments that take our breath away. > -----Original Message----- > From: C_Sharpgooglegroups.com [mailto:C_Sharpgooglegroups.com] On Behalf > Of Krij > Sent: Tuesday, October 03, 2006 8:43 AM > To: C-Sharp (C#) > Subject: C-Sharp (C#) Group: Re: Understanding stored procedures > > > Hi! > > Thanks for answering. > > I'm getting no error message, and no insert to the sql database. > However I get the message as defined in the inner finally statement. > > I was wondering if the stored procedure could be wrong. Here it is: > > CREATE PROCEDURE dbo.InsUnameAndPword > Username varchar(8) OUTPUT, > Password varchar(8) OUTPUT > AS > SET NOCOUNT ON > INSERT INTO tblUsers(Username,Password) > VALUES(Username,Password) > SET NOCOUNT OFF > GO > > As I'm new to these things I'd be grateful if you could correct me > here. > > I was also wondering if the third parameter, in the C# code, is > correct. What I'm aiming for is getting the values of the two textboxes > on the form for the > > cmdIns.Parameters.Add() thing. > > Thank you > Marty Thompson skrev: > > > What is the error message you are getting? I'm seeing two calls to " > > con.Close()" which if I'm not mistaken throws an exception. You should > only > > have to call it once(within the finally block is a good place). > > > > On 10/3/06, Krij <gsb58start.no> wrote: > > > > > > > > > Hi! > > > > > > As part of my school training I'm experimenting with the following: > > > > > > When trying to insert new username and password into a sql database > > > (SQL 2000) through C# code I did the following: > > > > > > private void btnInsertWithStoredProcedure_Click(object sender, > > > System.EventArgs e) > > > { > > > //Outer exception, nothing succeeded > > > try > > > { > > > SqlConnection con = new SqlConnection > > > > > > ("server=localhost;database=Users;uid=geir;pwd=geir&quo t;); > > > con.Open(); > > > > > > //Inner exception. ExecuteNonQuery failed > > > try > > > { > > > //Addressing stored procedure > > > SqlCommand cmdIns = new > > > > > > SqlCommand("InsUnameAndPword",con); > > > cmdIns.CommandType = > > > CommandType.StoredProcedure; > > > > > > > cmdIns.Parameters.Add("Username", > > > SqlDbType.VarChar,8,txtUsername.Text.Trim()); > > > > cmdIns.Parameters.Add("Password", > > > SqlDbType.VarChar,8,txtPassword.Text.Trim()); > > > > > > cmdIns.ExecuteNonQuery(); > > > } > > > catch(SqlException ex) > > > { > > > lblInfo.Text = "ExecuteNonQuery > > > failed because: n" + > > > "n" + > > > ex.Message; > > > } > > > finally > > > { > > > //Refresh datagrid and blank > > > out textboxes on form > > > dgUsers.Refresh(); > > > txtUsername.Text = ""; > > > txtPassword.Text = ""; > > > con.Close(); > > > lblInfo.Text = "Updated > database > > > with new user and password!"; > > > > > > }//End Inner exception > > > con.Close(); > > > } > > > catch(Exception ex) > > > { > > > lblInfo.Text = "Errormessage: " + > > > ex.Message; > > > }//End Outer Exception > > > > > > } > > > > > > Anybody that could help me out explaining what I miss here? > > > > > > This is just a simple training case in order to understand the 'rhytm' > > > of stored procedures. > > > > > > I use a sql 2000 database with three fields: > > > > > > UserID int(4) > > > Username varchar(8) > > > Password varchar(8) > > > > > > > > > > > > > > > > > ------=_Part_11191_21838794.1159878268494 > > Content-Type: text/html; charset=ISO-8859-1 > > Content-Transfer-Encoding: quoted-printable > > X-Google-AttachSize: 9841 > > > > What is the error message you are getting?  I'm seeing two calls to > &q= > > uot;con.Close()" which if I'm not mistaken throws an > exception.  = > > You should only have to call it once(within the finally block is a good > pla= > > ce).<br> > > <br> > > <div><span class=3D"gmail_quote">On 10/3/06, <b > class=3D"gmail_sendername">= > > Krij</b> <<a href=3D"mailto:gsb58start.no">gsb58start.no</a>> > wrote= > > :</span> > > <blockquote class=3D"gmail_quote" style=3D"PADDING-LEFT: 1ex; MARGIN: > 0px 0= > > px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid"><br>Hi!<br><br>As part of my > sch= > > ool training I'm experimenting with the following:<br><br>When trying to > in= > > sert new username and password into a sql database > > <br>(SQL 2000) through C# code I did the following:<br><br>private void > btn= > > InsertWithStoredProcedure_Click(object sender,<br>System.EventArgs > e)<br>&n= > > > bsp;     &n bsp;     &n bs > p= > > ;  > {<br>    &nbsp ;     &n= > > bsp;     &n bsp;      > //Ou= > > ter exception, nothing succeeded > > > <br>             &n bs > p= > > ;     &nbsp ;     > try<br> = > > >               &n > b= > > sp;     &nb sp;   > {<br>   = > > >               &n > b= > > > sp;     &nb sp;     &nb sp > ;= > >    SqlConnection con =3D new > SqlConnection<br><br>("server=3Dlocalho st;database=3DUsers;uid=3Dge ir > ;pwd=3Dgeir");<br>  = > > >               &n > b= > > > sp;     &nb sp;     &nb sp > ;= > >    =20 > > > con.Open();<br><br>  &nbsp ;     &nbsp ;& > n= > > bsp;     //Inner exception. ExecuteNonQuery > failed<br>&= > > > nbsp;     & nbsp;     & nb > s= > > > p;     &nbs p;     &nbs p; > &= > > nbsp;     > try<br>    &nb sp; &n= > > > bsp;     &n bsp;     &n bs > p= > > ;     &nbsp ;      > {<br>&n= > > > bsp;     &n bsp;     &n bs > p= > > > ;     &nbsp ;     &nbsp ;& > n= > > > bsp;     &n bsp;     &n bs > p= > > ;  //Addressing stored procedure > > > <br>             &n bs > p= > > > ;     &nbsp ;     &nbsp ;& > n= > > > bsp;     &n bsp;     &n bs > p= > > ;  SqlCommand cmdIns =3D > new<br><br>SqlCommand("InsUnameAndPword &= > > > quot;,con);<br>   &nbsp ;     &nb sp > ;= > > >               &n > b= > > > sp;     &nb sp;     &nb sp > ;= > >     cmdIns.CommandType =3D > CommandType.StoredProcedure;<br><= > > > br>     & ;nbsp;     & ;nb sp > ;= > > >               &n > b= > > > sp;     &nb sp;     &nb sp > ;= > >  =20 > > > cmdIns.Parameters.Add("Username",SqlDbType.VarChar,8,txtUsern am > e= > > > .Text.Trim());<br>   &n bsp;      &n > b= > > > sp;     &nb sp;     &nb sp > ;= > > >               &n > b= > > sp;    > cmdIns.Parameters.Add("Password",SqlDbType= > > .VarChar,8,txtPassword.Text.Trim()); > > > <br><br>            &nbs p; > &= > > > nbsp;     & nbsp;     & nb > s= > > > p;     &nbs p;     &nbs p; > &= > > nbsp;  > cmdIns.ExecuteNonQuery();<br>  & nbsp;  &nbs= > > > p;     &nbs p;     &nbs p; > &= > > nbsp;     & nbsp;      > }<b= > > > r>     & nbsp;     & nbs p; > &= > > > nbsp;     & nbsp;     & nb > s= > > p;      catch(SqlException > ex)<br>  &nbs= > > > p;     &nbs p;     &nbs p; > &= > > > nbsp;     & nbsp;     & nb > s= > > p;   > {<br>    &nbsp ;    &= > > > nbsp;     & nbsp;     & nb > s= > > > p;     &nbs p;     &nbs p; > &= > > nbsp;   =20 > > lblInfo.Text =3D "ExecuteNonQuery failed because: n" > +<br> = > > >               &n > b= > > > sp;     &nb sp;     &nb sp > ;= > > >               &n > b= > > sp;     &nb sp;  "n" > +<br> &nb= > > > sp;     &nb sp;     &nb sp > ;= > > >               &n > b= > > > sp;     &nb sp;     &nb sp > ;= > >          > ex.Message;<br>  &nbsp= > > > ;     &nbsp ;     &nbsp ;& > n= > > > bsp;     &n bsp;     &n bs > p= > > ;   } > > > <br>             &n bs > p= > > > ;     &nbsp ;     &nbsp ;& > n= > > bsp;      > finally<br>    &am p;nbsp= > > > ;     &nbsp ;     &nbsp ;& > n= > > > bsp;     &n bsp;     &n bs > p= > > ; > {<br>    &nbsp ;     &nbsp ;& n= > > > bsp;     &n bsp;     &n bs > p= > > > ;     &nbsp ;     &nbsp ;& > n= > > bsp;  //Refresh datagrid and blank<br>out textboxes on > form<br> &= > > > nbsp;     & nbsp;     & nb > s= > > > p;     &nbs p;     &nbs p; > &= > > nbsp;     & nbsp;      > dgU= > > sers.Refresh > > > ();<br>    &nb sp;     &nb sp ;& > n= > > > bsp;     &n bsp;     &n bs > p= > > > ;     &nbsp ;     &nbsp ;& > n= > > bsp;  txtUsername.Text =3D > "";<br>  &nbsp ; &n= > > > bsp;     &n bsp;     &n bs > p= > > > ;     &nbsp ;     &nbsp ;& > n= > > bsp;     &n bsp;   txtPassword.Text > =3D &= > > > quot;";<br>   & ;nbsp;     & ;n bs > p= > > > ;     &nbsp ;     &nbsp ;& > n= > > > bsp;     &n bsp;     &n bs > p= > > ;    > con.Close();<br>   &nbs p;  &nb= > > > sp;     &nb sp;     &nb sp > ;= > > >               &n > b= > > sp;     &nb sp;=20 > > lblInfo.Text =3D "Updated database with new user and > password!";<= > > > br><br>    & ;nbsp;     & ;nbsp ;& > n= > > > bsp;     &n bsp;     &n bs > p= > > ;     &nbsp ; }//End Inner > exception<br> &nbsp= > > > ;     &nbsp ;     &nbsp ;& > n= > > > bsp;     &n bsp;     &n bs > p= > > ;    > con.Close();<br>   &nbs p;  &nb= > > > sp;     &nb sp;     &nb sp > ;= > >     > }<br>    &nbsp ;   &nb= > > > sp;     &nb sp;     &nb sp > ;= > >   catch(Exception ex) > > > <br>             &n bs > p= > > ;     &nbsp ;     > {<br> &n= > > > bsp;     &n bsp;     &n bs > p= > > > ;     &nbsp ;     &nbsp ;& > n= > > bsp;    lblInfo.Text =3D "Errormessage: " + > ex.Mes= > > > sage;<br>    & nbsp;     & nb sp > ;= > >              > }//End O= > > uter > Exception<br><br>   & amp;nbsp;    &nbsp= > > ;      }<br><br>Anybody that could help me out > exp= > > laining what I miss here? > > <br><br>This is just a simple training case in order to understand the > 'rhy= > > tm'<br>of stored procedures.<br><br>I use a sql 2000 database with three > fi= > > elds:<br><br>   & nbsp;    UserID > int(4)<= > > br>     & ;nbsp;  Username > varchar(8)<br>&= > > nbsp;     & nbsp; Password varchar(8) > > <br><br><br> > > ------=_Part_11191_21838794.1159878268494-- > > > This e-mail or communication, including any attachments, is intended only for the person or entity to which it is addressed and may contain confidential and/or legally privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this message in error, please contact the sender immediately and permanently delete all copies, electronic or other, you may have or from your computer. Any disclosure, copying, distribution, reliance or use of the contents or information received in error is strictly prohibited. The foregoing applies even if this notice is imbedded in a message that is forwarded or attached. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "C-Sharp (C#)" group. To post to this group, send email to C_Sharpgooglegroups.com To unsubscribe from this group, send email to C_Sharp-unsubscribegooglegroups.com For more options, visit this group at http://groups. google.com/group/C_Sharp -~----------~----~----~----~------~----~------~--~---

C-Sharp (C#) Group: Re: Understanding stored procedures
	2006-10-03 14:05:56

Since you are assigning a value to lblInfo.Text in both the catch and finally blocks of the inner try-catch block, you would not see if an error occurred.  Take out the message from the finally block and see if you are getting an exception. It is important to understand that finally blocks will get executed regardless of whether or not an exception occurs. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "C-Sharp (C#)" group. To post to this group, send email to C_Sharpgooglegroups.com To unsubscribe from this group, send email to C_Sharp-unsubscribegooglegroups.com For more options, visit this group at http://groups.google.com/group/C_Sharp -~----------~----~----~----~------~----~------~--~---

[1-3]

about | contact Other archives ( Real Estate discussion Medical topics )