Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Searching SQL Db And Returning Results To Gridview


Hello....

I am in the process of building a search function for an application (VS2005, ASP.net, VB code, and AJAX functionality).The user's form has a textbox (for an input string) and two drop-downs.  The user would then click on a "Search Now" button, and the data should populate
in a Gridview to the left of the search form.  I'm trying to search over an SQL database and need to know how to exactly implement what I have so far:

<div id="mcb" style="background-color:#2e4b5b; width:195px; height:410px; float:left">
     <div id="ht" style="background-color:#1b2c36;padding:10px; border-bottom:solid 1px #fffdff; width:175px;float:left;">
           Quick Search
     </div>
<div style="clear:both;"></div>

<div style="padding:10px; background-color:#2e4b5b">
      <div id="tfta" style="padding:5px 0px 0px 0px">
                <b>Vendor Name</b>
                <asp:TextBox ID="txtVendorFilter" runat="server"></asp:TextBox>
                <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br />
                 <b>Contract Type:</b>
                 <asp:dropdownlist ID="ddlContractTypeSearch" runat="server" Width="155px">
                           <asp:ListItem Value="0">Select...</asp:ListItem>
                           <asp:ListItem Value="1">Vendor Maintenance</asp:ListItem>
                           <asp:ListItem Value="2">Consulting Contract</asp:ListItem>
                           <asp:ListItem Value="3">Hardware License</asp:ListItem>
                           <asp:ListItem Value="4">Software License</asp:ListItem>
                           <asp:ListItem Value="5">Statement of Work</asp:ListItem>
                           <asp:ListItem Value="6">Admin Svcs. Agreement</asp:ListItem>
                           <asp:ListItem Value="7">Master Service Agreement</asp:ListItem>
                  </asp:dropdownlist>

           <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br />
                   <b>Sort By:</b>
                   <asp:DropDownList ID="ddlSortBy" runat="server" Width="155px">
                          <asp:ListItem Value="0">Select...</asp:ListItem>
                          <asp:ListItem Value="1">Contract Name</asp:ListItem>
                          <asp:ListItem Value="2">Contract Type</asp:ListItem>
                    </asp:DropDownList>             <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br />
        </div>
    <br />
    <asp:Button ID="btnUpdateFilter" runat="server" Text="Search" width="120px"/>
    <asp:Button ID="btnReset" runat="server" Text="Reset" /></div>

</div>

 Here's my select statement used to initially populate the Gridview (set up using the configuration of the Gridview):
SELECT [ContractID], [ContractName], [ContractType], [EffectiveDate], [TerminationDate] FROM [Contract]
Here's my btnUpdateFilter codebehind (VB):


Protected Sub btnUpdateFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateFilter.Click
     Dim strVendorName As String
     Dim strContractType As String
     Dim strSortBy As String

     If txtVendorFilter IsNot "" Then
           strVendorName = txtVendorFilter.Text
     End If

