Insert data with a stored procedure

**Jim Mitchell** · Nov 22 '05, 02:55 AM

Re: Insert data with a stored procedure

Thanks for the tip on with cmd.Parameters. I had asked for this in a
previous post with no answer.

"William Ryan" <dotnetguru@com cast.nospam.net > wrote in message
news:uQ%23wlzkc DHA.1488@TK2MSF TNGP12.phx.gbl. ..[color=blue]
> Depends on how you define 'Beats'. With all due respe t, I don't think[/color]
you[color=blue]
> are comparing apples and oranges.
>
> There are a lot of other considerations, but to get the same[/color]
functionality,[color=blue]
> the syntax length and complexity is marginal. You could also use bound
> controls and with very little effort generate all of that logic with much
> less effort.
>
>
> You can certainly use Parameters in a less verbose syntax ie
> With cmd.Parameters
> .Add("@orgID", 0)
> .Add("@orgName" , txtorgOrgName.T ext)
>
> End With
>
> Since the concatenated string doesn't provide DataType information, you'd[/color]
be[color=blue]
> in the same boat.
> "Tavish Muldoon" <tmuldoon@splic ed.com> wrote in message
> news:e2470f35.0 309031033.7e99f 1d1@posting.goo gle.com...[color=green]
> > What a pain trying to insert data into a table from a stored proc. My
> > webform asks for 16 pieces of data - which then gets written to the
> > database.
> >
> > I found this easier than the crap below (after the ***********).
> >
> > This was eaiser:
> >
> > get a list of all the parameters you want to pass - put them in a
> > string:
> >
> > i.e.
> >
> > mysql = "'" & txtorgOrgName.T ext & "'," & ddorgCategory.S electedValue
> > & ",'" & txtorgAddress.T ext & "','" & _
> > txtorgCity.Text & "'," & ddorgProvince.S electedValue
> > & ",'" & txtorgPostal.Te xt & "','" & txtorgPhone1.Te xt & "','" &
> > txtorgPhoneX1.T ext & "','" & _
> > txtorgPhone2.Te xt & "','" & txtorgPhoneX2.T ext &
> > "','" & txtorgFax.Text & "','" & _
> > txtorgEmail.Tex t & "','" & Now.Date & "','" &
> > txtorgContact.T ext & "','" & _
> > txtorgTitle.Tex t & "','" & txtorgDept.Text & "'"
> >
> > Then put the directions into your command string
> >
> > Dim cmd As New SqlCommand("EXE CUTE sp_InsertOrgani zation " & mysql,
> > sqlAbComm)
> > sqlAbComm.Open( )
> >
> > Try
> > 'cmd.CommandTex t = "EXECUTE sp_InsertOrgani zation " &
> > mysql
> > cmd.CommandType = CommandType.Tex t
> > rows = cmd.ExecuteNonQ uery()
> >
> > lblInsert.Visib le = True
> > lblInsert.Text = "Record ADDED: " & rows
> >
> > Catch xcp As SqlException
> >
> > lblInsert.Visib le = True
> > lblInsert.Text = "Unexpected Exception:" & xcp.ToString
> >
> > End Try
> >
> > The stored proc looks like this:
> >
> > "EXECUTE sp_InsertOrgani zation 'WebCost',1,'50
> >[/color]
>[/color]
Havelot','Brigh ton',9,'q2r6t6' ,'555-8787','12','555-9901','22','555-5555','W[color=blue]
> eb@cost.com','9/3/2003','John[color=green]
> > Mckan','VP','Ta x'"
> >
> > The data after the stored proc gets inserted! Beats the crap below!
> >
> > Easier.
> >
> >
> > **************
> > 'insert organization data into table
> > Dim mysql As String
> > Dim rows As Integer
> >
> > 'CANNOT INSERT INTO AN ANTONUM / IDENTITY FIELD.
> > 'Dim pr1 As New SqlParameter
> > 'pr1.ParameterN ame = ("@orgId")
> > 'pr1.Direction = ParameterDirect ion.Input
> > 'pr1.DbType = DbType.Int16
> > 'pr1.Value = 0
> >
> > 'Dim pr2 As New SqlParameter
> > 'pr2.ParameterN ame = ("@orgName")
> > 'pr2.Direction = ParameterDirect ion.Input
> > 'pr2.DbType = DbType.String
> > 'pr2.Value = txtorgOrgName.T ext
> >
> > 'Dim pr3 As New SqlParameter
> > 'pr3.ParameterN ame = ("@catId")
> > 'pr3.Direction = ParameterDirect ion.Input
> > 'pr3.DbType = DbType.String
> > 'pr3.Value = ddorgCategory.D ataValueField
> >
> > 'Dim pr4 As New SqlParameter
> > 'pr4.ParameterN ame = ("@orgAddres s")
> > 'pr4.Direction = ParameterDirect ion.Input
> > 'pr4.DbType = DbType.String
> > 'pr4.Value = txtorgAddress.T ext
> >
> > 'Dim pr5 As New SqlParameter
> > 'pr5.ParameterN ame = ("@orgCity")
> > 'pr5.Direction = ParameterDirect ion.Input
> > 'pr5.DbType = DbType.String
> > 'pr5.Value = txtorgCity.Text
> >
> > 'Dim pr6 As New SqlParameter
> > 'pr6.ParameterN ame = ("@orgProvince" )
> > 'pr6.Direction = ParameterDirect ion.Input
> > 'pr6.DbType = DbType.Int16
> > 'pr6.Value = ddorgProvince.D ataValueField
> >
> > 'Dim pr7 As New SqlParameter
> > 'pr7.ParameterN ame = ("@orgPostalCod e")
> > 'pr7.Direction = ParameterDirect ion.Input
> > 'pr7.DbType = DbType.String
> > 'pr7.Value = txtorgPostal.Te xt
> >
> > 'Dim pr8 As New SqlParameter
> > 'pr8.ParameterN ame = ("@orgPhone1 ")
> > 'pr8.Direction = ParameterDirect ion.Input
> > 'pr8.DbType = DbType.String
> > 'pr8.Value = txtorgPhone1.Te xt
> >
> > 'Dim pr9 As New SqlParameter
> > 'pr9.ParameterN ame = ("@orgPhone1Ext ")
> > 'pr9.Direction = ParameterDirect ion.Input
> > 'pr9.DbType = DbType.String
> > 'pr9.Value = txtorgPhoneX1.T ext
> >
> > 'Dim pr10 As New SqlParameter
> > 'pr10.Parameter Name = ("@orgPhone2 ")
> > 'pr10.Direction = ParameterDirect ion.Input
> > 'pr10.DbType = DbType.String
> > 'pr10.Value = txtorgPhone2.Te xt
> >
> > 'Dim pr11 As New SqlParameter
> > 'pr11.Parameter Name = ("@orgPhone2Ext ")
> > 'pr11.Direction = ParameterDirect ion.Input
> > 'pr11.DbType = DbType.String
> > 'pr11.Value = txtorgPhoneX2.T ext
> >
> > 'Dim pr12 As New SqlParameter
> > 'pr12.Parameter Name = ("@orgFaxNumber ")
> > 'pr12.Direction = ParameterDirect ion.Input
> > 'pr12.DbType = DbType.String
> > 'pr12.Value = txtorgFax.Text
> >
> > 'Dim pr13 As New SqlParameter
> > 'pr13.Parameter Name = ("@orgEmail" )
> > 'pr13.Direction = ParameterDirect ion.Input
> > 'pr13.DbType = DbType.String
> > 'pr13.Value = txtorgEmail.Tex t
> >
> > 'Dim pr14 As New SqlParameter
> > 'pr14.Parameter Name = ("@orgDateRegis tered")
> > 'pr14.Direction = ParameterDirect ion.Input
> > 'pr14.DbType = DbType.Date
> > 'pr14.Value = Now.Date
> >
> > 'Dim pr15 As New SqlParameter
> > 'pr15.Parameter Name = ("@orgContactPe rson")
> > 'pr15.Direction = ParameterDirect ion.Input
> > 'pr15.DbType = DbType.String
> > 'pr15.Value = txtorgContact.T ext
> >
> > 'Dim pr16 As New SqlParameter
> > 'pr16.Parameter Name = ("@orgTitle" )
> > 'pr16.Direction = ParameterDirect ion.Input
> > 'pr16.DbType = DbType.String
> > 'pr16.Value = txtorgTitle.Tex t
> >
> > 'Dim pr17 As New SqlParameter
> > 'pr17.Parameter Name = ("@orgDept")
> > 'pr17.Direction = ParameterDirect ion.Input
> > 'pr17.DbType = DbType.String
> > 'pr17.Value = txtorgDept.Text
> >
> > ''Dim insertedrows As SqlParameter
> > ''insertedrows. ParameterName = ("@@IDENTITY ")
> > ''insertedrows. Direction = ParameterDirect ion.ReturnValue
> > ''insertedrows. DbType = DbType.Int16
> > ''rows = insertedrows.Va lue
> >
> >
> > ''cmd.Parameter s.Add(pr1)
> > 'cmd.Parameters .Add(pr2)
> > 'cmd.Parameters .Add(pr3)
> > 'cmd.Parameters .Add(pr4)
> > 'cmd.Parameters .Add(pr5)
> > 'cmd.Parameters .Add(pr6)
> > 'cmd.Parameters .Add(pr7)
> > 'cmd.Parameters .Add(pr8)
> > 'cmd.Parameters .Add(pr9)
> > 'cmd.Parameters .Add(pr10)
> > 'cmd.Parameters .Add(pr11)
> > 'cmd.Parameters .Add(pr12)
> > 'cmd.Parameters .Add(pr13)
> > 'cmd.Parameters .Add(pr14)
> > 'cmd.Parameters .Add(pr15)
> > 'cmd.Parameters .Add(pr16)
> > 'cmd.Parameters .Add(pr17)
> >
> > Dim cmd As New SqlCommand(" sp_InsertOrgani zation ",
> > sqlAbComm)
> > cmd.CommandType =CommandType.St oredProcedure
> > rows = cmd.ExecuteNonQ uery()
> >
> > This gave me grief - always gettinga stack error about the string
> > being incorrect. PAIN[/color]
>
>[/color]

