asked on

Assistance required with creating a well formed SQL Query working with DXeperience GridViews

I am experiencing a bit of difficulty using ASP.Net Visual Web Developer Express, MS SQL Server express and Devexpress's Dxperience ASPxGridview.

I have already created a mastergrid and a detailed tabbed grid to display information based on the information selected out of the mastergrid but i'm having some difficulty passing a value from the mastergrid into the detailed grid as follows:

Mastergrid has a "KeyFieldName="Name" which is the primary data key for my database table Client. I then use a C# Snipped of code to pass this key to the detailed data source in order to match up the "Name" stored in my detailed grid's database table which is Note; it has a foreign key called "Name" which is linked to the Client table.

Now what i'm trying to achieve is that on inserting that the value stored in Stored in Client.Name be set to the Note.Name. I've experimented with the SQL Query which i built using the custom database connection wizard in VWD Express but i'm missing something in order to set the value to the value from the primary grid.

ASP.NET:
 
//Mastergrid Header
<dxwgv:ASPxGridView ID="masterGrid" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
            KeyFieldName="Name"ClientInstanceName="masterGrid">
 
//Tabbed Detailedgrid Header
                            <dxtc:TabPage Text="Notes" Visible="true">
                                <controls>
                            <dxwgv:ASPxGridView ID="noteGrid" runat="server" AutoGenerateColumns="False" 
 DataSourceID="SqlDataSource4" 
                                KeyFieldName="NoteID" Width="100%" OnBeforePerformDataSelect="noteGrid_DataSelect">
 
C#:
//Code Snipped
    protected void noteGrid_DataSelect(object sender, EventArgs e)
    {
        Session["Param2"] = (sender as ASPxGridView).GetMasterRowKeyValue();
    }
 
SQL:
//
    <asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:EdonaiConnectionString %>"
            DeleteCommand="DELETE FROM [Notes] WHERE [NoteID] = @NoteID" InsertCommand="INSERT INTO [Notes] ([Name], [Note], [DateTime]) VALUES (@Name, @Note, @DateTime)"
            SelectCommand="SELECT * FROM [Notes] WHERE ([Name] = @Param2)" UpdateCommand="UPDATE [Notes] SET [Name] = @Name, [Note] = @Note, [DateTime] = @DateTime WHERE [NoteID] = @NoteID">
            <SelectParameters>
                <asp:SessionParameter Name="Param2" SessionField="Param2" Type="String" />
            </SelectParameters>
            <DeleteParameters>
                <asp:Parameter Name="NoteID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="Note" Type="String" />
                <asp:Parameter Name="DateTime" Type="DateTime" />
                <asp:Parameter Name="NoteID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="Name" Type="String" Direction="Input" />
                <asp:Parameter Name="Note" Type="String" />
                <asp:Parameter Name="DateTime" Type="DateTime" />
            </InsertParameters>
        </asp:SqlDataSource>

Open in new window

raterus

I have no idea what your problem is, however I do want to point out the syntax error in line 5 of your code snippet.

KeyFieldName="Name"ClientInstanceName="masterGrid"

Obviously, there should be a space after "Name"

TreSum

ASKER

thank you, for your reply. although the syntax is correct in the original code just missed the space when i copied the code over.

basically what i am trying to achieve is to find a way to pass the value of the master grid "Name" in table Client into the data that is being used in the second grid which uses a table Note with a data column "Name", which is a foreign key of the Client table. Because currently the user needs to type in the Client "Name" himself in the Note grid in order to link the Note to the appropriate Client.

What i'm trying to achieve is that the user will be able to automatically link up a note with a client when inserting a new note.

Is it possible to pass the value of Client.Name (which is selected in the master grid) into Notes.Name [Foreign Key] on insert?

I hope that makes a bit more sense?

raterus

First off, Sqldatasource4 should be right next to your nested grid.

Look at the example below and let me know if it works, I'm using a control parameter and setting the value through a hidden label field.

<dxtc:TabPage Text="Notes" Visible="true">
  <controls>
    <dxwgv:ASPxGridView ID="noteGrid" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource4" KeyFieldName="NoteID" Width="100%" OnBeforePerformDataSelect="noteGrid_DataSelect"></dxwgv:ASPxGridView>
    <asp:label id="lblParam2" runat="server" visible="false" Text='<%# Bind("param2") %>' />
    <asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:EdonaiConnectionString %>"
            DeleteCommand="DELETE FROM [Notes] WHERE [NoteID] = @NoteID" InsertCommand="INSERT INTO [Notes] ([Name], [Note], [DateTime]) VALUES (@Name, @Note, @DateTime)"
            SelectCommand="SELECT * FROM [Notes] WHERE ([Name] = @Param2)" UpdateCommand="UPDATE [Notes] SET [Name] = @Name, [Note] = @Note, [DateTime] = @DateTime WHERE [NoteID] = @NoteID">
            <SelectParameters>
                <asp:controlparameter name="Param2" controlid="lblParam2" Propertyname="Text"/>
            </SelectParameters>
            <DeleteParameters>
                <asp:Parameter Name="NoteID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="Note" Type="String" />
                <asp:Parameter Name="DateTime" Type="DateTime" />
                <asp:Parameter Name="NoteID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="Name" Type="String" Direction="Input" />
                <asp:Parameter Name="Note" Type="String" />
                <asp:Parameter Name="DateTime" Type="DateTime" />
            </InsertParameters>
        </asp:SqlDataSource>