''''''''''''''''''''''''''''
If ddlContractTypeSearch.SelectedValue = 1 Then
      strContractType = "1"
ElseIf ddlContractTypeSearch.SelectedValue = 2 Then
      strContractType = "2"
ElseIf ddlContractTypeSearch.SelectedValue = 3 Then
      strContractType = "3"
ElseIf ddlContractTypeSearch.SelectedValue = 4 Then
      strContractType = "4"
ElseIf ddlContractTypeSearch.SelectedValue = 5 Then
      strContractType = "5"
ElseIf ddlContractTypeSearch.SelectedValue = 6 Then
      strContractType = "6"
ElseIf ddlContractTypeSearch.SelectedValue = 7 Then
      strContractType = "7"
Else
      strContractType = "0"
End If

''''''''''''''''''''''''''''''
If ddlSortBy.SelectedValue = 1 Then
     strSortBy = "ContractName"
ElseIf ddlSortBy.SelectedValue = 2 Then
     strSortBy = "ContractType"
Else
     strSortBy = "ContractName"
End If

End Sub

 

My question is, "how can I form the proper SQL statement", and then implement it so that my gridview will populate using my generated SQL Statement?

Thank you for the help!!!

-S


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Want Error Message To Appear When No Database Results Were Returned In GridView, Also Other GridView Issues.
Hi, 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 Replies !   View Related
Searching A Gridview In ASP.NET 2.0
Hello,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 Replies !   View Related
Mind-boggling Gridview Results! Different Results For Different Teams..
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"  SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1)))  AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1.  This doesn't make sense to me at all!  For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI

BrianAustinHope83432230.7187500.7352941.15625014612201221

GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519

MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218

RobBennettMelville83029240.8275860.8333331.55172411904102117

AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015

ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP.  Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu  ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)*** 

View Replies !   View Related
Returning HTML Via SQL To Bind To A Gridview


So I have a stored procedure that returns rows that look similer to this


<a href="~/Manage/ManageVersion.aspx?viewstate=MTgwNywzMTc0LDguOSAgICAgICA=">DPF-13</a>

Unfortunatly when it is bound to the gridview it seems to convert the value to HTML safe.

The value it prints on the page is


&lt;a href=&quot;~/Manage/ManageVersion.aspx?viewstate=MTgwNywzMTc0LDguOSAgICAgICA=&quot;&gt;DPF-13&lt;/a&gt;



Hit quote to look at the actual values


How can I fix the output to the gridview to print the actual link and the the html safe value?

Can I accomplish this in SQL or am I going to have to do it all in the c# code (which will be a less clean solution and alot more work).


tia

View Replies !   View Related
Searching Relations And Returning Them In A Table
I have a table tblFriends:UserCodeOwner   FriendCode5                         545                         1235                         4785                         49054                       5123                     5478                     5478                     500490                     5490                     500500                      478500                    490500                    600600                   500As you can see I store each relation twice, i've done that because now I can create a clustered index on UserCodeOwner for faster searching.What I want is the following:I want to count how many steps it takes to get from one user to another via their relations and also via which usercode it goes.so: from 5 to 123 is one step.5-123from 5 to 500 is 2 steps via either 478 or 490I want to get a result like this:5-478-500and5-490-500from 5 to 600 is 3 steps:5-478-500-6005-490-500-600Does anyone have a good start for me on how im able to return such results and how I can search through the table most effecively?

View Replies !   View Related
Searching In Results In Sql
I want to search all of the employeeid #'s in the employee table for any mis-typed information.  so if they put a . in the string i want to return that employee's info.  as well as any letter a - z.  I need to do this in sql and return it to a dataset for my crystal report.  any ideas?

View Replies !   View Related
Returning No Results
I have the following stored procedure that is returning nothing can anyone please help?
 SELECT     job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date,                       customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM         tblOrderWHERE     (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End)
 When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id

View Replies !   View Related
Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns
I admit I am a novice toqueries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedureand have not been successfulat all.

What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selecteditems.

1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.

The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.


After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.

Hopefully someone can get me pointed in the right direction! Thanks for your help!

View Replies !   View Related
Returning The Results Of A Linq Query
I need some help. I have a function that used to use ADO.NET to return a dataview. Now I'm using linq, and I don't know how to make this work anymore.my original function looked like this:    public DataView getIssue()    {       //do some ADO, return the dataview       return ds.Tables["Articles"].DefaultView;    }  With this I could write Gridview1.dataSource = getIssue();I want to do the same thing with Linq, but I'm running into some trouble:Here's my function now:public DataView getIssue()    {        var query = from a in db.Articles        join i in db.Issues on a.IssueID equals i.IssueID       select a;            DataView dv = new DataView();            dv = query.asdatatable();        return query;    }OK, first off I can't use asdatatable since I am using a join, so I can't make the results of my query a dataview. I can't return the results of my query in this function, something that seemed simple to do in Linq now seems like something that may only be possible in ADO.Thanks for your time 

View Replies !   View Related
OpenXML Not Returning Desired Results
Hi all,I have a SQL job where I do the following -I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() - DECLARE @intDoc INTDECLARE @xmlDoc VARCHAR(8000)IF(SELECT COUNT(*) FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable)) > 0BEGINSET @xmlDoc = (SELECT * FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable) FOR XML RAW) SET @xmlDoc = '<TABLE>' + @xmlDoc + '</TABLE>'
PRINT @xmlDocEXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlDoc--INSERT INTO CleanTable(C1, C2, C3, C4, C5)
SELECT C1, C2, C3, C4, C5, C6 FROM OPENXML(@intDoc,'/row',1)WITH (C1 INT, C2 CHAR(3), C3 CHAR(3) , C4 FLOAT, C5 INT)EXEC sp_xml_removedocument @intDoc
END
ELSE Output that I get is - <TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE>(0 row(s) affected) SO "PRINT @xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply If anyone has any better method to do it, inputs are welcome. Thanks  

View Replies !   View Related
Stored Procedure Not Returning Results
Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables:  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Project](
[ProjID] [varchar](300) NOT NULL,
[ProjType] [varchar](20) NULL,
[ProjectTitle] [varchar](max) NULL,
[ProjectDetails] [varchar](max) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](max) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED
(
[ProjID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]


-----------------------------------------------------------------


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Report](
[ReportName] [varchar](50) NOT NULL,
[ProjID] [varchar](300) NULL,
[DeptCode] [varchar](50) NULL,
[ProjType] [varchar](50) NULL,
[ProjectTitle] [varchar](500) NULL,
[ProjectDetails] [varchar](3000) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](50) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED
(
[ReportName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]


--------------------------------------------------------------


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectStatus](
[ProjectStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [varchar](max) NULL,
CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED
(
[ProjectStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


-----------------------------------------------------------


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectManager](
[ProjectManagerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[Inactive] [int] NULL,
CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED
(
[ProjectManagerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

 And here is the stored procedure that I wrote (doesn't return results):  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReportQuery]
(
@ReportName varchar(100)
)

AS

BEGIN

SET
NOCOUNT ON

IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN

DECLARE @DeptCode varchar(50), @ProjID varchar(50)
SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName

SET @ProjID = @DeptCode + '-' + '%'

SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName))))
AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL)
AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL)
AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL)
AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL)
AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL)
AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL)
)
END

END Can someone see what's wrong? Thanks. 

View Replies !   View Related
SqlDataReader Returning Incorrect Results
I am/have been having an issue with Data Access from a Sql Server database. I have a class that contains a method called "GetDataReader" which takes in a string for the query. Occasionally, the DataReader returned has completely different columns thus resulting in an error when trying to read the data. Below is a small section of code that actually creates the datareader, opens the connection and executes the reader.SqlCommand cmdReader = new SqlCommand();
SqlDataReader drReturn = null;
try {
// Set the connection for the command object
cmdReader.Connection = new SqlConnection(this._csbGlobal.ConnectionString);
cmdReader.Connection.Open();
// Set the command type
cmdReader.CommandType = Type;
// Set the command text
cmdReader.CommandText = Sql;
// Set the command timeout
cmdReader.CommandTimeout = _iTimeout;
if (Parameters != null) {
// Set the parameters
for (int i = 0; i < Parameters.Count; i++) {
cmdReader.Parameters.AddWithValue("@" + Parameters.GetKey(i), Parameters[i]);
}
}

// Get the return value
drReturn = cmdReader.ExecuteReader(CommandBehavior.CloseConnection);
// Dispose of the command object
cmdReader.Dispose();
cmdReader = null;
} catch (SqlException e) {
this.HandleError(e);
} catch (Exception e) {
this.HandleError(e);
}
// Return the reader
return drReturn;The data access class is created on each page and only used for that page and any usercontrols on that page. I have checked all the datareaders and they are all being closed. I have been fighting with this issue for about a month and a half now. It only seems to happen when there are a lot of people on the site.If anyone has experienced this or konws how to fix it please let me know. Thanks-Jason

View Replies !   View Related
Executing A Script And Returning The Results..
You know how osql can return the results of executing a .sql script to a window? Is there any way to achieve that same behavior in .NET? I am in the process of working on an application that needs to execute a .SQL script (which is easy to do) and then return the actual SQL output from the execution to a window. How do I get that output? None of the sql data objects seem to support this.

View Replies !   View Related
Returning Results While The Query Is Executing
Hi,I wanted to know if this is possible and if so, how do Ido it. Say, I have a query "SELECT * FROM Table WHEREColumn="some_value". This executes on a very large dataset and I would like to return the results as they queryexecutes rather than wait for the whole query to execute.Basically, I want to get the results as they are preparedby the database. Any way to do this?Regards,San

View Replies !   View Related
NOT IN Vs. NOT EXISTS Returning Diff Results
select count(orgcode)
from orglevel
where orglvl = 1 and exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)

26 rows

select count(orgcode)
from orglevel
where orglvl = 1 and orgcode in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')

26 rows

select count(orgcode)
from orglevel
where orglvl = 1 and not exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)

6 rows

select count(orgcode)
from orglevel
where orglvl = 1 and orgcode not in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')

zero rows


Confusing the hell out of me .....

View Replies !   View Related
Query With Parameters Not Returning Right Results
I havea 2 part issue with a query I'm trying to run for a report.

I have an incident report that needs to show results based on dates from the week before. In addition, This report should run every Monday morning, with the exception of a Monday holiday, where in that case, the report will run the next business day. I have an idea to use a case statement, but it doesn't seem to work for me. Any suggestions?

Also,part of the query I have is pulling back the right data, as long as I don’t include parameters. When I do add values to the parameters the query includes all the dates. I need to see either/or - not both. I just want to either see dates without the param values or see only dates that I ask for in a parameter. Does that make sense?

Here’s the query (thanks!):

SELECT incident.incid_id,
incident.incid_short_desc,
incident.incid_received_date_time,
incident.incid_closed_date_time,
security_users.description,
incident.incid_assigned_to,
incident.tagged_delete_flag,
activity_result_master.result_desc,
incident_priority_master.priority_desc,
activities.result_id

FROM activities AS activities INNER JOIN
incident AS incident ON
activities.incid_id = incident.incid_id INNER JOIN
activity_result_master AS activity_result_master ON
activities.result_id =
activity_result_master.result_id
INNER JOIN security_users AS security_users ON incident.incid_assigned_to = security_users.name INNER JOIN
incident_priority_master AS incident_priority_master ON incident.priority_id = incident_priority_master.priority_id

WHERE incident.tagged_delete_flag = 'N' AND
activities.result_id = '6' AND
(incid_received_date_time >= DATEADD (d,-7,GETDATE()) AND
incid_closed_date_time <=DATEADD (d,-3,GETDATE())) OR
CONVERT(varchar,incident.incid_received_date_time,101) >= @StartDate AND CONVERT(varchar,incident.incid_closed_date_time,101) <= @EndDate

ORDER BY incident.incid_assigned_to, incident.incid_id

View Replies !   View Related
Sql Query Not Returning Correct Results From Db
Hello.

I am new at SQL and am using SQL server express edition and im a bit stuck! I am using ASP.NET and C# in my website which is using sql database back end.

String SQLroom = "SELECT DISTINCT RoomName FROM Room INNER JOIN RoomCalendar ON Room.RoomID = RoomCalendar.RoomID WHERE Capacity = '" + reqCapacity + "' " + " AND NOT ('" + newRoomEnd + "' <= roomStartDateTime OR '" + newRoomStart + "' >= roomEndDateTime) AND (OHP = '" + ohpYesNo + "' AND AV = '" + avYesNo + "') ";

This is my SQL string... what it is trying to do is:

find the room
where the capacity is the reqcapacity entered by user
and the startdatetime and enddatetime entered by the user are not present in the table for that room capacity
and then look at whether the user requires OHP or AV facilities, which are stored in the database as either yes or no values.
The problem i am having is with the condition in the sql query... because the user may require an OHP and not AV, but then the room returned "`could" have AV facilities, as it wouldnt make a difference to them if it was there or not, basically, the yes condition has to be satisfied.

Not sure whether i should be using AND, or OR? or a combination.

Any ideas gratefully appreciated....

Sandy

View Replies !   View Related
Returning Results Based On Dates


I want to write a stored procedure that takes two three paramters based on a case statement determining which values are null something like this

SelectInformationDate From thisTAble

Where

If @dateValue IsNot @Null
Informationdate = @dateValue
Else

Where

In this second where cluase I want to be able to pull out all the results based on a date range and i am not sure how the syntaz would go

InfomationDate IsBettween @daterangeFrom @dateRangeTwo


Any help on this i hope i was clear...thank you!

View Replies !   View Related
Containstable Not Returning Accurate Results


Does anyone lese get the same results that I do?

tblTest has an id field and a testName field....table has 2 records:
1 kevin test 1
2kevin test 3


SELECT *

FROM tblTest t

INNER JOIN CONTAINSTABLE(tblTest,*,'"kevin test 1"') as A ON t.id = A.[KEY]

Result isboth records returned, but since the search is in quotes as an exact phrase, should not just the first record be returned?

View Replies !   View Related
Why Is This JOIN Statement Not Returning The Right Results?


Hi,
I have three tables:
1 Order Details
2 Product Info
3 Received Qty


The query is pretty simple however I'm getting incorrect results.

The query grabs a load of information (including a SUM statement) from the order details tbl.. this is INNER JOIN ed to the products tbl to match an ID field which grabs some extra info from theproduct info tbl.

all fairly straightforward and works without a problem..

However I now need to add in the received qty tbl (as the name suggests this keep a track of the qty's of items received)

I need to join this tbl to the order details tbl based on a unique ID from the orders tbl and then SUM together all of the entries in that received qty tbl for that item.

Result is I get a single record which shows the qty ordered (from the order details tbl), and the total received (from the received qty) tbl

These are held in a seperate tbl as an order can be deliveryed in several stages.



Here is my statement:

SELECT PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID,
dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
SUM(dbo.Suppliers_POs_Details.Qty * dbo.Suppliers_POs_Details.PricePerItem) AS LineTotal, SUM(dbo.Suppliers_POs_Details_Received.Qty)
AS QtyReceived
FROM dbo.Suppliers_POs_Details INNER JOIN
PDS_Login.Supplier_Products ON dbo.Suppliers_POs_Details.ProductID = PDS_Login.Supplier_Products.ProductID LEFT OUTER JOIN
dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID
WHERE (dbo.Suppliers_POs_Details.POID = @POID)
GROUP BY dbo.Suppliers_POs_Details_Received.Qty, PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode,
dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
dbo.Suppliers_POs_Details_Received.Qty


The problem is, if there is more than one relating record in teh Received Qty tbl instead of that being added together as I want, two results appear...

It's obviously something simple but I can't seem to figure it out - anyone point me in the right direction?

View Replies !   View Related
TableAdapter/DataTable Query Returning No Results
Hello,
I have a query that works in query analyzer; it looks that a certain date is between the start and end date of a certain value.  I also have a status field, which can be null, but if provided, provides the appropriate status to filter by.
Again, the query works in QA, but not in the application.  I test in SQL by using start date = '1/1/1900', end date = '12/31/9999', and status = null.  Results are returned.  But, not when the results are done through code.  In code, I set the begin date to new DateTime(1900, 1, 1), the end date to DateTime.MaxValue, and the status to a null string.  But, no results are returning.  Why isn't that mapping over correctly?  In the function, it has the two dates as Nullable(Of DateTime), which I provide a date, and the string is getting passed Nothing.
Any ideas?  Can't post any code on this one...
Thanks.

View Replies !   View Related
Procedure/Cursor Question About Returning Results
I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?

To see what I mean, the following script for the Pubs DBreturns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.

DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor

WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end

Close Authors_cursor
deallocate authors_cursor

Thanks in advance

Tony Murunion

View Replies !   View Related
Returning Multiple Results From A Cursor Into A Table
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...

But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.

In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...





/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/

SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection);
sda.Fill(dt);

... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).

So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?

Any help is greatly appreciated

View Replies !   View Related
SqlDataSource And SSMSE Returning Different Results For Same SELECT Command
Hi,
Hope you guys won't mind this rather newbie question.  I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table).  The SqlDataSource control is:
<asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource>
This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table.  However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get)
 HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query:
blogID, title, description, tags, dateAdded, noOfComments
1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0
Can anyone help?  The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row).  Many thanks for any suggestions!

View Replies !   View Related
Returning Results Left Outer Join With Conditions
I have a SELECT Statement that I am using that is pulling from two tables.  There won't always be results in the second table so I made a LEFT OUTER JOIN.  The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value.  I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1
FROM employee LEFT OUTER JOIN
request AS request ON employee.emp_id = request.emp_id
WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))
AS dayspending
FROM employee AS employee_1 LEFT OUTER JOIN
request AS request_1 ON employee_1.emp_id = request_1.emp_id
WHERE (employee_1.emp_id = @emp_id)
GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial,
employee_1.emp_lname 

View Replies !   View Related
Select Script Returning Different Results When Used In A Stored Procedure.


I have a script with a number of different Left Joins. When I run it in Query Analyzer the Left Join works as a normal join and suppresses some rows. However when the same code is used in a stored procedure the correct results are produced when the stored procedure is executed.
Can anyone tell me what is causing this?


SELECTUSR.ROLEUSER
,PER.NAME
,EMP.PAYGROUP
,USR.ROLENAME
,OPR.EMPLID
,OPR.OPRDEFNDESC
,OPR.ACCTLOCK
,OPR.ROWSECCLASS
,EMP.JOBCODE
,JCD.DESCR
,EMP.DEPTID
,EMP.DEPTNAME


FROM PSROLEUSERUSR
JOIN
PSOPRDEFNOPR
ON
USR.ROLEUSER= OPR.OPRID

LEFT OUTER JOIN
PS_PERSONAL_DATAPER
ON
OPR.EMPLID= PER.EMPLID

LEFT OUTER JOIN
PS_EMPLOYEESEMP
ON
OPR.EMPLID= EMP.EMPLID


LEFT OUTER JOIN
PS_JOBCODE_TBLJCD
ON
JCD.SETID= 'RBLTT'
ANDEMP.JOBCODE= JCD.JOBCODE
ANDJCD.EFFDT=
(SELECT MAX(EFFDT)
FROM PS_JOBCODE_TBL
WHEREJCD.SETID= SETID
ANDJCD.JOBCODE= JOBCODE
ANDEFFDT<= GETDATE())

WHERE OPR.ACCTLOCK= 0
ANDUSR.ROLENAME= 'RBL MANAGER'

ANDEMP.PAYGROUPNOT IN ('RBA', 'RMA')

View Replies !   View Related
Visual Studio And Stored Procedures With Uniqueidentifier Not Returning Results
I have a stored procedure which simply does a SELECT on a table.  This table has as a column a uniqueidentifier, which is not part of the PK.  If I execute this procedure with the SQL Server tools, it works fine and returns the expected results. If I execute this SP with Visual Studio, or ASP.NET, no results are returned and the following comes back:Running [dbo].[spServiceDetail_Get] ( @ServiceDetailID = <DEFAULT>, @VictimWitnessID = <DEFAULT>).ServiceDetailID VictimWitnessID                                                                                                                                                                                                                       --------------- -------------------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[spServiceDetail_Get].  Any ideas what is going wrong?  This seems to be a common problem for many of our tables with a uniqueidentifier in them, and is specifically with VS 2005 and ASP.NET ObjectDataSources. 

View Replies !   View Related
Returning Closest Results When Search Term Doesn't Exist
I'd like to get some ideas for the following:
I am writing a quick mini-application that searches for records in a database, which is easy enough.  However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after.  (Obviously the results are ordered on the search term column)
So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it.
I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better.
Thanks!

View Replies !   View Related
Full-text Index Search Not Returning Expected Results
Hello,

My full-text search isn't working at all! I have a temporary table with full-text indexing enabled where files are scanned for social security numbers. If it has one, the user will see a message that it believes it's found a SSN and won't upload it. There is only ever one row in this table, as we overwrite the contents upon each upload.

I'm testing this search, and it doesn't work. The table has the following columns:
attachemtId (int) - primary key
fileContent (image) - contents of the file
fileExtension (varchar) - extension of the file (this is always either ".pdf" or ".doc")


I created a .doc file that simply says "ssn", and then run the following query:

SELECT * FROM TempAttachment
WHERE CONTAINS(fileContent,'ssn')


and nothing is returned! I tried the same thing with a .pdf file, and same results.

I'm not sure if this is related, but earlier I had this issue where I had to reset permissions for the directory. I've tried removing the full-text index and adding it again, but that didn't do anything. I also checked error logs on the server, and there were no messages. Any help would be appreciated! Thank you!

View Replies !   View Related
A SqlDataReader Is Returning An Int, When It Should Be Returning A Tinyint
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables.  The SELECT looks like this:
 SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
 The TreatmentStatus column is a tinyint.  When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2.  But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?

View Replies !   View Related
Stored Procedure Returning 2 Result Sets - How Do I Stop The Procedure From Returning The First?
I hvae a stored procedure that has this at the end of it:
BEGIN
      EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
 
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?

View Replies !   View Related
Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?
hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View Replies !   View Related
Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View Replies !   View Related
Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT



Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..

Thanks
dotnetdev1

View Replies !   View Related
Inner Joining Sql Server Results And Indexing Service Results
Hi,

I am trying to join sql server results and indexing service results. But I can't.

my query

Quote:
SELECT id,fname,a.FileName from mytbl
inner join OPENQUERY(IndexService, 'SELECT FileName, DocTitle FROM SCOPE() where freetext(contents,''health'')')
as a on mytbl.fname=a.FileName

But it gives the error following

Quote: Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'FileName'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'FileName'.

thank you.

View Replies !   View Related
Removing Individual Results From A Paged Set Of Results.
Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
 

View Replies !   View Related
Yukon Print Results To Results Window
Hello,In T-SQL stored procedures I could use a print statement within the stored procedure and it would output the results to the results window; I was wondering if I could do something similar with a .NET/CLR stored procedure.  The reason is I can't currently debug, due to a VS/SQL problem.Thanks.

View Replies !   View Related
PASTE SQL RESULTS INTO EXCEL - Funny Results
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.

View Replies !   View Related
Working With Daily Results : Results Per Day
Hi,I'm trying to get an overview of counted visits per day for awebapplication.The thing is that SQL doesn't work with full days. For instance: getdate()returns a fulldate instead of a date in the format 'yyyy-mm-dd'. I need thisformat, without time specifics, to be able to group rows.something like:SELECT count(id), dtVisitFROM tblNameGROUP BY dtVisitand I would like to be able to show the results per day for an x amount of(full) days.anyone any idea?

View Replies !   View Related
Gridview
Hi
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>

View Replies !   View Related
???GridView
HiI 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>

View Replies !   View Related
GridView Help
Hi,
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, &quot;M/dd/yyyy&quot;)" Type="DateTime" />
     </SelectParameters>
</asp:SqlDataSource>
 
Help Please!
 

View Replies !   View Related
Gridview In Asp.net
Can I directly Save data to sqlserver 2005 using gridview in frontend?
 
How?
 
 

View Replies !   View Related
Gridview And Sqldatasource
 
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.??
 

View Replies !   View Related
Help!!! Gridview Binding
Hey 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   

View Replies !   View Related
Gridview Sql Timeout
I 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.

View Replies !   View Related
SQL OutputCache GridView
I'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

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved