Link to home
Start Free TrialLog in
Avatar of fwstealer
fwstealerFlag for United States of America

asked on

bulk update via gridview

I'm trying to perform a bulk update from a gridview but its not working out so well.

so it fails at: SQLCmd.ExecuteNonQuery();

not sure why as it is just a series of update statements

see attached file
gridviewerror.txt
Avatar of pkbugudai
pkbugudai
Flag of India image

Dear!!!

Inplace of "\n" in your code use "go". Hope it will work.
Avatar of fwstealer

ASKER

changed to go and it threw an error.

query: {UPDATE dbo.ProjectedSalesUnits SET [newshares] = '1.00', [Lease2YrTerm] = '5.00', [Lease5YrTerm] = '2.00', [AAIROwned] = '1.50', [NonMMFPaying] '1.00', [UnderMMAgreement] '1.00', [TotalUsedShares] '3.50', [Edge25] '11.00', [Edge15] '4.00', [TotalEdge] '15.00', [FraxExpiring] '1', [TargetRenewalSales] '8.00', [DemoHours] '47.00' WHERE [iD] = '1';goUPDATE dbo.ProjectedSalesUnits SET [newshares] = '5.00', [Lease2YrTerm] = '15.00', [Lease5YrTerm] = '3.00', [AAIROwned] = '3.00', [NonMMFPaying] '3.00', [UnderMMAgreement] '3.50', [TotalUsedShares] '9.50', [Edge25] '40.00', [Edge15] '10.00', [TotalEdge] '50.00', [FraxExpiring] '1', [TargetRenewalSales] '6.00', [DemoHours] '43.20' WHERE [iD] = '2';go}


protected void updateData() 002 .Net SqlClient Data ProviderIncorrect syntax near '1.00'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Please try these below ways for this line ".Append(value14).Append("';\n"); "  as

.Append(value14).Append("'; \n " + " go " + " \n" );

or

.Append(value14).Append("'; \r\n " + " go " + " \r\n" );

or

.Append(value14).Append("'; "  + Constants.vbCrLf  + " go " +  Constants.vbCrLf );
well since you are passing all the parameters as string, maybe that is the reason.
I suppose some of the fields you are updating are supposed to be int or any other numeric types.

BTW it better to use command parameters to pass the parameters, here see an example:-
http://msdn.microsoft.com/en-us/magazine/cc163799.aspx
no luck with those options
yes they are decimal
Your missing the "=" signs in your UPDATE statement for some of your values:
UPDATE dbo.ProjectedSalesUnits SET [newshares] = '1.00', [Lease2YrTerm] = '5.00', [Lease5YrTerm] = '2.00', [AAIROwned] = '1.50', [NonMMFPaying] '1.00', [UnderMMAgreement] '1.00', [TotalUsedShares] '3.50', [Edge25] '11.00', [Edge15] '4.00', [TotalEdge] '15.00', [FraxExpiring] '1', [TargetRenewalSales] '8.00', [DemoHours] '47.00' WHERE [iD] = '1';

Open in new window


Starting with [NonMMFPaying].
ASKER CERTIFIED SOLUTION
Avatar of pkbugudai
pkbugudai
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
not sure how I missed the = sign.

anyways new error: Error converting data type varchar to numeric.

protected void updateData() 002 .Net SqlClient Data ProviderError converting data type varchar to numeric. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

so i probably need to convert the fields to decimal?
i know this will do it for a single row but how for the entire grid and find the row id?

SQLQryString = "update ProjectedSalesUnits set [newshares] = @newshares,[Lease2YrTerm] = @Lease2YrTerm,[Lease5YrTerm] = @Lease5YrTerm,[AAIROwned] = @AAIROwned,[NonMMFPaying] = @NonMMFPaying,
[UnderMMAgreement] = @UnderMMAgreement,[TotalUsedShares] = @TotalUsedShares,[Edge25] = @Edge25,[Edge15] = @Edge15,[TotalEdge] = @TotalEdge,[FraxExpiring] = @FraxExpiring,[TargetRenewalSales] = @TargetRenewalSales,[DemoHours] = @DemoHours where iD ='" + rowid? + "'"

SQLCmd = new SqlCommand(query);

using (SQLCon = new SqlConnection(SQLConnectionString))
{
SQLCmd.Connection = SQLCon;

SQLCmd.Parameters.Add("@newshares", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[0].FindControl("txtbxNewShares")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@Lease2YrTerm", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[1].FindControl("txtbxLease2YrTerm")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@Lease5YrTerm", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[2].FindControl("txtbxLease5YrTerm")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@AAIROwned", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[3].FindControl("txtbxAAIROwned")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@NonMMFPaying", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[4].FindControl("txtbxNonMMFPaying")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@UnderMMAgreement", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[5].FindControl("txtbxUnderMMAgreement")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@TotalUsedShares", SqlDbType.Decimal).Value = Convert.ToDecimal(((Label)row.Cells[6].FindControl("lblTotalUsedShares")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@Edge25", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[7].FindControl("txtbxEdge25")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@Edge15", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[8].FindControl("txtbxEdge15")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@TotalEdge", SqlDbType.Decimal).Value = Convert.ToDecimal(((Label)row.Cells[9].FindControl("lblTotalEdge")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@FraxExpiring", SqlDbType.Decimal).Value = Convert.ToDecimal(((Label)row.Cells[10].FindControl("lblFraxExpiring")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@TargetRenewalSales", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[11].FindControl("txtbxTargetRenewalSales")).Text.Replace("'", "''"));
SQLCmd.Parameters.Add("@DemoHours", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)row.Cells[12].FindControl("txtbxDemoHours")).Text.Replace("'", "''"));

try
{
SQLCon.Open();
SQLCmd.ExecuteNonQuery();
lblMessage.Visible = true;
lblMessage.Text = "success";
}
catch (Exception ex)
{
lblMessage.Text = "error";
}

}
oh - think this did the job:

query.Append("UPDATE dbo.ProjectedSalesUnits SET [newshares] = '")
                  .Append(Convert.ToDecimal((value1)))
                  .Append("', [Lease2YrTerm] = '")
                  .Append(Convert.ToDecimal((value2)))
                  .Append("', [Lease5YrTerm] = '")
                  .Append(Convert.ToDecimal((value3)))
                  .Append("', [AAIROwned] = '")
                  .Append(Convert.ToDecimal((value4)))
                  .Append("', [NonMMFPaying] = '")
                  .Append(Convert.ToDecimal((value5)))
                  .Append("', [UnderMMAgreement] = '")
                  .Append(Convert.ToDecimal((value6)))
                  .Append("', [TotalUsedShares] = '")
                  .Append(Convert.ToDecimal((value7)))
                  .Append("', [Edge25] = '")
                  .Append(Convert.ToDecimal((value8)))
                  .Append("', [Edge15] = '")
                  .Append(Convert.ToDecimal((value9)))
                  .Append("', [TotalEdge] = '")
                  .Append(Convert.ToDecimal((value10)))
                  .Append("', [FraxExpiring] = '")
                  .Append(Convert.ToDecimal((value11)))
                  .Append("', [TargetRenewalSales] = '")
                  .Append(Convert.ToDecimal((value12)))
                  .Append("', [DemoHours]  = '")
                  .Append(Convert.ToDecimal((value13)))
                  .Append("' WHERE [iD] = '")
                  .Append(value14)
                  .Append("';\n");
Dear!!!

please accept the solution if it worked for you.
Um, I was actually the original poster (38009906) that noticed you missing the equal sign (=) in your statements.

Is there another reason pkbugudai was awarded the points for my solution?