Null Problem With Databinding
Apr 18, 2007
We are tired of null values coming out of from the database and we are directly coding behind UI decleratively.
We set up a null value coding standarts but I wonder what are the disadvantages to our approach
DataType The default value we send ALLOW NULL
int,bit,decimal 0 NO
nvarchar(string) " "(one space) NO
datetime YES
binary YES
*in datetime we do not allow nulls in places such as _CREATEDATE and _LASTMODIFYDATE fields.
What are other approaches for null besides writing your own layer to handle it ?
View 3 Replies
ADVERTISEMENT
Sep 17, 2007
hello...
Probably a simple question but I have to ask...using visual basic 2005 express...
I have two tables one with last name and basic info, one with detailed info. Both set up with Primary key and Foreign key.
I have a combo box that displays the last names and then by clicking
the last name, text boxes fill with the "other" data from table1,
however,
there are some text boxes that I would like to fill with data from the table2.
Any pointers??
View 1 Replies
View Related
Mar 25, 2008
I’m trying to teach myself C# and trying to create a simple (so I thought) little database using Infragistic's UltraWebGrid (Althought my question is more ASPish). Every sample I run across seems to only show the very basics and doesn’t seem to go into anything more than just a simple table and displaying it in a Gird. Which is pretty easy and I can do that all day and night.
Anyway, my database has three tables in a one-to-many relationship. The database is of Trucking companies and which states they service and they can service multiple states.
The database is like this:
Compnies:
TruckerID, Company Name, Address, etc.
ServiceStates:
TruckerID, StateID
StateNames:
StateID, StateName, StateAbbreviation
I would like to show the States that they service in a Hierarchical Grid.
I can write the SQL to do what I want to, basically do an inner join on ServicesStates and StateNames based on the TruckerID. I can sit all day and night getting what I would like to display in the WebGrid in the query builder on the SQL server. I figured it wouldn’t be much of a stretch to be able to do that in UltraWebGrid (Or whatever floats your boat).
Anyway, on to my question. :)
While browsing the samples on everything, I've figured out, that I will need to use the SqlDataAdapter Class, to access the database. Then I'll need to use the SQLConnection Class, and populate a DataSet.
Now while in VS I can go to WebSite -> Add New Item -> DataSet slap my tables on there and away I go (at least it seems that way). If I do that, what exactly is the difference between doing that and using the dataset class? When I do that, is that not creating the dataset? I see all the classes it created (I named mine Trucking). I see all the SQL statements in the classes.
I imagine I must be missing something *REALLY* simple or this just went way over my head at by at least 40,000 ft.
Thanks,
marly
View 14 Replies
View Related
Jun 19, 2008
Hai,i'm new asp.net and vb.net
i have few textboxes and few dropdownlists.i want to insert data in (.mdf )database with these textboxes and dropdown lists
please anybody can tell me a sample and simple example.
Thanking you
View 3 Replies
View Related
Mar 11, 2007
Hi, I have a page created within VS 2005 which uses a detailsView with a SQLDataSource which has insert, edit and delete items allowed with it. The problem is that if I delete a record I dont want to refresh the page as I want to set a label value to say item deleted. The problem then though is to select the item to delete I have a drop down which populates the details view on index change, but if I delete the item I cannot do a databind when its complete because it just binds to the existing dataset and does not do a fresh call on the database.Is there a command I can run to refresh the dataset on click of the delete button?Thanks
View 3 Replies
View Related
Mar 4, 2008
Hi,i have a question, i have a VS2005 and a SQL server 2000 enterprise in my office that i used for web developement,iam new to this, i made a data driven site using some automated controls available in the toolbox, but i need to made some manual process, like i need to make a LABEL show the SUM of specific number fields from my data base, they say i have to make a OLEDB connection using VB, but i don't know how,can you tell me how?Thanks
View 13 Replies
View Related
Nov 9, 2006
Hey all,
I had a few questions about how to properly use the sqldatasource -
I have a form with a single dropdownlist, databound to an sqldatasource that just has a select statement. Over the course of time, the client has requested additional dropdownlists, all of which point to different sqldatasources. After I reached 4 or 5, I noticed the page started to load incredibly slower. I played around with the caching features, and after a good while of reading up on it, finally was able to cache the page /data and notice an increase.
Later, another 4 dropdownlists were added, 3 of which all take the same data. I assumed if I had 3 dropdownlists, which need the same data, I should use the same sqldatasource. What do you think? My page is behaving unaccepteably slow again.
As a test, I created a new page, and bound it in the same fashion. I slowly added more dropdownlists /sqldatasources, and found it lags a bit on the initial load time (maybe 10-12 seconds of processing time before the page loads). The tables it is pulling data from do not have a primary key, and i'm doing select statements that return roughly 100-200 rows of data (per dropdownlist on average). I'm only selecting 1 column name (no select * here)
I'm just curious if anyone has experienced similar issues, and also if there is anything I should do as a best practice while working with the sqldatasource. I've used hundreds of these on forms, and never experienced a problem. I only started noticing this on pages that have multiple. Also, when using multiple dropdowns that need the same data (I know that sounds goofy - why have 3 dropdownlists with the same data) should I use 3 individual datasources?
Thanks for your help in advance,
Mike
View 3 Replies
View Related
May 28, 2006
Hello,
I would like to databind a textbox (the Text property) and I have try the following syntax:
Text='<%# Bind("au_fname") %>'
but there's no Datasource property for this control. I have defined a SqlDatasource object but can I link it to my Textbox. I have looked at the properties of the control: there's a Expressions section in which I can configure a connection string but thar's all.
When I run the page I have no error but the textbox field is empty.
Thanks for your help.
View 1 Replies
View Related
Sep 18, 2007
Does anybody know a good way to get a (near) live view of a sqlce database that doesn't involve ResultSets? I tried to use a resultset with the Sensitive option set, but it doesn't like the joins that I have to do in order to have the data make sense to the user.
Thanks
View 1 Replies
View Related
Apr 27, 2006
I have a windows form containing some text boxes and radio buttons bount to an SQL Server database, with code like the following:
this.tbBorrowerLastName.DataBindings.Add (new Binding("Text", dsData.Tables["Results"],"BorrowerLastName"();
this.rbMale.DataBindings.Add(new Binding("Checked", dsData.Tables["Results"],"Male"))
If I add a new row and try to move there using the code below, nothing happens (neither movement to a new position nor error message), unless I remove the radio button bindings. (The same thing occurs if I attempt to move to any record that has a null value in the database field for a radio button).
myLastRow = dsData.Tables["Results"].NewRow();
dsData.Tables["Results"].Rows.Add(myLastRow);
I attempt to move by changing the value of the position property of the BindingContext object.
How can I retain the radio button bindings and still be able to add and move to a new row?
Any help would be appreciated.
View 1 Replies
View Related
Feb 19, 2008
Hello Everyone!
I am getting started with DataBinding with SSE 2005 and VC#
The problem i am facing is that i need to bind a group of radio buttons eg: Duration Type: Days, Weeks, Months, Years.
The field type in the database is tinyint and the values are 1,2,3,4 for Days, Weeks, Months and Years respectively.
How can i bind a group of radio buttons to the tinyint data in table?
Any help/hint is appericiated.
Thankyou for taking the time to read this.
View 1 Replies
View Related
Jan 22, 2007
Hello,
I have a page with a detailsview where I can add articles (Generated by visual studio). Now the table contains a field (Autor) wich must contain the username of the Autor from the article. But when I run my page now I have to give it in manually ( in a textbox). I've searching for a way to bind the Profile Username with the insert Sql Query, @Autor value.
I tought maybe I should insert the value of the Profile username in the textbox and put the textbox visibel on false.
But When i saw the component code I saw that Text= is already bound, so it's not possible to insert a value<asp:TextBox ID="auteurTextBox" Visible="true" runat="server" Text='<%# Bind("auteur") %>'> Here is whole the page code (line 23 is the textbox). 1 <asp:FormView ID="FormView1" runat="server" DataKeyNames="id" DataSourceID="SqlDataSource1" AllowPaging="True" CellPadding="4" ForeColor="#333333" style="left: 30%; position: relative">
2 <EditItemTemplate>
3 <asp:Label ID="idLabel1" Visible="false" runat="server" Text='<%# Eval("id") %>'></asp:Label>
4 <asp:TextBox ID="auteurTextBox" Visible="false" runat="server" Text='<%# Bind("auteur") %>'>
5 </asp:TextBox>
6 soort:
7 <asp:TextBox ID="soortTextBox" runat="server" Text='<%# Bind("soort") %>'>
8 </asp:TextBox><br />
9 titel:
10 <asp:TextBox ID="titelTextBox" runat="server" Text='<%# Bind("titel") %>'>
11 </asp:TextBox><br />
12 text:
13 <asp:TextBox ID="textTextBox" Height="200px" TextMode="MultiLine" Rows="20" Width="260px" runat="server" Text='<%# Bind("text") %>'>
14 </asp:TextBox><br />
15 <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
16 Text="Update">
17 </asp:LinkButton>
18 <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
19 Text="Cancel">
20 </asp:LinkButton>
21 </EditItemTemplate>
22 <InsertItemTemplate>
23 <asp:TextBox ID="auteurTextBox" Visible="true" runat="server" Text='<%# Bind("auteur") %>'>
24 </asp:TextBox><br />
25 Soort:
26 <asp:TextBox ID="soortTextBox" runat="server" Text='<%# Bind("soort") %>'>
27 </asp:TextBox><br />
28 titel:
29 <asp:TextBox ID="titelTextBox" runat="server" Text='<%# Bind("titel") %>'>
30 </asp:TextBox><br />
31 text:
32 <asp:TextBox ID="textTextBox" runat="server" Text='<%# Bind("text") %>'>
33 </asp:TextBox><br />
34 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
35 Text="Insert">
36 </asp:LinkButton>
37 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
38 Text="Cancel">
39 </asp:LinkButton>
40 </InsertItemTemplate>
41 <ItemTemplate>
42 <asp:Label ID="idLabel" Visible="false" runat="server" Text='<%# Eval("id") %>'></asp:Label><br />
43 <asp:Label ID="auteurLabel" Visible="false" runat="server" Text='<%# Bind("auteur") %>'></asp:Label><br />
44 soort:
45 <asp:Label ID="soortLabel" runat="server" Text='<%# Bind("soort") %>'></asp:Label><br />
46 titel:
47 <asp:Label ID="titelLabel" runat="server" Text='<%# Bind("titel") %>'></asp:Label><br />
48 text:
49 <asp:Label ID="textLabel" runat="server" Text='<%# Bind("text") %>'></asp:Label><br />
50 <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"
51 Text="Edit">
52 </asp:LinkButton>
53 <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"
54 Text="Delete">
55 </asp:LinkButton>
56 <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
57 Text="New">
58 </asp:LinkButton>
59 </ItemTemplate>
60 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
61 <EditRowStyle BackColor="#2461BF" />
62 <RowStyle BackColor="#EFF3FB" />
63 <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
64 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
65 </asp:FormView>
66 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
67 ConnectionString="<%$ ConnectionStrings<img src="images/smilies/biggrinn.gif" border="0" alt="">atabankConnectie %>" DeleteCommand="DELETE FROM [artikel2] WHERE [id] = @original_id AND [auteur] = @original_auteur AND [soort] = @original_soort AND [titel] = @original_titel AND [text] = @original_text"
68 InsertCommand="INSERT INTO [artikel2] ([auteur], [soort], [titel], [text]) VALUES (@auteur, @soort, @titel, @text)"
69 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [artikel2] WHERE [auteur] = @auteur"
70 UpdateCommand="UPDATE [artikel2] SET [auteur] = @auteur, [soort] = @soort, [titel] = @titel, [text] = @text WHERE [id] = @original_id AND [auteur] = @original_auteur AND [soort] = @original_soort AND [titel] = @original_titel AND [text] = @original_text">
71 <DeleteParameters>
72 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_id" Type="Int32">
73 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_auteur" Type="String">
74 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_soort" Type="String">
75 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_titel" Type="String">
76 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_text" Type="String">
77 </DeleteParameters>
78 <UpdateParameters>
79 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="auteur" Type="String">
80 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="soort" Type="String">
81 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="titel" Type="String">
82 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="text" Type="String">
83 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_id" Type="Int32">
84 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_auteur" Type="String">
85 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_soort" Type="String">
86 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_titel" Type="String">
87 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_text" Type="String">
88 </UpdateParameters>
89 <InsertParameters>
90 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="auteur" Type="String">
91 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="soort" Type="String">
92 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="titel" Type="String">
93 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="text" Type="String">
94 </InsertParameters>
95 </asp:SqlDataSource>
96 </LoggedInTemplate>
97 </asp:LoginView>
98 </asp:Content>
99
Can somebody help me?
View 14 Replies
View Related
Feb 16, 2007
Here's a new one I came across that stumped me for a bit.
DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'xxxx'.
It appears that one of my dropdown menus was referencing the old SqlDataSource1for its data source id.
I had already changed the name of the of the id to something else though.
To fix it, I just updated the reference to the new name.
Thought I'd share, just in case
View 2 Replies
View Related
Aug 11, 2006
I m trying to UPDATE database using FormView and SqlDataSource and here is my code:
<asp:SqlDataSource ID="sqlDS1" runat="server" ConnectionString="<%$ ConnectionStrings:myDB %>"UpdateCommand="UPDATE [Consultants] SET [firstName]=@FIRSTNAME,[lastName]=@LASTNAME,[skillCategoryID]=@SKILLCATID,[resourceManagerID]=@RMID,[AMgroupID]=@AMGROUPID,[SkillSet]=@SKILLSET,[statusID]=@STATUSID,[location]=@LOCATION,[comments]=@COMMENTS,[profile]=@PROFILE,[isAvailable]=@ISAVAILABLE,[dateModified]=getdate(),[focusID]=1 WHERE ([id]=@ID)">
<UpdateParameters>
<asp:FormParameter FormField="txtFName" Name="FIRSTNAME" />
<asp:FormParameter FormField="txtLName" Name="LASTNAME" />
<asp:FormParameter FormField="ddlSkillCat" Name="SKILLCATID" />
<asp:FormParameter FormField="ddlRM" Name="RMID" />
<asp:FormParameter FormField="ddlAMGroup" Name="AMGROUPID" />
<asp:FormParameter FormField="txtSkillset" Name="SKILLSET" />
<asp:FormParameter FormField="ddlStatus" Name="STATUSID" Type="Int16" />
<asp:FormParameter FormField="txtLocation" Name="LOCATION" />
<asp:FormParameter FormField="txtComments" Name="COMMENTS"/>
<asp:FormParameter FormField="txtProfile" Name="PROFILE" />
<asp:FormParameter FormField="cbAvailable" Name="ISAVAILABLE" />
<asp:QueryStringParameter Type="Int32"
Name="ID"
QueryStringField="id" />
</UpdateParameters>
</asp:SqlDataSource>
//*******************************************************************
<asp:FormView DefaultMode="Edit" ID="FormView1" runat="server" DataSourceID="sqlDS1" DataKeyNames="id">
<EditItemTemplate>
<tr>
<td class="blacktextbold">
Resource Manager:
</td>
<td class="blacktext">
<asp:DropDownList ID="ddlRM" CssClass="blacktext" runat="server" DataSource="<%#GetRM()%>" DataTextField="RMName" DataValueField="userID" SelectedValue="<%#Bind('RMID')%>" AppendDataBoundItems="true">
<asp:ListItem Value="-1">--Select RM--</asp:ListItem>
</asp:DropDownList>
<asp:CustomValidator ID="cvRM" runat="server" ValidationGroup="gpInsert" ControlToValidate="ddlRM" ClientValidationFunction="validateDropdown" OnServerValidate="servervalidateDropdown" Display="Dynamic" ErrorMessage="Required Field" CssClass="redtextsmallbold" />
</td>
</tr>
//********************************************************
here is my GetRM() function:
protected DataSet GetRM()
{
string strConnection = ConfigurationManager.ConnectionStrings["myDB"].ToString();
SqlConnection objConnection = new SqlConnection(strConnection);
String sqlSkillCats = "SELECT Roles.roleID, Roles.roleName, UsersInRoles.userID, UsersInRoles.roleID AS Expr1, Users.firstName + ' ' + Users.lastName AS RMName, Users.id AS Expr2" +
" FROM Users INNER JOIN" +
" UsersInRoles ON Users.id = UsersInRoles.userID CROSS JOIN" +
" Roles" +
" WHERE (Roles.roleName = 'accountmanager') AND (UsersInRoles.roleID = Roles.roleID) ORDER BY Users.firstName";
SqlDataAdapter objAdapter = new SqlDataAdapter(sqlSkillCats, objConnection);
objConnection.Open();
//ddlDataSet = new DataSet();
try
{
objAdapter.Fill(dsConsultantRM, "ConsultantRM");
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
objConnection.Close();
return dsConsultantRM;
}//******************************************************************
i get this error when i load the page:DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'RMID'.
basically i m trying to UPDATE database using the value selected in the DropDownList, can anyone tell me whats wrong here...PLZ HELP!
View 2 Replies
View Related
Sep 16, 2006
HAI guys,i am suneel.i have a problem..please help me..i am binding my datalist.i have set my dtakey field to the primary key of teh database.i am not displaying the primary key(file_id).but i am displaying the other fields in that table.i have set databinder.Eval(container,dataitem."file_name").i have put one delete button in the item template...if i want to build now...i am getting an error.....DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'file_ID'.wat i have to do..please get me the reply..
View 2 Replies
View Related
Mar 22, 2007
Hi,Seems like a lot of people are having a similiar problem that I am having right now, but I am not able to find the solution to it. On the Page_load event, the gridview does display the data from database. When I click a button to insert the same data but different Waste_Profile_Num value, it gives me the databinding error. Component_Profile_ID is declared as an Identity and it is the primary key to the table. Anyway help???Ok, the following are code that I have: 1 <asp:SqlDataSource ID="sqlDSMaterialComposition" runat="server" ConnectionString="<%$ ConnectionStrings:HAZConnectionString %>"
2 SelectCommand="SELECT dbo.Component_Profile.Component_Profile_ID AS Component_Profile_ID, dbo.Component.Component, dbo.Component_Profile.Concentration, dbo.Component_Profile.Range, dbo.Component_Profile.Waste_Profile_Num, dbo.Component.Component_ID FROM dbo.Component INNER JOIN dbo.Component_Profile ON dbo.Component.Component_ID = dbo.Component_Profile.Component_ID WHERE (dbo.Component_Profile.Waste_Profile_Num = @Waste_Profile_Num)">
3 <SelectParameters>
4 <asp:SessionParameter Name="Waste_Profile_Num" SessionField="Waste_Profile_Num" Type="Int32" />
5 </SelectParameters>
6 </asp:SqlDataSource>
7
8
9 <asp:GridView ID="GridViewMaterialComposition"
10 runat="server"
11 DataKeyNames="Component_Profile_ID,Component_ID"
12 AutoGenerateColumns="False" ShowFooter="true">
13 <Columns>
14 <asp:BoundField DataField="Component" HeaderText="Component" SortExpression="Component" FooterText="Total"/>
15 <asp:TemplateField HeaderText="Concentration" FooterStyle-Font-Bold="true">
16 <ItemTemplate>
17 <%# SumConcentration(decimal.Parse(Eval("Concentration").ToString())).tostring("N2") %>
18 </ItemTemplate>
19 <FooterTemplate>
20 <%# GetConcentration().tostring("N2") %>
21 </FooterTemplate>
22 </asp:TemplateField>
23 <asp:BoundField DataField="Range" HeaderText="Range" SortExpression="Range" />
24 <asp:BoundField DataField="Component_Profile_ID" HeaderText="Component_Profile_ID" ReadOnly="True" SortExpression="Component_Profile_ID" Visible="true" />
25 <asp:BoundField DataField="Component_ID" HeaderText="Component_ID" ReadOnly="True" SortExpression="Component_ID" Visible="true"/>
26 <asp:BoundField DataField="Waste_Profile_Num" HeaderText="Waste_Profile_Num" ReadOnly="true" SortExpression="Waste_Profile_Num" Visible="true" />
27 <asp:CommandField ShowEditButton="true" />
28 <asp:TemplateField Visible="false">
29 <ItemTemplate>
30 <asp:Label ID="lblComponentProfileID" runat="Server" Text='<% # Eval("Component_Profile_ID") %>'></asp:Label>
31 <asp:Label ID="lblComponentID" runat="Server" Text='<% # Eval("Component_ID") %>'></asp:Label>
32 </ItemTemplate>
33 </asp:TemplateField>
34 </Columns>
35 </asp:GridView>The following are the code-behind: 1 sqlSelect = "SELECT Range, Concentration, Component_ID FROM Component_Profile WHERE (Component_Profile.Waste_Profile_Num = " & previousWasteProfileNum & ")"
2 sqlDSMaterialComposition.SelectCommand = sqlSelect
3 Try
4 Dim dvComposition As Data.DataView = CType(sqlDSMaterialComposition.Select(DataSourceSelectArguments.Empty), Data.DataView)
5 For Each dr As Data.DataRow In dvComposition.Table.Rows
6 insertSql = "INSERT INTO Component_Profile ([Range], [Concentration], [Component_ID], [Waste_Profile_Num]) "
7 insertSql &= "VALUES (" & dr("Range").ToString.Trim & ", " & dr("Concentration").ToString.Trim & ", " & dr("Component_ID").ToString.Trim & ", " & CInt(Session("Waste_Profile_Num").ToString()) & ")"
8 sqlDSMaterialComposition.InsertCommand = insertSql
9 sqlDSMaterialComposition.Insert()
10 Next
11 Catch ex As Exception
12
13 End Try
View 1 Replies
View Related
Mar 13, 2008
I am actually a newbie to asp.net and i m using ASP.net 3.5 i.e, VWD 2008. i am using it for the first time as my tool to develop a website for my final year project. i am planning to develop an online job recruitment site like www.monster.com. Rigth now i am confused how will i manage my database. i've learned to use databinding concept of SQL SERVER in VWD 2008 but will it be enough to handle such huge # of Job postings and employers and as well as Resumes in pdf of word format? or do i have to create a separate databse in SQL Server and to connect it with my website? i am confused at the moment. please help me in this matter.
Regards,
Jigzy
View 6 Replies
View Related
Sep 20, 2006
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
View 3 Replies
View Related
Oct 20, 2015
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
Is it possible through SSRS designer?
View 5 Replies
View Related
Jul 15, 2015
I have a flat file with the following columns
SampleID Rep_Number Product Protein Fat Solids
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
View 7 Replies
View Related
May 3, 2007
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Key cannot be null. Parameter name: key
View 1 Replies
View Related
Nov 5, 2007
I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:
m_sqlCmd.ExecuteScalar();
The m_sqlCmd has been fed an SQLParameter with direction set to output.
When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok.
When the row doesn't exist, in my watch I have:
m_sqlParam.SqlValue with value {Null}
I can't seem to work out how to test this value out.
I've tried several things but none seem to work.
This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??
if (m_sqlParam.SqlValue != null)....
{
// I'm here!! I thought the watch says this is null???
}
Sorry if this is obvious, but I can't work this one out!!
View 3 Replies
View Related
Mar 26, 2007
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
View 1 Replies
View Related
Apr 2, 2007
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
View 8 Replies
View Related
Jun 28, 2004
I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks
View 7 Replies
View Related
Feb 26, 2004
I'm trying to set the concat... option to OFF
all the time and in all my databases
I tried this command
USE master
EXEC sp_dboption 'DatabaseName', 'concat null yields null', 'FALSE'
but it doesn't change anything
Select NULL + 'TOTO'
----> NULL
(it should be 'TOTO')
View 2 Replies
View Related
Aug 3, 2005
hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks
View 5 Replies
View Related
May 13, 2015
It's giving me an error while I'm trying to change column value from not null to null..
'tblid' table
- Unable to modify table.
Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.
The statement has been terminated.
View 4 Replies
View Related
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
Oct 27, 2015
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case
when col>'20150901' then 'NULL'
else col
end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
View 10 Replies
View Related
Nov 27, 2000
I have two columns A (which allows nulls) and B( which does not allow nulls).
How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.
The result of A+B concatanation will be stored in a column, C.
Appreciate your help
Ziggy
View 10 Replies
View Related
Apr 25, 2007
Hi guys,
I've got a query on a particular table returning an odd result:
SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStation
This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.
SELECT DISTINCT AccountNumber
FROM Suppliers
WHERE AccountNumber Is Not Null
ORDER BY AccountNumber
I can't reproduce these results on another site on a table of the same structure, or on another table at this site.
Any suggestions as to what might be going on?
Pertinent info:
---
select @@Version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
---
dbcc checkdb
Abridged result:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'.
---
SELECT * INTO #Inv FROM Invoice
SELECT DISTINCT WorkStation
FROM #Inv
WHERE WorkStation Is Not Null
ORDER BY WorkStation
Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?
TIA,
Karl.
View 9 Replies
View Related
Aug 2, 2015
I have 595 default constraints in my database. I can return a list of them using the following:
select * from sys.default_constraints
Is there a way I can return a list of just the ones where NULL is still allowed? I want to update all of the columns with a default value to not allow NULLs.
View 2 Replies
View Related
