Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Passing selected value to sql serverto retireve values using datagrids

Posted on 2005-04-09
30
Medium Priority
?
295 Views
Last Modified: 2010-04-07
i am having some trouble passing a value selected from a datagrid to a sql statement which then retrieves data and displays it into a datagrid.

basically, i want to get the module selected from one datagrid, then query that with the database to select all those documents associated with that module id and display them in a datagrid.

Here is my sqlquery:

sqlquery = "SELECT Document_ID, Document_Title, Module_ID, Submission_Date FROM TblDocuments INNER JOIN TblModule ON TblDocuments.Module_ID = TblModule.Module_ID = WHERE TblModule.Module_ID = " & Request.QueryString("Module_ID")

and this is what i put in the datagrid URL format string:

DownloadDocs.aspx?Module_ID={0}
0
Comment
Question by:Rpatel108
  • 14
  • 11
  • 4
29 Comments
 
LVL 7

Accepted Solution

by:
ayha1999 earned 1920 total points
ID: 13743839
Hi,

The hyperlink should be like this;

<asp:TemplateColumn HeaderText="Job Card No.">
<ItemTemplate>
<asp:HyperLink ID="YourID" Runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"YourDataFieldItem") %>' NavigateUrl='<%# String.Format("YourPage.aspx?VarName={0}",DataBinder.Eval(Container.DataItem, "YourDataFieldItem")%>'>
</asp:HyperLink>
</ItemTemplate>
</asp:TemplateColumn>

You can reterive the value in the other page:

Request.QueryString("VarName")

Hope this helps.

ayha
0
 

Author Comment

