Convert Gridview To Report
Nov 6, 2006Dear all,
May I know have anyone try to convert the gridview which bind to a sqldatasource to a report (any kind of report format)?
Thanks
ADVERTISEMENT
Error Editing Data Records In Gridview When Using Convert Function.
Jan 24, 2008Hello, i currently have a gridview that is populated with data from a SQLServer datasource. I have put an output mask in the select statement, so the date and time attributes are displayed in the format i prefer them to be in. SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment];
However when i click the 'edit' link for a record in the gridview, i am unable to edit the date/time attributes and when i click update to confirm any changes to the other attributes, the values in the date/time attributes are emptied. How can i solve this update problem. I'm guessing i need to configure my SQL UPDATE statement, but bit stuck how i do this. Please help!
Thanks,
James
Want Error Message To Appear When No Database Results Were Returned In GridView, Also Other GridView Issues.
Jun 12, 2008Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
View 2 Replies View RelatedConvert From SQL Report 2005 To SQL Report 2000
Jan 10, 2008Hi..
I have made a big mistake. I was suppose to make a report for SQL 2000 but I made it in 2005. Now the Reporting server does not take the report. Obviously, as suggested in other topic here that 2005 report wont work on 2000 server.
Is there any way of converting this report from 2005 to 2000 so that I dont have to redesign it again.
thanks.
Convert Crystal Report To Pdf
Sep 27, 2007How can I convert a crystal report to a pdf format.ASP.net is not loaded in my machine.pls help me...
View 3 Replies View RelatedConvert Report In PDF Format
Dec 6, 2006Hi,
I have three differents sub-reports, each one inside a table object.
Each table has a property "page break-before" enable.
When I execute the view-report in HTML 4.0 format, the reports are executed with success, but when I export to PDF format, the property "page break-before" inserts a blank page at the end of each sub-report. How can I do to suppress the blank-pages when exporting to PDF format ??
How To Convert Cube To Report?
May 15, 2006hi,
How to convert cubes , which are generated by Analysis Services, to generate end user reports like pivot tables for excell, pdfs etc. by using Report server.
ur cooperation is highly appreciated.
Satya
Convert Access Report To SSRS
Feb 12, 2008hi,
I'm using this query to generate a report. When I preview the report I should be able to select values from parameter drop down list for the following fields.
OfficerName
OfficerId,
BorrowerName
frmMain( is access form type. wonder how do I get this as a parameter to my ssrs report. how do I make available data set for this one?. I can not see available data set for this one when i click drop down menu in report parameter box)
Forms
ToDate
FromDate
But when I run the query I can see only date parameters. I can not see anyotehr parameters. I can add parameter and select fields like officerName, OfficerId etc. But it does not work when i preview it.
Actually one of the developers has created a stored proceedure for this report. He already created an Access report, What I really want to do is recreate it in SSRS or convert into SSRS. But in either way, this parameter thing does not work.
Here is the code;
Code Snippet
CREATE PROCEDURE [dbo].[GET_EXPENSES_REIMB_OFFICER]
(@FromDate DateTime, @ToDate DateTime)
AS
SELECT
BorrowerName,
TExpense,
TReimbursement,
CREATE_BY_DT,
OFFICER_ID,
RC_NUM,
OfficerName,
FROM (
SELECT
b.Title AS BorrowerName,
m.INVOICE_TOTAL_AMT AS TExpense,
0 AS TReimbursement,
CONVERT(varchar, m.EFFECTIVE_DT, 101) as CREATE_BY_DT,
m.OFFICER_ID,
m.RC_NUM,
o.OfficerName,
FROM EXPENSE m
INNER JOIN .adBorrowerObligation bo on m.Borrower_Obligation_ID = bo.BorrowerObligationID
INNER JOIN .entity_to_obligation eto on bo.BorrowerObligationID = eto.Borrower_Obligation_ID and eto.entity_type_id = 1
INNER JOIN .adEntity b on eto.Entity_ID = b.EntityID
LEFT OUTER JOIN OFFICER o ON m.OFFICER_ID = o.Enum
WHERE (m.EFFECTIVE_DT >= @FromDate)
AND (m.EFFECTIVE_DT <= @ToDate)
UNION ALL
SELECT
b.Title AS BorrowerName,
0 AS TExpense,
r.REIMBURSEMENT_AMT AS TReimbursement,
CONVERT(varchar, r.CREATE_BY_DT, 101)as CREATE_BY_DT,
r.LENDER_ID as OFFICER_ID,
r.RC_NUM,
o.OfficerName,
FROM REIMBURSEMENT r
INNER JOIN adBorrowerObligation bo on r.Borrower_Obligation_ID = bo.BorrowerObligationID
INNER JOIN entity_to_obligation eto on bo.BorrowerObligationID = eto.Borrower_Obligation_ID and eto.entity_type_id = 1
INNER JOIN adEntity b on eto.Entity_ID = b.EntityID
LEFT OUTER JOIN OFFICER o ON r.Lender_ID = o.Enum
WHERE (r.CREATE_BY_DT >= @FromDate)
AND (r.CREATE_BY_DT <= @ToDate)
)
ad
ORDER BY OFFICERNAME DESC, CREATE_BY_DT, BorrowerName
can anyone help me out to design this report?
Thanks in advance
Convert Sum(a,b) From Crystal Report To SSRS
Oct 19, 2007Hi all,
need help to convert Sum(a, b) i found in Crystal Report to SSRS.
a = the dataset that i want to subtotal
b = the group
thanks!
Addin
Convert VB Script To Function In SQL Server Report
Nov 9, 2006Hello Everyone,
I do not see the forum for Reporting Services, so I will post this here for now. I have a VB Script that I would like to use as a Function to return the Weekday Name for me in Reporting Services.
[code]
Function rDateTime(rDate, rTime)
DIM myDay, myDayName, myMonthName , myYear, myTime
DIM myAMPM, myHour, myColon, myMinute
'get AM or PM from the time
myAMPM = Right(rTime,2)
'get Hour and Minute
myColon = instr(1, rTime, ":",vbBinaryCompare)
IF myColon = 2 THEN
myHour = Left(rTime,1)
myMinute = Mid(rTime, 3, 2)
ELSE
myHour = Left(rTime,2)
myMinute = Mid(rTime, 4, 2)
END IF
'concatenate the time
myTime = myHour & ":" & myMinute & " " & myAMPM
'get weekday number
myDayName = weekday(rDate)
'convert weekday number to weekday name
myDayName = weekdayname(myDayName)
'get the day, e.g. oct 19, 2002, will return 19
myDay = Day(rDate)
'get month number
myMonthName = Month(rDate)
'convert month number to month name (abbreviation True)
myMonthName = MonthName(myMonthName, True)
'get the year
myYear = Year(rDate)
'concatenate variables
rDateTime = myDayName & myMonthName & ". " & myDay & ", " & myYear & myTime"
End Function
How should I use this as a funciton in SQL Server Reporting Services? I think that I need to use a calculated field. Am I correct or what do I need to do to implment this. This code will return the Weekday Name which is what I need.
TIA for any help in advance.
Have a great day!
Kurt
VS2008 RTM (trial) Won't Convert Report Project
Dec 12, 2007
When I try to convert a report project from 2005, it seems to convert fine but then the project closes because VS2008 doesn't support that project type (.rptproj ?). I see that there is a report project type under VB (and I assume C#) but there is no Business Intelligence project catagory. It's a new machine and I did install SQL Server 2005 before VS2008.
I'm in the process of moving a bunch of projects and websites from another machine and got hung up on this one. Any pointers greatly appreciated.
Can't Preview Report After Convert To 2005 With &&<CodeModule&&> [rsErrorLoadingCodeModule]
Aug 29, 2006I have a .rdl file which I opened in visual studio 2005. I answered Yes to the prompt Microsoft Report Designer. . . Do you want to convert this file? [Yes] Then I saved the file.
The issue is when I am in Visual Studio and click on the Preview tab, I get the following error:
[rsErrorLoadingCodeModule] Error while loading code module: €˜x.ReportDefinitionsHelper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null€™. Details: Could not load file or assembly 'AdvancedMD.Report.ReportDefinitionsHelper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
There was a <CodeModule> which existed prior to the conversion and the preview worked fine.
I tried to re-add the reference by clicking on the Layout tab then go to the report properties tab. Then, under references I clicked on the collection and browsed to the file which I wanted to reference and clicked ok, but I still get the error above when I click on the Preview tab. I tried to remove the reference save then come back in and add it again, but it still gives the error above.
The file I am trying to add to <CodeModule> (x.ReportDefinitionsHelper) references the following:
System
System.Data
System.Xml
Any help would be greatly appreciated.
Thanks
(I can load the .rdl to reporting services 2005 and go into Reporting Services Manager and view the report without any issues.)
Transact SQL :: Convert Entire Scheduling Functionality For A Report
Oct 19, 2015In our application, we do scheduling.  I need to convert the entire scheduling functionality into Transact-SQL for a report. I'm stumped on how to convert some of that functionality. We use a function from Delphi that I see no equivalent in SQL. Further, we still support SQL2005, so the Transact-sql has to be compatible with SQL2005. So, DateFromParts will not work at all for me as it was added in a later version.
The function looks like this:function EncodeDayOfWeekInMonth(const AYear, AMonth, ANthDayOfWeek, ADayOfWeek: Word): DateTime;
The purpose of the function is:Returns a DateTime that represents a specified occurrence of a day of the week within a specified month and year.how I can do this with a Transact-sql statement? As I said earlier, I'm constrained by the fact it must work in a SQL2005 instance.
SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.
Aug 15, 2006Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks
View 4 Replies View RelatedGridview In Asp.net
Nov 29, 2006Can I directly Save data to sqlserver 2005 using gridview in frontend?
How?
GridView Help
Feb 4, 2007Hi,
I use WVD and SQL Express 2005.
I have a table “SignIn� that one of fields inserted automatically by getdate()
And I have GridView that I use to display this table because I would like take advantage of GridView sorting and paging methods that are embedded in.
Currently I display all records at once.
My problem is how to make the GridView show today’s records only.
I tried this code below, but I get only this message “There are no data records to display.�
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:RC1 %>"
ProviderName="<%$ ConnectionStrings:RC1.ProviderName %>"
SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @SignIn)">
<SelectParameters>
<asp:QueryStringParameter Name="SignIn" QueryStringField="Format(Now, "M/dd/yyyy")" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
Help Please!
Gridview
Apr 17, 2008Hi
I have a business search box and gridview pair. When the user enters a business name, the search results are displayed. I also generate a "more information" link which takes the user to a new page, passing the business name ("memberId")to this page (see the template field below).
The problem I have is if the name contains a QUOTE (') or other special characters. The "memberId" is chopped off at the quote (e.g. "Harry's Store" is passed as "Harry").
Can anyone tell me a way around this please? Is there anything I can do with the Eval method?
Kind regards,
Garrett
<asp:TemplateField HeaderText="More Info">
<ItemTemplate>
<a href='member_page.aspx?memberId=<%# Eval("co_name") %>'>more</a>
</ItemTemplate>
<ItemStyle Font-Bold="False" />
</asp:TemplateField>
???GridView
Mar 29, 2006HiI need to add in gridview control asp code "delete from t1 where id=@id1"how to declare @id1 because the server give me mistake down is the code of asp i use GridView how i can link @id with field there id???Thank u and have a nice daybest regardsthe code if u need it i use c#
<ASP:SQLDATASOURCE id=SqlDataSource1 <br ConnectionString="<%$ ConnectionStrings:libraryConnectionString %>" runat="server"><BR></ASP:SQLDATASOURCE></ASP:BOUNDFIELD>
Datatable From Gridview
Aug 2, 2006hi all
the usual way to bid a gridview is to data soursce
is there a way to do the folowing , creat a data table from the gridview shown valus " currunt page "
thanks
Gridview Sorting
Aug 17, 2006I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got
ORDER BY CASE WHEN @sortBy='' THEN DateCreated END, CASE WHEN @sortBy='DateCreated' THEN DateCreated END
and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?
Gridview And DropDownList
Oct 18, 2006Hello:I have add a DropDownList to my GridView and binded the dropdownlist to a field from a select statement in the SQLDataSource. I have EnabledEditing for my GridView. The GridView is populated with information from the select statement. Some of the information returned from the select statement is null. The field where the dropdownlist is binded it is null in some cases and does not have a value that is in the dropdownlist so I get and error when I attempt to do an update. 'DropDownList1' has a SelectedValue which is invalid because it does not exist in the list of items.Parameter name: value Is there a way to get around this besides initializing all the columns in the table that are going to be binded to a dropdownlist to a value in the dropdownlist?
View 1 Replies View RelatedGridview Search
Jan 15, 2007I tried doing a text box search within Gridview. My code are as follows. However, when I clicked on the search button, nothing shown.
Any help would be appreciated. I'm using an ODBC connection to MySql database. Could it be due to the parameters not accepted in MySql?
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
SqlDataSource1.SelectCommand = "SELECT * FROM carrier_list WHERE carrierName LIKE '%' + @carrierName + '%'"
End Sub
Sub doSearch(ByVal Source As Object, ByVal E As EventArgs)
GridViewCarrierList.DataSourceID = "SqlDataSource1"
GridViewCarrierList.DataBind()
End Sub
HTML CODES (Snippet)
<asp:Button ID="btnSearchCarrier" runat="server" onclick="doSearch" Text="Search" />
' Gridview<asp:GridView ID="GridViewCarrierList" runat="server" DataSourceID="SqlDataSource1" >
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT * FROM carrier_list">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<SelectParameters>
<asp:ControlParameter ControlID="txtSearchCarrier" Name="carrierName" PropertyName="Text" Type="String"></asp:ControlParameter>
</SelectParameters>
</asp:SqlDataSource>
GridView...Problem
Mar 29, 2007I m creating the project in asp.net using c# and vb languages in 2005.I have used the asp standard controls(with table<td><tr>) and gridview to design the form.I m using sqldatasource to insert and update data from sql server 2005.I have written the following code <script runat="server"> Private Sub Page_Load() If Not Request.Form("SUBMIT") Is Nothing Then srccompany.Insert() End If End Sub</script> <asp:SqlDataSource id="srccompany" SelectCommand="SELECT * FROM companymaster" InsertCommand="INSERT companymaster(companyname) VALUES (@companyname)" UpdateCommand="UPDATE companymaster SET companyname=@companyname WHERE companyid=1" DeleteCommand="DELETE companymaster WHERE companyname=@companyname" ConnectionString="<%$ ConnectionStrings:companymaster %>" Runat="server"></asp:SqlDataSource> <asp:GridView id="GridCompanyMaster" DataSourceID="srccompany" Runat="server" />Please help me to insert the data in sql server.i m not been able to insert the data is there any problem in coding..Also i m not been able to edit the data and store back to sql server.Only i can do is i can view the contents in gridview Please give me some tips
View 1 Replies View RelatedSQL OutputCache GridView
Oct 24, 2007I'm trying to cache the contents of a gridivew unless another page, or sorting method are being called. I tried to use the VaryByParam method, but I'm not having any luck, I keep getting the same page sent back to me. Here's what my code looks like.
<%@ OutputCache Duration="180" VaryByParam="Page$, Sort$" %>
Any help would be appreciated.
Stephen
Gridview Sql Timeout
Dec 3, 2007I have a simple gridview displaying data from an MSSQL server 2005. Every now and then I get a sql timeout error. Listed below. Can anyone explain why I am getting this error? The connection pool is 100 and the timeout is set to 360. I have checked to current connections in SQL and they never max over 23. There are not locks in SQL when the problem occurs. The query is a stored procedure in sql and when sent sample data it normally takes about 5 seconds.
Event code: 3005 Event message: An unhandled exception has occurred. Event time: 12/3/2007 9:46:37 PM Event time (UTC): 12/4/2007 3:46:37 AM Event ID: 140501f9a7744dfea2e445ed00939e44 Event sequence: 42 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1/ROOT-1-128412128787656250 Trust level: Full Application Virtual Path: / Application Path: c:inetpubwwwroot Machine name: DD-MAIN Process information: Process ID: 5544 Process name: w3wp.exe Account name: NT AUTHORITYNETWORK SERVICE Exception information: Exception type: SqlException Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Request information: Request URL: http://localhost/Search_DG.aspx?SearchWord=1212 Request path: /Search_DG.aspx User host address: 10.10.10.1 User: Is authenticated: False Authentication Type: Thread account name: NT AUTHORITYNETWORK SERVICE Thread information: Thread ID: 1 Thread account name: NT AUTHORITYNETWORK SERVICE Is impersonating: False Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at System.Web.UI.WebControls.DataBoundControl.PerformSelect() at System.Web.UI.WebControls.BaseDataBoundControl.DataBind() at System.Web.UI.WebControls.GridView.DataBind() at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() at System.Web.UI.Control.EnsureChildControls() at System.Web.UI.WebControls.GridView.get_Rows() at Install_DG.Page_Load(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Custom event details:
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Help!!! Gridview Binding
Mar 20, 2008Hey guys, Am in need of help please, basically the program im working with at the moment is when you add a New Contract, it will grab the Contract Number you have entered, Post it over to another page, then using that number bind a Gridview. The SQL being "SELECT Contract_ID, Contract_Number, Start_Date, End_Date, Quarterly_Rent FROM ECS_Contracts_Test WHERE Contract_Number = " & conNoVar (this being the Contract Number of the recently added Contract), however then it comes to Bind the Grid it kicks up an System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value
'2009P7899' to a column of data type int.But the Column Contract_Number is set to Varchar and all I really want to do is to create this gridview using this criteria and not convert anything! The Contract_ID is an int, which is needed as I increment it. Heres the error code: Public Sub BindtheGrid()'Bind the Contract Grid, where ContractID is used
Dim SQL As String = "Contract_ID, Contract_Number, Start_Date, End_Date, Quarterly_Rent"
Dim objConn As SqlConnection = New SqlConnection(ConnectionString)
Dim cmdstock As SqlCommand = New SqlCommand("SELECT " & SQL & " FROM ECS_Contracts_Test WHERE Contract_Number = " & contractQueryID, objConn)
cmdstock.CommandType = CommandType.Text
objConn.Open()
GridView1.DataSource = cmdstock.ExecuteReader()
GridView1.DataBind()
objConn.Close()
End Sub If you need any more information then please let me know. Mucho Aprreciated
Gridview And Sqldatasource
May 3, 2008
i have a gridview which is bound to sqldatasource. i have a delete button in the gridview. I have written code for "deleting" a record in app_code directory.
now iam not using "deletecommand" of sqldatasource but on the click of "delete" link i want to call the procedure in the app_code.
Any ideas on how to do it.??
Searching A Gridview In ASP.NET 2.0
Dec 15, 2005Hello,How do I search through the gridview? Would I do this at the sqldatasourcelevel?I figured that I sould search with the datatable.select but how do I accessthe datatable of the sqldatasource?I am using ASP.NET 2.0 with VB.Thanks for your helpJ
View 2 Replies View RelatedGridView - SqlDataSource
Apr 14, 2006I have created a GridView that uses a SqlDataSource. When I run the page it does not pull back any data. However when I test the query in the SqlDataSource dialog box it pulls back data.
Here is my GridView and SqlDataSource:
<asp:GridView ID="Results" runat="server" AllowPaging="True" AllowSorting="True"
CellPadding="2" EmptyDataText="No records found." AutoGenerateColumns="False" Width="100%" CssClass="tableResults" PageSize="20" DataSourceID="SqlResults" >
<Columns>
<asp:BoundField DataField="DaCode" HeaderText="Sub-Station" SortExpression="DaCode" >
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="DpInfo" HeaderText="Delivery Point" SortExpression="DpInfo" >
<HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" />
<ItemStyle CssClass="tdResults" />
</asp:BoundField>
<asp:HyperLinkField DataNavigateUrlFields="CuCode,OrderID" DataNavigateUrlFormatString="TCCustDetail.asp?CuCode={0}&OrderID={1}"
DataTextField="OrderID" HeaderText="Order No" SortExpression="OrderID">
<ItemStyle CssClass="tdResults" HorizontalAlign="Center" />
<HeaderStyle CssClass="tdHeaderResults" HorizontalAlign="Center" />
</asp:HyperLinkField>
<asp:BoundField HeaderText="Order Date" SortExpression="OrderDate" DataField="OrderDate" >
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="ReqDeliveryDate" HeaderText="Req Delivery Date" SortExpression="ReqDeliveryDate" >
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="StatusDate" HeaderText="Status Date" SortExpression="StatusDate">
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="ManifestNo" HeaderText="Manifest No" SortExpression="ManifestNo">
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="CustomerPO" HeaderText="P.O. No" SortExpression="CustomerPO">
<ItemStyle HorizontalAlign="Center" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="Class" HeaderText="Class" SortExpression="Class">
<ItemStyle HorizontalAlign="Left" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" />
</asp:BoundField>
<asp:BoundField DataField="OrderStatus" HeaderText="Order Status" SortExpression="StatusSort">
<ItemStyle HorizontalAlign="Left" CssClass="tdResults" />
<HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" />
</asp:BoundField>
</Columns>
<HeaderStyle ForeColor="White" HorizontalAlign="Left" />
<AlternatingRowStyle CssClass="tdResultsAltRowColor" />
</asp:GridView>
<asp:SqlDataSource ID="SqlResults" runat="server" ConnectionString="<%$ ConnectionStrings:TransportationConnectionString %>" SelectCommand="GetOrderSummaryResults" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="10681" Name="CuCode" Type="String" />
<asp:Parameter DefaultValue="" Name="DaCode" Type="String" />
<asp:Parameter DefaultValue="" Name="DpCode" Type="String" />
<asp:Parameter DefaultValue="" Name="OrderID" Type="String" />
<asp:Parameter DefaultValue="" Name="ManifestNo" Type="String" />
<asp:Parameter DefaultValue="" Name="PONo" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
I can get it to fill with data by manually filling the GridView without using a SqlDataSource but then I cannot get the sorting to work when I do it that way. Actually not sure if the sorting will work this way either as I cannot get it to fill with data. Any ideas would be much appreciated.
Gridview Question
Jun 1, 2006Sqldatasources are used for a dropdownlist and a gridview. How can the dropdownlist selection refresh the gridview? This is done programmatically in ASP.NET 1.1 code behind. Can it be done in ASP.NET 2.0 without code behind? Thanks.
View 2 Replies View RelatedSpecifying Updateparameters In Gridview
Jun 7, 2006Where exactly are the updateparameters of a gridview picked up from? I have created 2 very similar gridviews and given the updateparameters the same names as in my edititemtemplates. Yet this method has worked for 1 gridview and failed for the second gridview. Here is my gridview :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:XConnectionString %>" SelectCommand="ViewForecast" SelectCommandType="StoredProcedure" DeleteCommand="DeleteForecast" DeleteCommandType="StoredProcedure" UpdateCommand="UpdateForecast" UpdateCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="ForecastKey" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ForecastKey" Type="Int32" /> <asp:Parameter Name="CompanyKey" Type="Int64" /> <asp:Parameter Name="ForecastType" Type="Char" /> <asp:Parameter Name="MoneyValue" Type="Double" /> <asp:Parameter Name="ForecastPercentage" Type="Double" /> <asp:Parameter Name="DueDate" Type="DateTime" /> <asp:Parameter Name="UserKey" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:XConnectionString %>" SelectCommand="GetCompaniesByUser" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:XConnectionString %>" SelectCommand="GetForecastTypes" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:XConnectionString %>" SelectCommand="GetForecastPercentages" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" SkinID="Grey" AutoGenerateColumns="false" DataKeyNames="ForecastKey" AllowSorting="true" OnRowDataBound="GridView1_RowDataBound" EditRowStyle-CssClass="dgedit" OnRowUpdated="GridView1_RowUpdated" OnRowEditing="GridView1_RowEditing" OnRowDeleting="GridView1_RowDeleting"> <Columns> <asp:TemplateField HeaderText="Key" SortExpression="ForecastKey"> <ItemTemplate> <asp:Label ID="lblForecastKey" Text='<%# Bind("ForecastKey") %>' runat="server"></asp:Label> </ItemTemplate> <ItemStyle Height="24px" Width="50px" /> </asp:TemplateField> <asp:TemplateField HeaderText="Company" SortExpression="CompanyName"> <ItemTemplate> <asp:Label ID="lblCompany" Text='<%# Eval("CompanyName") %>' runat="server"></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:DropDownList ID="ddlCompanyName" DataSourceID="SqlDataSource2" Runat="Server" DataTextField="CompanyName" DataValueField="CompanyKey" SelectedValue='<%# Bind("CompanyKey") %>' /> </EditItemTemplate> <ItemStyle Height="24px" Width="50px" /> </asp:TemplateField> <asp:TemplateField HeaderText="Forecast Type" SortExpression="ForecastDescription"> <ItemTemplate> <asp:Label ID="lblForecastType" Text='<%# Eval("ForecastDescription") %>' runat="server"></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:DropDownList ID="ddlForecastType" DataSourceID="SqlDataSource3" Runat="Server" DataTextField="ForecastDescription" DataValueField="ForecastType" SelectedValue='<%# Bind("ForecastType") %>' /> </EditItemTemplate> <ItemStyle Height="24px" Width="50px" /> </asp:TemplateField> <asp:TemplateField HeaderText="Value (£)" SortExpression="MoneyValue"> <ItemTemplate> <asp:Label ID="lblMoneyValue" Text='<%# Eval("MoneyValue", "{0:#,###.00}") %>' runat="server"></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtMoneyValue" Text='<%# Bind("MoneyValue", "{0:#,###.00}") %>' runat="server"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtMoneyValue" Display="None" ErrorMessage="Please enter a Value" /> <asp:CompareValidator ID="CompareValidator1" runat="server" Display="None" ErrorMessage="Value must be numeric" ControlToValidate="txtMoneyValue" Type="Double" Operator="DataTypeCheck"></asp:CompareValidator> </EditItemTemplate> <ItemStyle Height="24px" Width="50px" /> </asp:TemplateField> <asp:TemplateField HeaderText="Probability (%)" SortExpression="ProbabilityValue"> <ItemTemplate> <asp:Label ID="lblProbability" Text='<%# Eval("ForecastPercentage") %>' runat="server"></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:DropDownList ID="ddlForecastPercentage" DataSourceID="SqlDataSource4" Runat="Server" DataTextField="ForecastPercentageDescription" DataValueField="ForecastPercentage" SelectedValue='<%# Bind("ForecastPercentage") %>' /> </EditItemTemplate> <ItemStyle Height="24px" Width="50px" /> </asp:TemplateField> <asp:TemplateField HeaderText="Value (£) x Probability (%)" SortExpression="ProbabilityValue"> <ItemTemplate> <asp:Label ID="lblProbabilityValue" Text='<%# Eval("ProbabilityValue", "{0:#,###.00}") %>' runat="server"></asp:Label> </ItemTemplate> <ItemStyle Height="24px" Width="50px" /> </asp:TemplateField> <asp:TemplateField HeaderText="Due Date" SortExpression="DueDate"> <ItemTemplate> <asp:Label ID="lblDueDate" Text='<%# Eval("DueDate", "{0:dd/MM/yyyy}") %>' runat="server"></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtDueDate" Text='<%# Bind("DueDate", "{0:dd/MM/yyyy}") %>' runat="server"></asp:TextBox> <!--or use this in SQL : Select Convert(VarChar(10), GetDate(), 103)--> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtDueDate" Display="None" ErrorMessage="Please enter a Due Date" /> <asp:CompareValidator ID="CompareValidator2" runat="server" Display="None" ErrorMessage="Please enter a valid Due Date in format dd/mm/yyyy" ControlToValidate="txtDueDate" Type="Date" Operator="DataTypeCheck"></asp:CompareValidator> </EditItemTemplate> <ItemStyle Height="24px" Width="65px" /> </asp:TemplateField> <asp:TemplateField HeaderText="Last Edit Date" SortExpression="LastEditDate"> <ItemTemplate> <asp:Label ID="lblLastEditDate" Text='<%# Eval("LastEditDate", "{0:dd/MM/yyyy}") %>' runat="server"></asp:Label> </ItemTemplate> <ItemStyle Height="24px" Width="50px" /> </asp:TemplateField> <asp:CommandField ShowEditButton="True" ButtonType="Link" ShowCancelButton="True" UpdateText="Update" EditText="Edit" CancelText="Cancel" /> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="DeleteButton" CommandArgument='<%# Eval("ForecastKey") %>' CommandName="Delete" runat="server"> Delete</asp:LinkButton> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView>
And here is my stored proc :
CREATE procedure dbo.UpdateForecast
( @ForecastKey int, @CompanyKey bigint, @ForecastType char(1), @MoneyValue float, @ForecastPercentage float, @DueDate datetime, @UserKey int)
as
update Forecastset CompanySiteKey = @CompanyKey,MoneyValue = @MoneyValue,Probability = @ForecastPercentage,ForecastType = @ForecastType,InvoiceDate = @DueDate,UserKey = @UserKeywhere ForecastKey = @ForecastKeyGO
Can anybody with more experience of using gridview please tell me where I am going wrong?
Cheers,
Mike
Editing In The GridView
Nov 16, 2007Hi
I want to Enable Editing in the GridView
but there is a problem
the GridView take its data from 2 tables and I do this by the
build query
so I can't choose the " advance " button and check for enable update and delete statements
and when I try to build the update query in the update tap
I don't know what to write in the "new value" column
so how can enable editing in the GridView that take from
2 tables???????
please help me
and thanks
Crosstable In Gridview
Feb 29, 2008First of all I like to say hello. Its my first post and I hope somebody will be so kind and help me.
I have 3 tables in a database. They look like follow:
1. Table called State:
------------------------------
Id
Caption
2. Table called Craft:
-----------------------------
Id
Caption
3. Table called Wage:
-------------------------------
StateId
CraftId
Wage (decimal)
What I'm trying to do is, create a crosstable like the following and display it in a Gridview
Texas California Washington ...
digging 25,60 31,42 ...
masonry works 40.66 ... ...
So, the columns come from table State, the row titles come from Craft and the values from Wage. After changing the values I want to save values back to Wage.
I started to build this crosstable but faced several problems. For example I dont know how to set a Caption on the left side of each row (Caption of table Craft).
And even worse, how shall I update table Wage if there are no Id's in the crosstable anymore to make sure that I save the Value on the right place back in table Wage.
It would be very kind if somebody has a solution or a method of resolution.