Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

SQLDataSource Procedures and Parameters

I have been working on using SQLDataSources to display a grid of data with Stored procedures to add/update and delete the records the grid displays.  It has been a bit tricky with a fair amount of searching and trial and error.

Finally,  I can run the add/update proc passing it it's parameteres using the upate parematers, but the key field,  which I do not want to be editable and is set as readonly,  does not get passed and the proc runs and insert instead of an update.

Can anyone point me at a complete solution which uses a gridview and sql datasources to populate and manage the data?
<asp:SqlDataSource ID="SqlDataSourceClientLogin" runat="server" 
          ConnectionString="<%$ ConnectionStrings:WHOOZIE_DSN %>" 
          SelectCommand="SELECT ClientGuid as pClientGuid,  PhoneNumberGuid, PWD as pPWD, Salutation as pSalutation, FName as pFName, LName as pLName, Title as pTitle, MgmtLevel as pMgmtLevel, Email as pEmail FROM ClientLogin" 
          UpdateCommandType="StoredProcedure" 
          UpdateCommand="AddUpdateClient"      
          DeleteCommand="DeleteClient"
          DeleteCommandType="StoredProcedure"
          >     
          
          <UpdateParameters>
            <asp:Parameter  Name="pClientGuid" Type="String" Size="36" Direction="Input" DefaultValue="2e0d45b4-8fce-4c31-a283-d28beb23d15a"/>      
            <asp:Parameter  Name="pPWD" Type="String" Size="50" Direction="Input" DefaultValue="123" ConvertEmptyStringToNull="true" />      
            <asp:Parameter  Name="pSalutation" Type="String" Size="10" Direction="Input" DefaultValue="Mr." ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pFName" Type="String" Size="50" Direction="Input" DefaultValue="Howie" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pLName" Type="String" Size="50" Direction="Input" DefaultValue="Bashevkin" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pTitle" Type="String" Size="50" Direction="Input" DefaultValue="Bozo" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pMgmtLevel" Type="String" Size="50" Direction="Input" DefaultValue="King" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pEmail" Type="String" Size="80" Direction="Input" DefaultValue="howardbash@yahoo.com" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pWorkedOnClientGuid" Type="String" Size="36" Direction="Output" />            
        </UpdateParameters>          
        
        <DeleteParameters>
            <asp:Parameter Name="pClientGuid" Type="String" Size="36" Direction="Input" /> 
        </DeleteParameters>
        
       </asp:SqlDataSource>
 
 
 
       <asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSourceClientLogin" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">      
        <Columns>
 
          <asp:BoundField DataField="pClientGuid" HeaderText="ClientGuid" ReadOnly="true"  />  
          <asp:BoundField DataField="PhoneNumberGuid" HeaderText="PhoneNumberGuid"  ReadOnly="true" />  
          
          <asp:BoundField DataField = "pPWD" HeaderText="Password" />  
          <asp:BoundField DataField = "pSalutation" HeaderText="Salutation" />  
          <asp:BoundField DataField = "pFName" HeaderText="First Name" />  
          <asp:BoundField DataField = "pLName" HeaderText="Last Name" />  
          <asp:BoundField DataField = "pTitle" HeaderText="Title" />  
          <asp:BoundField DataField = "pMgmtLevel" HeaderText="MgmtLevel" />  
          <asp:BoundField DataField = "pEmail" HeaderText="Email" />           
          <asp:CommandField ShowEditButton="True" ShowDeleteButton="true"  />          
        </Columns>
        
        <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
        <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
        <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="Gainsboro" />        
      </asp:GridView>

Open in new window

0
Howard Bash
Asked:
Howard Bash
3 Solutions
 
meispiscesCommented:
please mention the stored procedure which you have written for code attached in the question
0
 
Howard BashSenior Software EngineerAuthor Commented:
CREATE PROCEDURE [dbo].[AddUpdateClient]
    @pClientGuid uniqueidentifier,
    @pPWD varchar(50),
    @pSalutation nchar(10),
    @pFName varchar(50),
    @pLName varchar(50),
    @pTitle varchar(50),
    @pMgmtLevel varchar(50),
    @pEmail varchar(80),
    @pWorkedOnClientGuid uniqueidentifier output

AS

BEGIN
   declare @LookupGuid uniqueidentifier;
   declare @bq uniqueidentifier;

   select @LookupGuid = A.ClientGuid from dbo.ClientLogin A Where A.ClientGuid = @pClientGuid;

   if @LookupGuid is null
     begin
      --Add
        set @bq = newID();
        insert into dbo.ClientLogin
          (
                  ClientGuid,
            pwd,
            Salutation,
            FName,
            LName,
            Title,
            MgmtLevel,
            Email
          )            
        values
          (
                  @bq,
                  @pPWD,
                  @pSalutation,
                  @pFName,
                  @pLName,
                  @pTitle,
                  @pMgmtLevel,
                  @pEmail
          );
     end
   else
      --Update
     begin
        update dbo.ClientLogin
           Set
              pwd = @pPWD,
                Salutation = @pSalutation,
              FName = @pFName,
              LName = @pLName,
              Title = @pTitle,
              MgmtLevel = @pMgmtLevel,
              Email = @pEmail              
          where ClientGuid = @pClientGuid;

          set @bq = @pClientGuid;

     end;

    set @pWorkedOnClientGuid = @bq;

END
0
 
Howard BashSenior Software EngineerAuthor Commented:
So, having posted these code sections,  the questions are:
1) Why does it appear that the update parameter pClientGuid does not appear to be passed to the stored procedure and so on the update,  it passes a null and a row is inserted instead of updating the row whose key (pClientGuid) I thought it would be getting passed to it?
2) The same question for the delete proc which does not apparently get the pClientGuid from the delete parameter list.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
SharathData EngineerCommented:
Your SP is looking fine. can you add highlighted PRINT statements for debugging purpose. and try to execute SP. Lets see whats happeneing.