**Jim Mitchell** · Nov 22 '05, 02:55 AM

Re: Insert data with a stored procedure

Sure, but what happens when you are passing input from user fields to the
stored procedure and the user inputs special characters like [']. Are you
going to do a replace("'","'' '") every time you want to pass the data? I
found it a pain to deal with checking to make sure "Brian D'Amico" was not
passed to my stored proceedure every time it was called.

"Tavish Muldoon" <tmuldoon@splic ed.com> wrote in message
news:e2470f35.0 309031033.7e99f 1d1@posting.goo gle.com...[color=blue]
> What a pain trying to insert data into a table from a stored proc. My
> webform asks for 16 pieces of data - which then gets written to the
> database.
>
> I found this easier than the crap below (after the ***********).
>
> This was eaiser:
>
> get a list of all the parameters you want to pass - put them in a
> string:
>
> i.e.
>
> mysql = "'" & txtorgOrgName.T ext & "'," & ddorgCategory.S electedValue
> & ",'" & txtorgAddress.T ext & "','" & _
> txtorgCity.Text & "'," & ddorgProvince.S electedValue
> & ",'" & txtorgPostal.Te xt & "','" & txtorgPhone1.Te xt & "','" &
> txtorgPhoneX1.T ext & "','" & _
> txtorgPhone2.Te xt & "','" & txtorgPhoneX2.T ext &
> "','" & txtorgFax.Text & "','" & _
> txtorgEmail.Tex t & "','" & Now.Date & "','" &
> txtorgContact.T ext & "','" & _
> txtorgTitle.Tex t & "','" & txtorgDept.Text & "'"
>
> Then put the directions into your command string
>
> Dim cmd As New SqlCommand("EXE CUTE sp_InsertOrgani zation " & mysql,
> sqlAbComm)
> sqlAbComm.Open( )
>
> Try
> 'cmd.CommandTex t = "EXECUTE sp_InsertOrgani zation " &
> mysql
> cmd.CommandType = CommandType.Tex t
> rows = cmd.ExecuteNonQ uery()
>
> lblInsert.Visib le = True
> lblInsert.Text = "Record ADDED: " & rows
>
> Catch xcp As SqlException
>
> lblInsert.Visib le = True
> lblInsert.Text = "Unexpected Exception:" & xcp.ToString
>
> End Try
>
> The stored proc looks like this:
>
> "EXECUTE sp_InsertOrgani zation 'WebCost',1,'50
>[/color]
Havelot','Brigh ton',9,'q2r6t6' ,'555-8787','12','555-9901','22','555-5555','W
eb@cost.com','9/3/2003','John[color=blue]
> Mckan','VP','Ta x'"
>
> The data after the stored proc gets inserted! Beats the crap below!
>
> Easier.
>
>
> **************
> 'insert organization data into table
> Dim mysql As String
> Dim rows As Integer
>
> 'CANNOT INSERT INTO AN ANTONUM / IDENTITY FIELD.
> 'Dim pr1 As New SqlParameter
> 'pr1.ParameterN ame = ("@orgId")
> 'pr1.Direction = ParameterDirect ion.Input
> 'pr1.DbType = DbType.Int16
> 'pr1.Value = 0
>
> 'Dim pr2 As New SqlParameter
> 'pr2.ParameterN ame = ("@orgName")
> 'pr2.Direction = ParameterDirect ion.Input
> 'pr2.DbType = DbType.String
> 'pr2.Value = txtorgOrgName.T ext
>
> 'Dim pr3 As New SqlParameter
> 'pr3.ParameterN ame = ("@catId")
> 'pr3.Direction = ParameterDirect ion.Input
> 'pr3.DbType = DbType.String
> 'pr3.Value = ddorgCategory.D ataValueField
>
> 'Dim pr4 As New SqlParameter
> 'pr4.ParameterN ame = ("@orgAddres s")
> 'pr4.Direction = ParameterDirect ion.Input
> 'pr4.DbType = DbType.String
> 'pr4.Value = txtorgAddress.T ext
>
> 'Dim pr5 As New SqlParameter
> 'pr5.ParameterN ame = ("@orgCity")
> 'pr5.Direction = ParameterDirect ion.Input
> 'pr5.DbType = DbType.String
> 'pr5.Value = txtorgCity.Text
>
> 'Dim pr6 As New SqlParameter
> 'pr6.ParameterN ame = ("@orgProvince" )
> 'pr6.Direction = ParameterDirect ion.Input
> 'pr6.DbType = DbType.Int16
> 'pr6.Value = ddorgProvince.D ataValueField
>
> 'Dim pr7 As New SqlParameter
> 'pr7.ParameterN ame = ("@orgPostalCod e")
> 'pr7.Direction = ParameterDirect ion.Input
> 'pr7.DbType = DbType.String
> 'pr7.Value = txtorgPostal.Te xt
>
> 'Dim pr8 As New SqlParameter
> 'pr8.ParameterN ame = ("@orgPhone1 ")
> 'pr8.Direction = ParameterDirect ion.Input
> 'pr8.DbType = DbType.String
> 'pr8.Value = txtorgPhone1.Te xt
>
> 'Dim pr9 As New SqlParameter
> 'pr9.ParameterN ame = ("@orgPhone1Ext ")
> 'pr9.Direction = ParameterDirect ion.Input
> 'pr9.DbType = DbType.String
> 'pr9.Value = txtorgPhoneX1.T ext
>
> 'Dim pr10 As New SqlParameter
> 'pr10.Parameter Name = ("@orgPhone2 ")
> 'pr10.Direction = ParameterDirect ion.Input
> 'pr10.DbType = DbType.String
> 'pr10.Value = txtorgPhone2.Te xt
>
> 'Dim pr11 As New SqlParameter
> 'pr11.Parameter Name = ("@orgPhone2Ext ")
> 'pr11.Direction = ParameterDirect ion.Input
> 'pr11.DbType = DbType.String
> 'pr11.Value = txtorgPhoneX2.T ext
>
> 'Dim pr12 As New SqlParameter
> 'pr12.Parameter Name = ("@orgFaxNumber ")
> 'pr12.Direction = ParameterDirect ion.Input
> 'pr12.DbType = DbType.String
> 'pr12.Value = txtorgFax.Text
>
> 'Dim pr13 As New SqlParameter
> 'pr13.Parameter Name = ("@orgEmail" )
> 'pr13.Direction = ParameterDirect ion.Input
> 'pr13.DbType = DbType.String
> 'pr13.Value = txtorgEmail.Tex t
>
> 'Dim pr14 As New SqlParameter
> 'pr14.Parameter Name = ("@orgDateRegis tered")
> 'pr14.Direction = ParameterDirect ion.Input
> 'pr14.DbType = DbType.Date
> 'pr14.Value = Now.Date
>
> 'Dim pr15 As New SqlParameter
> 'pr15.Parameter Name = ("@orgContactPe rson")
> 'pr15.Direction = ParameterDirect ion.Input
> 'pr15.DbType = DbType.String
> 'pr15.Value = txtorgContact.T ext
>
> 'Dim pr16 As New SqlParameter
> 'pr16.Parameter Name = ("@orgTitle" )
> 'pr16.Direction = ParameterDirect ion.Input
> 'pr16.DbType = DbType.String
> 'pr16.Value = txtorgTitle.Tex t
>
> 'Dim pr17 As New SqlParameter
> 'pr17.Parameter Name = ("@orgDept")
> 'pr17.Direction = ParameterDirect ion.Input
> 'pr17.DbType = DbType.String
> 'pr17.Value = txtorgDept.Text
>
> ''Dim insertedrows As SqlParameter
> ''insertedrows. ParameterName = ("@@IDENTITY ")
> ''insertedrows. Direction = ParameterDirect ion.ReturnValue
> ''insertedrows. DbType = DbType.Int16
> ''rows = insertedrows.Va lue
>
>
> ''cmd.Parameter s.Add(pr1)
> 'cmd.Parameters .Add(pr2)
> 'cmd.Parameters .Add(pr3)
> 'cmd.Parameters .Add(pr4)
> 'cmd.Parameters .Add(pr5)
> 'cmd.Parameters .Add(pr6)
> 'cmd.Parameters .Add(pr7)
> 'cmd.Parameters .Add(pr8)
> 'cmd.Parameters .Add(pr9)
> 'cmd.Parameters .Add(pr10)
> 'cmd.Parameters .Add(pr11)
> 'cmd.Parameters .Add(pr12)
> 'cmd.Parameters .Add(pr13)
> 'cmd.Parameters .Add(pr14)
> 'cmd.Parameters .Add(pr15)
> 'cmd.Parameters .Add(pr16)
> 'cmd.Parameters .Add(pr17)
>
> Dim cmd As New SqlCommand(" sp_InsertOrgani zation ",
> sqlAbComm)
> cmd.CommandType =CommandType.St oredProcedure
> rows = cmd.ExecuteNonQ uery()
>
> This gave me grief - always gettinga stack error about the string
> being incorrect. PAIN[/color]

**Jim Mitchell** · Nov 22 '05, 02:55 AM

Re: Insert data with a stored procedure

Thanks for the tip on with cmd.Parameters. I had asked for this in a
previous post with no answer.

"William Ryan" <dotnetguru@com cast.nospam.net > wrote in message
news:uQ%23wlzkc DHA.1488@TK2MSF TNGP12.phx.gbl. ..[color=blue]
> Depends on how you define 'Beats'. With all due respe t, I don't think[/color]
you[color=blue]
> are comparing apples and oranges.
>
> There are a lot of other considerations, but to get the same[/color]
functionality,[color=blue]
> the syntax length and complexity is marginal. You could also use bound
> controls and with very little effort generate all of that logic with much
> less effort.
>
>
> You can certainly use Parameters in a less verbose syntax ie
> With cmd.Parameters
> .Add("@orgID", 0)
> .Add("@orgName" , txtorgOrgName.T ext)
>
> End With
>
> Since the concatenated string doesn't provide DataType information, you'd[/color]
be[color=blue]
> in the same boat.
> "Tavish Muldoon" <tmuldoon@splic ed.com> wrote in message
> news:e2470f35.0 309031033.7e99f 1d1@posting.goo gle.com...[color=green]
> > What a pain trying to insert data into a table from a stored proc. My
> > webform asks for 16 pieces of data - which then gets written to the
> > database.
> >
> > I found this easier than the crap below (after the ***********).
> >
> > This was eaiser:
> >
> > get a list of all the parameters you want to pass - put them in a
> > string:
> >
> > i.e.
> >
> > mysql = "'" & txtorgOrgName.T ext & "'," & ddorgCategory.S electedValue
> > & ",'" & txtorgAddress.T ext & "','" & _
> > txtorgCity.Text & "'," & ddorgProvince.S electedValue
> > & ",'" & txtorgPostal.Te xt & "','" & txtorgPhone1.Te xt & "','" &
> > txtorgPhoneX1.T ext & "','" & _
> > txtorgPhone2.Te xt & "','" & txtorgPhoneX2.T ext &
> > "','" & txtorgFax.Text & "','" & _
> > txtorgEmail.Tex t & "','" & Now.Date & "','" &
> > txtorgContact.T ext & "','" & _
> > txtorgTitle.Tex t & "','" & txtorgDept.Text & "'"
> >
> > Then put the directions into your command string
> >
> > Dim cmd As New SqlCommand("EXE CUTE sp_InsertOrgani zation " & mysql,
> > sqlAbComm)
> > sqlAbComm.Open( )
> >
> > Try
> > 'cmd.CommandTex t = "EXECUTE sp_InsertOrgani zation " &
> > mysql
> > cmd.CommandType = CommandType.Tex t
> > rows = cmd.ExecuteNonQ uery()
> >
> > lblInsert.Visib le = True
> > lblInsert.Text = "Record ADDED: " & rows
> >
> > Catch xcp As SqlException
> >
> > lblInsert.Visib le = True
> > lblInsert.Text = "Unexpected Exception:" & xcp.ToString
> >
> > End Try
> >
> > The stored proc looks like this:
> >
> > "EXECUTE sp_InsertOrgani zation 'WebCost',1,'50
> >[/color]
>[/color]
Havelot','Brigh ton',9,'q2r6t6' ,'555-8787','12','555-9901','22','555-5555','W[color=blue]
> eb@cost.com','9/3/2003','John[color=green]
> > Mckan','VP','Ta x'"
> >
> > The data after the stored proc gets inserted! Beats the crap below!
> >
> > Easier.
> >
> >
> > **************
> > 'insert organization data into table
> > Dim mysql As String
> > Dim rows As Integer
> >
> > 'CANNOT INSERT INTO AN ANTONUM / IDENTITY FIELD.
> > 'Dim pr1 As New SqlParameter
> > 'pr1.ParameterN ame = ("@orgId")
> > 'pr1.Direction = ParameterDirect ion.Input
> > 'pr1.DbType = DbType.Int16
> > 'pr1.Value = 0
> >
> > 'Dim pr2 As New SqlParameter
> > 'pr2.ParameterN ame = ("@orgName")
> > 'pr2.Direction = ParameterDirect ion.Input
> > 'pr2.DbType = DbType.String
> > 'pr2.Value = txtorgOrgName.T ext
> >
> > 'Dim pr3 As New SqlParameter
> > 'pr3.ParameterN ame = ("@catId")
> > 'pr3.Direction = ParameterDirect ion.Input
> > 'pr3.DbType = DbType.String
> > 'pr3.Value = ddorgCategory.D ataValueField
> >
> > 'Dim pr4 As New SqlParameter
> > 'pr4.ParameterN ame = ("@orgAddres s")
> > 'pr4.Direction = ParameterDirect ion.Input
> > 'pr4.DbType = DbType.String
> > 'pr4.Value = txtorgAddress.T ext
> >
> > 'Dim pr5 As New SqlParameter
> > 'pr5.ParameterN ame = ("@orgCity")
> > 'pr5.Direction = ParameterDirect ion.Input
> > 'pr5.DbType = DbType.String
> > 'pr5.Value = txtorgCity.Text
> >
> > 'Dim pr6 As New SqlParameter
> > 'pr6.ParameterN ame = ("@orgProvince" )
> > 'pr6.Direction = ParameterDirect ion.Input
> > 'pr6.DbType = DbType.Int16
> > 'pr6.Value = ddorgProvince.D ataValueField
> >
> > 'Dim pr7 As New SqlParameter
> > 'pr7.ParameterN ame = ("@orgPostalCod e")
> > 'pr7.Direction = ParameterDirect ion.Input
> > 'pr7.DbType = DbType.String
> > 'pr7.Value = txtorgPostal.Te xt
> >
> > 'Dim pr8 As New SqlParameter
> > 'pr8.ParameterN ame = ("@orgPhone1 ")
> > 'pr8.Direction = ParameterDirect ion.Input
> > 'pr8.DbType = DbType.String
> > 'pr8.Value = txtorgPhone1.Te xt
> >
> > 'Dim pr9 As New SqlParameter
> > 'pr9.ParameterN ame = ("@orgPhone1Ext ")
> > 'pr9.Direction = ParameterDirect ion.Input
> > 'pr9.DbType = DbType.String
> > 'pr9.Value = txtorgPhoneX1.T ext
> >
> > 'Dim pr10 As New SqlParameter
> > 'pr10.Parameter Name = ("@orgPhone2 ")
> > 'pr10.Direction = ParameterDirect ion.Input
> > 'pr10.DbType = DbType.String
> > 'pr10.Value = txtorgPhone2.Te xt
> >
> > 'Dim pr11 As New SqlParameter
> > 'pr11.Parameter Name = ("@orgPhone2Ext ")
> > 'pr11.Direction = ParameterDirect ion.Input
> > 'pr11.DbType = DbType.String
> > 'pr11.Value = txtorgPhoneX2.T ext
> >
> > 'Dim pr12 As New SqlParameter
> > 'pr12.Parameter Name = ("@orgFaxNumber ")
> > 'pr12.Direction = ParameterDirect ion.Input
> > 'pr12.DbType = DbType.String
> > 'pr12.Value = txtorgFax.Text
> >
> > 'Dim pr13 As New SqlParameter
> > 'pr13.Parameter Name = ("@orgEmail" )
> > 'pr13.Direction = ParameterDirect ion.Input
> > 'pr13.DbType = DbType.String
> > 'pr13.Value = txtorgEmail.Tex t
> >
> > 'Dim pr14 As New SqlParameter
> > 'pr14.Parameter Name = ("@orgDateRegis tered")
> > 'pr14.Direction = ParameterDirect ion.Input
> > 'pr14.DbType = DbType.Date
> > 'pr14.Value = Now.Date
> >
> > 'Dim pr15 As New SqlParameter
> > 'pr15.Parameter Name = ("@orgContactPe rson")
> > 'pr15.Direction = ParameterDirect ion.Input
> > 'pr15.DbType = DbType.String
> > 'pr15.Value = txtorgContact.T ext
> >
> > 'Dim pr16 As New SqlParameter
> > 'pr16.Parameter Name = ("@orgTitle" )
> > 'pr16.Direction = ParameterDirect ion.Input
> > 'pr16.DbType = DbType.String
> > 'pr16.Value = txtorgTitle.Tex t
> >
> > 'Dim pr17 As New SqlParameter
> > 'pr17.Parameter Name = ("@orgDept")
> > 'pr17.Direction = ParameterDirect ion.Input
> > 'pr17.DbType = DbType.String
> > 'pr17.Value = txtorgDept.Text
> >
> > ''Dim insertedrows As SqlParameter
> > ''insertedrows. ParameterName = ("@@IDENTITY ")
> > ''insertedrows. Direction = ParameterDirect ion.ReturnValue
> > ''insertedrows. DbType = DbType.Int16
> > ''rows = insertedrows.Va lue
> >
> >
> > ''cmd.Parameter s.Add(pr1)
> > 'cmd.Parameters .Add(pr2)
> > 'cmd.Parameters .Add(pr3)
> > 'cmd.Parameters .Add(pr4)
> > 'cmd.Parameters .Add(pr5)
> > 'cmd.Parameters .Add(pr6)
> > 'cmd.Parameters .Add(pr7)
> > 'cmd.Parameters .Add(pr8)
> > 'cmd.Parameters .Add(pr9)
> > 'cmd.Parameters .Add(pr10)
> > 'cmd.Parameters .Add(pr11)
> > 'cmd.Parameters .Add(pr12)
> > 'cmd.Parameters .Add(pr13)
> > 'cmd.Parameters .Add(pr14)
> > 'cmd.Parameters .Add(pr15)
> > 'cmd.Parameters .Add(pr16)
> > 'cmd.Parameters .Add(pr17)
> >
> > Dim cmd As New SqlCommand(" sp_InsertOrgani zation ",
> > sqlAbComm)
> > cmd.CommandType =CommandType.St oredProcedure
> > rows = cmd.ExecuteNonQ uery()
> >
> > This gave me grief - always gettinga stack error about the string
> > being incorrect. PAIN[/color]
>
>[/color]

**Jim Mitchell** · Nov 22 '05, 02:55 AM

Re: Insert data with a stored procedure

Sure, but what happens when you are passing input from user fields to the
stored procedure and the user inputs special characters like [']. Are you
going to do a replace("'","'' '") every time you want to pass the data? I
found it a pain to deal with checking to make sure "Brian D'Amico" was not
passed to my stored proceedure every time it was called.

"Tavish Muldoon" <tmuldoon@splic ed.com> wrote in message
news:e2470f35.0 309031033.7e99f 1d1@posting.goo gle.com...[color=blue]
> What a pain trying to insert data into a table from a stored proc. My
> webform asks for 16 pieces of data - which then gets written to the
> database.
>
> I found this easier than the crap below (after the ***********).
>
> This was eaiser:
>
> get a list of all the parameters you want to pass - put them in a
> string:
>
> i.e.
>
> mysql = "'" & txtorgOrgName.T ext & "'," & ddorgCategory.S electedValue
> & ",'" & txtorgAddress.T ext & "','" & _
> txtorgCity.Text & "'," & ddorgProvince.S electedValue
> & ",'" & txtorgPostal.Te xt & "','" & txtorgPhone1.Te xt & "','" &
> txtorgPhoneX1.T ext & "','" & _
> txtorgPhone2.Te xt & "','" & txtorgPhoneX2.T ext &
> "','" & txtorgFax.Text & "','" & _
> txtorgEmail.Tex t & "','" & Now.Date & "','" &
> txtorgContact.T ext & "','" & _
> txtorgTitle.Tex t & "','" & txtorgDept.Text & "'"
>
> Then put the directions into your command string
>
> Dim cmd As New SqlCommand("EXE CUTE sp_InsertOrgani zation " & mysql,
> sqlAbComm)
> sqlAbComm.Open( )
>
> Try
> 'cmd.CommandTex t = "EXECUTE sp_InsertOrgani zation " &
> mysql
> cmd.CommandType = CommandType.Tex t
> rows = cmd.ExecuteNonQ uery()
>
> lblInsert.Visib le = True
> lblInsert.Text = "Record ADDED: " & rows
>
> Catch xcp As SqlException
>
> lblInsert.Visib le = True
> lblInsert.Text = "Unexpected Exception:" & xcp.ToString
>
> End Try
>
> The stored proc looks like this:
>
> "EXECUTE sp_InsertOrgani zation 'WebCost',1,'50
>[/color]
Havelot','Brigh ton',9,'q2r6t6' ,'555-8787','12','555-9901','22','555-5555','W
eb@cost.com','9/3/2003','John[color=blue]
> Mckan','VP','Ta x'"
>
> The data after the stored proc gets inserted! Beats the crap below!
>
> Easier.
>
>
> **************
> 'insert organization data into table
> Dim mysql As String
> Dim rows As Integer
>
> 'CANNOT INSERT INTO AN ANTONUM / IDENTITY FIELD.
> 'Dim pr1 As New SqlParameter
> 'pr1.ParameterN ame = ("@orgId")
> 'pr1.Direction = ParameterDirect ion.Input
> 'pr1.DbType = DbType.Int16
> 'pr1.Value = 0
>
> 'Dim pr2 As New SqlParameter
> 'pr2.ParameterN ame = ("@orgName")
> 'pr2.Direction = ParameterDirect ion.Input
> 'pr2.DbType = DbType.String
> 'pr2.Value = txtorgOrgName.T ext
>
> 'Dim pr3 As New SqlParameter
> 'pr3.ParameterN ame = ("@catId")
> 'pr3.Direction = ParameterDirect ion.Input
> 'pr3.DbType = DbType.String
> 'pr3.Value = ddorgCategory.D ataValueField
>
> 'Dim pr4 As New SqlParameter
> 'pr4.ParameterN ame = ("@orgAddres s")
> 'pr4.Direction = ParameterDirect ion.Input
> 'pr4.DbType = DbType.String
> 'pr4.Value = txtorgAddress.T ext
>
> 'Dim pr5 As New SqlParameter
> 'pr5.ParameterN ame = ("@orgCity")
> 'pr5.Direction = ParameterDirect ion.Input
> 'pr5.DbType = DbType.String
> 'pr5.Value = txtorgCity.Text
>
> 'Dim pr6 As New SqlParameter
> 'pr6.ParameterN ame = ("@orgProvince" )
> 'pr6.Direction = ParameterDirect ion.Input
> 'pr6.DbType = DbType.Int16
> 'pr6.Value = ddorgProvince.D ataValueField
>
> 'Dim pr7 As New SqlParameter
> 'pr7.ParameterN ame = ("@orgPostalCod e")
> 'pr7.Direction = ParameterDirect ion.Input
> 'pr7.DbType = DbType.String
> 'pr7.Value = txtorgPostal.Te xt
>
> 'Dim pr8 As New SqlParameter
> 'pr8.ParameterN ame = ("@orgPhone1 ")
> 'pr8.Direction = ParameterDirect ion.Input
> 'pr8.DbType = DbType.String
> 'pr8.Value = txtorgPhone1.Te xt
>
> 'Dim pr9 As New SqlParameter
> 'pr9.ParameterN ame = ("@orgPhone1Ext ")
> 'pr9.Direction = ParameterDirect ion.Input
> 'pr9.DbType = DbType.String
> 'pr9.Value = txtorgPhoneX1.T ext
>
> 'Dim pr10 As New SqlParameter
> 'pr10.Parameter Name = ("@orgPhone2 ")
> 'pr10.Direction = ParameterDirect ion.Input
> 'pr10.DbType = DbType.String
> 'pr10.Value = txtorgPhone2.Te xt
>
> 'Dim pr11 As New SqlParameter
> 'pr11.Parameter Name = ("@orgPhone2Ext ")
> 'pr11.Direction = ParameterDirect ion.Input
> 'pr11.DbType = DbType.String
> 'pr11.Value = txtorgPhoneX2.T ext
>
> 'Dim pr12 As New SqlParameter
> 'pr12.Parameter Name = ("@orgFaxNumber ")
> 'pr12.Direction = ParameterDirect ion.Input
> 'pr12.DbType = DbType.String
> 'pr12.Value = txtorgFax.Text
>
> 'Dim pr13 As New SqlParameter
> 'pr13.Parameter Name = ("@orgEmail" )
> 'pr13.Direction = ParameterDirect ion.Input
> 'pr13.DbType = DbType.String
> 'pr13.Value = txtorgEmail.Tex t
>
> 'Dim pr14 As New SqlParameter
> 'pr14.Parameter Name = ("@orgDateRegis tered")
> 'pr14.Direction = ParameterDirect ion.Input
> 'pr14.DbType = DbType.Date
> 'pr14.Value = Now.Date
>
> 'Dim pr15 As New SqlParameter
> 'pr15.Parameter Name = ("@orgContactPe rson")
> 'pr15.Direction = ParameterDirect ion.Input
> 'pr15.DbType = DbType.String
> 'pr15.Value = txtorgContact.T ext
>
> 'Dim pr16 As New SqlParameter
> 'pr16.Parameter Name = ("@orgTitle" )
> 'pr16.Direction = ParameterDirect ion.Input
> 'pr16.DbType = DbType.String
> 'pr16.Value = txtorgTitle.Tex t
>
> 'Dim pr17 As New SqlParameter
> 'pr17.Parameter Name = ("@orgDept")
> 'pr17.Direction = ParameterDirect ion.Input
> 'pr17.DbType = DbType.String
> 'pr17.Value = txtorgDept.Text
>
> ''Dim insertedrows As SqlParameter
> ''insertedrows. ParameterName = ("@@IDENTITY ")
> ''insertedrows. Direction = ParameterDirect ion.ReturnValue
> ''insertedrows. DbType = DbType.Int16
> ''rows = insertedrows.Va lue
>
>
> ''cmd.Parameter s.Add(pr1)
> 'cmd.Parameters .Add(pr2)
> 'cmd.Parameters .Add(pr3)
> 'cmd.Parameters .Add(pr4)
> 'cmd.Parameters .Add(pr5)
> 'cmd.Parameters .Add(pr6)
> 'cmd.Parameters .Add(pr7)
> 'cmd.Parameters .Add(pr8)
> 'cmd.Parameters .Add(pr9)
> 'cmd.Parameters .Add(pr10)
> 'cmd.Parameters .Add(pr11)
> 'cmd.Parameters .Add(pr12)
> 'cmd.Parameters .Add(pr13)
> 'cmd.Parameters .Add(pr14)
> 'cmd.Parameters .Add(pr15)
> 'cmd.Parameters .Add(pr16)
> 'cmd.Parameters .Add(pr17)
>
> Dim cmd As New SqlCommand(" sp_InsertOrgani zation ",
> sqlAbComm)
> cmd.CommandType =CommandType.St oredProcedure
> rows = cmd.ExecuteNonQ uery()
>
> This gave me grief - always gettinga stack error about the string
> being incorrect. PAIN[/color]

Insert data with a stored procedure

Insert data with a stored procedure

Comment

Comment

Comment

Comment