[RESOLVED] gridview with bound sqldatasource

hi, did i miss something on the code? im using gridview bound to sqldatasource, the data source use a valid stored procedure with temporary tables return through tsql select command but im not getting any result in gridview.

vb Code:

With requisition_detailSqlDataSource
  .SelectCommandType = SqlDataSourceCommandType.StoredProcedure
 
  .SelectCommand = "sp_tempweb_requisition_status"
  .SelectParameters.Add("@requisition_no", TypeCode.String, TextBox1.Text.ToString)
   requisition_detailSqlDataSource.DataBind()
 
End With

Code:

---

ALTER PROCEDURE [dbo].[sp_tempweb_requisition_status]
        -- Add the parameters for the stored procedure here
@requisition_no varchar(20)
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        create table #tempweb_requisition_status(id varchar(20), lpono varchar(30), details varchar(50)
                ,unit varchar(30), req_qty float, rcvd_qty float, balance float, rcvd_from varchar(100))

        insert into #tempweb_requisition_status(lpono, details,unit, req_qty,rcvd_qty,balance, rcvd_from)
                select ...
                        where pk_=@requisition_no
       

        select * from #tempweb_requisition_status order by details

END

---

there should be a better workaround to do that. thanks for the help.

I don't see a reference to your gridview here.

gv.DataSource = <data back from your sproc>
gv.DataBind()

First of all do you get a result set by running your sp on the sql server?
I always use ( ) in the initial params but it may work your way.
There is no need to databand the gridview in code, you can do it on the markup.
Can you show us the relative markup?
In which part of your code you use the With requisition_detailSqlDataSource?
Sqldatasource has it's own events for handling the data, so as a brief example you would bind any select parameter in sqldatasouce_selecting event.
Also sqldtasource is not recommended in general and at all for large data manipulations( i just saved Gary some time, he would have said that for sure :) ).

@MMock the source is bind at markup

@sapator yes the sp works and it return rows but im not able to hook it on gridview
i use the requisition_detailsqldatasource in a user bindgrid procedure. the bindgrid() is call in one button.

Code:

---

<asp:GridView ID="requisition_detailGridView" runat="server" DataSourceID="requisition_detailSqlDataSource"
AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None"
Width="100&#37;">

  <Columns>

                           
    <asp:BoundField DataField="lpono" HeaderText="LPO No">
    <ItemStyle HorizontalAlign="Center" Width="10%" />
    </asp:BoundField>

    more columns here...
 </Columns>

 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
 <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
 <EditRowStyle BackColor="#999999" />
 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>

---

you mention not to use sqldatasource what would you suggest rather doing that way?

Quote:

---

Originally Posted by jlbantang

you mention not to use sqldatasource what would you suggest rather doing that way?

---

sapator is referring to the fact that you should be handing the connection to the database in some form of Business Logic Layer and Data Access Layer.

Coupling your UI directly to the Database, using a SqlDataSource is quite simply a bad idea.

Gary

i agree but rather i want to keep the code simple i would consider doing that next time :D
anyway the whole codes work as of now and i need to add running balance for each item at the stored procedure itself.

im looking at something like this:
http://i53.tinypic.com/2mynibt.gif

Hello,

There is always a fine balance between keeping the code "simple" and keeping it "easier to maintain". I would argue that the latter is what you should be striving for, and IMO, the SqlDataSource is definitely not that, but, anyway...

So, are you saying that the problem that you were having is now gone? And you are onto a new problem? If so, I am not clear on what you are asking?!?

Gary

hi gary, yes the i manage to make the code works. the data result when displayed to grid is also identical to the screenshot post #6. but im having issue how to make running balance for each item. again i used stored procedure sp_tempweb_requisition_status post #1 and it could also be the right place to take balance result if im not wrong.

thanks.

Hello,

You really have two options here.

1) You do the calculation within the Stored Procedure
2) You do the action once you pull the information back from the database. The easiest way to do this would be to use the RowDataBound event of the GridView and then sum the values.

Gary

Hello,

You really have two options here.

1) You do the calculation within the Stored Procedure
2) You do the action once you pull the information back from the database. The easiest way to do this would be to use the RowDataBound event of the GridView and then sum the values.

Gary

Hello.Finally internet is back(damn cable company :mad: ).
As Gary suggests there are 2 option with the rowdatabound been easier.
But the best way for me would be to handle this in the stored procedure.If you don't know how maybe you could ask at the database forum that i'm sure you will get the exact answer.
What i'm fully against is the "*" u use.I'm not evn sure if you can sum correctly by using "*" .It should only be used for testing purposes not to mention unnecessary columns brought up, even if you don't use them.
Don't use it, it's one of the worst "habits" programmers have.

You have not yet run the stored procedure, so you are just binding an empty dataview to the target.

So you need to add in a .Select() to run the procedure

What you have done is defined the SelectCommandType but have not run it yet.

Then the .Databind should be called from the object you are binding on, say a gridview (grd) - in this case it would be grd.DataBind()

Hope that helps

Quote:

---

Originally Posted by sapator

What i'm fully against is the "*" u use.I'm not evn sure if you can sum correctly by using "*" .It should only be used for testing purposes not to mention unnecessary columns brought up, even if you don't use them.
Don't use it, it's one of the worst "habits" programmers have.

---

I have to agree with this statement.

Gary

thanks all for the pointers. it work well in rowdatabound though i prefer doing that in SPROC itself.


@saparator i used '*' since i know i need to return all rows from SPROC. does it make any difference if i will explicitly mention the columns in the SELECT? but i would like to know why does it become a bad habit.

http://i55.tinypic.com/2laxwg.gif

No it actually does not make a difference to the functionality, except it is considered bad practise. There are many discussions/debates over the topic and the consenses is that you should really try and list the items.

I guess one of the big reasons is that the underlying tables could change and you could be returning great amounts of data without knowing it. - there are other good reason but you will need to do some reading.

I would suggest you list the items as you really don't want to get into bad habits.

From my experience with coding is that you will in fact learn the hard way and it will be painful - so try and do things correctly upfront and don't panic about the extra time it will cost in the beginning as it will pay itself back in the long run.

If your project is successful you will be maintaining it and adding to it for many years to come and you will pay for the short cuts/bad coding in the long run and it will be far more expensive than having done it correctely in the first place.

I hope this helps you.

Actually there is no debate for "*" because there is nothing positive about using it.
I agree with what BryanJ said.Also you cannot use vital sql functions with "*".Try using group by p.e. , you can't.
Ok if you are certain that you will never ever change the select and that you will never ever change data structure and that you will never ever have to modify your sproc then i guess you can leave it that way.Are you certain?

Quote:

---

Originally Posted by jlbantang

@saparator i used '*' since i know i need to return all rows from SPROC. does it make any difference if i will explicitly mention the columns in the SELECT? but i would like to know why does it become a bad habit.

---

Putting a * in the select does not change the amount of rows that are returned. It changes the number of columns that are returned. * gives you all the columns, where as naming the columns that you want only pulls back the requested columns.

Gary