If SqlDataSource.Select = Empty Then...?
May 28, 2007
(Newbie). I'm trying to: 1) check if the SELECT command has returned any records, and 2) put a msg box on the screen if there are no records returned from the SELECT query. The type of code I'm heading towards is: (but it's wrong). Thank you in advance for your C# code suggestion.
protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{if (SqlDataSource1.SelectParameters.Contains is "") then
MessageBox.show ("There are no records available") ;
}
View 8 Replies
ADVERTISEMENT
Feb 10, 2006
This is probably an easy one.
What is best way to determine if a SqlDataSource is empty (i.e. the query produced no results) in the CodeBehind?
I'm using this:
if (SqlDataSource1.SelectCommand.Contains(String.Empty))
{
//Add code for scenario here.
}
It seems to work, but something just doesn't feel right about it for some reason.
Thanks
View 2 Replies
View Related
Mar 1, 2006
I have an app which uses SQLDataSource to update the information to database (SQL Server 2000). Is there a way to trim the input of users somehow (blank lines) ? For example if I have an Comments-textarea on my form and users enter text to it, can I somehow take out the extra blank lines out of that textarea before submitting the data to database ? I know how to do this programmatically, but was just wondering if this could be done when using SQLDataSource-control.
I'm using DataReader to extract the information from database to an CSV-file. Because of the blank lines, occasionally CSV are really messed up because of that. Is there a way to remove the blanklines when iterating the DataReader values ?
View 1 Replies
View Related
Nov 24, 2006
If a sqldatasource is programed to send textbox1.text to a stored procedure, and the .text property is left empty, and there is no default value set for the parameter, what exactly is the stored procedure receiving?I would like to run a IF BEGIN statement on the value of the parameter in the stored procedure but the following does not work:IF @Parameter IS NULL BEGINor IF @Parameter = '' BEGINThe only way I've gotten it to work is if I set the default value of the parameter being sent to a specific alphanumeric value. Then do something like:IF @Parameter = '99' BEGIN<Code Here>END
View 4 Replies
View Related
Aug 1, 2007
hi,
i want to execute a finctionX() based on the returned resultset of SQLDataSource. For example, if the SELECT command returns nothing based on a specific search criteria then I want to execute functionX(). Currently, I have a string that displays "No Result" in the GridView in such case.
How to catch the resultset of SQLDataSource?
-nero
View 1 Replies
View Related
Jul 23, 2007
What is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
View 7 Replies
View Related
Jan 30, 2001
I need to do a select on a table for all empty items in a column. What is the syntax for selecting the empty (NULL)?
View 3 Replies
View Related
Apr 20, 2008
Hello,I'm a beginner in SQL and I have been searching through the SQL Cookbook and Google but I can't seem to find an example of what I want to do. I want to create a report that will return names and emails using two of my tables. I want to use the email in my primary table in the select but if it is null or empty I want to replace it with an email from my secondary table. Below is what I would like to do but I got a syntax error with it in SQL Server 2000. SELECT MemberID As ID, MemberFirstName As FirstName, MemberLastName As LastName, (IF MemberEmail = '' THEN SELECT TOP 1 OtherEmail FROM OtherTable WHERE OtherID = MemberID) As EmailFROM PrimaryTableThanks for your time.Jason
View 8 Replies
View Related
May 15, 2008
I am using a SQLDataSource with the following Select query. If the "spouse" values are not in the database, I get the HTML non-breaking-space character back, rather than an empty string.
SelectCommand="select applicant_id, (applicant_last_name + ', ' + applicant_first_name) as applicant_name, CONVERT(varchar(10), applicant_dob, 101) as applicant_dob, applicant_ssn, (spouse_last + ', ' + spouse_first) as spouse_name, CONVERT(varchar(10), spouse_dob, 101) as spouse_dob, spouse_ssn from applicant where applicant_last_name like '%'+@last_name+'%' order by applicant_last_name"
Here is the relevant code-behind:
TextBox tb = (TextBox)formView.FindControl("SpouseName");
tb.Text = e.Item.Cells[4].Text;
DatePicker dp = (DatePicker)formView.FindControl("SpouseDateOfBirth");
if (e.Item.Cells[5].Text.Length > 0)
{
try // this is a try/catch because nbsp is not parsed as a date
{
dp.DateValue = DateTime.Parse(e.Item.Cells[5].Text);
}
catch
{
dp.txtDate.Text = "";
}
}
tb = (TextBox)formView.FindControl("SpouseSocialSecurityNumber");
tb.Text = e.Item.Cells.Text;
Thoughts?
Todd
View 4 Replies
View Related
Jun 21, 2014
SELECT DISTINCT S.EnrollNo
,S.Name
,ET.Descriptions AS EventName
,SA.Name AS AttendStudent
,'' AS AttendFaculty
FROM StudentEvent SE
INNER JOIN SStudent S ON SE.PresentatorID = S.StudentID
[code]....
Expected OutPut
EnrollNo NameEventName AttendStudentAttendFaculty
PH1201ASHKARALIPresentationKUMARA Sawadkar
PH1201ASHKARALIPresentationNILESHRAJ. GNaveen
PH1201ASHKARALIPresentationSEENIVASAN Nitya
PH1201ASHKARALIPresentation-----------Ramesh
PH1201ASHKARALIPresentation-----------Sakthi
View 2 Replies
View Related
Sep 15, 2007
I'm trying to import XML Data from a .xml file and then running openXML command to insert relevent data into SQL server 2005.
First I'm importing XML records with help of Script Task(In SSIS) in batch of 1000 records in temporary String n passing this to Stored procedure ,which parses XML String with help of "sp_xml_preparedocument" .This SP it gives following error..
"An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name."
I'm clueless about error,can some someone guide to workaround?
View 5 Replies
View Related
Mar 27, 2007
Hi all,I drag sqldatasource to my form, and then adding a button there. I want when clicking the button to be able to use the sqldatasource1.select statement . I found some parameters that this method used but still dont know how to figure it out, which was IEnumerable Select (DataSourceSelectArguments a)for example when the button it clicked I want to perform the select * from employee Thanks
View 1 Replies
View Related
Mar 28, 2007
I would like to use the value returned from my SqlDataSource SELECT method, in the INSERT method for the same SqlDataSource.
Any ideas how this is done?
View 1 Replies
View Related
Jul 20, 2007
I'm sure that is really simple, but how do I pass a parameter with multiple value to a SQLdatasource?
ex: SELECT field1 from tblTableA where idTableA IN ( @Param1)
Let's say I want to pass 1,2,3,4 as Param1 (SELECT field1 from tblTableA where idTableA IN ( 1,2,3,4))
How I am supposed tu use the .SelectParameters.Add() to pass a list of integers instead of a single value??
Thanks in advance.
View 2 Replies
View Related
Aug 10, 2007
I am using <asp:SqlDataSource ID and for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly:
SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @LastName) ORDER BY [LastName]DESC">
I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE". In this situation how would the syntax be written.... I tried:
LastName LIKE '%" & LastName & "%'"
But I get an error???? Any suggestions, please...
Thanks !!
View 4 Replies
View Related
Nov 15, 2007
I have a SqlDataSource object that is bound to a GridView control. I have configured the SqlDataSource with a default select command. Under certain values of query strings on the URL for this page (Default.aspx), I want to change the select command. So I put the statements in the Page_Load method for Default.aspx to define SqlDataSource1.SelectCommand. The changed SelectCommand works fine for the first page of GridView data and shows 5 GridView pages, but if I switch to one of the other pages, it seems to revert to the default SelectCommand (which generates 19 GridView pages). I assume I should put my code to change the SelectCommand somewhere else. Can someone help me with where to put it? Thanks!
View 1 Replies
View Related
Dec 5, 2007
Is it possible to use a WHERE-IN statement with a SqlDataSource control. For instance:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="SELECT [Id], [Name], [Phone] FROM [Table1] WHERE ([Id] IN @Id)">
<SelectParameters>
<asp:Parameter DefaultValue="( 1, 3, 5, 7, 11 )" Name="Id" Type="Int32" />
</SelectParameters></asp:SqlDataSource>
I'm hoping the gridview would then display a table with rows for records 1,3,5,7,11. Thanks for any help...
View 2 Replies
View Related
Dec 1, 2005
hiplz can any one tellHow should i use select method of SqlDatasourceplz show little code toothanks
View 1 Replies
View Related
Apr 5, 2007
Thanks in advance....
Let me simplify what I'm trying to do....
Basically, I want to run the following query by using SqlDataSource.
SELECT data FROM table WHERE row in ('one', 'two', 'three')
Simple enough....
So, here's my code.....which obviously doesn't work properly.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStr %>" SelectCommand="SELECT data FROM table WHERE row in (@username)">
<SelectParameters><asp:ControlParameter ControlID="TextBox1" Name="username" PropertyName="Text" Type="String" /></SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="TextBox1" runat="server" Text="'one', 'two', 'three'" Width="125px" Visible="True"></asp:Label>
Is my SelectCommand correct? Thanks.
View 3 Replies
View Related
May 26, 2007
My compiler says that the line in bold below is illegal. The error msg I'm getting is: No overload for method 'select' takes '0' arguments. How can I correct this error and execute a SELECT?
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource2.Select ();
}
protected void SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs e)
{string strReadyFirstName = e.Command.Parameters["@FirstName"].Value.ToString();string strReadyLastName = e.Command.Parameters["@LastName"].Value.ToString();
}
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [User_ID], [User_Name], [FirstName], [LastName], [Company_Name], [Department_Name] FROM [CompanyDepartment] WHERE ([User_Name] = @User_Name)" OnSelected="SqlDataSource2_Selected">
<selectparameters>
<asp:sessionparameter DefaultValue="TheirUserName" Name="User_Name" SessionField="TheirUserName" Type="String" />
</selectparameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Jul 20, 2007
I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid. I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e. SelectCommand="usp_reports_shortages" to "usp_reports_shortagesbyID" and "usp_reports_shortagesbyDate" depending on the value selected in the dropdownlist. So, is there any way to do this????<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$
ConnectionStrings:TESTDrivercommunication %>"
SelectCommand="usp_reports_shortages" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblDriver" Name="date1" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="lblTODate" Name="date2" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="DDlDriver" Name="driver" PropertyName="SelectedValue"
Type="Int32" />
<asp:SessionParameter Name="week" SessionField="s_week" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Sep 25, 2007
I am using the SqlDataSource to access the dB from my page. Basically this is what I do with it ds.SelectParameters.Clear();
ds.SelectParameters("Id", TypeCode.Int32, id.ToString());
DataSourceSelectArguments dssa = new DataSourceSelectArguments();
dssa.MaximumRows = 1;
dssa.AddSupportedCapabilities(DataSourceCapabilities.Page);
DataView dv = (DataView)ds.Select(dssa);
if (dv.Count > 0)
{
// collect the information
string title = (string)dv[index].Row.ItemArray[0];
}
And the SelectCommand attribute of the SqlDataSource is set in design mode to "SELECT * from vw_Items ORDER BY Category".
So, since I am trying to retrieve just the item with the given Id I was expecting just one record but when I step through I see that the data view has a count of 9 (all records in the table) !!!
What am I doing wrong here? why can't it return just one? as per the select statement which after adding the parameter should be something like "SELECT * FROM vw_Items WHERE ID = 5 ORDER BY Category
View 1 Replies
View Related
Dec 12, 2007
I'm a traditional asp guy and I'm having a heck of a time getting my arms around this SQLDataSource provided in ASP.NET 2.0
I've setup the Connection String and successfully used the SQLDataSource.Insert method.
I can't for the life of me figure out how to use the SQLDataSource.Select command.
I want to get the @@IDENTITY of the last record inserted.
Example:
---Works Fine---
SQLDataSource.InsertCommand = "Insert into Engines(Type, Description)values('" & DrpType.Text & "', '" & txtDescription.Text & "')"
SQLDataSource.Insert()
--- End Works Fine------- Doesn't Work---
SQLDataSource.SelectCommand = "Select @@IDENTITY as 'Identity'"
set RecordSetVar = SQLDataSource.Select <--- this wants some kind of arguements
--- End Doesn't Work---
View 1 Replies
View Related
Mar 13, 2008
Is it possible within a Sqldatasource to select data from one database table and the insert that data into another?
eg:
1 <asp:SqlDataSource ID="SqlDataSource" runat="server"
2 ConnectionString="<% Something %>"
3 InsertCommand="INSERT INTO [Resumes] ([ResumeID], [UserID], [CompanyID], [ResumeTitle], [ResumeDesc]) VALUES (@ResumeID, @UserID, @CompanyID, @ResumeTitle, @ResumeDesc)"
4 SelectCommand="SELECT [UserId], [CompanyID], [CompanyName] FROM [Info] WHERE ([UserId] = @UserId)" <SelectParameters>
5 <skm:MembershipUserIdParameter Name="UserId" />
7 </SelectParameters>
8 <InsertParameters>
9 <skm:MembershipUserIdParameter Name="UserId" />
10 <asp:Parameter Name="CompanyID" Type="Int32" />
11 <asp:Parameter Name="ResumeTitle" Type="String" />
12 <asp:Parameter Name="ResumeDesc" Type="String" />
13 </InsertParameters>
14 </asp:SqlDataSource>
View 1 Replies
View Related
Apr 25, 2008
I am trying to implement an "advanced search" feature on my ASP.NET 2.0 web form. I have a GridView control and a SqlDataSource. The SqlDataSource control successfully retrieves data when the SelectCommand attribute is set in the aspx page. I need to make it so when a user clicks on a button, it can take a value from a text box and use it in the WHERE clause. I have tried setting the SelectCommand programmatically and then DataBinding but it never accepts the new SelectCommand. What can I do to fix this?
View 5 Replies
View Related
Apr 29, 2008
Hello,
I am trying to write a select command that will select a table by year using asp:sqldatasource selectcommand. The table name includes the year, ie: table98, table99, table00, table01, etc. These tables do not have a 'year' field but there is a separate table called "Years" where each record is a different year. In other words, my select statement is static where is needs to be dynamic. Not sure if that makes sense, but any help would be great!!!
View 1 Replies
View Related
May 19, 2008
I cant seen to change the Select command for a SQL Datasourcetry #1 SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters("ProfileID").DefaultValue = pProfileID SqlDataSourceProfilesThatMatch.SelectParameters("LoggedInUsersZipcode").DefaultValue = pUsersZipCode SqlDataSourceProfilesThatMatch.SelectParameters("ZipDistance").DefaultValue = pDistance
NewProfilesThatMatchGridView.DataBind()
try #2 SqlDataSourceProfilesToBeMatched.SelectParameters.Clear() SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters.Add("ProfileID", pProfileID) SqlDataSourceProfilesThatMatch.SelectParameters.Add("LoggedInUsersZipcode", pUsersZipCode) SqlDataSourceProfilesThatMatch.SelectParameters.Add("ZipDistance", pDistance)
NewProfilesThatMatchGridView.DataBind()
No errors but no rows show in the gridview. If I debug and get the value strSQLForSearch and paste it into a new SQL query window I get results.
Any ideas????
Thanks
View 3 Replies
View Related
May 21, 2008
Why does this not work? <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:Parameter Name="@employeeID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>In code behind: protected void OnSourceSelecting(object sender, SqlDataSourceCommandEventArgs args) { // THIS LINE THROWS EXCEPTION: An SqlParameter with ParameterName '@campaignID' is not contained by this SqlParameterCollection. args.Command.Parameters["@employeeID"].Value = 3; // In future, will use dynamic value. } If, instead I do this: <asp:SqlDataSource ID="employeeSource" runat="server" ConnectionString="<%$ ConnectionStrings:mainDB %>" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" OnSelecting="OnSourceSelecting" SelectCommand="SELECT * FROM Employees WHERE ID = @employeeID"> <SelectParameters> <asp:QueryStringParameter Name="@campaignID" QueryStringField="id" /> </SelectParameters> </asp:SqlDataSource>(Of course I call page with EmployeePage.aspx?id=123)I get the exception: Must declare the scalar variable "@employeeID".
View 5 Replies
View Related
May 23, 2008
Hi,I've never used an SQLDataSource programatically before.Its not even executing the select command because I've got a trace on the database and theres no executing of the Sproc.Here's my code behind: Dim sql As New SqlDataSource sql.ConnectionString = ConfigurationManager.ConnectionStrings("My connection").ConnectionString sql.SelectCommand = "Select_All" sql.SelectCommandType = SqlDataSourceCommandType.StoredProcedure sql.SelectParameters.Add("@Param1", txtParam1.Text) sql.SelectParameters.Add("@Param2", txtParam2.Text) sql.DataSourceMode = SqlDataSourceMode.DataReader gvOrderItemsReport.DataSource = sql gvOrderItemsReport.DataBind() Any ideas? I must be missing something.
View 5 Replies
View Related
Mar 13, 2006
I Have a SQLDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"ConnectionString="<%$ ConnectionStrings:SqlServer %>"ProviderName="<%$ ConnectionStrings:SqlServer.ProviderName %>"SelectCommandType="StoredProcedure"SelectCommand="pe_getAppraisals" ConflictDetection="CompareAllValues"><SelectParameters><asp:ControlParameter ControlID="ddlPType" PropertyName="Value" Direction="Input" Name="PType" ConvertEmptyStringToNull="true" /><asp:ControlParameter ControlID="ddlClient" PropertyName="Value" Direction="Input" Name="Client" ConvertEmptyStringToNull="true" /><asp:Parameter Direction="Input" Name="PageSize" DefaultValue="20" Type="Int32" /></SelectParameters></asp:SqlDataSource>
When I load the page no results are returned. The only way I get results is to select an item from both drop down lists... My stored procedure works if I pass NULL or '' (an empty string) for PType and.or Client. Any Ideas on how to fix this or work around it? Is there any way for me to see what is being passed to the database for these values before I select an item from the dropdown lists?
Thank YouJason
View 3 Replies
View Related
May 16, 2006
I have multiple SqlDataSources on a page. I only want them to execute the select query when the user performs a certain action. Is this possible? Or do they all execute as soon as the page is loaded?
View 1 Replies
View Related
Sep 1, 2006
I would like to introduce myself as ASP.Net 2.0 beginner. I face a problem when using gridview and sqldatasource.
View 1 Replies
View Related
Jan 12, 2007
I'm having trouble using session data in my select parameters. If I manually plug a value into the selectcommand or create a hard coded value using a parameter it works, but if I try to use the session data the query pulls no results. I know the session data is set because I print the value at the top of the page, but for some reason it's not getting passed to the sessionparameter???
This is the datasource code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:flautoconnstr %>"
SelectCommand="SELECT * FROM [tblVehicles] WHERE ([profileid] = @profileid)">
<SelectParameters>
<asp:SessionParameter Name="profileid" SessionField="profileid" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
This is the line I use to set the session data.
Session["profileid"] = myQuote.Profileid;
View 1 Replies
View Related
