SqlDataSource.Select Is Working, But Assigning Null

Mar 28, 2008

The problem I'm having is described below all this code.

---------------------------------------------------------
My content page has a SqlDataSource:
<asp:SqlDataSource ID="sqlGetUserInfo" runat="server"
ConnectionString="<%$ ConnectionStrings:RemoteNotes_DataConnectionString %>"
SelectCommand="SELECT [UserFirstName], [UserLastName] FROM [Users] WHERE ([UserGUID] = @UserGUID)"
onselecting="sqlGetUserInfo_Selecting">
<SelectParameters>
<asp:Parameter Name="UserGUID" Type="String" />
</SelectParameters>
</asp:SqlDataSource> 

-----------------------------------------------------
Inside my OnSelecting event, I have: protected void sqlGetUserInfo_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
sqlGetUserInfo.SelectParameters["UserGUID"].DefaultValue = Membership.GetUser().ProviderUserKey.ToString();


---------------------------------------------------------------------
And, inside my Page_Load, the relevant code is:
//String strUserFirstName = ((DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty)).Table.Rows[0]["UserFirstName"].ToString();
DataView dvUserDetails = (DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty);
if ((dvUserDetails != null) && (dvUserDetails.Count > 0))
{
DataRow drUserInfo = dvUserDetails.Table.Rows[0];

lblHelloMessage.Text = "Hello, " + drUserInfo["UserFirstName"].ToString() + ((drUserInfo["UserLastName"].ToString() == "") ? "" : " " + drUserInfo["UserLastName"].ToString());

}  
---------------------
Now, here's the problem. My "if" statement is never executing because "dvUserDetails" is null. However, when I break the execution and put a
watch on the actual Select() statement, it shows a DataView with the correct return rows! You can see the commented line where I tried to
bypass the DataView thing (just as a test), but I get an object reference is null error.

The weird thing is that it was working fine one minute, then started getting "funky" (working, then not, then working, then not), and now it just doesn't work at all. All this
without me changing one bit of my code because I was checking out some UI flow and stopping and restarting the application. I've tracked down
the temporary files directory the localhost web server runs from, deleted all those files, and cleaned my solution. I've even tried
rebooting, and nothing seems to make it work again.

My relevant specs are VS2008 9.0.21022.8 RTM on Vista Enterprise x64. 

View 3 Replies


ADVERTISEMENT

SqlDataSource.Select Command Not Working?

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

Getting The Value Of The Sqldatasource And Assigning It To A Label

Mar 1, 2008

Hi,
I have a sqldatasource which returns the result I want, but I need to assign it to a label or text box.  Is there an easy way of doing this?  I attempted it using this code:
PropertyFriendIDLabel.Text = PropUserIdSqlDataSource
Thanks

View 4 Replies View Related

Assigning Value To SessionParameters In SqlDataSource

May 22, 2008

Hello, 
I am using SessionParameters within a sqldatasource control, which is the datasource for a formview control.
The Session["PoolID"] has a value which is '0000009485'.<InsertParameters><asp:SessionParameter SessionField="PoolID" Name="pool_id"  Size="10" Type="String" ConvertEmptyStringToNull="true" />      
</InsertParameters>But when I click 'Save' I get the message 'Cannot insert the value null into column 'pool_id'. Column does not allow nulls. Insert fails'.Do i need to specify something like DefaultValue = <%Session["PoolID"] %>?Thank you in advance for your help.RajanP.S. How can I avoid double line spacing while writing a post? Thanks.

View 2 Replies View Related

Xml Value Method Randomly Returns Null When Assigning To A Variable

Apr 20, 2007

I'm using SSEE 9.0.3042 and started loosing sleep over trying to figure out why the following piece of SQL



DECLARE @x xml

DECLARE @a nvarchar(400);

DECLARE @b nvarchar(400);

DECLARE @c nvarchar(400);

