Click here to Skip to main content
15,895,746 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am inserting and updating data in database MS SQL using stored procedure in sql. This is upon button click, The SQL script is working when ran on SQL Management studio, but not in my program. My code is as follow.

C#
C#
protected void Button1_Click(object sender, EventArgs e)
    {
        /**String docID = docID_txt.Text;
        String pass = pass_txt.Text;**/

        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand();
        System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection("Data Source=UPHMC-DC37; Initial Catalog=TrainingDB_UPHMC; User ID=sa; Password=2016Bizbox");
        sqlCmd = new System.Data.SqlClient.SqlCommand("A_Update_DoctorsPass_PFMonitor", connection);
        sqlCmd.Parameters.AddWithValue("@docID", docID_txt.Text.ToString());
        sqlCmd.Parameters.AddWithValue("@pass", pass_txt.Text.ToString());
        /**sqlCmd.CommandText = "Execute A_Update_DoctorsPass_PFMonitor";**/
        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
        connection.Open();
        sqlCmd.ExecuteNonQuery();
        connection.Close();
    }


Stored Procedure:
SQL
USE [X]
GO

/****** Object:  StoredProcedure [dbo].[A_Check_UserAccess_3]    Script Date: 3/12/2018 1:43:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[A_Update_DoctorsPass_PFMonitor]
--Declare 
@docID as varchar(15),
@pass as varchar(100)

--set @EmpID = '4025'
--set @Pass = '1209'
AS

IF EXISTS(select A_PFMonitoring_Users.usercode from A_PFMonitoring_Users where A_PFMonitoring_Users.usercode = @docID)
   update A_PFMonitoring_Users set  A_PFMonitoring_Users.userpass=@pass where A_PFMonitoring_Users.usercode=@docID
ELSE
   insert into A_PFMonitoring_Users values(@docID, @pass)


What I have tried:

I've already tried using the raw sql statement then jumped into stored procedure, but still no work.
Posted
Updated 22-Mar-18 18:57pm
v3
Comments
Richard Deeming 23-Mar-18 13:30pm    
NEVER store passwords in plain text!

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

Also, NEVER connect to the database as sa. This is an unrestricted user, which could be used to destroy your database, your server, and possibly even your network. Instead, connect as a user which has only the permissions required by your application.

Particularly worrying given the stored procedure name - you seem to be writing an application to work with medical records. I can only hope this is a school project, and not something intended for real-world use!

One possible source of error is that you don't define the column names when inserting the data. If the columns are in different order you could end up inserting but in the wrong columns. In this kind of case you wouldn't find newly inserted rows.

Try changing the insert into
SQL
insert into A_PFMonitoring_Users (usercode, userpass) values(@docID, @pass)

Also since you're updating only one table, you can simplify the update by removing the aliases. For example
SQL
update A_PFMonitoring_Users set userpass=@pass where usercode=@docID
 
Share this answer
 
check with debugger if your connection is opens properly then check Sp executing with parameters in sql server.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900