Hello Dennis,

Would you mind to be more specific about this issue? Of course you can also delete by using SQLDataSource.
If you don't mind, please provide a simple runable sample. Perhaps, there is something wrong with your delete parameter/methods in SQLDataSource.

Regards,
Handy

Sorry took so long to get back.

I am working on a 'simple' runable sample - which is not trivial since I use SQL database I am trying to duplicate with Access Database I can provide to you.  will be a few days, lots going on.

I have attached a runnable sample of the issue I am having. 
Please,  review and let me know the solution (if any).

The SQL database is located on a shared server and the username and password are in the web.conf connection statement if you need to create a connection in Visual Studion 2008.

Please note that the functionality of the demonstration must not be changed.

- The ID field value in both the Access and SQL database are automatically generated with a new record.

- I must select a subset of the database table when the page opens.  I select a value of 'division' in the page load, but have also done that in the selecting events without any change to symptoms

- Inserts must set the 'division' parameter value to the value used in the selection. I do that in the Datasource 'Inserting' event code.   Perhaps there is a way to do that in WebGrid events or in a way that does not create the issue.

- Edits must not change the value of 'division' .  That parameter is not included in the datasource update command.

Again the issue is:

If I add a record to an SQL database using WebGrid then delete the record before refreshing the Client WebGrid I get an un-trapped error and...  I cannot find a solution in any of your website areas.

This does NOT happen if the datasource is AccessDatasource when used and configured the same way as my SQL datasource.

Hello Dennis,

I am unable to run your sample. It is missing SQL database. I tried to create a table that similar with your AccessDatabase, but it seems the datatype of some fields are not correct. Would you mind to send the database? We need to ensure that all the data type are the same for the testing.

As far I see, in the Access database might be have a different data type with your current SQL.
e.g Please tried to see the parameter in insert, delete, and update in your aspx.
Try to compare between between SQL and Access.

I also noticed that you were using auto increment/identity for the ID.
Perhaps, you can let us know what exactly the error message.
As far I know, when you use auto increment for the ID, you will need to handle select identity.
If not, when you insert a row, the current ID is not correct. This will cause error when you delete your added row, except you refresh the grid before delete your added row.

Regards,
Handy

Handy,  Thank you for your response and help. 

See my first post:

Is there a place and time and way to cause the refresh from serverside code (visual basic)?  That would seem to fix the issue.

Now, regards your last reply:

I think I can summarize this issue and a solution as follows:
1.  In my opinion, WebGrid should "behave" the same with SQL auto uniqueidentifier keys as it does with Access autoincrement keys..  but it does not.  (See my details below) 
    - Perhaps that is not possible?
    - Perhaps WebGrid can be enhanced to fetch the key for a row added to grid
        to allow delete before refresh  (as you obviously do for/with AccessDatasource  (database).
    -  Or, this fix or enhancement will not be implemented.
    -  Or, I am not understanding WebGrid and you can explain or tell me where is the API manual.

2. If no solution requested in number 1 above is possible, then...
     - I cannot ask my users to click the refresh button on WebGrid after each add.   
    - I must know how to 'refresh'  WebGrid in the 'SQLDataSource_Inserted' 
         event code or in javascript ...
    - Please provide sample code I can add to my 'SQLDataSource_Inserted'  event
          to cause refresh of WebGrid.  This will solve my problem!

3. You must know I am very frustrated! I have put too much time in trying to evaluate WebGrid, searching for help, trying to find documentation - and providing details and creating examples.  I cannot purchase the product if this is going to take much more time to resolve.  Thank you for helping to resolve this very soon.  

--------------------------------------------

If you still need (want?) more data, then I address all of your other comments or requests as follows:

Re: Access database might be have a different data type with your current SQL.
   Yes of course -   SQL uses different method to create a unique key (ID).  
    -  Access does autoincrement.  
    -  SQL does auto create of uniqueidentifier.
NOTE:  I must use uniqueidentifier in SQL but I cannot demo that in the Access database - Access has no such datatype to my knowledge ( I do not use Access).

However:
Please do not focus on data types.  I can assure you, that is not the problem.

Re: I am unable to run your sample. It is missing SQL database.
     - The SQL database is on a server.  dbs5.dailyrazor.com 
     - You should be able to "run" the sample because the connection string for the SQL database is in web.conf file?? 
     - Pehaps you need to change this part of connection string 
             providerName="System.Data.SqlClient" 
         I do not know what provider you use.  I do not have control of your SQL server. 

Re: Would you mind to send the database? We need to ensure that all the data type are the same for the testing.
     FIRST,  It is not possible to make the data type the same.  See my notes below.
     SECOND: I must have a solution using the SQL datatypes in my example.
     - You can make a connection in Visual Studio to view the data types and structure of the SQL database (see my details below)
     -  You can make a copy of my SQL database  if you know how to use SQL Server  (see my details below)
     -  As you should know,  one cannot just 'send' an SQL database. 

Re: Perhaps, you can let us know what exactly the error message.
     I have attached the text from the webpage error in text file.    

Obviously WebGrid does not have access to ID after I add a new row to SQL database.
       - If delete without refresh then get error.
       - If user clicks 'refresh' then ID is loaded so can now delete.      
       -  WebGrid dlls COULD fetch the added record and retrieve the key  (ID in this case) to allow the delete. 
         
    WebGrid DOES have access to ID after I add a new row to Access database - before refresh!  Why?  How?
      (we know this because you can delete the added Access database row in WebGrid before 'refresh' WITHOUT ERROR)
       I assume your software (dll) fetches the added record to obtain key (ID) somehow - without user clicking 'refresh'
      
Other comments
 
1.) Yes, data types are different.  Access has no datatype of uniqueidentifier - Access uses long integer for same purpose to create a unique ID for a 'key' via auto increment.   SQL does same function by creating a uniqueidentifier for the 'key' for new records 
2.) ID is key parameter in both Access and in SQL - used for updates and deletes.  
3.) ID is inserted automatically by Access and by SQL server. That is why I do not Insert that parameter in  Insert. 
4.) I must never change the value of division once the division records are selected - so that is why that parameter is not in the Update Command.
5.) I cannot demonstrate  the problem in SQL using long integer because that will not work with my application.
6.) I cannot demo the problem in Access using uniqueidentifier because Access has no such parameter type - to my knowledge.

