How to Execute Update SQL Script in ASP.Net

Question

I have this code to execute a stored procedure (Update SP) in ASP.Net, unfortunately record is not updating when I run the code.

This is my code:

using (SqlConnection sqlConnection = Connt.GetConnection(TblName))
{
        sqlConnection.Open();
        using (SqlDataAdapter adapter = new SqlDataAdapter(SqlScript, sqlConnection))
        {
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.SelectCommand.Parameters.AddRange(SqlParam);
        }
}

Where SqlScript is the variable for the stored procedure name and SqlParam is the parameters.

Please help me figure out what is wrong with my code.

Your main issue is that you have an UPDATE stored procedure and you are using the SelectCommand property of the DataAdapter. The constructor is also assigning the SqlScript to the SelectCommand. — rikitikitik
– rikitikitik, Commented Dec 3, 2013 at 2:11
Yes and thats the main mistake. SQLCommand should be use instead. — Stuart
– Stuart, Commented Dec 3, 2013 at 3:19

Angelo · Accepted Answer · 2013-12-03 02:15:02Z

4

Hi you can try something like this

SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
sqlConnection.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=True";

public void samplefunct(params object[] adparam)
   {
       sqlConnection.Open();
       sqlCommand.Connection = sqlConnection;
       sqlCommand.CommandType = CommandType.StoredProcedure;
       sqlCommand.CommandText = "SPName";

       sqlCommand.Parameters.Add("@param1", SqlDbType.VarChar).Value = adparam[0];
       sqlCommand.Parameters.Add("@param2", SqlDbType.VarChar).Value = adparam[1];
       sqlCommand.Parameters.Add("@Param3", SqlDbType.VarChar).Value = adparam[2];
       sqlCommand.ExecuteNonQuery();
}

answered Dec 3, 2013 at 2:15

Angelo

3354 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Stuart Over a year ago

This works fine with just minor adjustments to my code but this code contains the functionality and logic you need. Cheers!

Angelo Over a year ago

Thanks for voting hope it helps you a lot

Sudhakar Tillapudi Over a year ago

@Angelo : nice answer +1, i have a question here that how would you know that there are only 3 parameters in the adparam object array? and what happens when user only sends 2 parameters to the samplefunct function?

Angelo Over a year ago

ahh you have to send the same parameter count like samplefunct(param1,param2,param3)if not it gives you error unless you put something like try catch

Sudhakar Tillapudi Over a year ago

@Angelo : yes that is ok, but as you are using params it allows any no of parameters , even while complining it wont let user know so i think it is good to check the count of parameters using .Length property before proceeding further otherwise it can cause exception when parameters are less than 3 IMO.

|

Sudhakar Tillapudi · Accepted Answer · 2013-12-03 03:46:10Z

1

Try:

using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("ProcedureName", conn) { 
                           CommandType = CommandType.StoredProcedure }) {
   conn.Open();
   command.ExecuteNonQuery();
   conn.Close();
}

With a Param:

command.Parameters.Add(new SqlParameter("@ID", 123));

edited Dec 3, 2013 at 3:46

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

answered Dec 3, 2013 at 2:04

David J Barnes

5069 silver badges15 bronze badges

1 Comment

Stuart Over a year ago

Hi, thanks for your reply. How can I add parameter to the SP using your given code.

Collectives™ on Stack Overflow

How to Execute Update SQL Script in ASP.Net

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related