Delet Function Not Working On Gridview

Mar 25, 2007

I 'm having trouble with the delete function on Gridview - the update works great but I keep getting the error below when I try to delete
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.

I using the products table in Northwind to learn this stuff.

 

The code that I used is below:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Gigs.aspx.cs" Inherits="Gigs" %>

<!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>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID" InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)"

SelectCommand="SELECT * FROM [Products]" UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, [Discontinued] = @Discontinued WHERE [ProductID] = @ProductID">

<DeleteParameters>

<asp:Parameter Name="ProductID" Type="Int32" />

</DeleteParameters>

<UpdateParameters>

<asp:Parameter Name="ProductName" Type="String" />

<asp:Parameter Name="SupplierID" Type="Int32" />

<asp:Parameter Name="CategoryID" Type="Int32" />

<asp:Parameter Name="QuantityPerUnit" Type="String" />

<asp:Parameter Name="UnitPrice" Type="Decimal" />

<asp:Parameter Name="UnitsInStock" Type="Int16" />

<asp:Parameter Name="UnitsOnOrder" Type="Int16" />

<asp:Parameter Name="ReorderLevel" Type="Int16" />

<asp:Parameter Name="Discontinued" Type="Boolean" />

<asp:Parameter Name="ProductID" Type="Int32" />

</UpdateParameters>

<InsertParameters>

<asp:Parameter Name="ProductName" Type="String" />

<asp:Parameter Name="SupplierID" Type="Int32" />

<asp:Parameter Name="CategoryID" Type="Int32" />

<asp:Parameter Name="QuantityPerUnit" Type="String" />

<asp:Parameter Name="UnitPrice" Type="Decimal" />

<asp:Parameter Name="UnitsInStock" Type="Int16" />

<asp:Parameter Name="UnitsOnOrder" Type="Int16" />

<asp:Parameter Name="ReorderLevel" Type="Int16" />

<asp:Parameter Name="Discontinued" Type="Boolean" />

</InsertParameters>

</asp:SqlDataSource>

 

</div>

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"

CellPadding="4" DataKeyNames="ProductID" DataSourceID="SqlDataSource1" ForeColor="#333333"

GridLines="None">

<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

<Columns>

<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

<asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False"

ReadOnly="True" SortExpression="ProductID" />

<asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />

<asp:BoundField DataField="SupplierID" HeaderText="SupplierID" SortExpression="SupplierID" />

<asp:BoundField DataField="CategoryID" HeaderText="CategoryID" SortExpression="CategoryID" />

<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" SortExpression="QuantityPerUnit" />

<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />

<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" SortExpression="UnitsInStock" />

<asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" SortExpression="UnitsOnOrder" />

<asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" SortExpression="ReorderLevel" />

<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" SortExpression="Discontinued" />

</Columns>

<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />

<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />

<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="White" />

</asp:GridView>

</form>

</body>

</html>

 Any help would be appreciated - thanx

View 12 Replies


ADVERTISEMENT

UPDATE Not Working In Gridview

Sep 26, 2006

I am trying to update a field in gridview. My update is not working, sort of. The original value is being updated with a NULL value when I click on the update button.here is my code <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" AutoGenerateEditButton="True" DataKeyNames="rqt_id">UpdateCommand="UPDATE [rqt_data] SET [rqt_title]=@rqt_title WHERE [rqt_id] = @rqt_id" >      <asp:Parameter Name="rqt_title" Type="String" /> the field rqt_title is set up as a NVarChar(25) in the db. I can't specify that for the Type property. Could that be the issue?thx

View 4 Replies View Related

Update In Gridview Not Working

Jan 5, 2006

I may have posted this in the wrong forum before, but i am trying to update a table in SQL2000 from asp.net2.0 gridview. I follow all the recommended ways and it doesnt update the row. Please help
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="ID" CellPadding="4" ForeColor="#333333" GridLines="None">
 