Open in new window

TreSum

ASKER

Thank you for your reply and for assisting me! I'm trying to implement the code and i must admit to you that i haven't used a controlparameter in this manner before but it makes sense to me to try and use it to store the value of 'Name' into a hidden field. But here is where i need some extra guidance.

Now i have the value for name bound to lblParam2 now how to i set the value into the InsertCommand? I would imagine it would be something like:

InsertCommand="INSERT INTO [Notes] ([Name], [Note], [DateTime]) VALUES ((@Name = @Param2), @Note, @DateTime)"

But i'm not really sure of the syntax that must be used to form such a commend. This would allow the value from the first grid (in theory) to pass to a field automatically (based on the selected entry) into that of the second grid.

raterus

Wouldn't it just be something simple like this?

InsertCommand="INSERT INTO [Notes] ([Name], [Note], [DateTime]) VALUES (@Param2, @Note, @DateTime)"

If not, sorry, I'm not sure I really understand your issue.

TreSum

ASKER

I'm going to attach the two code segments, the ASP.NET and the C#. Maybe it'll help you understand what i'm trying to achieve... i just tried to implement the code and got the follow message:

Could not find control 'Param2' in ControlParameter 'lblParam2'.

Please have a look and see if it makes sense too you.
Page-Language.doc

TreSum

ASKER

I quickly troubleshooted the above code and got it to function perfectly except for the INSERT Command.

I know have a mastergrid which gives a summary of a client's details such as Name, Registration number, Year, VAT etc. which can INSERT/DELETE and UPDATE without effort. When you click that it opens the detailed grid it will display any notes made for that SPECIFIC client based on his primary key Id.

Then you can INSERT/DELETE and UPDATE without any issues but the pain is that for every new note that you want to insert you have to manual type in the client Id as to link up the note in the Note Table with the Client in the Client Table.

Now what i want to do is have it automatically assign the Id based on what record you've chosen from the main grid. I've tried originally setting it as you suggested:

InsertCommand="INSERT INTO [Notes] ([Name], [Note], [DateTime]) VALUES (@Param2, @Note, @DateTime)"

but it gave me an error along the lines of Param2 not being a scalable variable?

Please have a look at the new code attached to this post... i feel so close to getting this right but just missing this one little code...

Thank you again for all your help thus far!
Reworked.doc

raterus

Sorry, I was away from my computer all day yesterday. Still stumpped? I looked at your code shortly, the "Reworked.doc", and I didn't see anything like what we've been talking about, using a ControlParameter and a parameter named Param2?

TreSum

ASKER

It's not a problem, thank you very much for you help!

Tried to apply the ControlParameter into my test project but unfortunately i kept receiving errors.

I then reworked my code and used a SelectParameter instead (which i understand a bit better), that got my code working 100%. Except allowing me to automatically assign the value.

Could you possible help me implement the code into my "reworked.doc", it would basically replace the SelectParameter if my understanding is correct?

raterus

I think the first thing you need to do is move the "noteData" SqlDataSource, so it's right next to the "noteGrid", in the <DetailRow>.

Also, what was wrong with the ControlParameter? I'd really like to work toward that solution, and ignore the Session use completely.

Can you rework your code up until the point you got the "Param2 not being a scalable variable?" and show me what code you were using, so we can just fix that?

TreSum

ASKER

Hi Raterus,

Sorry i have been away from my computer with our long weekend this side. But have now reworked the code to include a Control Parameter instead of the Session Parameter.

I'm running into a bit of a hassle loading the detailed grid when i click on it, getting an error:
"Databinding: 'DevExpress.Web.Data.WebCachedDataRow' does not contain a property with the name 'Param' "

I renamed Param2 to Param... since there's only one in the code.

Other than that i moved the DataSource next to the detailedgrid.

Would you have a look at the code attached?! I think i've missed type somewhere... maybe in the C# code-behind file? Sure I'm not suppose to declare Session[Param]?
Reworked.doc

raterus

Make sure you set the "PropertyName" in the ControlParameter. This is the property of the label control you want to get the value from, so in this case it's "Text"

<asp:ControlParameter Name="Param" Controlid="lblParam" Type="Int32" PropertyName="Text" />

TreSum

ASKER

I've added the "propertyname="text" to the ControlParameter string but the error still presists. It's not finding Param it seems. I'll still try making changes to see if i can get it to work!

ASKER CERTIFIED SOLUTION

raterus

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

TreSum

ASKER

I contacted DevExpress themselves in regard to my problem and in the end they actually had an easy built in solution to pass the value.