SET @x=N'<SuchKriterienDataSet xmlns="http://tempuri.org/SuchKriterienDataSet.xsd">

<SuchKriterien><Index>0</Index><Name>a</Name><Wert>Test1</Wert><Bedingung>0</Bedingung></SuchKriterien>

<SuchKriterien><Index>1</Index><Name>b</Name><Wert>Test2</Wert><Bedingung>0</Bedingung></SuchKriterien>

<SuchKriterien><Index>2</Index><Name>c</Name><Wert>Test3</Wert><Bedingung>0</Bedingung></SuchKriterien>

</SuchKriterienDataSet>';

WITH XMLNAMESPACES ('http://tempuri.org/SuchKriterienDataSet.xsd' AS ds)

SELECT

@a=@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "a"])[1]','nvarchar(400)'),

@b=@x.value('(/dsuchkriterienDataSet/dsuchkriterien/ds:Wert[../ds:Name = "b"])[1]','nvarchar(400)'),

@c=@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "c"])[1]','nvarchar(400)');

WITH XMLNAMESPACES ('http://tempuri.org/SuchKriterienDataSet.xsd' AS ds)

SELECT

@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "a"])[1]','nvarchar(400)'),

@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "b"])[1]','nvarchar(400)'),

@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "c"])[1]','nvarchar(400)')

UNION ALL

SELECT @a,@b,@c



returns



Test1 Test2 Test3

Test1 NULL Test3



instead of



Test1 Test2 Test3

Test1 Test2 Test3



This is the shortest repro I could assemble. Once you increase the number of variables, a random pattern of dropped values emerges.



I could repro on fully patched Windows XP German w/ SSEE ENU and Windows Server 2003 64-bit w/ SSEE GER.



Any workaround highly appreciated.

Thx,

Henry

View 5 Replies View Related

Assigning A Select Value To Each Row Of A Dataset

Oct 4, 2007

I have the following problem:
in a data flow, if inserting new records, there are columns that take some default values. These default values are kept in a table in case the user wants to change them some day. Def. values could not be assigned at a table level because there's another dataflow that populates the same table, but the rules for the default values are different.

Since I want to extract these values only if there is at least one new row, I'm not fond of the idea to use Execute SQL Task (to save the default values in a variable) before the actual Data Flow. What are my options in getting these values in a Data Flow right before inserting? Thank you for the help.

View 5 Replies View Related

WITH RETURNS NULL ON NULL INPUT Not Working?

May 3, 2006

Hello.

I've built a sample CLR function with the following declaration....

CREATE FUNCTION GetManager(@DeptCode nvarchar(3))
RETURNS nvarchar(1000)
WITH RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME Assembly1.[ClassLibrary1.MyVBClass].MyManager

And it works as expected, except when I use NULL:

DECLARE @MyManager nvarchar(1000)
EXEC @MyManager = dbo.GetManager NULL
PRINT @MyManager

It returns the value "Unknown" as it would have for any unknown DeptCode, as-programmed.

I'm of the theory it should have returned NULL without actually firing the function? Or is this only for non-CLR items... or stored procedures, not functions?

View 3 Replies View Related

Assigning Column Types In Select Into

Jul 23, 2005

Group,Is there a way to assign nullability on a column when using a select into?I've tried some of the usual things like coalsce, isnull, and cast. Sincethe new table gets definition from the source table or can be somewhatadjusted with cast is there a way to cast a not null? In the example belowhow can I select into causing tableone_new..col2 to be not null. Wetypically must use an alter statement after the select into but this seemsinefficient.Thanks,Raycreate table tableone (col1 int not null,col2 int )goinsert tableone values (1, 1)insert tableone values (2, 2)insert tableone values (3, 3)goselectcol1,col2into tableone_newfrom tableonegoexec sp_help tableone_newgodrop table tableonegodrop table tableone_newgo

View 3 Replies View Related

SELECT A Single Row With One SqlDataSource, Then INSERT One Of The Fields Into Another SqlDataSource

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