.......misc code.........
UpdateCommand="UPDATE vacationrequest SET hrapprvl = @hrapprvl, notes = @notes WHERE ([ID] = @original_ID)">
<SelectParameters>
<asp:ControlParameter ControlID="HiddenField1" Name="StartDate" PropertyName="Value"
Type="DateTime" />
<asp:Parameter DefaultValue="false" Name="hrapprvl" Type="Boolean" />
</SelectParameters>

<UpdateParameters>
<asp:Parameter Type=Boolean Name="hrapprvl" />
<asp:Parameter type=String Name="notes" />
<asp:Parameter Name="original_ID"/>

View 1 Replies View Related

GridView Delete Function

Jun 28, 2007

This is killing me. I've searched the forums for hours and can't find the answer. My SQLDataSource is working fine except when I want to delete. I've allowed the delete function to be shown on the gridview. This is my SQLDataSource: <asp:SqlDataSource ID="IndexDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IndexConnectionString %>"
SelectCommand="SELECT *
FROM [Index]
WHERE (Type LIKE '%' + @SearchText2 + '%')
OR (Product LIKE '%' + @SearchText2 + '%')
OR (Version LIKE '%' + @SearchText2 + '%')
OR (Binder LIKE '%' + @SearchText2 + '%')
OR (Language LIKE '%' + @SearchText2 + '%')
OR (CDName LIKE '%' + @SearchText2 + '%')
OR (Details LIKE '%' + @SearchText2 + '%')
OR (ISOLink LIKE '%' + @SearchText2 + '%')"
DeleteCommand="DELETE FROM [Index] WHERE [ID] = @original_ID"
UpdateCommand="UPDATE [Index] SET Type = @Type, Product = @Product , Version = @Version, Binder = @Binder, Language = @Language, CDName = @CDName, Details = @Details, ISOLink = @ISOLink WHERE ID = @ID">
<SelectParameters>
<asp:ControlParameter Name="SearchText2" Type="String" ControlID="SearchText2" PropertyName="Text" ConvertEmptyStringToNull="False" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="original_ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Type" />
<asp:Parameter Name="Product" />
<asp:Parameter Name="Version" />
<asp:Parameter Name="Binder" />
<asp:Parameter Name="Language" />
<asp:Parameter Name="CDName" />
<asp:Parameter Name="Details" />
<asp:Parameter Name="ISOLink" />
</UpdateParameters>

</asp:SqlDataSource>
  It doesn't give me an error if I click delete but it doesn't delete the record. I've tried changing the DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> but it gives me the error "Must declare the scaler variable of '@ID'"... I saw in this post http://forums.asp.net/p/1077738/1587043.aspx#1587043 that the answer was that "The variable you have declared in the definition of the proc is
different from the variable you are using in the WHERE clause." when they are both the same. Thanks for any help.-Brandan   

View 4 Replies View Related

Problem With Delete Function Within A Gridview

Mar 1, 2007

I've got an issue that when I update a record in the gridview it works fine.  When I click the delete link to remove the record from the database, I get the following error, "System.FormatException: Input string was not in a correct format".  Part of the Stack Trace refers to "String oldValuesParameterFormatString".  This parameter is in my SqlDataSource.  It was dynamically created when I originally created the SqlDataSource with VWD 2005 Express Edition.  The delete function will work if I remove "OldValuesParameterFormatString="original_{0}" ProviderName="System.Data.SqlClient", and any reference to "original_" in the DeleteCommand the SqlDataSource.  But if I do, the update function doesn't work.  Anyway, here's the SqlDataSource:  Any help would be greatly appreciated!!!!!
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"
DeleteCommand="DELETE FROM [houses] WHERE [intID] = @original_intID AND [street] = @original_street AND [city] = @original_city AND [state] = @original_state AND [zip] = @original_zip AND [status] = @original_status AND [pDate] = @original_pDate AND [sPrice] = @original_sPrice AND [asPrice] = @original_asPrice AND [actSalePrice] = @original_actSalePrice AND [cToDate] = @original_cToDate AND [rehabBudget] = @original_rehabBudget AND [tDay] = @original_tDay AND [eDate] = @original_eDate AND [loDate] = @original_loDate AND [rsDate] = @original_rsDate AND [flooringDate] = @original_flooringDate AND [estCompDate] = @original_estCompDate AND [actCompDate] = @original_actCompDate AND [coe] = @original_coe AND [lDate] = @original_lDate AND [credits] = @original_credits AND [agent] = @original_agent AND [insComplete] = @original_insComplete AND [cEscrowDate] = @original_cEscrowDate AND [bidValue] = @original_bidValue AND [thomGuideNumber] = @original_thomGuideNumber AND [locksmith] = @original_locksmith AND [notes] = @original_notes AND [hoa] = @original_hoa"
 