CREATE PROCEDURE [dbo].[AddUpdateClient]
@pClientGuid uniqueidentifier,
@pPWD varchar(50),
@pSalutation nchar(10),
@pFName varchar(50),
@pLName varchar(50),
@pTitle varchar(50),
@pMgmtLevel varchar(50),
@pEmail varchar(80),
@pWorkedOnClientGuid uniqueidentifier output
AS
BEGIN
declare @LookupGuid uniqueidentifier;
declare @bq uniqueidentifier;
select @LookupGuid = A.ClientGuid from dbo.ClientLogin A Where A.ClientGuid = @pClientGuid;
print '@LookupGuid is ' + @LookupGuid
if @LookupGuid is null
begin
--Add
print 'Inside if'
print @LookupGuid

set @bq = newID();
insert into dbo.ClientLogin
(
ClientGuid,
pwd,
Salutation,
FName,
LName,
Title,
MgmtLevel,
Email
)
values
(
@bq,
@pPWD,
@pSalutation,
@pFName,
@pLName,
@pTitle,
@pMgmtLevel,
@pEmail
);
end
else
--Update
begin
print 'in else part'
print @LookupGuid

update dbo.ClientLogin
Set
pwd = @pPWD,
Salutation = @pSalutation,
FName = @pFName,
LName = @pLName,
Title = @pTitle,
MgmtLevel = @pMgmtLevel,
Email = @pEmail
where ClientGuid = @pClientGuid;
set @bq = @pClientGuid;
end;
set @pWorkedOnClientGuid = @bq;
END
0
 
GiftsonDJohnCommented:
Hi,

Can you try this.

Instead of setting Readonly=true set InsertVisible=false


<asp:BoundField DataField="pClientGuid" HeaderText="ClientGuid" InsertVisible="false"  />  
         
0
 
Howard BashSenior Software EngineerAuthor Commented:
GiftsonDJohn,
I will try your suggestion this evening (EST).

Thanks.
0
 
Howard BashSenior Software EngineerAuthor Commented:
OK.  I have set the grid column to :
          <asp:BoundField DataField="pClientGuid" HeaderText="ClientGuid" ReadOnly="true"  />  
as you suggested above and that indeed passes the field value in to the add/update stored proc that I am using which is great.

But the delete does not seem to "see" that same value so that my delete SP is not getting the pClientGuid to allow it's deleting a row and tells me it cannot fund @pClientGuid although as you see from the snippet below and the delete proc,  I a supplying them.





        <DeleteParameters>
            <asp:Parameter Name="pClientGuid" Type="String" Size="36" Direction="Input" /> 
        </DeleteParameters>
 
 
 
 
CREATE PROCEDURE [dbo].[DeleteClient]
 
    @pClientGuid uniqueidentifier
 
AS
BEGIN
    delete dbo.ClientLogin
    Where ClientGuid = @pClientGuid;
 
END

Open in new window

0
 
GiftsonDJohnCommented:
Yes. The declaration is fine. But you have to pass the value to the parameter.

put DataKeyNames ="pClientGuid " in GridView
0
 
Howard BashSenior Software EngineerAuthor Commented:
I made that change and when I click the update (which works without adding the DataKeyNames attribute),  I get the following:

Object must implement IConvertible ....

Also I get this same error on click the delete link on the grid.



0
 
Howard BashSenior Software EngineerAuthor Commented:
Another thought about this.  Could this be related to the Guid parameters which are defined in the sql table and the proc param type as uniqueidentifier whereas in my aspx code I have declared these columns as string and length 36 which definitely works with my add/update stored procedure.
0
 
Howard BashSenior Software EngineerAuthor Commented:
And to ask the obvious why doesn't Microsoft have a uniqueidentifier type for an aspx page's parameter declaration?  How much of this iConvertible is about that?
0
 
Howard BashSenior Software EngineerAuthor Commented:
Ok.  I found the trick/answer.  To use the sql server data type uniqueidentifier with the sqldatasource control,  when you specifiy the guid as the datakeyname and also want to specifiy that parameter within your associated stored procedures,  you need to leave off the type="..." and the size="..." from the paramer declaration so in my case:

          <UpdateParameters>
            <asp:Parameter  Name="pClientGuid" Direction="Input" DefaultValue="2e0d45b4-8fce-4c31-a283-d28beb23d15a"/>      
            <asp:Parameter  Name="pPWD" Type="String" Size="50" Direction="Input" DefaultValue="123" ConvertEmptyStringToNull="true" />      
            <asp:Parameter  Name="pSalutation" Type="String" Size="10" Direction="Input" DefaultValue="Mr." ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pFName" Type="String" Size="50" Direction="Input" DefaultValue="Howie" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pLName" Type="String" Size="50" Direction="Input" DefaultValue="Bashevkin" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pTitle" Type="String" Size="50" Direction="Input" DefaultValue="Bozo" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pMgmtLevel" Type="String" Size="50" Direction="Input" DefaultValue="King" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pEmail" Type="String" Size="80" Direction="Input" DefaultValue="howardbash@yahoo.com" ConvertEmptyStringToNull="true"/>      
            <asp:Parameter  Name="pWorkedOnClientGuid" Type="String" Size="36" Direction="Output" />            
        </UpdateParameters>          
       
        <DeleteParameters>
            <asp:Parameter Name="pClientGuid" Direction="Input" />
        </DeleteParameters>

 
0
 
Howard BashSenior Software EngineerAuthor Commented:
All good suggestions leading to a working solution.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now