Assigning Results Of A Select Query To Variables...

Jul 13, 2004

Hi,

I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.

So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like
"select height, width, depth from products where id=@idinput"

OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?

This is what I've got so far...

[code]
cmdSelect = New SqlCommand( "GetProd", connstr )
cmdSelect.CommandType = CommandType.StoredProcedure
dbcon.Open()

dbcon.Close()
[/code]

The main prob is just what to connect this record to in order to access the individual fields.

Thx :)

View 2 Replies View Related

Update Not Working In An SQLDataSource

Sep 6, 2006

I'm new to ASP and ASP.NET so I used the Wizards in Visual Web Deverlopment Express 2005 to build the following code:<asp:DetailsView ID="DetailsView" runat="server" DataSourceID="TracksDataSource"
Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False">
<Fields>
<asp:BoundField DataField="pk_trackID" HeaderText="pk_trackID" ReadOnly="True" SortExpression="pk_trackID" />
<asp:BoundField DataField="trackName" HeaderText="trackName" SortExpression="trackName" />
<asp:BoundField DataField="trackPath" HeaderText="trackPath" SortExpression="trackPath" />
<asp:BoundField DataField="lyrics" HeaderText="lyrics" SortExpression="lyrics" />
</Fields>
</asp:DetailsView>

<asp:SqlDataSource ID="TracksDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:connectionString %>"
SelectCommand="SELECT * FROM [Tracks] WHERE ([pk_trackID] = @pk_trackID)" UpdateCommand="UPDATE [Tracks] SET [trackName] = @trackName, [trackPath] = @trackPath, [lyrics] = @lyrics WHERE [pk_trackID] = @pk_trackID" >
<UpdateParameters>
<asp:Parameter Name="trackName" Type="String" />
<asp:Parameter Name="trackPath" Type="String" />
<asp:Parameter Name="lyrics" Type="String" />
<asp:Parameter Name="pk_trackID" Type="String" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TracksListBox" Name="pk_trackID" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>  However, when I click Edit, change something, and then Update it doesn't update the database. However, if I remove the DataField bindings and use the AutoGenerateRows feature it works fine.

View 7 Replies View Related

SQLDataSource Update Using Parameters Not Working

Jul 10, 2006

I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast.  And then firing off the update command that contains that parameter from a button.  But it's not changing my data on my server when I do so.
I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes.  All this works fine, just not sp that should update my data when I hit the submit button.
It's supposed to update one of my tables to whatever the selected value is from my drop down list.  But it doesn't even change anything.  It just refreshes the page and goes back to the original value for my drop down list.
Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc.  It just won't change the data from the page when I try to.
 
This is what the stored procedure looks like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS
BEGIN
UPDATE [Current_tbl]
SET ID = @SelectedID
WHERE PrimID = '1'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my aspx page:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"
UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;">
<tr>
<td colspan="2" style="font-family:Tahoma; font-size:10pt;">
Please select one of the following:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%;">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%;">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label>
</td>
</tr>
</table>
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my code behind:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Imports System.Data
Imports System.Data.SqlClient
Partial Class Editor
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Saved_lbl.Text = ""
Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;")
Dim View1 As New DataView
Dim args As New DataSourceSelectArguments
View1 = SQLDS_Fill.Select(args)
Dim Row As DataRow
For Each Row In View1.Table.Rows
Ver_ddl.SelectedValue = Row("ID")
Next Row
SQLDS_Fill.Dispose()
End Sub
Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click
SQLDS_Update.Update()
Saved_lbl.Text = "Thank you. Your changes have been saved."
SQLDS_Update.Dispose()
End Sub
End Class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Any help is much appreciated.

View 4 Replies View Related

Connection Pooling Not Working With SqlDataSource

Jun 14, 2008