InsertCommand="INSERT INTO [houses] ([street], [city], [state], [zip], [status], [pDate], [sPrice], [asPrice], [actSalePrice], [cToDate], [rehabBudget], [tDay], [eDate], [loDate], [rsDate], [flooringDate], [estCompDate], [actCompDate], [coe], [lDate], [credits], [agent], [insComplete], [cEscrowDate], [bidValue], [thomGuideNumber], [locksmith], [notes], [hoa]) VALUES (@street, @city, @state, @zip, @status, @pDate, @sPrice, @asPrice, @actSalePrice, @cToDate, @rehabBudget, @tDay, @eDate, @loDate, @rsDate, @flooringDate, @estCompDate, @actCompDate, @coe, @lDate, @credits, @agent, @insComplete, @cEscrowDate, @bidValue, @thomGuideNumber, @locksmith, @notes, @hoa)"
OldValuesParameterFormatString="original_{0}" ProviderName="System.Data.SqlClient"
SelectCommand="SELECT * FROM [houses] WHERE ([intID] = @intID)"
UpdateCommand="UPDATE [houses] SET [street] = @street, [city] = @city, [state] = @state, [zip] = @zip, [status] = @status, [pDate] = @pDate, [sPrice] = @sPrice, [asPrice] = @asPrice, [actSalePrice] = @actSalePrice, [cToDate] = @cToDate, [rehabBudget] = @rehabBudget, [tDay] = @tDay, [eDate] = @eDate, [loDate] = @loDate, [rsDate] = @rsDate, [flooringDate] = @flooringDate, [estCompDate] = @estCompDate, [actCompDate] = @actCompDate, [coe] = @coe, [lDate] = @lDate, [credits] = @credits, [agent] = @agent, [insComplete] = @insComplete, [cEscrowDate] = @cEscrowDate, [bidValue] = @bidValue, [thomGuideNumber] = @thomGuideNumber, [locksmith] = @locksmith, [notes] = @notes, [hoa] = @hoa WHERE [intID] = @original_intID">
<DeleteParameters><asp:Parameter Name="original_intID" Type="Int32" /><asp:Parameter Name="original_street" Type="String" /><asp:Parameter Name="original_city" Type="String" /><asp:Parameter Name="original_state" Type="String" /><asp:Parameter Name="original_zip" Type="String" /><asp:Parameter Name="original_status" Type="String" /><asp:Parameter Name="original_pDate" Type="DateTime" /><asp:Parameter Name="original_sPrice" Type="Decimal" /><asp:Parameter Name="original_asPrice" Type="Decimal" /><asp:Parameter Name="original_actSalePrice" Type="Decimal" /><asp:Parameter Name="original_cToDate" Type="Decimal" /><asp:Parameter Name="original_rehabBudget" Type="Decimal" /><asp:Parameter Name="original_tDay" Type="DateTime" /><asp:Parameter Name="original_eDate" Type="DateTime" /><asp:Parameter Name="original_loDate" Type="DateTime" /><asp:Parameter Name="original_rsDate" Type="DateTime" /><asp:Parameter Name="original_flooringDate" Type="DateTime" /><asp:Parameter Name="original_estCompDate" Type="DateTime" /><asp:Parameter Name="original_actCompDate" Type="DateTime" /><asp:Parameter Name="original_coe" Type="DateTime" /><asp:Parameter Name="original_lDate" Type="DateTime" /><asp:Parameter Name="original_credits" Type="String" /><asp:Parameter Name="original_agent" Type="String" /><asp:Parameter Name="original_insComplete" Type="String" /><asp:Parameter Name="original_cEscrowDate" Type="DateTime" /><asp:Parameter Name="original_bidValue" Type="Decimal" /><asp:Parameter Name="original_thomGuideNumber" Type="String" /><asp:Parameter Name="original_locksmith" Type="String" /><asp:Parameter Name="original_notes" Type="String" /><asp:Parameter Name="original_hoa" Type="String" /></DeleteParameters>
<UpdateParameters><asp:Parameter Name="street" Type="String" /><asp:Parameter Name="city" Type="String" /><asp:Parameter Name="state" Type="String" /><asp:Parameter Name="zip" Type="String" /><asp:Parameter Name="status" Type="String" /><asp:Parameter Name="pDate" Type="DateTime" /><asp:Parameter Name="sPrice" Type="Decimal" /><asp:Parameter Name="asPrice" Type="Decimal" /><asp:Parameter Name="actSalePrice" Type="Decimal" /><asp:Parameter Name="cToDate" Type="Decimal" /><asp:Parameter Name="rehabBudget" Type="Decimal" /><asp:Parameter Name="tDay" Type="DateTime" /><asp:Parameter Name="eDate" Type="DateTime" /><asp:Parameter Name="loDate" Type="DateTime" /><asp:Parameter Name="rsDate" Type="DateTime" /><asp:Parameter Name="flooringDate" Type="DateTime" /><asp:Parameter Name="estCompDate" Type="DateTime" /><asp:Parameter Name="actCompDate" Type="DateTime" /><asp:Parameter Name="coe" Type="DateTime" /><asp:Parameter Name="lDate" Type="DateTime" /><asp:Parameter Name="credits" Type="String" /><asp:Parameter Name="agent" Type="String" /><asp:Parameter Name="insComplete" Type="String" /><asp:Parameter Name="cEscrowDate" Type="DateTime" /><asp:Parameter Name="bidValue" Type="Decimal" /><asp:Parameter Name="thomGuideNumber" Type="String" /><asp:Parameter Name="locksmith" Type="String" /><asp:Parameter Name="notes" Type="String" /><asp:Parameter Name="hoa" Type="String" /><asp:Parameter Name="original_intID" Type="Int32" /><asp:Parameter Name="original_street" Type="String" /><asp:Parameter Name="original_city" Type="String" /><asp:Parameter Name="original_state" Type="String" /><asp:Parameter Name="original_zip" Type="String" /><asp:Parameter Name="original_status" Type="String" /><asp:Parameter Name="original_pDate" Type="DateTime" /><asp:Parameter Name="original_sPrice" Type="Decimal" /><asp:Parameter Name="original_asPrice" Type="Decimal" /><asp:Parameter Name="original_actSalePrice" Type="Decimal" /><asp:Parameter Name="original_cToDate" Type="Decimal" /><asp:Parameter Name="original_rehabBudget" Type="Decimal" /><asp:Parameter Name="original_tDay" Type="DateTime" /><asp:Parameter Name="original_eDate" Type="DateTime" /><asp:Parameter Name="original_loDate" Type="DateTime" /><asp:Parameter Name="original_rsDate" Type="DateTime" /><asp:Parameter Name="original_flooringDate" Type="DateTime" /><asp:Parameter Name="original_estCompDate" Type="DateTime" /><asp:Parameter Name="original_actCompDate" Type="DateTime" /><asp:Parameter Name="original_coe" Type="DateTime" /><asp:Parameter Name="original_lDate" Type="DateTime" /><asp:Parameter Name="original_credits" Type="String" /><asp:Parameter Name="original_agent" Type="String" /><asp:Parameter Name="original_insComplete" Type="String" /><asp:Parameter Name="original_cEscrowDate" Type="DateTime" /><asp:Parameter Name="original_bidValue" Type="Decimal" /><asp:Parameter Name="original_thomGuideNumber" Type="String" /><asp:Parameter Name="original_locksmith" Type="String" /><asp:Parameter Name="original_notes" Type="String" /><asp:Parameter Name="original_hoa" Type="String" /></UpdateParameters>
<SelectParameters><asp:QueryStringParameter Name="intID" QueryStringField="intID" Type="Int32" /></SelectParameters>
<InsertParameters><!-- removed to save space --></InsertParameters>
</asp:SqlDataSource>

