locked
Gridview Update command dynamically. RRS feed

  • Question

  • User-1380358420 posted

    Hi,

    I have a web form where I populate the gridview dynamically based on the listbox selection, which consist of various tables from a database.

    My problem is in the update command. Since the columns are dynamically added to the gridview, the parameters are unknown to update the SQL table.

    I was able to create the an update query from the code below, but the insert doesn't work because the values aren't declared. I get incorrect syntax where there are spaces in update values.

    For Each item As DictionaryEntry In e.NewValues
    sqlCommand = sqlCommand & item.Key & " = " + item.Value & ","
    Next

    sqlCommand = sqlCommand.TrimEnd(","c)

    For Each de As DictionaryEntry In e.Keys
    sqlCommand = sqlCommand & " WHERE " & de.Key.ToString() & " = " + de.Value.ToString()
    Next

    Any ideas or solution to a better approach?

    Thanks

    Saturday, July 4, 2020 5:01 PM

All replies

  • User-1380358420 posted

    I figured it out. Enclosed the values with '.

    But still looking for ideas to do this more efficiently.

    Thanks.

    Saturday, July 4, 2020 7:30 PM
  • User-1330468790 posted

    Hi hutty123,

     

    I understand that you want to deal with the values which is not initialized and insert the "nothing" into database. 

    Here is an option but I am not sure if you are willing to use it.  --- IF Operator.

    For example, if the variable "a" is nothing, then the variable "s" will be assigned with "NULL". (Null is recommended in database if the column contains nothing) 

    Dim a As String = Nothing
    Dim s As String = If(String.IsNullOrEmpty(a), "NULL", a)

    In your scenario, you might use IF operator to construct the sql command. 

     

    Hope this can help you.

    Best regards,

    Sean

    Monday, July 6, 2020 8:17 AM
  • User-1380358420 posted

    Hey Sean,

    I'm not following your suggestion.

    As it stands now, I'm able to commit the current row that's being edited to the database. Even where there are blank fields.

    I do get an error when updating bit field, changing it from checked to unchecked.

    "Conversion from string " = '" to type 'Double' is not valid." on this line  sqlCommand = sqlCommand & item.Key & " = '" + item.Value & "'" & ","

    Thanks

    Tuesday, July 7, 2020 2:31 AM
  • User-1330468790 posted

    Hi hutty123,

     

    No worries. If your design is working now, just keep it working.

    However, I have another suggestion to improve the codes quality. 

    "Conversion from string " = '" to type 'Double' is not valid." on this line  sqlCommand = sqlCommand & item.Key & " = '" + item.Value & "'" & ","

    I think you should use SqlParameter which could match the value to the column of the data table and prevent the SQL injection attack.

    You could refer to below links: 

    Introduction about SQL Injection

    SQL Injection And Parameterized Queries (How to prevent SQL Injection)

     

    Hope this can help you.

    Best regards,

    Sean

    Tuesday, July 7, 2020 8:39 AM
  • User-1380358420 posted

    Hey Sean,

    I would prefer to use parameters like my other applications. However, this one presents a challenge since I don't know the table being updated until it's selected from a listbox.

    From the code above, I'm able to get the update statement below, but creating a parameter for each line is the challenge.

    SQLCOMMAND="UPDATE tbl_debt SET DataDate = @DataDate,Last = @Last,First = @First,Program = @Program,Debt1516 = @Debt1516,DataSource = @DataSource,Comments = @Comments WHERE TUID = 440"

    I think the bit field on my original code is coming across as null, so I'm going to look into an IF statement on "item.value".

    Thanks for your input. I will study the parameters more closely.

    Wednesday, July 8, 2020 3:17 AM
  • User-1330468790 posted

    Hi hutty123,

     

    I could completely understand that it is really annoying to dynamically construct a sql.

    However, you might need to think about updating all columns for one row even though some of columns does not change.

    The impact of updating non-changed field in database is not BAD as you expected. You could refer to this article: UPDATE performance where no data changes.

     

    The advantage of updating all columns for data row is that you could do batch update.

    For example, there is an sql statement -- "UPDATE Production.ProductCategory SET " + "Name=@Name WHERE ProductCategoryID=@ProdCatID;". You could put all to-update-fields and the condition in the sql statement. Therefore, it would be possible to use Sql parameter.

    What you need to do is fetch all of the data for selected rows.

     

    You could find out that in EF/EF core, the DbContext uses the similar way to do update. 

     

    Best regards,

    Sean 

    Thursday, July 9, 2020 8:22 AM
  • User-1380358420 posted

    Hey Sean,

    That's exactly what I'm doing with my current code, which is updating the non-changed field as well. Pretty much overwriting the current values with the same values.

    Right now, I have no control over defining the parameters because the gridview displays a different table from the listbox selected values.

    I have about 20 tables. Data updates as expected for most of the tables until I get to one that has a bit field. Then I get the error on the column. It sees it has a blank, when it should be checked(True) or unchecked(False).

    So, I'm getting there. Hopefully more reading will yield the results I'm looking for in the end.

    Thanks for your help.

    Friday, July 10, 2020 2:54 AM