Avatar of fwstealer
fwstealer
Flag 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
ASP.NETC#

Avatar of undefined
Last Comment
CtrlAltDl

8/22/2022 - Mon
pkbugudai

Dear!!!

Inplace of "\n" in your code use "go". Hope it will work.
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()
pkbugudai

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 );
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
BuggyCoder

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
fwstealer

ASKER
no luck with those options
fwstealer

ASKER
yes they are decimal
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CtrlAltDl

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
pkbugudai

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
fwstealer

ASKER
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?
fwstealer

ASKER
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";
}

}
Your help has saved me hundreds of hours of internet surfing.
fblack61
fwstealer

ASKER
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");
pkbugudai

Dear!!!

please accept the solution if it worked for you.
CtrlAltDl

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.