View 4 Replies View Related

Error Editing Data Records In Gridview When Using Convert Function.

Jan 24, 2008

Hello, i currently have a gridview that is populated with data from a SQLServer datasource. I have put an output mask in the select statement, so the date and time attributes are displayed in the format i prefer them to be in. SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment];
 However when i click the 'edit' link for a record in the gridview, i am unable to edit the date/time attributes and when i click update to confirm any changes to the other attributes, the values in the date/time attributes are emptied. How can i solve this update problem. I'm guessing i need to configure my SQL UPDATE statement, but bit stuck how i do this. Please help!
Thanks,
James

View 9 Replies View Related

Complex Delet Query .. Help Me!

Feb 25, 2004

I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
----------- ----------- ----------
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.

View 14 Replies View Related

Want Error Message To Appear When No Database Results Were Returned In GridView, Also Other GridView Issues.

Jun 12, 2008

Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having.   Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class  I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks! 

View 2 Replies View Related

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.

Jan 19, 2007

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure  in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View 9 Replies View Related

How Can I Get This Function Be Working?

Jun 2, 2006

How can I get this function be working?
 
CREATE FUNCTION MyFunc
(
   @MyDate as datetime,
   @MyTableName varchar(50),
)
RETURNS TABLE
AS
RETURN 
  SELECT * FROM @MyTableName Where myDate=@MyDate
 