My total test page is shown below.  I monitor the connections by SP_WHO2.  Without the second call, connection pooling seems to be working, ie I refresh my browser repeately but the number of connections as seen from SP_WHO2 does not increase.
However, if I have the second call, every time I refresh the page at the browser, the number of connections increases by one.  This is obviously not acceptable in a real world application.
I tried both Integrated Authentication (with no impersonation) and using a hardcoded service account.  Both have the exact same results.  In fact this test is not about multi-user yet, it is the same single user just refreshing the same page.
May I know what have I done wrong?  All the documentation from Microsoft says close the connection after using it.  In the case of SqlDataSource how do I close the connection?
Thanks 
 <%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
SqlDataSource1.SelectCommand = "Select ID from Master";
System.Data.SqlClient.SqlDataReader reader =
(System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
if (reader.HasRows && reader.Read())
Label1.Text = reader["ID"].ToString();
SqlDataSource1.Dispose();

//second call: read from another table
SqlDataSource1.SelectCommand = "Select Name from Students";
reader = (System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
if (reader.HasRows && reader.Read())
Label1.Text += reader["Name"].ToString();

reader.Close();
}
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
DataSourceMode="DataReader"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
 

View 2 Replies View Related

SqlDataSource Insert Query Is Not Working Properly

Jan 7, 2008

 Hello,
I have a SqlDataSource that is not doing my inserts properly. even if the user is logged in (UserName!=""), it always inserts a null in the UserName field.
SqlDataSource:
         <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imLLConnectionString %>"            DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID"            InsertCommand="INSERT INTO [tblDiaryEntries] ([DiaryEntry], [Subject], [EntryDate], [UserName]) VALUES (@DiaryEntry, @Subject, @EntryDate, @UserName)"            SelectCommand="SELECT [DiaryEntry], [Subject], [EntryDate], [DiaryEntryID], [UserName] FROM [tblDiaryEntries] WHERE [UserName]=@UserName"            UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [Subject] = @Subject, [EntryDate] = @EntryDate WHERE [DiaryEntryID] = @DiaryEntryID">            <DeleteParameters>                <asp:Parameter Name="DiaryEntryID" Type="Int32" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="DiaryEntry" Type="String" />                <asp:Parameter Name="Subject" Type="String" />                <asp:Parameter Name="EntryDate" Type="String" />                <asp:Parameter Name="UserName" Type="String"/>                <asp:Parameter Name="DiaryEntryID" Type="Int32" />            </UpdateParameters>            <InsertParameters>                <asp:Parameter Name="DiaryEntry" Type="String" />                <asp:Parameter Name="Subject" Type="String" />                <asp:Parameter Name="EntryDate" Type="String" />                <asp:Parameter Name="UserName"  Type="String"/>            </InsertParameters>        </asp:SqlDataSource>
 
and from code behind, i do:
    protected void Page_Load(object sender, EventArgs e)    {        if (!this.IsPostBack)        {            SqlDataSource1.SelectParameters.Add("UserName", this.User.Identity.Name);            SqlDataSource1.InsertParameters.Add("UserName", this.User.Identity.Name);                        }    }
 
Any ideas/suggestions? Thanks! 

View 9 Replies View Related

SQLDataSource And Null Values

Dec 20, 2006

I have a sproce that accepts null for one of its parameters I can execute the sproce and enter null and it works fine, it returns all rows. When I try doing this with my GridView and the SQLDataSource it does not work. I need some help in understanding how the SQLDatasource wants a null. Here is what the parameter row of the SQLDataSource looks like.
<asp:ControlParameter ControlID="EnteredByText" DefaultValue="Null" Name="EnteredBy" PropertyName="Text"
Type="String" ConvertEmptyStringToNull="true" />
In my sproce I have setup the parameter as follows;
@EnteredBy Nvarchar(50)=Null
In my WHERE Clause I have:
WHERE (tblClient.EnteredBy = @EnteredBy OR @EnteredBy IS NULL)

View 3 Replies View Related

Sqldatasource Return Null !

Apr 12, 2008

hi
 i am trying to get the output of the select statements of sqldatasource :
 protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)        {            DataView dv;            dv = (DataView)(this.SqlDataSourcePictures.Select(DataSourceSelectArguments.Empty));        }
}
the problem is that dv returns null ?
 and here is the sqldatasource definition in .aspx page
