PeterErhard
asked on
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 StartSavingReferralService Provider()
If Not ValidateReferralServicePro viderField s() Then Exit Sub
'Check the status of the connection
CheckConnectionStatus()
Dim cmd As SqlCommand = cnn.CreateCommand
Try
cmd.CommandType = CommandType.StoredProcedur e
cmd.CommandText = "procInsertReferralService Provider"
cmd.Parameters.Add(New SqlParameter("@Name", SqlDbType.Text, 100))
cmd.Parameters("@Name").Va lue = 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").Va lue = txt_City.Text
cmd.Parameters.Add(New SqlParameter("@Phone", SqlDbType.Text, 30))
cmd.Parameters("@Phone").V alue = txt_Phone.Text
cmd.Parameters.Add(New SqlParameter("@Email", SqlDbType.Text, 60))
cmd.Parameters("@Email").V alue = txt_Email.Text
cmd.Parameters.Add(New SqlParameter("@ContactPers on", SqlDbType.Text, 35))
cmd.Parameters("@ContactPe rson").Val ue = txt_ContactPerson.Text
cmd.Parameters.Add(New SqlParameter("@Notes", SqlDbType.Text, 6000))
cmd.Parameters("@Notes").V alue = txt_OrganisationNotes.Text
cmd.Parameters.Add(New SqlParameter("@UserCreated ", SqlDbType.Text, 35))
cmd.Parameters("@UserCreat ed").Value = g_Username
cmd.Parameters.Add(New SqlParameter("@BranchID", SqlDbType.Int))
cmd.Parameters("@BranchID" ).Value = g_UserCentre
cmd.Parameters.Add(New SqlParameter("@ReferralSer viceProvid erIDAuto", SqlDbType.Int))
cmd.Parameters("@ReferralS erviceProv iderIDAuto ").Value = m_ReferralServiceProviderI DAuto
cmd.Parameters.Add(New SqlParameter("@DateCreated ", SqlDbType.DateTime, 40))
cmd.Parameters("@DateCreat ed").Value = New Date(Today.Year, Today.Month, Today.Day)
cmd.ExecuteNonQuery()
If m_ReferralServiceProviderI DAuto = 0 Then
MessageBox.Show("New Referral Service Provider Successfully Saved!", "Save Successful", MessageBoxButtons.OK, MessageBoxIcon.Information )
Else
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)
Finally
cnn.Close()
End Try
txtSearch.Focus()
GroupBox_Details.Enabled = False
ToolBar1.Buttons(2).Enable d = False
End Sub
******************
CREATE PROCEDURE procInsertReferralServiceP rovider
@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,
@ReferralServiceProviderID Auto int,
@DateCreated datetime
AS
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 @ReferralServiceProviderID Auto = 0
BEGIN
Insert into ReferralServiceProvider (UserCreated, DateCreated, BranchID, ProviderName, Address, City, Phone, Email, ContactPerson, Description)
Values (@UserCreated, @DateCreated, @BranchID, @Name, @Address, @City, @Phone, @Email, @ContactPerson, @Notes)
END
Else
Update ReferralServiceProvider
Set UserCreated = @UserCreated, BranchID = @BranchID, ProviderName = @Name, Address = @Address,
City = @City, Phone = @Phone, Email = @Email, ContactPerson = @ContactPerson, Description = @Notes
where ReferralServiceProviderIDA uto = @ReferralServiceProviderID Auto
GO
***********************
Private Sub StartSavingReferralService
If Not ValidateReferralServicePro
'Check the status of the connection
CheckConnectionStatus()
Dim cmd As SqlCommand = cnn.CreateCommand
Try
cmd.CommandType = CommandType.StoredProcedur
cmd.CommandText = "procInsertReferralService
cmd.Parameters.Add(New SqlParameter("@Name", SqlDbType.Text, 100))
cmd.Parameters("@Name").Va
cmd.Parameters.Add(New SqlParameter("@Address", SqlDbType.Text, 35))
cmd.Parameters("@Address")
cmd.Parameters.Add(New SqlParameter("@City", SqlDbType.Text, 35))
cmd.Parameters("@City").Va
cmd.Parameters.Add(New SqlParameter("@Phone", SqlDbType.Text, 30))
cmd.Parameters("@Phone").V
cmd.Parameters.Add(New SqlParameter("@Email", SqlDbType.Text, 60))
cmd.Parameters("@Email").V
cmd.Parameters.Add(New SqlParameter("@ContactPers
cmd.Parameters("@ContactPe
cmd.Parameters.Add(New SqlParameter("@Notes", SqlDbType.Text, 6000))
cmd.Parameters("@Notes").V
cmd.Parameters.Add(New SqlParameter("@UserCreated
cmd.Parameters("@UserCreat
cmd.Parameters.Add(New SqlParameter("@BranchID", SqlDbType.Int))
cmd.Parameters("@BranchID"
cmd.Parameters.Add(New SqlParameter("@ReferralSer
cmd.Parameters("@ReferralS
cmd.Parameters.Add(New SqlParameter("@DateCreated
cmd.Parameters("@DateCreat
cmd.ExecuteNonQuery()
If m_ReferralServiceProviderI
MessageBox.Show("New Referral Service Provider Successfully Saved!", "Save Successful", MessageBoxButtons.OK, MessageBoxIcon.Information
Else
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)
Finally
cnn.Close()
End Try
txtSearch.Focus()
GroupBox_Details.Enabled = False
ToolBar1.Buttons(2).Enable
End Sub
******************
CREATE PROCEDURE procInsertReferralServiceP
@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,
@ReferralServiceProviderID
@DateCreated datetime
AS
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 @ReferralServiceProviderID
BEGIN
Insert into ReferralServiceProvider (UserCreated, DateCreated, BranchID, ProviderName, Address, City, Phone, Email, ContactPerson, Description)
Values (@UserCreated, @DateCreated, @BranchID, @Name, @Address, @City, @Phone, @Email, @ContactPerson, @Notes)
END
Else
Update ReferralServiceProvider
Set UserCreated = @UserCreated, BranchID = @BranchID, ProviderName = @Name, Address = @Address,
City = @City, Phone = @Phone, Email = @Email, ContactPerson = @ContactPerson, Description = @Notes
where ReferralServiceProviderIDA
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.