by:Rpatel108
ID: 13744085
It doesnt seem to like the code. i get red jagged lines underneath this: DataBinder.Eval(Container.DataItem,"YourDataFieldItem"

This what mine looks like at the moment:
<asp:HyperLinkColumn DataNavigateUrlField="Module_ID" DataNavigateUrlFormatString="DownloadDocs.aspx?Module_ID={0}" DataTextField="Module_ID" HeaderText="Module ID"></asp:HyperLinkColumn>
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13744156
Hi,

try this

<asp:HyperLink ID="yourID" Runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Module_ID") %>' NavigateUrl='<%# String.Format("DownloadDocs.aspx?Module_ID={0}",DataBinder.Eval(Container.DataItem, "Module_ID"))%>'></asp:HyperLink>

ayha
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Rpatel108
ID: 13744316
i get an error:

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30676: 'DataBinding' is not an event of 'System.Web.UI.WebControls.HyperLinkColumn'.

Source Error:

 

Line 23:                         <Columns>
Line 24:                               <asp:HyperLinkColumn DataTextField="Document_ID" HeaderText="ID"></asp:HyperLinkColumn>
Line 25:                               <asp:HyperLinkColumn DataTextField="Module_ID" Runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Module_ID") %>' NavigateUrl='<%# String.Format("DownloadDocs.aspx?Module_ID={0}",DataBinder.Eval(Container.DataItem, "Module_ID"))%>'></asp:HyperLinkColumn>
Line 26:                               <asp:HyperLinkColumn DataTextField="Submission_Date" HeaderText="Submission Date"></asp:HyperLinkColumn>
Line 27:                         </Columns>
 
i dont think it likes the databinding
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13744342
Hey,

Not HyperLinkColumn, make it HyperLink. Please paste the code I gave instead of yours which should work fine.

ayha
0
 

Author Comment

by:Rpatel108
ID: 13744378
yeah i did it as hyperlinkcolumn and it didn't like it! it said that the hyperlinkcolumn is not part of the datagrid
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13744416
Hi,

you put again hyperlinkcolumn not hyperlink. plase replace your hyperlinkcolumn with my hyperlink.

ayha
0
 

Author Comment

by:Rpatel108
ID: 13744439
ok i used your one that you gave above and i get an error:

Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: System.Web.UI.WebControls.DataGridColumnCollection must have items of type 'System.Web.UI.WebControls.DataGridColumn'. 'asp:HyperLink' is of type 'System.Web.UI.WebControls.HyperLink'.

Source Error:

<asp:HyperLink ID="Module_ID" Runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Module_ID") %>' NavigateUrl='<%# String.Format("DownloadDocs.aspx?Module_ID={0}",DataBinder.Eval(Container.DataItem, "Module_ID"))%>'></asp:HyperLink>
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 13746264
could u pls post ur current aspx code .. the datagrid part ...
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13746535
Hi,

I don't see anything wrong with the hyperlink. please post you datagrid part containing the hyperlink.

ayah
0
 

Author Comment

by:Rpatel108
ID: 13746855
                 <asp:DataGrid id="grdAttachments" style="Z-INDEX: 102; LEFT: 80px; POSITION: absolute; TOP: 88px"
                        runat="server" AutoGenerateColumns="False" Width="400px" Height="128px" BorderColor="#E7E7FF"
                        BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="0" GridLines="Horizontal"
                        CellSpacing="1">
                        <SelectedItemStyle Font-Bold="True" ForeColor="#F7F7F7" BackColor="#738A9C"></SelectedItemStyle>
                        <AlternatingItemStyle BackColor="#F7F7F7"></AlternatingItemStyle>
                        <ItemStyle ForeColor="#4A3C8C" BackColor="#E7E7FF"></ItemStyle>
                        <HeaderStyle Font-Bold="True" ForeColor="#F7F7F7" BackColor="#4A3C8C"></HeaderStyle>
                        <FooterStyle ForeColor="#4A3C8C" BackColor="#B5C7DE"></FooterStyle>
                        <Columns>
                              <asp:HyperLinkColumn DataTextField="Document_ID" HeaderText="ID"></asp:HyperLinkColumn>
                              <asp:HyperLink ID="Module_ID" Runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Module_ID") %>' NavigateUrl='<%# String.Format("DownloadDocs.aspx?Module_ID={0}",DataBinder.Eval(Container.DataItem, "Module_ID"))%>'></asp:HyperLink>

                              <asp:HyperLinkColumn DataTextField="Submission_Date" HeaderText="Submission Date"></asp:HyperLinkColumn>
                        </Columns>
                        <PagerStyle HorizontalAlign="Right" ForeColor="#4A3C8C" BackColor="#E7E7FF" Mode="NumericPages"></PagerStyle>
                  </asp:DataGrid>
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 13747062
u cannot add hyoerlinks directly to columns .. u will need a template column within which u will add the hyperlink
 <Columns>
<asp:TemplateColumn HeaderText="Job Card No.">
<ItemTemplate>
<asp:HyperLink ID="Module_ID" Runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Module_ID") %>' NavigateUrl='<%# String.Format("DownloadDocs.aspx?Module_ID={0}",DataBinder.Eval(Container.DataItem, "Module_ID"))%>'></asp:HyperLink>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13747264
Hey Rpatel108,

See my first psot where I have given correct hyperlink in the template column but you tried it wrong way that's what Rejojohny posted now.

ayha
0
 

Author Comment

by:Rpatel108
ID: 13747449
oh right i see... sorry my bad
0
 

Author Comment

by:Rpatel108
ID: 13747806
ok i did what you said, and the code runs fine. However once i click on the hyperlink i get an error message saying:

Invalid column name 'C221'. - this is the hyperlink that i have selected.

this is my code for the other page that should be displaying the documents:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here

        Dim sqlcon As SqlConnection
        Dim myDataReader As SqlDataReader
        Dim sqlquery As String
        Dim cmdSelectDoc As SqlCommand

        sqlcon = New SqlConnection("server=localhost;uid=sa;pwd=;database=stmdb")
        sqlquery = "SELECT Document_ID, Document_Title, Submission_Date FROM TblDocuments INNER JOIN TblModule ON TblDocuments.Module_ID = TblModule.Module_ID WHERE TblModule.Module_ID =  " & Request.QueryString("Module_ID")
        cmdSelectDoc = New SqlCommand(sqlquery, sqlcon)

        sqlcon.Open()
        mydatareader = cmdSelectDoc.ExecuteReader
        grdAttachments.DataSource = myDataReader
        grdAttachments.DataBind()
        sqlcon.Close()
    End Sub




0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13747866
Hi,

Where this error occurs? In the same page where you hvve grid or in the redirected page?

ayha
0
 

Author Comment

by:Rpatel108
ID: 13748006
ok i think its when the page is re-directed, i will paste the entire error you might be able to tell more than me:

Server Error in '/StudentApp' Application.
--------------------------------------------------------------------------------

Invalid column name 'C221'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'C221'.

Source Error:


Line 38:
Line 39:         sqlcon.Open()
Line 40:         mydatareader = cmdSelectDoc.ExecuteReader
Line 41:         grdAttachments.DataSource = myDataReader
Line 42:         grdAttachments.DataBind()
 

Source File: c:\inetpub\wwwroot\StudentApp\DownloadDocs.aspx.vb    Line: 40

Stack Trace:

[SqlException: Invalid column name 'C221'.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
   System.Data.SqlClient.SqlCommand.ExecuteReader() +42
   StudentApp.DownloadDocs.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\StudentApp\DownloadDocs.aspx.vb:40
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13748108
Hi,

first make sure that you have correctly spelled databse field names in the query then makde correct data type is done.

Is Module_ID a Char or Int?

Put some break points and see what you get in "sqlquery"?

ayha
0
 

Author Comment

by:Rpatel108
ID: 13749415
i think that might be the problem, the Module_ID is varchar, e.g. C221 and it is not auto incremented.
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13750042
Hi,

I think if you want to autoincrement then go to sql table design view, put datatype as int and Identity = yes.  Or through coding also you can do that.

ayha
0
 

Author Comment

by:Rpatel108
ID: 13750920
no i dont want to auto increment it, but i was thinking that the datatype of the Module_Id was causing a problem- i'm not sure to be honest.
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13751694
Hi,

What is the datatype of module_id?

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here

        Dim sqlcon As SqlConnection
        Dim myDataReader As SqlDataReader
        Dim sqlquery As String
        Dim cmdSelectDoc As SqlCommand
        dim txtID as string = Request.QueryString("Module_ID")

        sqlcon = New SqlConnection("server=localhost;uid=sa;pwd=;database=stmdb")
        sqlquery = "SELECT Document_ID, Document_Title, Submission_Date FROM TblDocuments INNER JOIN TblModule ON TblDocuments.Module_ID = TblModule.Module_ID WHERE TblModule.Module_ID =  " & txtID & "

        cmdSelectDoc = New SqlCommand(sqlquery, sqlcon)

        sqlcon.Open()
        mydatareader = cmdSelectDoc.ExecuteReader
        grdAttachments.DataSource = myDataReader
        grdAttachments.DataBind()
        sqlcon.Close()
    End Sub

If Module_Id is VarChar then = '" & txtID & "'.

If prblem stil persists then just remove  =  " & txtID &  from query and try to make sure that prblem is from module_id.  when displaying your datagrid, did u give any format for module_id column?

ayha

0
 
LVL 26

Assisted Solution

by:Rejojohny
Rejojohny earned 80 total points
ID: 13751811
>> i think that might be the problem, the Module_ID is varchar, e.g. C221 and it is not auto incremented.

then ur sql statement should have the module Id within single quotes .. like this
        sqlquery = "SELECT Document_ID, Document_Title, Submission_Date FROM TblDocuments INNER JOIN TblModule ON TblDocuments.Module_ID = TblModule.Module_ID WHERE TblModule.Module_ID =  '" & Request.QueryString("Module_ID") & "'"

notice the single quotes before and after the double quotes
TblModule.Module_ID =  '" & Request.QueryString("Module_ID") & "'"
0
 

Author Comment

by:Rpatel108
ID: 13755485
using the single quotes worked.

can i ask why is there single quotes? sorry i'm just learning asp.net

thank you very much to the both of you... you have helped me tremendiously.
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13755576
Hey Rptel108,

I have already posted correct solution for you. But you didn't accept my solution, why?

dim txtID as string = Request.QueryString("Module_ID")
If Module_Id is VarChar then = '" & txtID & "'.

This's what Rejojonny, posted now. Anohter thing is, I have given you right hyperlink too. but you splitted ponts 50/50.

I am waiting for you reply to complain to the community.

ayha

0
 

Author Comment

by:Rpatel108
ID: 13755726
i clicked on both yours and Rejojonnny answer as you did give me the right answer, and rejojonnny gave me the query string with the single quotes. i thought that by clicking on both that both answers were accepted as the right answer, was it not? I'm sorry but i did mean to give you the credit of the accepted answer as well.

As for the points he helped me as much as you did. i'm sorry i did mean to accept your answer. i didn't realise that it didn't accept both

0
 

Author Comment

by:Rpatel108
ID: 13755755
its the first time i have split points, i did it wrong i'm sorry but i did intend to give you the credit for the right solution
0
 

Author Comment

by:Rpatel108
ID: 13755924
thank you for reopening the question.

ayha,

i have accepted your answer as the correct answer. I read through the comments again to give the points accordingly, after reading through you have provided the most assistance so i awarded you with most of the points and i awarded rejojonny 20 points for giving me the select statement in single quotes which worked.

I hope this rectifies the issue?

Rpatel
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 13779086
ayha,
Ok .. Now is this not fair .. is it? I agree that you pointed out the answer, but just my mentioning the answer is not enough is it? u have to make sure that the author understands it too .. i noticed that even after u provided the answer Rpatel108 was facing problems and that is the reason y i asked him to provided the code for the datagrid because I was sure that he was not following the complete instructions and I wanted to make changes to his existing code and show him how it is done .. the second time about the single quotes, if u notice I identified exactly where the problem is and did not give any long explanation and aked him to make the required changes in the exact place .. quoting his line where he mentions that the field is Varchar .. I did  not ask him to change the table structure or any property of a field .. I thought the initial split of points was fair as both put in equal efforts to help Rpatel108 out ..
anyway ...

Rpatel108,
I am not asking you to again change the grade nor am I thretening that I will complian to the support team, as I am sure I will be able to earn it somewhere else .. The next time make up your mind what you want to do and stick to it giving proper explanation.

At the end you asked
>>can i ask why is there single quotes? sorry i'm just learning asp.net
The reason is that the field is a Varchar .. u will requre the single quotes for a field having date but NOT for numeric fields ..

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

571 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