<asp:SqlDataSource ID="SqlDataSourcePictures" runat="server" ConnectionString="<%$ ConnectionString:con1%>"
SelectCommand="SELECT URL FROM SchoolPictures WHERE (School_Code = @School_Code) AND (SchoolPictureCategory = @SchoolPictureCategory)" OnSelecting="SqlDataSourcePictures_Selecting" OnSelected="SqlDataSourcePictures_Selected">
<SelectParameters>
<asp:QueryStringParameter Name="School_Code" QueryStringField="bid" Type="Int16" />
<asp:ControlParameter ControlID="ddlCat" Name="SchoolPictureCategory" PropertyName="SelectedValue"
Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>
 
thanks for help

View 2 Replies View Related

SQLDataSource Parameters Passing Null

Aug 23, 2007

 I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!?  I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter.
 I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter?
Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure"
DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="locationStationId" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="locationStationId" Type="String" />
<asp:Parameter Name="locationType" Type="String" />
<asp:Parameter Name="locationName" />
<asp:Parameter Name="division" Type="String" />
</InsertParameters>

View 3 Replies View Related

How To Pass A Null To SelectParameters In SqlDataSource?

Feb 17, 2006

How to pass a null to SelectParameters in SqlDataSource?
The type of "CreateDate" is DateTime, the following code can be run correctly!
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "2006-11-12";
now I hope to pass null value to the Parameter "CreateDate", but the following 3 section codes don't work!
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = string.Empty;
or
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue =null;
or
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "";

View 4 Replies View Related

Filtering SqlDataSource To Show All Vs. Non-null Records

Aug 29, 2006

Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it.  In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object).  When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null.  But I've been unable to construct the WHERE clause of the SQLDataSource object correctly.  I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time.  Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition?  Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???)  Seems like a bit of overhead with that approach.  I'm hoping there's a more elegant way to get this done.  Please let me know if you need more information.  Thanks in advance. Bill

View 2 Replies View Related

How To Pass NULL To SQLdatasource Stored Param

Sep 23, 2007

I'm developing a web app using VS2005.  I have a webpage with panel containing a gridview populated by a SQLdatasource.  The SQLdatasource in turn is populated by a stored procedure that can take up to 5 parameters.  The user types in up to 5 separate words (searchterms) in a text box which are then parsed and passed to the stored proc in the datasource which performs a fulltext search.  The gridview then becomes visible.  My problem is that unless the user types in 5 searchterms (no less), the gridview returns zero rows.  5 searchterms returns the proper results.  Somehow, I need to be able to pass in null or empty values for unneeded parameters.
I've tested the stored procedure in Query Analyzer and from within the SQLdatasource configuration (using Test Query) using 0 up to 5 parameters and it works fine, so that's not my problem.  Here's the code that runs after the user types in their search term(s) and presses a button:Public Sub FTSearch_Command(ByVal sender As Object, ByVal e As CommandEventArgs) Handles btnFullText.Command
Dim x As Integer
pnlFullText.Visible = Falsefiltertext = Replace(txtSearchTxt.Text, "'", "''")
If Not filtertext Is Nothing Then
filtertext = filtertext.Trim
Else
Return
End IfDim arrayString() As String = filtertext.Split(" ")
Dim length As Integer = arrayString.LengthFor x = 0 To (length - 1)
If Not arrayString(x) Is Nothing ThenSelect Case x
Case 0 : lblFTParm1.Text = arrayString(0)Case 1 : lblFTParm2.Text = arrayString(1)
Case 2 : lblFTParm3.Text = arrayString(2)Case 3 : lblFTParm4.Text = arrayString(3)
Case 4 : lblFTParm5.Text = arrayString(4)
End Select
End If
Next
pnlFullText.Visible = "True"
End Sub
Any ideas? 
Thanks in advance.
 