View 2 Replies View Related

How Can I Get This Function Be Working?

Jun 2, 2006

How can I get this function be working?

CREATE FUNCTION MyFunc
(
@MyDate as datetime,
@MyTableName varchar(50),
)
RETURNS TABLE
AS
RETURN
SELECT * FROM @MyTableName Where myDate=@MyDate

View 4 Replies View Related

C# Update Function. Where AND Where Not Working.

Jan 29, 2008

Can someone please tell me why in the bloody hell this isnt working? It ignores the WHERE VENDORID match portion and marks all instances of USERID match to TRUE. I've been banging my head for an hour... have I really forgotten basic sql???!!!!public static void UpdateVendor(VendorEvaluationEntity VEE)
{int vendorid = Convert.ToInt32(VEE.VendorevalVendor);
int userid = Convert.ToInt32(VEE.VendorevalUser);SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("Update tblVendorUser set vendoruser_vendor_evaluated = 'true' where (vendoruser_vendor_id = @vendorid) and (vendoruser_user_id=@userid)", conn);SqlParameter pmvendorid = new SqlParameter();
SqlParameter pmuserid = new SqlParameter();pmvendorid.ParameterName = "@vendorid";pmvendorid.SqlDbType = SqlDbType.Int;
pmvendorid.Value = vendorid;
pmuserid.ParameterName = "@userid";pmuserid.SqlDbType = SqlDbType.Int;
pmuserid.Value = userid;
cmd.Parameters.Add(pmvendorid);
cmd.Parameters.Add(pmuserid);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
 
}

View 3 Replies View Related

Replace Function Not Working

Oct 19, 2005

Hi,
I posted a request here and am still working on it when I landed on this bug.


select top 10 replace(comma_separated_string,',','giveaverylongp assagehere') from table



