ASP.NET - sql server sqlcommand.executenonquery() result show

Asked By chandan
31-Oct-11 02:49 AM
hello


i have executed a query in sqlserver. using cmd.executeNonQuery  and it returns -1 in my web page.
when i execute the same query it returns an error result in sqlServer.
how can i read the same error result in my web page(.aspx).
Is it possible?


Please help me

chandan
Advance thanks
  smr replied to chandan
31-Oct-11 02:51 AM
hi

It executes a Transact-SQL statement against the connection and returns the number of rows affected.

syntax:

public override int ExecuteNonQuery()

follow
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx
  James H replied to chandan
31-Oct-11 02:54 AM
Hi always try to use the Try Catch Final block to execute this queries.In Catch block capture the Error and Show to the User.
Do like this

 

SqlConnection connection = null;

 

Byte[] imgByte = null;

 

if (imgUpload.PostedFile != null && imgUpload.PostedFile.FileName != "" && imgUpload.HasFile)

{

 

//To create a Posted File

 

HttpPostedFile file = imgUpload.PostedFile;

 

//Create byte Array with File Length

imgByte =

new Byte[file.ContentLength];

 

//Force the control to load the data in Array

file.InputStream.Read(imgByte, 0, file.ContentLength);

}

 

//Insert the Employee name and Image into DB

 

string con = ConfigurationManager.ConnectionStrings["TESTDB"].ConnectionString;

connection =

new SqlConnection(con);

connection.Open();

 

try

{

 

string Insert_Query = "insert into empdetails(empname,empimg) values(@en,@eimg) select @@identity";

 

SqlCommand cmd = new SqlCommand(Insert_Query, connection);

cmd.Parameters.Add(

new SqlParameter("@en", txtEName.Text));

cmd.Parameters.Add(

new SqlParameter("@eimg", imgByte));

id =

Convert.ToInt32(cmd.ExecuteScalar());

lblResult.Text =

String.Format("Employee ID is {0}", id);

}

 

catch (Exception exp)

{

lblResult.Text = exp.Message;

}

  dipa ahuja replied to chandan
31-Oct-11 02:55 AM
Use the try catch block to get the error

void export()
{
  try
  {
    SqlConnection conn = new SqlConnection("connectionstring");
    SqlCommand comm;
    for (int i = 0; i < dt.Rows.Count; i++)
    {
      string q = "inser into table1 (name) values ('" + dt.Rows[0]["names"].ToString() + "')";
 
      conn.Open();
      comm = new SqlCommand(q, conn);
      comm.ExecuteNonQuery();
      conn.Close();
    }
  }
  catch (Exception ex)
  {
    Response.Write(ex.Message);
  }
}

  Reena Jain replied to chandan
31-Oct-11 02:59 AM
Hi,

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

The following example creates a http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx and then executes it using ExecuteNonQuery. The example is passed a string that is a Transact-SQL statement (such as UPDATE, INSERT, or DELETE) and a string to use to connect to the data source.
private static void CreateCommand(string queryString,
  string connectionString)
{
  using (SqlConnection connection = new SqlConnection(
         connectionString))
  {
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Connection.Open();
    command.ExecuteNonQuery();
  }
}

  Sree K replied to chandan
31-Oct-11 03:00 AM

Change your catch to be catch (Exception ex) and you can then use Response.Write(ex.Message)

Ideally, you would have different catches for different exception types. This is a bucket approach to catching all exceptions.

