Solved

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

Posted on 2008-06-10
15
974 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:TreSum
  • 8
  • 7
15 Comments
 
LVL 33

Expert Comment

by:raterus
ID: 21752491
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"
0
 

Author Comment

by:TreSum
ID: 21753023
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?

0
 
LVL 33

Expert Comment

by:raterus
ID: 21753811
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

0
 

Author Comment

by:TreSum
ID: 21768172
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.
0
 
LVL 33

Expert Comment

by:raterus
ID: 21768231
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.
0
 

Author Comment

by:TreSum
ID: 21768354
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
0
 

Author Comment

by:TreSum
ID: 21768658
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 33

Expert Comment

by:raterus
ID: 21778390
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?
0
 

Author Comment

by:TreSum
ID: 21778723
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?
0
 
LVL 33

Expert Comment

by:raterus
ID: 21779123
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?

0
 

Author Comment

by:TreSum
ID: 21801470
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
0
 
LVL 33

Expert Comment

by:raterus
ID: 21802490
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" />
0
 

Author Comment

by:TreSum
ID: 21802744
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!
0
 
LVL 33

Accepted Solution

by:
raterus earned 500 total points
ID: 21803120
When you are binding the label, you are binding from the master sqldatasource's view.  You would need a "Param" field there.  Do you have a "Param" field in the [Clients] table?

0
 

Author Closing Comment

by:TreSum
ID: 31465665
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now