Delete Statement With Parameters Not Working
Jun 13, 2008
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc.
SqlCommand DeleteCommand = new SqlCommand();
DeleteCommand.Connection = DBConnectionClass.myConnection;
DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click
// in a parameter for DeleteCommand
SqlParameter DeletePrimaryKeyParam = new SqlParameter();
DeletePrimaryKeyParam.ParameterName = "@PhotoID";
DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object
DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection
DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)";
Response.Write(DeleteCommand.CommandText);
// DeleteCommand.Connection.Close();
DeleteCommand.Connection.Open();
DeleteCommand.ExecuteNonQuery();
DeleteCommand.Connection.Close();
View 9 Replies
ADVERTISEMENT
Nov 8, 2004
hi
win 2k and xp
excel 2k
sqlserver version 7
the code below execute but when i query the table, the data is still in there. can anyone help?
Sub UPDATED_DELETE()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
dim MyDate As Date
MyDate = Format(Date, "MM/DD/YYYY")
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
conn.ConnectionString = "ODBC=SQL Server;DSN=LOGCALL_TABLE;UID=richard;APP=Microsoft ® Query;WSID=RICHARD;Trusted_Connection=Yes"
conn.ConnectionTimeout = 30
conn.Open
Set cmd.ActiveConnection = conn
cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute
conn.Close
End Sub
View 4 Replies
View Related
Aug 12, 2007
I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?
View 5 Replies
View Related
Jan 5, 2007
Hi Everyone,
I am having a problem with the RS utility not recognizing my query parameters that I am passing to my timed subscription. I have set up my report with a "WorkOrder" parameter. I have created a trigger that causes my subscription to fire, and that trigger passes in the workorder number to the utility. The subscription fires successfully, but I am getting a blank report.
I set the parameter value to "can be blank" so that I could create my subscription w/o an error. I know that my subscription id is correct when I use the fire method, because I DO get the appropriate report.
I am also having the same problem when I actually log in to the report server and execute the utility from the command line. I believe my report is good. Once the report is generated, I am able to follow my URL, plug in the workorder id, and it works.
What would cause the variable not to be passed in/recognized? This is how I am calling it from the command line:
rs -i C:ReportingServicesasbuilts.rss -v WorkOrder="10610" -s http://(servername)/ReportServer
Thanks!
Wanda
View 14 Replies
View Related
Jun 13, 2008
The method I wrote to delete records is working if there's one record, but not for more than one. This method takes a string, and I've examined what is passed in and everything looks ok, but then no error occurs where there's more than one record, but no delete occurs either.
Here's what I see in my Trace.Warn statement: Delete From Photo_TBL where PhotoID IN ('223,224')
So the sql looks fine but I can't figure out why it's not working. Here's my method for deleting. Can you see what might be wrong? Thanks
public void PerformDeletion(string photoID)
{
//Response.Write(photoID);
// Set up SqlCommand, connection to db, sql statement, etc.
SqlCommand DeleteCommand = new SqlCommand();
DeleteCommand.Connection = DBConnectionClass.myConnection;
DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click
// in a parameter for DeleteCommand
SqlParameter DeletePrimaryKeyParam = new SqlParameter();
DeletePrimaryKeyParam.ParameterName = "@PhotoID";
DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object
DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection
DeleteCommand.CommandText = ("Delete From Photo_TBL where PhotoID IN ('" + photoID + "')");
Trace.Warn(DeleteCommand.CommandText);
// DeleteCommand.Connection.Close();
DeleteCommand.Connection.Open();
DeleteCommand.ExecuteNonQuery();
DeleteCommand.Connection.Close();
// Call BindData so GridView is binded and most recent changes appear
BindData();
}
View 1 Replies
View Related
Dec 21, 2007
Hello,
My delete record from database table below is not working, do anyone here know why delete is not working.
Code Snippet
Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click
Dim conn As New SqlClient.SqlConnection(My.Settings.HACS)
Dim strSQL As String
Dim cmd As New SqlCommand
strSQL = ("DELETE FROM CUSTOMER WHERE CUSTOMER_ID =" & Me.IDTextBox.Text)
If conn.State <> ConnectionState.Open Then conn.Open()
If MessageBox.Show("Are you sure you want to delete the current record?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question, _
MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.OK Then
cmd = New SqlCommand(strSQL, conn)
End If
conn.Close()
End Sub
Thanks.
View 4 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
Nov 30, 2005
i am inserting into a table using the sqlCommand parameters property.for example i do :sqlCmd.Parameters.Add("@date_birth_hebrew", Request("date_birth_hebrew"))the date_birth_hebrew is not a must and has allow null value in the db.when the user submits the form and leaves the textbox of date_birth_hebrewnot field out and i try to add this value to parameters as the above code i recive :Prepared statement '(@date1 datetime,@first_name nvarchar(4000),@last_na' expects parameter @date_birth_hebrew, which was not supplied. as i understand beacuse the Request("date_birth_hebrew") is empty the sqlCommand.Parameters acts as no value entered.i hae solved this problem by on each value checking thatif Parameters Request("date_birth_hebrew")="" thensqlCmd.Parameters.Add("@date_birth_hebrew", "")but isnt there any another way?thnaks in advancepeleg
View 1 Replies
View Related
May 24, 2007
I have 9 parameters which all have defaul values and 1 that does not, 4 of them show the correct default but 5 of them state <Select a Value>.
I have copied the report project to another pc and the same problem occurs.
I have tried deleting 'reports' and 'reportserver' from iis and recreating them, as well as deleting the actual report from http://localhost/Reports/Pages/Folder.aspx [then selecting 'show details' and deleting and redeploying the report].
I have even tried toggling the default values on/off. I am at a loss, I can only think that the report project file(s) need to be manually amended in some way. There is nothing significantly different between the parameters to justify them not defaulting
View 1 Replies
View Related
Jun 4, 2006
Hey gang I am trying to use the delete feature of a sqldatasource and having issues with the delete feature. My code is below the issue is if I just call the delete and don't have a paramter in my delete statement then it deletes the records in my table. However when I add a parameter to my delete query (@maID) and try to set it then it doesn't delete the record. What am I doing wrong? The e.CommandArgument is passing the correct record ID (which is an Integer).....can someone please help?
PS I also tried without the @ symbol before my parameter and no luck either.
DELETE FROM tempMbrAccounts WHERE (maID = @maID) is my delete query Public Sub OnDeleteButtonClick(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) Handles gvEnterAcct.RowCommand
Dim RecID As Integer
If e.CommandName = "Delete" Then
RecID = e.CommandArgument
dsInsert.DeleteParameters.Add("@maID", RecID)
dsInsert.Delete()
tempAcctTable()
End If
End Sub
View 3 Replies
View Related
Jul 10, 2006
I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast. And then firing off the update command that contains that parameter from a button. But it's not changing my data on my server when I do so.
I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes. All this works fine, just not sp that should update my data when I hit the submit button.
It's supposed to update one of my tables to whatever the selected value is from my drop down list. But it doesn't even change anything. It just refreshes the page and goes back to the original value for my drop down list.
Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc. It just won't change the data from the page when I try to.
This is what the stored procedure looks like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS
BEGIN
UPDATE [Current_tbl]
SET ID = @SelectedID
WHERE PrimID = '1'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my aspx page:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"
UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;">
<tr>
<td colspan="2" style="font-family:Tahoma; font-size:10pt;">
Please select one of the following:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%;">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%;">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label>
</td>
</tr>
</table>
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my code behind:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Imports System.Data
Imports System.Data.SqlClient
Partial Class Editor
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Saved_lbl.Text = ""
Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;")
Dim View1 As New DataView
Dim args As New DataSourceSelectArguments
View1 = SQLDS_Fill.Select(args)
Dim Row As DataRow
For Each Row In View1.Table.Rows
Ver_ddl.SelectedValue = Row("ID")
Next Row
SQLDS_Fill.Dispose()
End Sub
Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click
SQLDS_Update.Update()
Saved_lbl.Text = "Thank you. Your changes have been saved."
SQLDS_Update.Dispose()
End Sub
End Class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is much appreciated.
View 4 Replies
View Related
Jan 12, 2008
Hi,
I've tried using the same method mentioned in the dozens of posts dedicated to this topic but it's not working.
My SQL statement is:
SELECT [University Origins].Origin, COUNT([University Origins].Origin) AS TotalCount, Hospitals.Name
FROM Posts INNER JOIN
Hospitals ON Posts.HospitalID = Hospitals.[Hospital ID] CROSS JOIN
[University Origins] INNER JOIN
Incumbents ON [University Origins].[Uni Origin ID] = Incumbents.[University Origin]
WHERE (NOT ([University Origins].Origin = 'No answer')) AND (Hospitals.Name IN (@HospitalFilter))
GROUP BY [University Origins].Origin, Hospitals.Name
The HospitalFilter parameter is set to Multivalue and it's populated with it's own SQL statement giving the Distinct Hospitals.
I have setup a filter on my table with the following expressions:
Expression: =Trim(Fields!Name.Value)
Operator: = IN
Value: =Trim(Parameters!HospitalFilter.Value.ToString )
I have used Trim to remove leading and trailing spaces.
Running the report always returns zero results and I'm puzzled by this. Would appreciate any advice and suggestions
Thanks,
John
View 8 Replies
View Related
Jun 2, 2007
Hi all,
I'm looking to return certain rows from our db into an SSRS based on a user selected date range using the parameters calendar.
My query/analyzer returns all required fields/rows, but how do I
pull the specific rows, (that are based on a date range that the user enters),into the report? I've tried expressions, and vb functions to no avail. The users will be using the calendar parameter to select date ranges .So far the reports pull in all rows from my query.
Thanks,
Scott
View 1 Replies
View Related
Feb 24, 2006
I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not.
CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO
<asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button>
Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdUpdateCartItem As SqlCommand Dim UpdateCartItem Dim strCartItemID As String Dim txtQuantity As TextBox strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) txtQuantity = e.Item.FindControl("txtQuantity") connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr) cmdUpdateCartItem.CommandType = CommandType.StoredProcedure cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID) cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text) connStr.Open() cmdUpdateCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
____________________________________________________________
CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO
<asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button>
Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdDeleteCartItem As SqlCommand Dim DeleteCartItem Dim strCartItemID strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr) cmdDeleteCartItem.CommandType = CommandType.StoredProcedure cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID) connStr.Open() cmdDeleteCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
View 2 Replies
View Related
Mar 13, 2006
I have a dataview control with the delete method pointing to a logical delete stored procedure in SQL SERVER Express. I am getting an error message saying too many parameters provided. I've check and there is one parameter expected and one passed in. This is my SP, the html, and the debug infor I'm looking at. Any ideas?
PROCEDURE dbo.usp_Drivers_Delete
@mintDriver_ID int
AS
UPDATE tblDrivers
SET Active= 0
WHERE Driver_ID=@mintDriver_ID
html:
<DeleteParameters>
<asp:ControlParameter ControlID="GridView1" Name="mintDriver_ID" PropertyName="SelectedValue"
Type="Int32" />
</DeleteParameters>
Debug:
?SqlDataSourceDrivers.DeleteParameters(0)
{System.Web.UI.WebControls.ControlParameter}
System.Web.UI.WebControls.ControlParameter: {System.Web.UI.WebControls.ControlParameter}
ConvertEmptyStringToNull: True
DefaultValue: Nothing
Direction: Input {1}
Name: "mintDriver_ID"
Size: 0
Type: Int32 {9}
?SqlDataSourceDrivers.DeleteParameters.Count
1
Error:
Exception Details: System.Data.SqlClient.SqlException: Procedure or function usp_Drivers_Delete has too many arguments specified.
View 1 Replies
View Related
Sep 27, 2006
DELETE FROM StoresViewToTable2
WHERE (AllZips = store1.AllZips) AND (StoreNo = store1.StoreNo)
I want to delete from StoresViewToTable2 where the fields match in another table (Store1). How do you get the where parameters to be from another table? is this possible? or do i just need to do an Outer Join. Im trying to avoid this becuase the StoresViewToTable2 tbl is very large.
StoresViewToTable2 (tbl)
AllZips | StoreNo
Store1 (tbl)
AllZips | StoreNo
View 1 Replies
View Related
Jul 21, 2004
Hi
if i try to delete data via MFC from Microsofts SQL Server 2000
i get following message: Data can be read only.. Weird i can write and read already. do i have to setup anything in Micrsofts SQL Server 2000 ??
please help its urgent.
Greets
Indian
View 1 Replies
View Related
Dec 27, 2006
I am currently working with 3 multi-valued parameters whose data sources are queries. The first 2 are required to have entries, 100% of the time, but the third one may or may not require selecting a value. Parm3's data source is filtered by the selections of Parm1 & Parm2. The data source for my report references Parm3 in a derived table that is then LEFT OUTER JOINed.
In the cases where the report does not require any selection from Parm3 I am still required to pick at least 1 entry. Can anyone shed some light on this, or provide a solution so I am not forced to pick any if I don't want?
Thanks in advance,
Michael
View 6 Replies
View Related
Feb 18, 2006
The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?
System::Boolean rs::sp(System::String ^ ssp){
System::String ^ k0s0; bool bOK;
System::Data::SqlClient::SqlParameter ^ parami0;
System::Data::SqlClient::SqlParameter ^ parami1;
System::Data::SqlClient::SqlParameter ^ parami2;
System::Data::SqlClient::SqlParameter ^ paramz0;
System::Data::SqlClient::SqlParameter ^ paramz1;
System::Int32 pz0=0;System::Int32 pz1=0;
k0s = ssp;
k0->CommandType=System::Data::CommandType::StoredProcedure;
k0->CommandText=k0s;
paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
paramz0->Direction=System::Data::ParameterDirection::ReturnValue;
paramz0->DbType=System::Data::DbType::Int32;
parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");
parami0->Direction=System::Data::ParameterDirection::Input;
parami1=k0->Parameters->AddWithValue("@SRCXV","UU");
parami1->Direction=System::Data::ParameterDirection::Input;
//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);
paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);
paramz1->Direction=System::Data::ParameterDirection::InputOutput;
paramz0->DbType=System::Data::DbType::Int32;
//k0->Parameters->GetParameter("@RCOUNT");
r0=k0->ExecuteReader();
//pz0=System::Convert::ToInt32(paramz0->SqlValue);
bOK=k0->Parameters->Contains("@RCOUNT");
//k0->Parameters->GetParameter("@RCOUNT");
pz0=System::Convert::ToInt32(paramz0->Value);
pz1=System::Convert::ToInt32(paramz1->Value);
ndx = -1;
while(r0->Read()){
if (ndx == -1){
ndx=0;
pai0ndx=0;
pad0ndx=0;
r0nf=r0->FieldCount::get();
for (iG1_20=0;iG1_20<r0nf;iG1_20++){
this->psf0[iG1_20]=this->r0->GetName(iG1_20);
this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);
this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
else {
pai0ndx=0;
pad0ndx=0;
for (iG1_20=0;iG1_20<r0nf;iG1_20++)
{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
ndx++;
}
r0nr=ndx;
r0->Close();
k0->Parameters->Remove(paramz0);
k0->Parameters->Remove(parami1);
k0->Parameters->Remove(parami0);
k0->Parameters->Remove(paramz1);
return true;
}
View 5 Replies
View Related
Apr 23, 2015
I have created two report parameters and want them as Cascading. District Parameter depends on Region Parameter which should allow Multi selection. When I select single value in Region it works perfectly. But when I choose multiple values, District turns out to be a blank text box. I have used the In clause in my code :
SELECT
DISTINCT
SalesDistrict
FROMDistrict(NOLOCK)
WHERESalesRegion
IN(@SalesRegion)
View 2 Replies
View Related
Dec 11, 2007
I'm a SQL newbie, and I'm trying to write a report that returns records based on a beginning and end date that the user supplies. But when I run the query and supply the dates (begin 11/29/2007 / end 11/30/2007, for example), I'm only getting back one record, when there should be at least 3. It appears that my query is ignoring the =, and only returning those records that have a date > or <, not <= or >=.
I was told that it's possible that the time needs to be converted to UTC as well, but I'm not sure how to do that ( I tried, but I really don't know what I'm doing so I commented it out)... Can someone offer some guidance on how to get all the records to show up? I've pasted my query below, thanks in advance!
SELECT FilteredContact.fullname, FilteredContact.new_memberleadsourcename, FilteredContact.new_prospectforclubname, FilteredContact.owneridname,
FilteredContact.new_referred_by, convert(varchar(10),FilteredContact.createdon,101) as Date--, SELECT GETDATE(FilteredContact.createdon) AS CurrentTime, GETUTCDATE(FilteredContact.createdon) AS UTCTime
FROM FilteredContact JOIN filteredOpportunity on
FilteredOpportunity.contactid = Filteredcontact.contactid
where filteredopportunity.new_opportunitytype = 1 and (FilteredContact.createdon >= '11/29/2007') and (FilteredContact.createdon <= '11/30/2007')
View 3 Replies
View Related
May 1, 2008
Hi all,
i m using VS 2005 and I have to display records with feature of INSERT / DELETE ITEMS But when i connect to Sql Server Database and select * from columns but here when clicking the "Advance" button , i do not get "Advance Sql generation Option " highlighted. Instead , it is turned off. i.e
The Following options are not highlighting
------ Generate Insert, Update, Delete statements
------ use optimistic concurrency
Plz guide me anyone..... is anything wrong with our VS 2005 software installed?
Bilal
View 4 Replies
View Related
Jan 28, 2004
Any ideas why this line is bringing up just titles rather than titles and slogan:
dbsreq = "SELECT data_contents, data_style FROM content WHERE data_language = '" & siaith & "' AND data_type = 'title' AND 'slogan';"
(obviously it's all on one line in my code)
I don't understand why it brings up just 'title', not 'title' and 'slogan'?
Any ideas/fizes/suggestion would be greatfully appreciated.
Thanks,
Deian
View 7 Replies
View Related
May 8, 2008
I have a stored procedure that has an @Style parameter passed into it.
And down at the very bottom of the procedure it has this line:
And Style Like @Style
Now I realize that is not really valid, and I changed it to Style=@Style. But I'd like to understand WHY that isn't working as it was previously.
On our development box (single core X64) it works fine but on our multiple core X64 production box, this doesn't work.
Any ideas?
This is a MS SQL 2005 server we are talking to.
View 15 Replies
View Related
Mar 21, 2000
Hi,
I am trying to write an update query to update rows in one table.
Structure of Table1:
SocialSecurityNumber Varchar(9) -- Primary Key
Name Varchar(30)
Structure of Table2 :
SocialSecurityNumber Varchar(9)
Name Varchar(30)
Table 1 contains:
Row1: "123456789" Sally
Row2: "999999999" Bill
Row3: "333333333" Alex
Table 2 contains:
Row1: "123456789" <NULL>
Row2: "123456789" <NULL>
Basically I want to update the name column in Table 2 (based on the SocialSecurityNumber column) so that after
the update Table 2 will contain:
Row1: "123456789" Sally
Row2: "123456789" Sally
------------------------------------------------------------
First I tried:
UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
This works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Then I tried:
UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name
Again this works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Finally I tried:
UPDATE Table2, Table1
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
This also did not work.
------------------------------------------------------------
Is there any way to write this update statement without cursors?
Thanks.
View 1 Replies
View Related
Jun 16, 2006
I have this statement buried in a sproc:
INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID)
SELECT PLAN_SHIP.[YEAR], PLAN_SHIP.BOD_INDEX, 1
FROM PLAN_SHIP LEFT JOIN PLAN_DEMAND ON
PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR]
AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX
WHERE PLAN_DEMAND.BOD_INDEX IS NULL
When I run the sproc in QA, the statements returns records to the grid, but does not insert them into the table. If I just copy the statement into QA and execute it, it works fine.
I'm sure it's something obvious (but not to me).
Any help would be much appreciated.
View 7 Replies
View Related
Jun 2, 2008
Hi,
Mine Below Query is not Printing Anything though there is a matching record in table "gpcb_proj_paras"
Can any one help me??
declare @catg as char(1)
set @catg = '''A'''
declare @qry as nvarchar(3000)
declare @paras as varchar(200)
Set @paras = (Select convert(nvarchar(200), app_paras) from gpcb_proj_paras t
where t.act = @catg)
print @paras
-- Regards
Prashant Hirani
View 2 Replies
View Related
Oct 2, 2007
I have the following update statement, which when executed, updates zero rows. However, if I replace the first two lines with a SELECT * , I will get records. Can somebody tell me why?
UPDATE [DW_DatamartDB]. [dbo].[FactLaborDollars]
SET [LaborBudget_USD] = Week1
FROM [DW_StagingDB].[ETL].[Transform_FactLaborBudget] BUDGET JOIN
[DW_StagingDB].[ETL].[Transform_FactLaborDollars] LABOR ON
Labor.Location_Code = Budget.Location_Code
JOIN [DW_DatamartDB]. [dbo].[DimDate] DATE ON
Date.FiscalWeekOfPeriod = Labor.FiscalWeekOfPeriod AND
Date.FiscalPeriodOfYear = Labor.FiscalPeriodOfYear
WHERE Labor.FiscalYear = CAST(SUBSTRING(DATE.Date_Code,1,4) AS NVARCHAR(50)) AND
Budget.FiscalYear = CAST(SUBSTRING(DATE.Date_Code,4,1) AS NVARCHAR(50)) AND
Date.FiscalWeekofYear = 1
View 6 Replies
View Related
Nov 19, 2007
I followed an example using the AdventureWorks database to set up a simple messaging test on one database:
Code Block
-- We will use adventure works as the sample database
USE AdventureWorks
GO
-- First, we need to create a message type. Note that our message type is
-- very simple and allowed any type of content
CREATE MESSAGE TYPE JobRequest
VALIDATION = NONE
GO
-- Once the message type has been created, we need to create a contract
-- that specifies who can send what types of messages
CREATE CONTRACT JobRequestor
(JobRequest SENT BY INITIATOR)
GO
-- The communication is between two endpoints. Thus, we need two queues to
-- hold messages
CREATE QUEUE RequestorQueue
CREATE QUEUE ReceiverQueue
GO
-- Create the required services and bind them to be above created queues
CREATE SERVICE Requestor
ON QUEUE RequestorQueue
CREATE SERVICE Receiver
ON QUEUE ReceiverQueue (JobRequestor)
GO
-- At this point, we can begin the conversation between the two services by
-- sending messages
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @conversationHandle
FROM SERVICE Requestor
TO SERVICE 'Receiver'
ON CONTRACT JobRequestor
WITH ENCRYPTION=OFF, LIFETIME= 600;
-- Send a message on the conversation
SET @message = N'Hello, World';
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE JobRequest (@message)
COMMIT TRANSACTION
END
GO
-- Receive a message from the queue
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue
-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO
This all works fine but if I run the section that creates the message and then copy the RECEIVE section to a new query window and execute it, nothing is returned. If I run the RECEIVE section within the same query window it returns the 'Hello World' message as expected. I am new to Service Broker and so am assuming that I am missing something obvious!!
View 5 Replies
View Related
Apr 11, 2006
I've got the following update statement:
UPDATE ISSUE_ACTIONS
SET BAE_FLAG = 2
WHERE IA_ISSUE_NO = 399
AND IA_SEQUENCE = 20
The fields BAE_FLAG, IA_ISSUE_NO, and IA_SEQUENCE are all of the type int.
When I run this code inside of my windows app (C#),
cmd3.CommandText = "UPDATE ISSUE_ACTIONS " +
"SET BAE_FLAG = 2 " +
"WHERE IA_ISSUE_NO = 437 " +
"AND IA_SEQUENCE = 13";
try
{
cmd3.ExecuteNonQuery();
}
catch (Exception e)
{
throw (e);
}
I get a timeout error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
But when I run the SAME statement from Query Analyzer, it executes without a problem.
Has anyone run into this issue before? How do I get around this?
The CommandTimeout property of cmd3 is set to the default because this is not a complex query and should not take more than .5 seconds to execute.
View 5 Replies
View Related
Dec 17, 2007
Hi..
I have inserted couple of data in a particular table which is as follows..
Portfolio Table
PortId PlanId PortfolioName PorfolioDescription ClientPortolioId
771
17838
BALPORT
NULL
NULL
772
17838
HIGHGROW
NULL
NULL
773
17838
MODGROW
NULL
NULLMy FundDBF is as follows
RowNumber FUND_ID f.ASSETDESC Import
20
BALPORT
Balanced
True
21
MODGROW
Moderate Growth
True
22
HIGHGROW
High Growth
True
and this is my Update statement UPDATE
Statements..PlanPortfolio
SET
PlanId = pm.PlanId,
PortfolioName = pm.FUND_ID,
PortfolioDescription = pm.ASSETDESC
FROM
Statements..PlanPortfolio p
Join (
SELECT DISTINCT
p.PlanId,
pd.FUND_ID,
f.ASSETDESC ---pd.FUND_ID
FROM
PartDBF pd
INNER JOIN Statements..ClientPlan p
on pd.PLAN_NUM = p.ClientPlanId
INNER JOIN FundDBF f
on pd.FUND_ID = f.FUND_ID
WHERE
pd.Import = 1
AND NOT (
pd.FUND_ID IS NULL
OR
Len(pd.FUND_ID) = 0
OR
pd.FUND_ID NOT IN (
SELECT
PortfolioName
FROM
Statements..PlanPortfolio pp
Where
pp.PlanId = p.PlanId
)
)
) pm
on p.PlanId = pm.PlanId
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field..
Any help will be appreciated..
Regards
Karen
View 17 Replies
View Related
May 21, 2008
myCommand2.CommandText = "INSERT INTO testimonials(name,email,testimonial,approved,time) VALUES ('" + exp.escString(txtName.Text) + "','" + exp.escString(txtEmail.Text) + "','" + txtTestimonial.Text + "','" + false + "','" + DateTime.Now + "')"; ok the form has a field txtTestimonial.Text these strings work when i submit them thoughthats all folksthat"s (double quotes) that\"sthat\"s these failthat's that's that\'s that\'s tried up to 7 just to make sure Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 's'.Unclosed quotation mark after the character string ')'. I figure the " works because it ends up being ' " ' in the sql statement and it doesnt mind thatI tried researching this. I could not find any info stating that perhaps sql escapes things differently. I was on the assumption that in the sql itself the database would reconise the before the '.I read i can use html decode but it seems that then i will have to undecode everytime I read from the database and it could be a major pain if I am using a datagrid or something and a big mess. Any help would be greatly appreciated. Jim
View 5 Replies
View Related
Nov 6, 2006
hi.
can somebody explain to me why the below
update fails to update one row and updates
the entire table?
Code:
UPDATE addlist
SET add_s = 1
WHERE EXISTS
(SELECT a.add_s, a.email_address, e.public_name
FROM add a, edit e
WHERE a.email_address = e.email_address
and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');
and, what is the solution? thank you.
View 8 Replies
View Related