View 2 Replies View Related

Configure SqldataSource With Null Date Values

Mar 20, 2008

I am attempting to create search parameters for a gridview control and I am experiencing a small issue. When I get to a date parameter I am unable to display null values.
I setup a sqldatasource and created the parameters below to handle the selections for minimum date required and the maximum date required for the date columns in the database. The problem is I do not know how to display null dates.
Is there a way to incorporate something into the search page to show null dataes? 
   
Sql Where Clause  1 WHERE (LSS_Requests.TypeCode = @TypeCode) AND (LSS_Requests.PersonNo LIKE '%' + @PersonNo + '%') AND
2 (LSS_Requests.TicketNo LIKE '%' + @TicketNo + '%') AND (LSS_Requests.Name LIKE '%' + @Name + '%') AND
3 (LSS_Requests.RequestName LIKE '%' + @RequestName + '%') AND (LSS_Requests.RequiredDate >= @Fromrequireddate) AND
4 (LSS_Requests.RequiredDate <= @ToRequiredDate) AND (LSS_Requests.OriginationDate >= @SearchFromOriginationDate) AND
5 (LSS_Requests.OriginationDate <= @SearchToOriginationDate) AND (LSS_Requests.LastUpdated >= @SearchFromUpdatedDate) AND
6 (LSS_Requests.LastUpdated <= @SearchUpdatedToDate) AND (LSS_Users_1.userFullName LIKE @SearchDDLUsers) AND
7 (LSS_Users.userFullName LIKE @SearchddlCIAsignee) AND (LSS_Requests.TypeCode = 'CC') AND (LSS_lu_Status.stNm LIKE '%' + @StatusName + '%')
  

View 2 Replies View Related

Passing NULL Value Not Working

Oct 24, 2007

I currently have a stored procedure that looks something like this  SELECT * FROM tblQuestions WHERE Title LIKE ISNULL('%'+@Name+'%', Title)I have a form that supplies this value.  This statement should make it so that if a NULL value is passed in, then it will return all the rows, if some text is specified, then it will not.  On my SQLDataSource on the page where the parameter is supplied I have set ConvertEmptyStringsToNull to True, but when I say in my code,SqlDataSource1.SelectParameters.Add("@Name", TextBox1.Text);It won't give me back any of the rows, I know that the stored procedure works fine because I can get it to work by a basic query and other testing on it, so somewhere in my form, the NULL value isn't being passed, I belive that it is passing an empty string and I don't know why.  Thank you in advance /jcarver 

View 4 Replies View Related

&#39;not Null &#39; Not Working!!URGENT!!

Feb 28, 2000

Hello, everyone!
I have some fields that must have data entered into them(i.e they shd not be left blank) & since the data has to be different almost everywhere I cannot set a default also. However I have defined these columns as NOT NULL, but still when data is entered they accept null values. WHY?
Please help!
Thanks in advance!
Adie

View 2 Replies View Related

If @Title IS NULL Not Working

Jul 3, 2006

I'm building a stored procedure to edit a row in my database but first I'm wanting to check for null values in the parameters and set them to their respective value in the row I'm attempting to edit.

Here's my code:

Code:

CREATE PROCEDURE dbo.spModifyProject
(
@ProjectID int,
@Title nvarchar(50),
@Description nvarchar(50),
@DueDate smalldatetime,
@ProjectLead nvarchar(50),
@Completed bit
)

AS

IF @Title IS Null Then
SET @Title = (SELECT Title FROM Projects WHERE [ID] = @ProjectID)
END IF



I get syntax errors in two places. The first is near Null and the second is near END. Any help you can give is appreciated.

View 2 Replies View Related

Date IS NULL Not Working......?