Create New Account
help
sqlhelper class Hi This the sql helper class, when i use it i got cmd error.wt is the soltion using System; using System.Data; using System.Xml; using System.Data SqlHelper()" private SqlHelper() {} / / / <summary> / / / This method is used to attach array of SqlParameters to a SqlCommand. / / / / / / This method will assign a value of DbNull to any parameter with a direction of commandParameters"> An array of SqlParameters to be added to command< / param> private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { if( command = = null ) throw new ArgumentNullException( "command" ); if( commandParameters ! = null ) { foreach (SqlParameter p in commandParameters) { if( p ! = null ) { / / Check for derived output value with no value assigned dataRow used to hold the stored procedure's parameter values< / param> private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow) { if ((commandParameters = = null) | | (dataRow = = null)) { / / Do nothing if we get no data return; } int i = 0; / / Set the parameters values foreach(SqlParameter commandParameter in commandParameters) { / / Check the parameter name if( commandParameter.ParameterName = = null | | commandParameter.ParameterName.Length < = 1 parameterValues"> Array of objects holding the values to be assigned< / param> private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters = = null) | | (parameterValues = = null)) { / / Do nothing if we get no data
controls are created and data is provided. . . . but if users select less text box then error occurs that " no. of rows specified or columns specified does not match". . . . . . . . . . HOPE you all Parameters) { if (param.Value = = null) param.Value = DBNull.Value; } OR like below var valueToBePassed = null; SqlCommand command = new SqlCommand(); command.Parameters.AddWithValue("@valueToBePassed", valueToBePassed?? DBNull.Value); Hope this information helps. I think you have it goes. . . private void SaleSave_Click(object sender, EventArgs e) { if (SaleTotalTran.SelectedIndex = = 0) { con = new SqlConnection("Data Source = RISHI-PC \ RISHI; Initial Catalog = retail; User ID = sa; Password = helloworld"); con.Open(); cmd4 = new SqlCommand("insert into Sales values (@Name, @Address, @VNo, @VDate, @BillNo, @Remark, @ItemName1, @Qty1, @Pcs1, @Unit1, @Rate1 Qty6, @Pcs6, @Unit6, @Rate6, @Amt6, @DiscAmt, @StAmt, @VatAmt, @AVatAmt, @NetTotal)", con); cmd4.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar)); cmd4.Parameters.Add(new SqlParameter("@Address", SqlDbType.VarChar)); cmd4.Parameters.Add(new SqlParameter("@VNo", SqlDbType.Int)); cmd4.Parameters.Add(new SqlParameter("@VDate", SqlDbType.DateTime)); cmd4.Parameters.Add(new
can browse the products but when I try to register a user I get the error System.InvalidCastException: Specified cast is not valid. on line 503 of the customerdb.cs / / Calculate so many times and with different data type and cases. your stack trace specifies that error is at line 503 of CustomersDB.cs. try debugging method AddCustomer Thanks , This is from Stored Procedure< / a> / / / / * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** public CustomerDetails GetCustomerDetails(String customerID) { / / Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_CustomerDetail", myConnection); / / Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; / / Add Parameters to SPROC SqlParameter parameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Value = Int32.Parse(customerID, CultureInfo.InvariantCulture); myCommand.Parameters.Add(parameterCustomerID
insert, update, delete. so i gave coding insert button like bellow format. / / object obj = 0; SqlConnection conn = new SqlConnection("server = MYLAPTOP;uid = sa;pwd = hari_123; database = test1"); / / conn.Open(); SqlCommand cmd = new SqlCommand("spInsertEmp", conn); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spInsertEmp"; SqlParameter inparm = cmd.Parameters.Add("@Empname", SqlDbType.VarChar, 50); inparm.Direction = ParameterDirection.Input; inparm.Value = txtAdditionalChargeType Text; SqlParameter inparm1 = cmd.Parameters.Add("@Idemplayee", SqlDbType.Int, 15); inparm.Direction = ParameterDirection.Input; inparm.Value = txtAdditionalChargeType records already exits"; } else { lblname.Text = "new record added successfully"; } so in that i got error Procedure or function spInsertEmp has too many arguments specified for this line Object obj1 = cmd thanks hari First you need to pass one more parameter "option" from code behind like; SqlParameter inparm = cmd.Parameters.Add("@ option ", SqlDbType.VarChar, 10); inparm.Direction = ParameterDirection.Input; inparm.Value = " insert
Error on update qry ? kindly tel me this pls ? it shows this error ? Error:System.NullReferenceException: Object reference not set to an instance of an object. at creditadd.GridView1_RowCommand Parameters.Add(param); / / to this executing correctly below this is not executing correctly it shows error ? / / cmd = new SqlCommand("update creditadd set total = @total, @total = @total + credit where cid = '" + lbid.Text + "'", con); cmd = new SqlCommand ( "update creditadd set total = @total, @total = @total + credit where userid = '" + txusrid.Text + "' and username = '" + txusrname Text + "'" , con); error it shows this line ? cmd.ExecuteNonQuery(); Response.Write( "<script> alert(Running Total Updated Successfully');< / script> " ); GridView1.EditIndex = -1; bindgrid(); } You can