The function works fine if the comma separated string is small or if the passage is small. It fails for long passages..

Is this a mssql bug?

View 1 Replies View Related

Function Using Comparing Dates Not Working Right

Sep 12, 2006

Hi,

I'm trying to write a function to return all notes with date. Sample data for 1 record=187189 as follows:
iincidentid,iWorkNoteId,iSeqnum, dtEntryDate, workNoteAll
1871893440 1 2006-04-24 note1
1871893545 1 2006-06-22 note2
1871893547 1 2006-06-22 note3
1871893653 1 2006-08-10 note4
1871893653 2 2006-08-10 note5

funtion will return = 2006_08-10 note4 note5 for iincidentid=187189
-----------------------------------------------------
CREATE FUNCTION dbo.getIncidentNotesRev(@iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
declare @incidentId int
declare @worknoteid int
declare @worknotesaveid int
declare @seqnum int
declare @dtEntryDate smalldatetime
declare @worknoteall varchar(8000)
declare@allnotes varchar(8000)
declare @currentWEDate smalldatetime
declare @beginWEDate smalldatetime

select @allnotes=''
select @currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date
select @beginWEDate = DATEADD(d, - 28, @currentWEDate)--get the last 4 weeks

declare CursorIncident CURSOR
LOCAL FOR SELECT iIncidentId, iWorkNoteID, iSeqNum, dtEntryDate,worknoteall FROM dbo.rpt_weekly_prospect_status_vw
where iIncidentId=@iIncidentID order by iWorkNoteId

OPEN CursorIncident
FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall

--store 1st record of cursor
select @worknotesaveid =@worknoteid
WHILE (@@FETCH_STATUS=0)
BEGIN
if @dtEntryDate >=@beginWEDate AND @dtEntryDate <= @currentWEDate
Begin
if @worknotesaveid <> @worknoteid
Begin
Select @allnotes = @allnotes + @dtEntryDate + @worknoteall
End
else
BEgin
select @allnotes = @allnotes + @worknoteall
End

select @worknotesaveid = @worknoteid --save next worknoteId
End
else
Begin
select @allnotes=''
End
FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall
END --WHILE (@@FETCH_STATUS=0)

CLOSE CursorIncident
DEALLOCATE CursorIncident

return @allnotes
END

----------
Function not working right. I appreciate any help.
Thanks in advance.

View 3 Replies View Related

Sql Function Not Working For TEXT Size More Than 8000

Feb 24, 2005

Hi,

I wrote this sql function which takes a comma seperated string of numbers, splits the numbers seperately and stores it in a table. I have specified the input parameter type as text instead of varchar, the size of the string can get more than 8000.

But the function is not working properly if the input size is more than 8000. For example if the input string is of length 8005 and this is the input string from 7995 to 8005 - '123,124,125'. It works fine till 123 and after that it throws an error, Syntax error converting the varchar value '124,125' to a column of data type int. Can anyone tell me what is wrong with this. I am using string functions like charindex, substring. I can post the full function if you want.

Thanks.

View 2 Replies View Related

Stuff Function Not Working With Left Or Right Join

Jan 1, 2015

I found something very strange...stufff function working with self join but not working with left or right join,. I have a table

**Id name**

1 samar

1 Harry

2 jack

I want the output as

**Id name**

1 samar Harry

2 jack

The below query works fine with self join

Select b.id, stuff ((select ` ` + a.name from #test a where a.id = b.id
for xml path (``)),1,1,``)

From #test b
Group by id

But when i do right join i get error _ invalid object name `b`. ....

Select b.id, stuff ((select ` ` + a.name from #test a
right join b on b.id = a.id for xml path (``),1,1,``)

From #test b
Group by id

View 4 Replies View Related

Retrieve Data For Working Days Using Date Function

Aug 22, 2007

Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?

Thanks...

View 2 Replies View Related

Transact SQL :: Select Statement Not Working In User Defined Function

Nov 3, 2015

The select statement:

SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
       CAST(Date AS DATETIME) + CAST(Time AS DATETIME))
   FROM [DataGapTest]

Gives the right output:

NULL
1
1
3548
0

However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).

CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time)
RETURNS INT
AS
  BEGIN
 DECLARE @timeInterval INT
   SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
       CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME))
   FROM dbo.[DataGapTest]
  RETURN @timeInterval
  END

View 5 Replies View Related

Transact SQL :: Creating Comma Separated Value Logic Not Working Inside Function?

Jul 16, 2015

I am need to create comma separated list in sql and I am using below query.

declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
select @ConcatenatedString

When I use the above code inside a function, i am not getting desired output.

create function GetConcatenatedValue
AS
(
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
return @ConcatenatedString
)

View 3 Replies View Related

Can Not Delet A Publication In SQL Server 2005: The Publication Does Not Exist [SQL Server Error: 20026]

Sep 21, 2007

Hi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.

Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.

View 6 Replies View Related

SQL 2012 :: Function With 2nd Part Working On Results 1st Part

Jan 28, 2015

I have made the following Scalar-valued function:

CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)

[Code] ....

What it does is convert numbers to times

E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)

So far so good (function works perfectly)

My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.

This means that, e.g. with a time like 3.23.40 the last zero must be deleted.

My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,

is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))

There are 5 codes: 0 1 2 3 and 4

View 9 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

Gridview In Asp.net

Nov 29, 2006

Can I directly Save data to sqlserver 2005 using gridview in frontend?
 
How?
 
 

View 2 Replies View Related

GridView Help

Feb 4, 2007

Hi,
I use WVD and SQL Express 2005.
I have a table “SignIn� that one of fields inserted automatically by getdate()
And I have GridView that I use to display this table because I would like take advantage of GridView sorting and paging methods that are embedded in.
Currently I display all records at once.
 
My problem is how to make the GridView show today’s records only.
I tried this code below, but I get only this message “There are no data records to display.�
 
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:RC1 %>"
ProviderName="<%$ ConnectionStrings:RC1.ProviderName %>"
SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @SignIn)">
     <SelectParameters>
       <asp:QueryStringParameter Name="SignIn" QueryStringField="Format(Now, &quot;M/dd/yyyy&quot;)" Type="DateTime" />
     </SelectParameters>
</asp:SqlDataSource>
 
Help Please!
 

View 6 Replies View Related

Gridview

Apr 17, 2008

