Insert data with a stored procedure

[Tavish Muldoon]

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.Text & "'," & ddorgCategory.SelectedValue
& ",'" & txtorgAddress.Text & "','" & _
txtorgCity.Text & "'," & ddorgProvince.SelectedValue
& ",'" & txtorgPostal.Text & "','" & txtorgPhone1.Text & "','" &
txtorgPhoneX1.Text & "','" & _
txtorgPhone2.Text & "','" & txtorgPhoneX2.Text &
"','" & txtorgFax.Text & "','" & _
txtorgEmail.Text & "','" & Now.Date & "','" &
txtorgContact.Text & "','" & _
txtorgTitle.Text & "','" & txtorgDept.Text & "'"

Then put the directions into your command string

Dim cmd As New SqlCommand("EXECUTE sp_InsertOrganization " & mysql,
sqlAbComm)
sqlAbComm.Open()

Try
'cmd.CommandText = "EXECUTE sp_InsertOrganization " &
mysql
cmd.CommandType = CommandType.Text
rows = cmd.ExecuteNonQuery()

lblInsert.Visible = True
lblInsert.Text = "Record ADDED: " & rows

Catch xcp As SqlException

lblInsert.Visible = True
lblInsert.Text = "Unexpected Exception:" & xcp.ToString

End Try

The stored proc looks like this:

"EXECUTE sp_InsertOrganization 'WebCost',1,'50
Havelot','Brighton',9,'q2r6t6','555-8787','12','555-9901','22','555-5555','(e-mail address removed)','9/3/2003','John
Mckan','VP','Tax'"

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.ParameterName = ("@orgId")
'pr1.Direction = ParameterDirection.Input
'pr1.DbType = DbType.Int16
'pr1.Value = 0

'Dim pr2 As New SqlParameter
'pr2.ParameterName = ("@orgName")
'pr2.Direction = ParameterDirection.Input
'pr2.DbType = DbType.String
'pr2.Value = txtorgOrgName.Text

'Dim pr3 As New SqlParameter
'pr3.ParameterName = ("@catId")
'pr3.Direction = ParameterDirection.Input
'pr3.DbType = DbType.String
'pr3.Value = ddorgCategory.DataValueField

'Dim pr4 As New SqlParameter
'pr4.ParameterName = ("@orgAddress")
'pr4.Direction = ParameterDirection.Input
'pr4.DbType = DbType.String
'pr4.Value = txtorgAddress.Text

'Dim pr5 As New SqlParameter
'pr5.ParameterName = ("@orgCity")
'pr5.Direction = ParameterDirection.Input
'pr5.DbType = DbType.String
'pr5.Value = txtorgCity.Text

'Dim pr6 As New SqlParameter
'pr6.ParameterName = ("@orgProvince")
'pr6.Direction = ParameterDirection.Input
'pr6.DbType = DbType.Int16
'pr6.Value = ddorgProvince.DataValueField

'Dim pr7 As New SqlParameter
'pr7.ParameterName = ("@orgPostalCode")
'pr7.Direction = ParameterDirection.Input
'pr7.DbType = DbType.String
'pr7.Value = txtorgPostal.Text

'Dim pr8 As New SqlParameter
'pr8.ParameterName = ("@orgPhone1")
'pr8.Direction = ParameterDirection.Input
'pr8.DbType = DbType.String
'pr8.Value = txtorgPhone1.Text

'Dim pr9 As New SqlParameter
'pr9.ParameterName = ("@orgPhone1Ext")
'pr9.Direction = ParameterDirection.Input
'pr9.DbType = DbType.String
'pr9.Value = txtorgPhoneX1.Text

'Dim pr10 As New SqlParameter
'pr10.ParameterName = ("@orgPhone2")
'pr10.Direction = ParameterDirection.Input
'pr10.DbType = DbType.String
'pr10.Value = txtorgPhone2.Text

'Dim pr11 As New SqlParameter
'pr11.ParameterName = ("@orgPhone2Ext")
'pr11.Direction = ParameterDirection.Input
'pr11.DbType = DbType.String
'pr11.Value = txtorgPhoneX2.Text

'Dim pr12 As New SqlParameter
'pr12.ParameterName = ("@orgFaxNumber")
'pr12.Direction = ParameterDirection.Input
'pr12.DbType = DbType.String
'pr12.Value = txtorgFax.Text

'Dim pr13 As New SqlParameter
'pr13.ParameterName = ("@orgEmail")
'pr13.Direction = ParameterDirection.Input
'pr13.DbType = DbType.String
'pr13.Value = txtorgEmail.Text

'Dim pr14 As New SqlParameter
'pr14.ParameterName = ("@orgDateRegistered")
'pr14.Direction = ParameterDirection.Input
'pr14.DbType = DbType.Date
'pr14.Value = Now.Date

'Dim pr15 As New SqlParameter
'pr15.ParameterName = ("@orgContactPerson")
'pr15.Direction = ParameterDirection.Input
'pr15.DbType = DbType.String
'pr15.Value = txtorgContact.Text

'Dim pr16 As New SqlParameter
'pr16.ParameterName = ("@orgTitle")
'pr16.Direction = ParameterDirection.Input
'pr16.DbType = DbType.String
'pr16.Value = txtorgTitle.Text

'Dim pr17 As New SqlParameter
'pr17.ParameterName = ("@orgDept")
'pr17.Direction = ParameterDirection.Input
'pr17.DbType = DbType.String
'pr17.Value = txtorgDept.Text

''Dim insertedrows As SqlParameter
''insertedrows.ParameterName = ("@@IDENTITY")
''insertedrows.Direction = ParameterDirection.ReturnValue
''insertedrows.DbType = DbType.Int16
''rows = insertedrows.Value


''cmd.Parameters.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_InsertOrganization ",
sqlAbComm)
cmd.CommandType=CommandType.StoredProcedure
rows = cmd.ExecuteNonQuery()

This gave me grief - always gettinga stack error about the string
being incorrect. PAIN

 

[Jim Mitchell]

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

 

[Jim Mitchell]

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.