Updating Data With SqlDataSource Object
Mar 6, 2007
I am updating data with sqlDatasource but it is inserting NULL, tell what I am missing <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1"
DefaultMode="Insert" Width="100%">
<InsertItemTemplate>
<table width="100%">
<tr>
<td style="width: 100px">
Name</td>
<td style="width: 100px">
<asp:TextBox ID="txtCategory" runat="server" Text='<%# Eval("CategoryName") %>'></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
Parent</td>
<td style="width: 100px">
<asp:DropDownList ID="ddlParent" runat="server" DataSourceID="SqlDataSource1" DataTextField="CategoryName" DataValueField="CategoryID" Width="100%">
</asp:DropDownList></td>
</tr>
<tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
<asp:Button ID="btnAdd" runat="server" Text="Add" CommandName="Insert"/></td>
</tr>
</table>
</InsertItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>"
SelectCommand="SELECT [CategoryID], [CategoryName], [CategoryMID] FROM [CMRC_Categories]"
DeleteCommand="DELETE FROM [CMRC_Categories] WHERE [CategoryID] = @CategoryID"
InsertCommand="INSERT INTO [CMRC_Categories] ([CategoryName], [CategoryMID]) VALUES (@CategoryName, @CategoryMID)"
UpdateCommand="UPDATE [CMRC_Categories] SET [CategoryName] = @CategoryName, [CategoryMID] = @CategoryMID WHERE [CategoryID] = @CategoryID">
<DeleteParameters>
<asp:Parameter Name="CategoryID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CategoryName" Type="String" />
<asp:Parameter Name="CategoryID" Type="Int32" />
<asp:Parameter Name="CategoryMID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:FormParameter Name="CategoryName" Type="String" FormField="txtCategory" />
<asp:FormParameter Name="CategoryMID" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
Please suggest cheers
View 4 Replies
ADVERTISEMENT
May 21, 2007
I'm an "old" programmer but new to ASP.NET.
I want to get a value from the SQL Dataset.
What I would normally do in other environments is iterate through the dataset to get the value I would be intrested in, but I can't figure out how to do this without using a visual data display object like a Grid view.
Typically I want to get a value from the database that I then after manipulating it, like multply by 5, use to format something on the page.
thanks in advance,
Thommie
View 3 Replies
View Related
Apr 3, 2008
hello.i'm having the following problem.in one page i have a dataset created at runtime along side with sqldataadapter and an sqlconnection.i'm using a dataset here since i'm working with heirarchical tables.when i click on child table [in a spcific column] it opens up a new aspx page with an editing form [formview in edit mode]when
i press the update button it claims to update the data, but when i
close the form and reopen it from the same column it opens the form
again, but with the old data, and the new data doesn't get updated
until i close the openinig aspx page [the one with the table]
refreshing it doesn't work, nor does creating a postback.anyone has any ideas ?
View 2 Replies
View Related
Oct 19, 2006
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. 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.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
View 3 Replies
View Related
Dec 12, 2006
I am looking for a way to update a sqldatasource what I have is a ASP Wizard applicationstep oneis a dataview with the select ability it displays an ID and Namein step two what i want it to do is take the ID from step ones select and put that into the where clause so I have select * from table where id = step1selectedID
Code:
View 1 Replies
View Related
Apr 27, 2006
When I bind a GridView to a SQLDataSource where are the update parameter values set. What event will allow me to see the paramter values. I am trying to understand a little better how things work under the covers.
Jay
View 2 Replies
View Related
May 29, 2008
If a page contains a SQLDataSource object with a connection string that works on other pages, can that datasource be used in the codebehind to create a DataReader thereby avoiding the need to DIM and create a new connection string? My code fails but I can't figure out what to try next. Thanks.
Dim queryString As String = "SELECT * FROM users "
Using SqlDataSource1
Dim command As New Data.SqlClient.SqlCommand(queryString)
Dim oReader As Data.SqlClient.SqlDataReader = command.ExecuteReader()
' Call Read before accessing data.
While oReader.Read()
Console.WriteLine(String.Format("{0}, {1}", oReader(0), oReader(1)))
End While
' Call Close when done reading.
oReader.Close()
End Using
View 9 Replies
View Related
Apr 7, 2007
Hello, thanks for reading my post!
Ok, so I connected to my database. I can insert data and all that good stuff, but when it comes time to make the login page for a project I am working on, sadly I cant figure out how to read data from my datasource object O_O.
So I have an SqlDataSource object, its bound to my sql database ( successfully ), I can even set it to return the results I want, my question though is how do I access the results it returns so I can compair it with code?
Hope I'm making sense! Any help is *greatly* appreciated!
Thanks!,
-Bob
View 2 Replies
View Related
Nov 8, 2007
.. the Membership ProviderUserKey value as the parameter of a stored procedure ?It seems obvious to me that you would want to do this, but I can only see that it allows you to pass Profile parameters.
View 2 Replies
View Related
Oct 6, 2006
Hello, I just started working with ASP.NET.I'm trying to use the CheckBoxList Control. As I understand it, you can bind a SqlDataSource to this control and it loads the list for you. However tp precheck the items, you have to do this manually. This part works fine. Next part was to save whatever the user checks. I wrote stored Procedure and now just trying to pass 1 parameter to the stored procedure using a second SqlDataSource. I get the error: "A severe error occurred on the current command. The results, if any, should be discarded."Here is the second datasource I am using to try and save the data:<asp:SqlDataSource ID="sds_PersonRole" runat="server" ConnectionString="<%$ ConnectionStrings:Development %>"SelectCommand="usp_selectPersonRole"SelectCommandType="StoredProcedure"UpdateCommand="usp_updatePersonRole"UpdateCommandType="StoredProcedure"><SelectParameters> <asp:ControlParameter ControlID="gv_Person" Name="PERSON_ID" PropertyName="SelectedValue" Type="Int32" /></SelectParameters><UpdateParameters> <asp:Parameter Name="strXML" Size="8000" Type="String" /> <asp:Parameter Direction="InputOutput" Name="err_msg" Type="String" Size="150" DefaultValue="0" /></UpdateParameters></asp:SqlDataSource>I have a code behind file with the following modules: (btnEditPerson is clicked to start the process. the sds_PersonDetails is updated via form contolls and works fine.) Error occurs on the bolded line (Stored procedure is just accepting the string and saving to a field. It works fine, I tested it. Its just erroring out before it runs the stored procedure. Protected Sub btnEditPerson_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEditPerson.Click sds_PersonDetails.Update() gv_Person.DataBind() sds_PersonRole.Update()End Sub Protected Sub sds_PersonRole_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sds_PersonRole.Updating Dim command As Data.Common.DbCommand command = e.Command 'un-check all checkboxlist items (count - 1 to account for starting at 0) Dim listCount As Integer = cbl_Role.Items.Count() - 1 Dim strXML As String strXML = "<personRole>" For x As Integer = 0 To listCount If cbl_Role.Items(x).Selected() = False Then strXML = strXML & "<person id='" & gv_Person.SelectedValue & "' />" strXML = strXML & "<role id='" & cbl_Role.Items(x).Value & "' />" End If Next strXML = strXML & "</personRole>" command.Parameters("@strXML").Value = strXML lbl_ErrMsg.Text = command.Parameters("@err_msg").Value.ToString() End Sub
View 1 Replies
View Related
May 29, 2007
I have such a problem:i try to update a row in my table using: protected void selectButton_Click(object sender, EventArgs e) { String taskID = projectsGridView.SelectedRow.Cells[0].Text; usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@task where [UserID]=1"; usersSqlDataSource.UpdateParameters.Add("task", taskID); usersSqlDataSource.Update(); }And i receive error on usersSqlDataSource.Update():You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is emptyWhat have i done wrong? Parameter are not set?
View 3 Replies
View Related
Nov 19, 2007
I am experiencing some wacky errors here. While trying to update a field that does not allow nulls and the default value is set to '', I keep receiving an exception error that:
Cannot insert the value NULL into column 'image_name', table 'DB_123871.dbo.tWebBlogs'; column does not allow nulls. UPDATE fails. The statement has been terminated.
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlGetBlogs" DataKeyNames="article_id" AutoGenerateColumns="False" CssClass="GridView" CellPadding="4" HorizontalAlign="Center" Width="875px">
<Columns>
<asp:CommandField CancelImageUrl="~/images/Cancel.gif" EditImageUrl="~/images/Edit.gif"
UpdateImageUrl="~/images/Update.gif" ButtonType="Image" HeaderText="Edit" ShowEditButton="True">
</asp:CommandField>
<asp:BoundField DataField="article_id" HeaderText="ID" ReadOnly="True" />
<asp:TemplateField HeaderText="Artilce Header">
<EditItemTemplate>
<asp:TextBox ID="ArticleHeaderTxt" runat="server" Text='<%# Bind("article_header") %>' Width="250"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleHeaderTxtReq" runat="server" ControlToValidate="ArticleHeaderTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Header Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleHeaderLbl" runat="server" Text='<%# Eval("article_header") %>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Article Description">
<EditItemTemplate>
<asp:TextBox ID="ArticleDescriptionTxt" runat="server" Text='<%# Bind("article_description") %>' Width="325" Rows="8" TextMode="MultiLine"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleDescTxtReq" runat="server" ControlToValidate="ArticleDescriptionTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Description Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleDescriptionLbl" runat="server" Text='<%# Eval("article_description") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Link Short Text">
<EditItemTemplate>
<asp:TextBox ID="ArticleLinkTxt" runat="server" Text='<%# Bind("short_link_text") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleLinkTxtReq" runat="server" ControlToValidate="ArticleLinkTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Link Text Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleLinkLbl" runat="server" Text='<%# Eval("short_link_text") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Article Link">
<EditItemTemplate>
<asp:TextBox ID="ArticleLink" runat="server" Text='<%# Bind("article_link") %>' Width="250"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleLinkReq" runat="server" ControlToValidate="ArticleLink" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Link URL Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:HyperLink ID="ArticleLinkLnk" runat="server" CssClass="LinkNormal" NavigateUrl='<%# Eval("article_link") %>'
Target="_blank" Text="View Link"></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image Name">
<EditItemTemplate>
<asp:TextBox ID="Image1Txt" runat="server" Text='<%# Bind("image_name") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Image ID="Image1Img" runat="server" AlternateText='<%# Eval("image_name") %>' ImageUrl='<%# Eval("image_name", "~/Blogs/Images/Thumbs/{0}") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Entered">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("article_date_entered", "{0:d}") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("article_date_entered", "{0:d}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Active?">
<EditItemTemplate>
<asp:CheckBox ID="ActiveChk" runat="server" Checked='<%# Bind("active") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="ActiveChk" runat="server" Checked='<%# Eval("active") %>' Enabled="false" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete?">
<ItemTemplate>
<asp:ImageButton ID="DeleteBtn" runat="server" AlternateText="Delete Record" CommandName="Delete"
ImageUrl="~/images/Delete.gif" OnClientClick="return confirm('Are you sure you want to delete this blog?');" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle HorizontalAlign="Left"></RowStyle>
<EditRowStyle Font-Bold="False"></EditRowStyle>
<HeaderStyle CssClass="GridViewHeader" HorizontalAlign="Left"></HeaderStyle>
<AlternatingRowStyle CssClass="GridViewAltRow"></AlternatingRowStyle>
</asp:GridView>
</td>
</tr>
<tr>
<td>
<div style="text-align: center;"><asp:Label ID="recordset_lbl" runat="server" CssClass="HelpTextNormal"></asp:Label></div>
</td>
</tr>
</table>
</div>
<asp:SqlDataSource ID="SqlGetBlogs" runat="server" ConnectionString="<%$ connectionStrings:dbconn1 %>"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT article_id, article_header, article_description, image_name, short_link_text, article_link, article_date_entered, active FROM tWebBlogs ORDER BY article_date_entered DESC"
SelectCommandType="Text"
UpdateCommand="UPDATE tWebBlogs SET article_header=@article_header, article_description=@article_description, image_name=@image_name, short_link_text=@short_link_text, article_link=@article_link, active=@active WHERE article_id=@article_id"
UpdateCommandType="Text"
DeleteCommandType="Text"
DeleteCommand="DELETE tWebBlogs WHERE article_id=@article_id">
<DeleteParameters>
<asp:Parameter Name="article_id" />
</DeleteParameters>
</asp:SqlDataSource>
Please help! I am stumped!
View 1 Replies
View Related
Mar 4, 2007
Hi, I'm new in ASP 2.0. I need to incorporate edit and delete capability in GridView. Using the wizard, i've generated this code. When I delete a row, it gets deleted but update does not work. I've tried several ways. I got no error or exception. But row is not updated. I've checked database, and I think the update query is not executing at all. Please let me know, what I'm doing wrong? Here is the source code for reference. I'm using Visual Studio 2005 with SQL server 2005 Express Edition. Regards
==========================================================
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="QuoteItemID" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" CausesValidation="False" /> <asp:BoundField DataField="QuoteItemID" HeaderText="QuoteItemID" InsertVisible="False" ReadOnly="True" SortExpression="QuoteItemID" /> <asp:BoundField DataField="QuoteID" HeaderText="QuoteID" SortExpression="QuoteID" /> <asp:BoundField DataField="ChargeCodeID" HeaderText="ChargeCodeID" SortExpression="ChargeCodeID" /> <asp:BoundField DataField="RateItemAmount" HeaderText="RateItemAmount" SortExpression="RateItemAmount" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ratesdb %>" DeleteCommand="DELETE FROM [Quote_item] WHERE [QuoteItemID] = @QuoteItemID" InsertCommand="INSERT INTO [Quote_item] ([QuoteID], [ChargeCodeID], [RateItemAmount]) VALUES (@QuoteID, @ChargeCodeID, @RateItemAmount)" SelectCommand="SELECT * FROM [Quote_item]" UpdateCommand="UPDATE [Quote_item] SET [QuoteID] = @QuoteID, [ChargeCodeID] = @ChargeCodeID, [RateItemAmount] = @RateItemAmount WHERE [QuoteItemID] = @QuoteItemID" ConflictDetection="CompareAllValues"> <DeleteParameters> <asp:Parameter Name="QuoteItemID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="QuoteID" Type="Int32" /> <asp:Parameter Name="ChargeCodeID" Type="Int32" /> <asp:Parameter Name="RateItemAmount" Type="Decimal" /> <asp:Parameter Name="QuoteItemID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="QuoteID" Type="Int32" /> <asp:Parameter Name="ChargeCodeID" Type="Int32" /> <asp:Parameter Name="RateItemAmount" Type="Decimal" /> </InsertParameters> </asp:SqlDataSource>
View 6 Replies
View Related
Sep 13, 2006
I am trying to set the value of a SqlDataSource parameter named "InvestmentGroup" with the following code:ProjectData.SelectParameters["InvestmentGroup"] = Request.QueryString[0];ProjectData.DataBind();When I try this, I get an error saying that the right side is a string and the left side is not....but I cannot find a property of the SelectParameters["InvestmentGroup"] object like Value or something that would allow me to set the value of the parameter with a string. Please help!
View 4 Replies
View Related
Oct 27, 2006
Background - I have a page that uses a numeric value stored in a Session object variable as the parameter for three different SQLDataSource objects, which provide data to two asp:Repeaters and an asp:DataList. Also, in the Page_Load, I use this value to to seed a stored procedure and an SQLDataReader to populate several unbound Labels. This works fine. In addition, I have a collection of 6 TextBoxes, an unbound Listbox, and two Buttons to allow the user to do searching and selection of potential matches. This basically identifies a new numeric value that I store in the Session variable and PostBack the page (via one of the buttons). This also works fine.Problem - I have been tasked with taking a different page and adding six textboxes to collect the search values, but to post over to this page, populate the existing search-oriented TextBoxes, adn programmatically triggering the search. Furthermore, I have to detect the number of matching records and, if only 1, have the Repeaters and DataList display the results based on the newly selected record's key numeric value, as well as populating the unbound Labels. I have managed to get all of this accomplished except for programmatically triggering the Repeaters and DataList "refresh". These controls only populate as expected if a button is clicked a subsequent time, which makes sense, since that would trigger a PostBack and the Page_Load uses the new saved numeric key value from the Session.My history in app development is largely from Windows Forms development (VB6), this is my second foray into Web Form dev with ASP.NET 2.0. I am willing to acceptthat what I am trying to do does not fit into the ASP environment, but I have to think that this is something that has been done before, and (hopefully) there is a way to do what I need. Any ideas, oh great and wise Forum readers? *smile*
View 3 Replies
View Related
Dec 29, 2007
hi i want to select data based on a user id, which is stored in a custom object-> sessionhandler.user.id
how can i put that in the select parameter?
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:RentTodaySQL %>" DeleteCommand="DELETE FROM [Rentals] WHERE [RentalID] = @original_RentalID"
InsertCommand="INSERT INTO [Rentals] ([Headline], [Description], [MoveInSpecial], [MonthlyCost], [Deposit], [AvailableDate], [FeaturedListing], [UserID], [Address], [Address2], [City], [State], [Zip], [LeaseTermID], [LeaseDetails], [Section8], [Section8Details], [PetsAllowed], [PetsDetails], [PetDeposit], [ApplicationFee], [ApplicationDetails], [SmokingAllowed], [RentalCategoryID], [Bedrooms], [Bathrooms], [SqFootage], [LotSize], [YearBuilt], [DefaultImageID]) VALUES (@Headline, @Description, @MoveInSpecial, @MonthlyCost, @Deposit, @AvailableDate, @FeaturedListing, @UserID, @Address, @Address2, @City, @State, @Zip, @LeaseTermID, @LeaseDetails, @Section8, @Section8Details, @PetsAllowed, @PetsDetails, @PetDeposit, @ApplicationFee, @ApplicationDetails, @SmokingAllowed, @RentalCategoryID, @Bedrooms, @Bathrooms, @SqFootage, @LotSize, @YearBuilt, @DefaultImageID)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Rentals] WHERE ([UserID] = @UserID)"
UpdateCommand="UPDATE [Rentals] SET [Headline] = @Headline, [Description] = @Description, [MoveInSpecial] = @MoveInSpecial, [MonthlyCost] = @MonthlyCost, [Deposit] = @Deposit, [AvailableDate] = @AvailableDate, [FeaturedListing] = @FeaturedListing, [UserID] = @UserID, [Address] = @Address, [Address2] = @Address2, [City] = @City, [State] = @State, [Zip] = @Zip, [LeaseTermID] = @LeaseTermID, [LeaseDetails] = @LeaseDetails, [Section8] = @Section8, [Section8Details] = @Section8Details, [PetsAllowed] = @PetsAllowed, [PetsDetails] = @PetsDetails, [PetDeposit] = @PetDeposit, [ApplicationFee] = @ApplicationFee, [ApplicationDetails] = @ApplicationDetails, [SmokingAllowed] = @SmokingAllowed, [RentalCategoryID] = @RentalCategoryID, [Bedrooms] = @Bedrooms, [Bathrooms] = @Bathrooms, [SqFootage] = @SqFootage, [LotSize] = @LotSize, [YearBuilt] = @YearBuilt, [DefaultImageID] = @DefaultImageID WHERE [RentalID] = @original_RentalID">
<SelectParameters>
<asp:Parameter DefaultValue="<%= SessionHandler.User.ID %>" Name="UserID" Type="Int32" />
</SelectParameters>
View 1 Replies
View Related
Oct 7, 2005
In the master database for a SQL Server instance I noticed the dt... objects (procedures, etc.) in create date of today about 7 minutes before I looked in sysobjects. No changes have been made to that instance for months. Also in other cases I noticed the same thing just different dates for each instance. Any ideas on why the dates would change?
View 1 Replies
View Related
Jun 6, 2006
Hi all,
We have sql sever 2000 as our backend database system. on the front, we have vb medical application screen for users to do the data entry.
for 7th screen, it is assoicated with table Case_Custom. user enter data througth 7th screen to populate Case_Custom table.
There is one column called Case_Custom.CaseID ( a foreign key to to parent table Cases) was populated automatically instead of user input.
However, I could not find which stored procedure, views or trigger or application code to populate this field? I have queried INFORMATION_SCHEMA.ROUTINES, dbo.syscomments and did not find anything updating Case_Custom.CaseID.
I have contacted our app vendor and have got any answer from them yet.
Any ideas?
Thanks,
Jane
View 1 Replies
View Related
Feb 22, 2007
Hi,
I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following:
1. Pass an array into SqlDataSource Select OR 2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?3. Update a parameter programmatically
Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated.
Thanks in advance!!
DataTable dt = new DataTable(); DataTable dt2 = new DataTable(); DataView dv = new DataView();
foreach(ListItem liOptions in ListBox1.Items) { if(liOptions.Selected) { SqlDataSource1.SelectParameters.Add("Parameter1", liOptions); dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty); dt2 = dv.Table; dt.Merge(dt2); dt2.Dispose(); SqlDataSource1.SelectParameters.Clear(); } }
if (dt.Rows.Count > 0) { Graph(dt); //Pass original datatable (dt) to Graph(); dt.Columns.RemoveAt(2); //Reformat datatable (dt) and remove last column before binding to Gridview1
GridView1.DataSource = dt; GridView1.DataBind(); } else {
errorMessage.Text = "No data was returned!"; }
View 3 Replies
View Related
Oct 11, 2006
Hi There,This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.I have a project that I was working on with this ms access db and using sql controls, everything was working just finesince one day I started getting "Object reference not set to an instance of an object" messages when I try to designa query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lotof work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado componentsbut nothing seems to fix it, did this ever happen to any of you guys?any tip is really appreciated thanks a lot
View 2 Replies
View Related
Dec 28, 2004
Each time I press submit to insert data into the database I receive the following message. I use the same code on another page and it works fine. Here is the error:
Object reference not set to an instance of an object.
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.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 125: MyCommand.Parameters("@Balance").Value = txtBalance.Text
Line 126:
Line 127: MyCommand.Connection.Open()
Line 128:
Line 129: Try
Source File: c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb Line: 127
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
CreditRepair.CreditRepair.Vb.Creditor_Default.btnSaveAdd_Click(Object sender, EventArgs e) in c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb:127
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If (Page.IsValid) Then
Dim DS As DataSet
Dim MyCommand As SqlCommand
Dim AddAccount As String = "insert into AccountDetails (Account_ID, Report_ID, Balance) values (@Account_ID, @Report_ID, @Balance)"
MyCommand = New SqlCommand(AddAccount, MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@Account_ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Account_ID").Value = txtAccount_ID.Text
MyCommand.Parameters.Add(New SqlParameter("@Report_ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Report_ID").Value = txtReport_ID.Text
MyCommand.Parameters.Add(New SqlParameter("@Balance", SqlDbType.Char, 50))
MyCommand.Parameters("@Balance").Value = txtBalance.Text
MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()
Response.Redirect("Customer_Details.aspx?SS='CustHeadGrid.Columns[0].Item.lblSS.Text)")
MyCommand.Connection.Close()
End If
View 2 Replies
View Related
Dec 1, 2005
I've been playing around with the new data controls (DetailsView,FormView) and have been having problems when attempting to update a record that has a uniqueidentifier as its primary key.I get the error message:
Object must implement IConvertible. 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.InvalidCastException: Object must implement IConvertible.I gather this is because there is a bug that has propagated from the beta version.One suggested work around is from http://64.233.183.104/search?q=cache:GDjA62POtgcJ:scottonwriting.net/sowBlog/archive/11162005.aspx+Implicit+conversion+from+data+type+sql_variant+to+uniqueidentifier+is+not+allowed.+Use+the+CONVERT+function+to+run+this+query.&hl=en
The crux of the problem, it appears, is that the <asp:Parameter> value for the uniqueidentifier field is, by default, set to Type=�Object�. To fix this, simply remove the Type property altogether. That is, change the SqlDataSource parameter setting from something like:
<asp:SqlDataSource ...> <InsertParameters> <asp:Parameter Name=�UserId� Type=�Object� /> ... </InsertParameters></asp:SqlDataSource>
to:
<asp:SqlDataSource ...> <InsertParameters> <asp:Parameter Name=�UserId� /> ... </InsertParameters></asp:SqlDataSource>
This change worked for me; once the Type was removed the exception ceased and the updates/inserts worked as expected.Unfortunately this only partially worked for me as while it is fine for deletes it won't work for updates.If anyone can help shed any light on this I would greatly appreciate it.CheersMark
View 28 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Nov 27, 2007
hi i dont know how to do type casting.
this.Variables.ObjVariable this objVariables i have create in variable place below like this
Name:Variable datatype int32 values 0
Name: NumberofRowsdatatype int32 values 10000
Name: ObjVariable datatype Object
My code
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
System.Data.OleDb.OleDbDataAdapter oLead = new System.Data.OleDb.OleDbDataAdapter();
//System.Data.Odbc.OdbcDataAdapter oLead = new System.Data.Odbc.OdbcDataAdapter();
//SqlDataAdapter oLead = new SqlDataAdapter();
System.Data.DataTable dt = new System.Data.DataTable();
DataSet ds = (DataSet)this.Variables.ObjVariable; // here i am getting error
ds.Tables.Add(dt);
ADODB.Record rs = new ADODB.Record();
oLead.Fill(ds, rs, "Variables");
foreach (DataRow row in dt.Rows)
{
{
Output0Buffer.AddRow();
Output0Buffer.Column = (int)row["Column"];
Output0Buffer.Column1 = row["Column1"].ToString();
Output0Buffer.Column2 = row["Column2"].ToString();
}
}
}
}
This is the error
Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.
at ScriptMain.CreateNewOutputRows()
at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
thanks
kedaranth
View 5 Replies
View Related
Nov 17, 2006
Hi,
I am trying to setup Trans Replication with updating subscriber on sql2000. One column on few tables got data with single quote (').
How do I handle in this case? Did any one come across such case?
Can I Change default QUOTED IDENTIFIER from ' (single quote) to something else (@@@) on SQL2000?
If yes, how to do?
Thanks
mka
View 1 Replies
View Related
Jul 13, 2015
I'm using a legacy application built using VB5 and SQL Server 7. After recompiling it, and putting the database in SQL Server 2012. I want to access the current login user using the SQL function SUSER_SNAME().
This is the code.
Set rdoRes = goDatabase.Connection.OpenResultset("select suser_sname()")
But I'm unable to get the current user login in the application. If I write any other SQL statement instead of this, then it runs. But only this statement is not running. Is there any security reasons for this?
View 8 Replies
View Related
Apr 18, 2008
building some kind of ecommerce site.
I want to allow the user to modify the image that he has stored for a product.
The image is stored in a directory, in the table GAMME I only have the image name.
When the user selects a new Image, I first delete the old image in the directory, then save the new image, but what I can't do is to update the table with the new image name, when I write sqldatasource1.update() I have an error "the dictionnary passed with old values is empty"(translated from frengh).
the update statement in sqldatasource1 is: " UPDATE Gamme SET imagename = @imagename, imagesize = @imagesize WHERE (product_ID = @original_product_ID)"
on events sqldatasource1.updating I have written:
e.Command.Parameters("@imagename").Value = (FileUpload1.FileName).ToString()
e.Command.Parameters("@taillevignette").Value = FileUpload1.PostedFile.ContentLength
e.Command.Parameters("@original_product_ID").Value = Session("Product_ID").ToString
this is not written but the session("product_ID") is the good value
the problem must come from the last lign, but I don't Understand the problem and how to solve it.
View 1 Replies
View Related
Oct 2, 2000
Hi,
I would like to update about 4,000 records. I would only be updating one column for 4,000 rows in a table that has 50,000 rows. The update information is not the same for each row. I will be updating this from an excell file. Can somebody please tell me how to do this without messing the rest of the data in the table?
Thank you
JG
View 1 Replies
View Related
Apr 23, 1999
I rebuilt my databases using bcp,in order to change sort order and char set. That was OK,the data were re-inserted,but now i can only modify data by SQL Server Enterprise Manager.When a try to chage data by Visual Data Manager or VB application using ODBDC,it's accused that the database is not updatable,it's read only.But in the properties it's updatable.Anybody has a hint of what'sgoing on??
Thanks for yoour attention again,
Luciano
View 1 Replies
View Related
May 13, 2008
Hi,
What is the best to update line_no in following code.
As you can see 1 invoice can have multiple lines.
I would like to assign sequential number to all lines in invoice.
CREATE TABLE #xx (invoicenumber INT, line_no int )
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
SELECT * FROM #xx
result of select * from #xx should be:
10001
10002
10003
10004
20001
20002
20003
20004
Thanks
mk_garg
View 2 Replies
View Related
May 13, 2008
Hi,
What is the best to update line_no in following code.
As you can see 1 invoice can have multiple lines.
CREATE TABLE #xx (invoicenumber INT, line_no int )
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(1000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
INSERT INTO #xx VALUES(2000,0)
SELECT * FROM #xx
result of select * from #xx should be:
1000 1
1000 1
1000 1
1000 1
2000 2
2000 2
2000 2
2000 2
Thanks
mk_garg
View 3 Replies
View Related
Jun 24, 2008
I have posted on this problem before, and got some wonderful help, but the problems keep growing!
In my table, I have projects,activities, credit amount, debit amount, starting balance.
Each project can have several activities associated with it. Each project has a starting balance. Each activity posts an expense to the starting balance of the project.
If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". Then I want to re-adjust the balance and
check the second activity. If there is still enough money to handle the charge from the second activity, then the expenses can be posted and the balance
adjusted again. In this checking, I am creating a field called status, and flagging if the activity can clear or not, and a new balance field. The data I have can fall into
different examples, listed below.
First example:Only one activity for a project
project: 122400
activity: 0000
Cr_Amt: 2145.00
Dr_Amt: 0
Balance: 1190.00
In the above case, as the balance is less than the Cr_Amt, I want to set the Cr_Amt = Balance, and the new balance = 0 and have done that as follows:
Update r
set r.Post_Cr = r.Bal_2300,
r.new_Status = 'Can Clear',
r.new_Balance = 0
From Rev_Rec_Check r
WHERE r.project IN (Select project
from Rev_Rec_Check d
group by d.Project
having count(d.project) = 1)
That seems to work for me.
Second Example: More than one activity, with credits and debits
project: 145587
activity: 0000
Cr_Amt: 0
Dr_Amt: 2500
Balance: 1452
project: 145587
activity: 0110
Cr_Amt: 3953
Dr_Amt: 0
Balance: 1452
So, in this case, I need to add the Dr_Amt to the Balance and create a new balance (3952), and then check that the Balance can cover the Cr_Amt
UPDATE t
SET t.New_Balance=(t.Bal_2300 + b.postDrSum) - b.PostCRSum,
t.New_Status=(CASE WHEN (t.Bal_2300 -b.PostCRSum) >0 THEN 'Can Clear'
ELSE 'Still Check'
END)
FROM Rev_Rec_Check t CROSS APPLY
(SELECT SUM(post_Cr) as postCrSum, SUM(post_Dr) as postDrSum
FROM Rev_Rec_Check a
WHERE (a.project =t.project)
AND (a.activity<=t.activity)) b
This does seems to work, and gives me the correct status, and adjusts the new balance correctly, but I realize that doing that doesn't achieve what I want.
Ideally, in this example - where there is more than one project/activity, this is what I would like to see:
project: 145587
activity: 0000
Cr_Amt: 0
Dr_Amt: 2500
Balance: 1452
newBalance: 3952
project: 145587
activity: 0110
Cr_Amt: 3952
Dr_Amt: 0
Balance: 1452
newBalance: 0
I want the amount that gets posted (the Cr_Amt) to reflect what is left in the balance.
There could be cases where there are several activities for one project, so I want to be able to scroll through each activity and post what I can from what
they have left in their balance.
I don't know how to approach this. Am I making it more complicated than it actually is? Have I made any sense in trying to explain it?
Thanks in advance for any help!
View 6 Replies
View Related
Sep 12, 2006
skumar writes "Hi guys,
i'm a newbie in database and i need some ideas for the below mentioned
problem.
i'm creating sql table from a txt file using DTS package, now once the table
gets created, i need to multiple some revenue accounts with xyz number and
expense accounts with abc number.
i'm thinking more towards the line of writing a store procedure but don't
have any experience in it.
PLease guide me in the right direction, also if a good book could be refered
for future; which will help me query language and this kind of issues, that
will be GREAT!!
Thanx in advance"
View 2 Replies
View Related