Jul 20, 2004

Hi,

I have a problem with a SQL that check if a date column IS NULL.
On one server the check work ok but on another (the same data is on both - restored copy) the check does not find any NULL values.
If check where datecolumn =convert(datetime,'9999-12-31 23:59:59.000',121) I get the same result as when checking for NULL in the other.

Is there any parameter set somewhere that tell the server to return a value even if NULL is stored in the database?

thank you for reading,
YakoBay

View 3 Replies View Related

If @Title IS NULL Not Working

Jul 3, 2006

I'm building a stored procedure to edit a row in
my database but first I'm wanting to check for null values in the
parameters and set them to their respective value in the row I'm
attempting to edit.



Here's my code:

Code: CREATE PROCEDURE dbo.spModifyProject
(
@ProjectID int,
@Title nvarchar(50),
@Description nvarchar(50),
@DueDate smalldatetime,
@ProjectLead nvarchar(50),
@Completed bit
)

AS

IF @Title IS Null Then
SET @Title = (SELECT Title FROM Projects WHERE [ID] = @ProjectID)
END IF



I get syntax errors in two places. The first is near Null and the second is near END. Any help you can give is appreciated.

View 3 Replies View Related

Update Via SQLDataSource Fails For Record With A Null Value In One Column

Oct 16, 2006

Hi,I have an updatable DataGrid linked to a SQLDataSource in a web site developed using VS2005. Update works fine unless a value in the existing row is Null. Only one column in the database allows nulls.Putting a debug stop in the SqlDataSource1_Updating event, I checked the parameter value in the Immediate window and got the following result:?e.Command.Parameters(16){System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameter: {System.Data.SqlClient.SqlParameter}DbType: Int32 {11}Direction: Input {1}IsNullable: FalseParameterName: "@original_FiresolveJobNo"Size: 0SourceColumn: ""SourceColumnNullMapping: FalseSourceVersion: Current {512}Value: NothingIs there a property I can set to generate a suitable Null check clause for the Update statement?Many Thanks,Keith.

View 3 Replies View Related

Deleted Event On Sqldatasource And Output Parameters... Always NULL!!

Apr 18, 2007

I have an event:
Private Sub SqlDataSourceIncome_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceIncome.Deleted
Dim command As SqlClient.SqlCommand
command = e.Command
If command.Parameters("@nReturnCode").Value <> 0 Then
    DROPDEAD()
End If
That  fires from:
<DeleteParameters>
<asp:Parameter Name="nDeletebyId" Type="Int64" />
<asp:Parameter Name="nOtherId" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />
</DeleteParameters>
End Sub
 
When I:
GridViewIncome.DeleteRow(GridViewIncome.SelectedRow.RowIndex)
But nReturnCode is ALWAYS NULL... I even did a stored procedure that just:
ALTER PROCEDURE [dbo].[sp_nDeletebyId]
 @nReturnCode bigint output,
@nReturnId bigint output AS
SET @nReturnCode = 0
SET @nReturnId = 0
And STILL got nothing but the NULLS... the insert & update stuff works fine, with identical code... it's just the DELETED event that I can't seem to knock.  Has anyone seen this before?  The above sample stored proc did return 0 when executed one the server...
and, BTW, the row is deleted!
 
Chip Kigar
 

View 2 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Scd Type 2 Historical Component Not Working When Using Not Null In Columns

Aug 23, 2007



I have a timestamp column and a username column with default values of getdate and system user in my table and they are defined as not null.

even though i donot use these columns in scd type 2 in wizard

if the columns are not null the scd deosnot work and If the columns are defined null they work

Can anyone please explain or help me with what might be the problem associated with this

View 1 Replies View Related

NULL Values In A SELECT In Another SELECT

Jan 23, 2008

Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks

View 7 Replies View Related

Sqldatasource Select Statement

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

How Do I Use A Value Returned From SQLDataSource Select?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved