Is this the right way to do it? Saving data....

The below code is what I'm using to write to the database. It works but I'm wondering whether it's the best way of doing it. Any comments?


    Private Sub StartSavingReferralServiceProvider()
        If Not ValidateReferralServiceProviderFields() Then Exit Sub

        'Check the status of the connection

        Dim cmd As SqlCommand = cnn.CreateCommand

            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "procInsertReferralServiceProvider"

            cmd.Parameters.Add(New SqlParameter("@Name", SqlDbType.Text, 100))
            cmd.Parameters("@Name").Value = txt_Name.Text
            cmd.Parameters.Add(New SqlParameter("@Address", SqlDbType.Text, 35))
            cmd.Parameters("@Address").Value = txt_Address.Text
            cmd.Parameters.Add(New SqlParameter("@City", SqlDbType.Text, 35))
            cmd.Parameters("@City").Value = txt_City.Text
            cmd.Parameters.Add(New SqlParameter("@Phone", SqlDbType.Text, 30))
            cmd.Parameters("@Phone").Value = txt_Phone.Text
            cmd.Parameters.Add(New SqlParameter("@Email", SqlDbType.Text, 60))
            cmd.Parameters("@Email").Value = txt_Email.Text
            cmd.Parameters.Add(New SqlParameter("@ContactPerson", SqlDbType.Text, 35))
            cmd.Parameters("@ContactPerson").Value = txt_ContactPerson.Text
            cmd.Parameters.Add(New SqlParameter("@Notes", SqlDbType.Text, 6000))
            cmd.Parameters("@Notes").Value = txt_OrganisationNotes.Text
            cmd.Parameters.Add(New SqlParameter("@UserCreated", SqlDbType.Text, 35))
            cmd.Parameters("@UserCreated").Value = g_Username
            cmd.Parameters.Add(New SqlParameter("@BranchID", SqlDbType.Int))
            cmd.Parameters("@BranchID").Value = g_UserCentre
            cmd.Parameters.Add(New SqlParameter("@ReferralServiceProviderIDAuto", SqlDbType.Int))
            cmd.Parameters("@ReferralServiceProviderIDAuto").Value = m_ReferralServiceProviderIDAuto
            cmd.Parameters.Add(New SqlParameter("@DateCreated", SqlDbType.DateTime, 40))
            cmd.Parameters("@DateCreated").Value = New Date(Today.Year, Today.Month, Today.Day)


            If m_ReferralServiceProviderIDAuto = 0 Then
                MessageBox.Show("New Referral Service Provider Successfully Saved!", "Save Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)
                MessageBox.Show("Referral Service Provider Successfully Updated!", "Save Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If

        Catch e As Exception
            MessageBox.Show("An exception of type " & e.GetType().ToString() & _
            " was encountered while inserting the data. Record failed writing to the database.", "Database Transaction Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        GroupBox_Details.Enabled = False
        ToolBar1.Buttons(2).Enabled = False

    End Sub


CREATE PROCEDURE procInsertReferralServiceProvider
      @Name varchar(100),
      @Address varchar(35),
      @City varchar(35),
      @Phone varchar(30),
      @EMail varchar(60),
      @ContactPerson varchar(35),
      @Notes varchar(6000),
      @UserCreated varchar(35),
      @BranchID int,
      @ReferralServiceProviderIDAuto int,
      @DateCreated datetime
      SELECT @Name = REPLACE(@Name,"'","''")
      SELECT @Address = REPLACE(@Address,"'","''")
      SELECT @City = REPLACE(@City,"'","''")
      SELECT @Email = REPLACE(@Email,"'","''")
      SELECT @ContactPerson = REPLACE(@ContactPerson,"'","''")
      SELECT @Notes = REPLACE(@Notes,"'","''")
      If @ReferralServiceProviderIDAuto = 0
            Insert into ReferralServiceProvider (UserCreated,  DateCreated, BranchID, ProviderName, Address, City, Phone, Email, ContactPerson, Description)
            Values (@UserCreated, @DateCreated,  @BranchID, @Name, @Address, @City, @Phone, @Email, @ContactPerson, @Notes)
            Update ReferralServiceProvider
            Set UserCreated = @UserCreated,  BranchID = @BranchID, ProviderName = @Name, Address = @Address,
            City = @City, Phone = @Phone, Email = @Email, ContactPerson = @ContactPerson, Description = @Notes
            where ReferralServiceProviderIDAuto = @ReferralServiceProviderIDAuto

Who is Participating?
123654789987Connect With a Mentor Commented:
I have only one comment. Instead of doing this in the stored procedure

     SELECT @Name = REPLACE(@Name,"'","''")

u can apply the replace to textbox text

 cmd.Parameters("@Name").Value = txt_Name.Text.Replace("'","''")

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.