[Code]....
I want to keep the old password if the user leaves the password field blank, otherwise store the new password.
I have also tried "IS NOT NULL" instead of "= NULL", but without success. I've tried to send in an empty string and a DBNull value but it never keeps the old password.
[Code]....
pass values to sproc from sproc?
Anyone know how to do this? I was thinking about making a sproc with something like
select * from users where @group not in member_of
select * from users where @group in member_of
Since I'm dead tired, that's not supposed to be a real query...just an approximation ;)
I have been trying to get this sproc to return all users if null is passed to it from @UserName and a single users if a username is passed to @Username.Can anyone see where I'm going wrong?
[Code]....
In my project, I've been told to create a sproc to get some data. Fine. No problem. However, as we're moving away from a solution created by an external consultant I've had to spend a couple of days trying to figure out how the tables are linked. Sure... However, the big problem is that since this consultant is not supposed to know about this move just yet, I've been told to put my sproc in our production database (same server), and I've been unable to find any good info on this on google S
View 8 Repliesi want to get first picture of any album in photo table. i want get output table for show to the datalist control.i need to get albumid in output
so i create a sproc similar this:
create PROC USP_GETFirstPhoto
@ALBUMID INT OUTPUT,
@CATEGORY NVARCHAR(50) OUTPUT ,
@CAPTION NVARCHAR(50) OUTPUT
AS
SELECT @ALBUMID = A.AlbumID,@CATEGORY=A.Category,@CAPTION=TMP.Caption FROM Albums A
CROSS APPLY
( SELECT top 1 * FROM Photos
WHERE AlbumID=A.AlbumID
)TMP
order by A.AlbumID
DECLARE @x INT,@y NVARCHAR(50),@z NVARCHAR(50)
EXEC USP_GETFirstPhoto @x output,@y output,@z output
select @x,@y,@z
but when i exec this proc i have a recorde not a result set
with this query i have resultset
SELECT * FROM Photos
CROSS APPLY (
SELECT TOP 1 AlbumID FROM Photos
GROUP BY AlbumID)TMP
I have a table with about 300 OrgID's that I would like to pass into this sproc as the @Org_ID, so I don't have to do them one at a time. How would I do that?
insert into tblRelClients (client_ID, clientRel_ID)
select c1.client_ID, c2.client_ID from tblclients c1 inner join tblclients c2 on c2.OrgID = c1.OrgID
where c1.OrgID = @Org_ID
I'm trying to call a linked server, SS2000 that has NText field and instert that data into an nvarchar(120) field on SS2008.
If I execute the SPROC from SS2008 Management Studio, it works fine. If I try to call the SPROC from my ASP.NET 3.5 web application, I don't get any errors, but the data doesn't get updated.
I'm using the following SPROC:
CREATE PROCEDURE [dbo].[usp_UpdateMilestoneDescription]
@UID int
AS
BEGIN
[Code]....
i have a stored procedure with multiple parameters. and several statement which utilities a specific set of queries. therefore, every time its not necessary data will be passed through the parameters, so how can i make "providing data to parameter" optional
My stored procedure:
[Code]....
I am having trouble with this I have 3 Data tables i use over and over again which are cached I would like to write a LINQ statement which would do the following is this possible?
T-SQL VERSION:
SELECT P.[CID],P.[AID]
,B.[AID], B.[Data], B.[Status], B.[Language]
FROM MY_TABLE_1 P
JOIN
(
SELECT A.[AID], A.[Data], A.[Status], A.[Language] FROM MY_TABLE_2 A
UNION ALL
SELECT B.[AID], B.[Data], B.[Status], B.[Language] FROM MY_TABLE_3 B
) B on P.[AID] = B.[AID]
WHERE B.[Language] = 'EN' OR B.[Language] = 'ANY' AND B.STATUS = 1 AND B.[Language] = 'EN' OR B.[Language] = 'ANY' AND B.STATUS = 1
Then i would like it to create a result set of the following
Results:
|CID|AID|DATA|STATUS|LANGUAGE
If statement is not working
View 3 RepliesI want to return values where Worldwide = yes or where visible = yes and in State = Florida but this is not returning all the other values where the Worldwide is yes
select * from Table1
where (visible = 'yes' and State = 'Florida') or Worldwide= 'yes'
order by ID DESC
Edit: My BAD
Shit sorry guys/girls, this statement does work! I had Select TOP 8 * in my statement that is why it did not return all the records! When I took the TOP 8 out it worked!
have a very important issue,i have three Stored Procedures Sp1,Sp2 and Sp3 .the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:INSERT EXEC statement cannot be nested I tried to change the place of execute Sp2 and it display me another error:Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
View 4 RepliesMy switch is based on a string, the text value of an server control. code:
[Code]....
[Code]....
I am new to LINQ. I have a GridView which I am populating using LINQ. My LINQ statement is taking query string from previous page. The query string is in string format. Here is the code:
protected void Page_Load(object sender, EventArgs e)
{
string getEntity = Request.QueryString["EntityID"];
[code]...
I am trying to have the collection of order IDs be used in my where statement how come i can't get this to work?
List<int> orders = new List<int>(){1,2,3,4,5};
DataTable dtTable1 = getOrders();
DataTable dtTable2 = getOrderDetails();
var results = from a in dtTable1.AsEnumerable()
join b in dtTable2.AsEnumerable() on a.Field<int>("ID") equals b.Field<int>("ID")
where orders.Contains(b.Field<int>("OrderID"))
select a;
On Page1 of a project, I have a Gridview showing a table of customer names (showing CustID, CustName, CustJoinDate). When I click on a record, I have passed a querystring to Page2 showing an address table for that customer. This part works for me.
The SQL statement in simply: Select * from CustAddress where CustID = @cust
What I would like to do though, is capture that @cust to a session variable so that I can use it later on.
The following stored procedure updates the value in LeaveTransaction table.
[code].,...
My Question here:
I have written the above trigger,which would update the value for the corresponding records in another table ie) LeaveCumulative table. The trigger is not updating the value in the designated rows in the leavecumulative table. Whereas when I excute the same update command separately in the Query Editor window the trigger is working fine.
I've built a Formview, but need to implement some back end functionality - I have a dataset with stored procedures, and am using ObjectDataSources. I've put an asp:button on the page and called it btnDelete, but how do I call the stored procedure ?
Something along the lines of
[Code]....
I want to be able to click on the Delete button, and have it call the stored procedure to delete the record from the dataset...but I don't know C# well enough to be able to work out what the code should be..
Why am I getting two records inserted into the table, when I'm expecting and needing one? The VB code below is checking whether the HTTP is secure or not, and if it is then execute the SPROC. But for whatever reason, I'm getting two records inserted about 5 seconds apart. Does anyone know why and how to fix? I just need one record.
SPROC [Code]....
VB [Code]....
GLOBAL.ASAX [Code]....
I have a list of numbers I need to submit one at a time to a stored procedure to add to a table. I don't know any other way around this. The database for this is an AS400 so it's not like using mssql server. I have a connection string and use the SQLDatasource controls to select data because I can select a stored procedure with it to call. Now I need to do an update and if it was just one time of submitting information I would do it the same way. But I need to submit each number till I do them all. How can I do this? Do I still use a sqldatasource control and set the value to be pulled from like a session var and just loop through setting the session and then sqlds.databind over and over? I added a DBML but can't do that with the as400 stored procedures as far as adding them like I do mssql.
View 5 RepliesFirst I am using Entity Framework 3.5.
I have been listening volumes about this entity framework and though to have a dive in it. I got 3 tables and got the entities and model setup for my database, upto the point where I tried to execute sproc with the datacontext class. I added the sproc to Function Import but as Function Import does not support returning custom dataset that m sproc generates. I googled and found to create Complex Type, but here is the problem, "Get Column Information" in Add Function Import is disabled for me . why the heck its disabled, how to make this work and don't want to revert to older approach just that I could not execute sproc with EF.
At University it was suggested to us that the best way to learn about designing and implementing databases is to practice the art. So I chose the following statement to implement and refine my skills:
Harpers Hauliers deliver shipments on contract for customers. Each contract is identified by a unique contract number. The contract may require a single or many shipments. Each shipment must be allocated resources of a vehicle (regisatration number, type, volume, weight), which is suitable for the shipment and a driver (name, address, telephone number) who is licensed to drive that type of vehicle.
As soon as a customer (companyname, address, telephone number) makes an enquiry, a pending contract is set up. The contract is then negotiated on price between the customer and the haulier. Not all pending contracts are successfully negotiated. Either the customer or the haulier may back out at this stage. If the haulier has previously carried for this customer and financial settlements were not made to his satisfaction, he may reject the contract. He may also reject the contract on the basis that he thinks the workload being undertaken is too big a risk for his company with this customer that is the contract is worth more than he is willing to risk for this customer.
An agreed contract requires the haulier to deliver the listed shipments between a start and an end date, at an agreed price. The haulier always takes out an insurance policy on the contract, in case he cannot allocate enough resources to the contract to get it delivered on time.For each shipment in the delivery there is an origin and destination. The shipment has a description, a weight and a volume. The haulier has a fleet of vehicles of different types and a staff of drivers with different license types, to match the vehicle types. During the contract, the haulier tries to allocate vehicle, driver and time-slot resources to each ship-ment. The vehicle must have a suitable volume and weight capacity and the driver must have a suitable license to drive that vehicle.
The time must be when the driver and vehicle are free and before the contract end date. As each shipment is delivered, it is marked off as delivered by the driver, giving the date and time of delivery. If not all shipments are delivered by the contract end date, the con-tract becomes overdue, incurring a penalty cost on the haulier. When all shipments are delivered, the haulier invoices the customer for the required amount. Each invoice is has a date and is identified by a unique invoice number . Payment from the customer may be in full or may be partial. The contract is kept active until all payments have been made against it.
And here is the ERD that I have come up with. I have created the ERD in Visio 2010.
[IMG]http://i56.tinypic.com/6s43t4.jpg[/IMG]
Can someone who's an expert in Database design kindly check my ERD and see if it perfectly matches the Problem Statement.
[Code]....
View 2 Replies I have a requirement in my app to find out if a certain row exists in a table and then if it does get some fields from another row
I have two parameters an EventId and a UserId.
This is the normal statement I would use to get that row
[Code]....
Then I would check if the result > 0. If it is greater than 0 this is the statement I would use to get the other details
[Code]....
So how can I join this into one statement? This is what I have tried
[Code]....