This should not matter, your WebGrid should behave the same if using long integer key ID or uniqueidentifier key ID.  The LOGIC of adding a record is EXACTLY the same in my code.   I add the division parameter= value set in Page Load.  Access or SQL server add the key (ID) automatically.  So in either case, the WebGrid does not have access to the value of ID after the add - unless your product somehow reads back the record key (ID) prior to delete a delete even if no refresh has been done.  

Re: "As far I know, when you use auto increment for the ID, you will need to handle select identity".
    See my code in Page Load - I select on division  not on ID
    See my code in Insert - I add the 'division' value. Access (auto increment) and SQL (New Uniqueidentifier)  add the value of key ID. 

Re: If not, when you insert a row, the current ID is not correct.
   Well,  It somehow is in the Access Demo:
    - WebGrid  somehow knows the new key ID after I add a row to the Access database 
    - I do nothing extra in the Access example - not even 'refresh'.     
    - WebGrid gives no error if delete row after add row in Access demo.   How come? 
           (I do nothing to refresh and I do not click the client side refresh.)
    However, In SQL Demo:
     - WebGrid obviously does not know the new key ID after I add a row - gives error.
     - WHY NOT?   It knows the new key ID if I use Access database (AccessDataSource)
    -   My 'Inserting event'  code is exaclty the same for Access and for SQL. (same functionality)

-------------------------------------------------   

Re you need a copy of the SQL Database

I cannot just send you a copy.  It is SQL.  I could send you a backup, or a script or...

However - If you have access to an SQL server or server express you can get copy:

To obtain a copy or to view all the parameters of the SQL database:

First option.   You can make a connection in Visual Studio 2008 as follows
Open Server Explorer in Visual Studio 2008
Right click Data connections and select Add connection
Data source = SQL Server (SqlClient)
Server name = dbs5.dailyrazor.com
Select 'Use SQL Server Authentication'
   User Name:  webgridtest
   Password:    webgridpass
Select or enter a database name:   webgridtest
Click OK
You may now see all details of the SQL Database using the Server Explorer.

If you really need a copy of the SQL data base (I doubt that) you may use SQL Server management Studio to create a script to generate a copy on your machine or on your own SQL Server.   I can not help you other than to do the following to get started:

Open SQL Server management Studio Express or SQL Server Management Studio on your PC
1.  Connect to server:   Server name= dbs5.dailyrazor.com   Authentication= SQL Server Authentication   Login= webgridtest   password = webgridpass   
Click Connect.
2. Expand Databases,     scroll down until you find database webgridtest.
3. Expand webgrid test or perform tasks as you desire to get a copy. 

Please do NOT change the data types - they are as required for my application
 

Hi Dennis,

I am really sorry for the late response. I have tried to access your DB but I got "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections" error. But I have created simple DB that used GUID and I can replicate the issue.

I will discuss about this issue with the development team but for now you can refresh the grid from SqlDataSource1_Inserting event handler. You can use the following code to refresh the grid from server side:

WebGrid1.ClientAction.Refresh()

or from client-side:

ISGetObject("WebGrid1").Refresh();

Also if you want to get information about client-side reference, you can check our Client_FundamentalAPI.aspx and Client_FundamentalElement.aspx in WebGridSamples.

Hope this helps. I will get back to you once I got answer from our development team.

Best Regards,

Gordon Tumewu

Thank you for your reply - it was fast enough given the weekend and all.

I added WebGrid2.ClientAction.Refresh to my SqlDataSource1_Inserted(    event code and that solved my issue.

You do not have to spend anymore time on this if issue is not important to Intersoft Solutions

Dennis