Hi
I have a business search box and gridview pair. When the user enters a business name, the search results are displayed. I also generate a "more information" link which takes the user to a new page, passing the business name ("memberId")to this page (see the template field below).
The problem I have is if the name contains a QUOTE (') or other special characters. The "memberId" is chopped off at the quote (e.g. "Harry's Store" is passed as "Harry").
Can anyone tell me a way around this please? Is there anything I can do with the Eval method?
Kind regards,
Garrett
 
<asp:TemplateField HeaderText="More Info">
<ItemTemplate>
<a href='member_page.aspx?memberId=<%# Eval("co_name") %>'>more</a>
</ItemTemplate>
<ItemStyle Font-Bold="False" />
</asp:TemplateField>

View 2 Replies View Related

???GridView

Mar 29, 2006

HiI need to add in gridview control  asp code "delete from t1 where id=@id1"how to declare @id1 because the server give me mistake down is the code of asp i use GridView how i can link @id with field there id???Thank u and have a nice daybest regardsthe code if u need it i use c#
<ASP:SQLDATASOURCE id=SqlDataSource1 <br ConnectionString="<%$ ConnectionStrings:libraryConnectionString %>" runat="server"><BR></ASP:SQLDATASOURCE></ASP:BOUNDFIELD>

View 1 Replies View Related

Datatable From Gridview

Aug 2, 2006

hi all
 
the usual way to bid a gridview is to data soursce
is there a way to do  the folowing , creat a data table from the gridview shown valus " currunt page "
 
thanks
 

View 1 Replies View Related

Gridview Sorting

Aug 17, 2006

I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc.  So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this.  For only ASC sorting, I've got
ORDER BY  CASE WHEN @sortBy='' THEN DateCreated END,  CASE WHEN @sortBy='DateCreated' THEN DateCreated END
and so on.  However, columns can also be sorted with DESC.  I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC.  How can I do this?

View 2 Replies View Related

Gridview And DropDownList

Oct 18, 2006

Hello:I have add a DropDownList to my GridView and binded the dropdownlist to a field from a select statement in the SQLDataSource. I have EnabledEditing for my GridView. The GridView is populated with information from the select statement. Some of the information returned from the select statement is null. The field where the dropdownlist is binded it is null in some cases and does not have a value that is in the dropdownlist so I get and error when I attempt to do an update. 'DropDownList1' has a SelectedValue which is invalid because it does not exist in the list of items.Parameter name: value Is there a way to get around this besides initializing all the columns in the table that are going to be binded to a dropdownlist to a value in the dropdownlist?

View 1 Replies View Related

Gridview Search

Jan 15, 2007

I tried doing a text box search within Gridview. My code are as follows. However, when I clicked on the search button, nothing shown.
Any help would be appreciated. I'm using an ODBC connection to MySql database. Could it be due to the parameters not accepted in MySql?
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
 
           SqlDataSource1.SelectCommand = "SELECT * FROM carrier_list WHERE carrierName LIKE '%' + @carrierName + '%'"
 
End Sub
 
Sub doSearch(ByVal Source As Object, ByVal E As EventArgs)
GridViewCarrierList.DataSourceID = "SqlDataSource1"
GridViewCarrierList.DataBind()
 
End Sub
 
HTML CODES (Snippet)
<asp:Button ID="btnSearchCarrier" runat="server" onclick="doSearch" Text="Search" />
' Gridview<asp:GridView ID="GridViewCarrierList" runat="server" DataSourceID="SqlDataSource1" >
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT * FROM carrier_list">
</asp:SqlDataSource>
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<SelectParameters>
<asp:ControlParameter ControlID="txtSearchCarrier" Name="carrierName" PropertyName="Text" Type="String"></asp:ControlParameter>
</SelectParameters>
 
</asp:SqlDataSource>

View 8 Replies View Related

GridView...Problem

Mar 29, 2007

I m creating the project in asp.net using c# and vb languages in 2005.I have used the asp standard controls(with table<td><tr>) and gridview to design the form.I m using sqldatasource to insert and  update data from sql server 2005.I have written the following code  <script runat="server">     Private  Sub Page_Load()        If Not Request.Form("SUBMIT") Is Nothing Then            srccompany.Insert()        End If    End Sub</script>   <asp:SqlDataSource        id="srccompany"        SelectCommand="SELECT * FROM companymaster"        InsertCommand="INSERT companymaster(companyname)           VALUES (@companyname)"      UpdateCommand="UPDATE companymaster SET companyname=@companyname WHERE companyid=1"       DeleteCommand="DELETE companymaster WHERE companyname=@companyname"        ConnectionString="<%$ ConnectionStrings:companymaster %>"       Runat="server"></asp:SqlDataSource>   <asp:GridView        id="GridCompanyMaster"        DataSourceID="srccompany"        Runat="server" />Please help me to insert the data in sql server.i m not been able to insert the data is there any problem in coding..Also i m not been able to edit the data and store back to sql server.Only i can do is i can view the contents in gridview Please give me some tips  

View 1 Replies View Related

SQL OutputCache GridView

Oct 24, 2007

I'm trying to cache the contents of a gridivew unless another page, or sorting method are being called.  I tried to use the VaryByParam method, but I'm not having any luck, I keep getting the same page sent back to me. Here's what my code looks like.
<%@ OutputCache Duration="180" VaryByParam="Page$, Sort$" %>
 Any help would be appreciated.
Stephen

View 2 Replies View Related







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