Can't create table


Why can't I create that table

CREATE TABLE students (
PRIMARY KEY (id),
name varchar(255) NOT NULL,
email varchar(255) NOT NULL
);

every time I try to create that table the output shows: Error Code: 1072. Key column 'students_id' doesn't exist in table

I'm using MySQL Workbench.
Posted On: Sunday 30th of December 2012 10:19:35 PM Total Views:  304
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




How do I create formatted lines of text?

I am new to MS SQL. Am used to MySQL. How do I format the output records of an SQL query into formatted lines of text Select firstname, lastname, accesscard from table1 where accesscard > 10000 Results Joe, Bloggs, 31234 Jane, Bloggs, 76543 Format I would want has access card E.g. Joe Bloggs has access card 31234 Jane Bloggs has access card 76543 Any suggestions that would lead me in the right direction
VIEWS ON THIS POST

63

Posted on:

Thursday 25th October 2012
View Replies!

How to create a counter?

, I have a data set that looks like this: Group Value A 33 A 43 A 2 B 3 B 5 B 3 B 43 C 54 C 34 I want to add a counter to each group, such that i can see which "A" entry I am at ... The table should look like this Group Value Counter A 33% 1 A 43% 2 A 2% 3 B 3% 1 B 5% 2 B 3% 3 B 43% 4 C 54% 1 C 34% 2 Can anyone help me out
VIEWS ON THIS POST

131

Posted on:

Thursday 25th October 2012
View Replies!

How to create horizontal table from vertical table

Hi I have a vertical table i would like to modify it as horizontal SOURCE_COLUMN TARGET_COLUMN TARGET_TABLE ------------------------------------------------------------- ADDRESS_LINE1_TXT ADDRESS_LINE1 ADDRESS ADDRESS_LINE2_TXT ADDRESS_LINE2 ADDRESS ADDRESS_LINE3_TXT ADDRESS_LINE3 ADDRESS TO BE SC 1 SC2 SC3 TC1 TC2 TC3 TT --------------------------------------- ADD1 ADD2 ADD3 1ADD 2ADD 3ADD ADDRESS and if it is possible also an additional information how i could make generation of the column dynamically according to the number of row it return (Option to create a better dynamic SQL)
VIEWS ON THIS POST

117

Posted on:

Thursday 25th October 2012
View Replies!

Which is faster? create table, or maintain list?

, . A site I'm working on needs to grab products from a database, but exclude certain ones based on which page the user is viewing. Is it faster/better to do something like this: Select * from Blah WHERE sku NOT IN (SELECT sku FROM excludeTable) or should it be more like Select * from Blah WHERE sku NOT IN (123,234,345,456) Over time, the list could potentially get pretty lengthy
VIEWS ON THIS POST

86

Posted on:

Thursday 25th October 2012
View Replies!

Viewing created procedures and functions

I'am using SqlServer 2005.. I'am able to view created tables via: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES; How do I do the same with created procedures and functions i.e how do I view all procedures and functions using SELECT query
VIEWS ON THIS POST

118

Posted on:

Friday 26th October 2012
View Replies!

DSN create...

I am used to MySQL, so all this MSSQL is a bit new to me.. so I am writnig a perl script to connect to a database and do some querying. I have to get a DSN to a specific database on my local machine. How do I do this I have created a DSN but it just points, it seems, to the machine and not the database itself.... help
VIEWS ON THIS POST

127

Posted on:

Friday 26th October 2012
View Replies!

Automatcaly create and/or update Table link from Access

I'm aware of how to create a function that automaticaly update my Table links from my Access FrontEnd to tables in a MS-SQL database : Public Sub RefreshLinksSQL() On Error GoTo RefreshLinks_Error Dim X As Integer, tbds As TableDefs Set tbds = CurrentDb.TableDefs For X = 0 To tbds.Count - 1 Select Case tbds(X).Attributes Case dbAttachExclusive, dbAttachSavePWD, dbAttachedTable, dbAttachedODBC tbds(X).RefreshLink End Select Next X Exit Sub RefreshLinks_Error: MsgBox Err.Number & ":" & Err.Description, vbCritical End Sub ----------------------------------- How do I create a link to another MS-SQL database (a subscriber with the same tables). Either by pointing to another database (System DSN) and just let it change the table links to this, or by writing a code with the name of each table I wornt to link. If this is posible, is there then a way where at the same time can point out what is the key in each table (or view) Romanov
VIEWS ON THIS POST

168

Posted on:

Friday 26th October 2012
View Replies!

SQL Foreign Keys

Ok, now I can't get my Foreign keys to work >_< I've tried compiling my code and I get an error message saying "Foreign key 'received_fkey' references invalid column 'product_order_ID' in referencing table 'customer'". Below is my code for creating my tables. Could I maybe have them in the wrong order or something create table customer (customer_ID varchar(7), customer_name varchar(30), customer_street varchar(30), customer_town varchar(30), customer_county varchar(30), customer_postcode varchar(8), customer_telephone varchar(12), customer_email varchar(30), constraint customerkey primary key (customer_ID), constraint cust_ID check ((customer_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint cust_postcode check ((customer_postcode like '[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]') or (customer_postcode like '[A-Z][A-Z][0-9][ ][ ][0-9][A-Z][A-Z]')), constraint cust_phone check ((customer_telephone like '[0-9][0-9][0-9][0-9][0-9][ ][0-9][0-9][0-9][0-9][0-9][0-9]')), constraint received_fkey foreign key (product_order_ID) references product_order on delete cascade on update cascade); create table product_order (product_order_ID char(7) not null, constraint product_orderkey primary key (product_order_ID), constraint prodorder_ID check ((product_order_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint sentby_fkey foreign key (warehouse_ID) references warehouse on update cascade on delete cascade); create table customer_order (customer_order_ID char(7) not null, order_date char(10) not null, delivery_date char(10) not null, constraint customer_orderkey primary key (customer_order_ID), constraint custorder_ID check ((customer_order_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint orddate check ((order_date like '[1-9[0-9][/][0-1][1-9][/][1-9][0-9][0-9][0-9]') or (order_date like '[1-9][/][0-1][1-9][/][1-9][0-9][0-9][0-9]')), constraint delivdate check ((delivery_date like '[1-9[0-9][/][0-1][1-9][/][1-9][0-9][0-9][0-9]') or (delivery_date like '[1-9][/][0-1][1-9][/][1-9][0-9][0-9][0-9]')), constraint makes_fkey foreign key (customer_ID) references customer on update cascade on delete cascade); create table product (product_ID char(7) not null, product_description char not null, constraint productkey primary key (product_ID), constraint prod_ID check ((product_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint orders_fkey foreign key (customer_order_ID) references customer_order on update cascade on delete cascade); create table supplier_order (supplier_order_ID char(7) not null, constraint supplier_orderkey primary key (supplier_order_ID), constraint supporder_ID check ((supplier_order_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint creates_fkey foreign key (product_ID) references product on update cascade on delete cascade); create table supplier (supplier_ID char(7) not null, supplier_name char(30) not null, supplier_street char(30) not null, supplier_town char(30) not null, supplier_county char(30) not null, supplier_postcode char(8) not null, supplier_telephone int not null, supplier_email char(30) not null, VAT_code int not null, supplier_contact char(30) not null, constraint supplierkey primary key (supplier_ID), constraint supp_ID check ((supplier_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint supp_postcode check ((supplier_postcode like '[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]') or (supplier_postcode like '[A-Z][A-Z][0-9][ ][ ][0-9][A-Z][A-Z]')), constraint supp_phone check ((supplier_telephone like '[0-9][0-9][0-9][0-9][0-9][ ][0-9][0-9][0-9][0-9][0-9][0-9]')), constraint sentto_fkey foreign key (supplier_order_ID) references supplier_order on update cascade on delete cascade); create table warehouse (warehouse_ID char(7) not null, product_stock int not null, warehouse_name char(30) not null, warehouse_street char(30) not null, warehouse_town char(30) not null, warehouse_county char(30) not null, warehouse_postcode char(8) not null, warehouse_telephone int not null, warehouse_email char(30) not null, constraint warhousekey primary key (warehouse_ID), constraint ware_ID check ((warehouse_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint ware_postcode check ((warehouse_postcode like '[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]') or (warehouse_postcode like '[A-Z][A-Z][0-9][ ][ ][0-9][A-Z][A-Z]')), constraint ware_phone check ((warehouse_telephone like '[0-9][0-9][0-9][0-9][0-9][ ][0-9][0-9][0-9][0-9][0-9][0-9]')), constraint ware_fkey foreign key (supplier_ID) references supplier on update cascade on delete cascade); create table supplier_service (supplier_service_ID char(7) not null, constraint supplier_servicekey primary key (supplier_service_ID), constraint suppservice_ID check ((supplier_service_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint offers_fkey foreign key (supplier_ID) references supplier on update cascade on delete cascade); create table web_service (web_service_ID char(7) not null, web_service_desription char not null, web_service_URL char not null, constraint web_servicekey primary key (web_service_ID), constraint webserv_ID check ((web_service_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')), constraint webserv_fkey foreign key (supplier_service_ID) references supplier_service on update cascade on delete cascade);
VIEWS ON THIS POST

34

Posted on:

Wednesday 7th November 2012
View Replies!

Upsizing to sql server from access

hi, I first installed msde on my pc. Then later I installed sql server 2000. I had things working great for a while, until I decided to review how to go about creating an adp. I created a little mdb database then tried to use the upsize wizard to create an adp. Well, I am getting all kinds of sql server errors that I dont understand. This worked about 3 months ago and I haven't changed a thing. So I don't know what's wrong now. I have a mdb that I want to upsize to sql server 2000. My sql server is running it seems. But, when I try to upsize, I get a message that "sql server does not exist". Then it says "Upsize wizard only works for version 6.5 or sp5 or higher ( huh).... Can somebody shed some light for me on this I have to start working with a database pretty soon and I am a little desperate.
VIEWS ON THIS POST

109

Posted on:

Saturday 10th November 2012
View Replies!

Select largest JobID

I am trying to select job duties that are listed under each employee's job description. The only problem is that an employee may have more than one job ID to associate their duties with. I'm trying to grab the max JobID number as I know that that will be the most current. The following SQL is working, but it is still pulling in all of the JobID numbers, not just the MAX JobID. Any help would be much appreciated. SELECT D.JobID, D.Duties FROM Emp_Users U, Emp_UserJob J, Emp_JobDuties D WHERE U.UserID = MMColParam AND U.UserID = J.UserID AND J.JobID = D.JobID; (SELECT MAX (D.JobID) FROM Emp_Users U, Emp_JobDuties D, Emp_UserJob J WHERE U.UserID = MMColParam AND U.UserID = J.UserID AND J.JobID = D.JobID)
VIEWS ON THIS POST

67

Posted on:

Saturday 10th November 2012
View Replies!

SQL Statement Help: Count Group By

My experience in SQL does not go much beyond SELECT WHERE statements and INSERT. So, although what I am attempting do do may be rather easy, I am struggling with it: Table Sample: ID | COLOR | Size 01 B S 01 B M 01 B LG 01 R M 02 B M 02 R XL What I am trying to do is find out how many of each color belongs to each ID. I have tried: SELECT ID, COLOR, COUNT (COLOR) AS QTY FROM SHIRTS GROUP BY ID, COLOR This gets me close to where I need to be, but no cigar. It tells me that ID COLOR QTY 01 B 4 But it does not let me know that there are actually 3 B's and 1 R. But what I need to know is: ID COLOR QTY 01 B 3 01 R 1 02 B 1 02 R 1 How can I break this down further to get the ID and count by color
VIEWS ON THIS POST

84

Posted on:

Saturday 10th November 2012
View Replies!

Auto increment field using UPDATE statement

I have a field in my table called 'Hits'. I want it to increment by one each time the form is submitted. I have come across this code from the web and have incorporated it into my code and it seems to do what I need but when I run it nothing happens:- Code: DECLARE @counter int SET @counter = 0 UPDATE users Set @counter = Hits = @counter + 1 Where Sitename = '" + Site.Text + "' I can't have my 'Hits' field as the Primary key and then increment this way because there will be different 'Sites' picked from my form. So for Site 1 it could have 20 in the 'Hits' field. For Site 2 it could have 10 etc etc. So I want to have an increment or running total for each site. Is there a better/easier way of doing this
VIEWS ON THIS POST

145

Posted on:

Sunday 11th November 2012
View Replies!

Calculate totals with group by

Hi I have not been using SQL Server very long and i am totally stuck on what is possibly quite a simple query assuming it is possible. I have a single table that i wan to query, it is a database of songs with all the usual fields Artist, Title, Album, Duration etc. What i need to do is calculate the total track time for each artist so i end up with... Artist Duration Artist1 625549876 Artist2 882655489 etc. The durations are all stored as integers in milliseconds but all i need is the total duration for each artist. Anyone give me a clue on how to achieve this.
VIEWS ON THIS POST

163

Posted on:

Sunday 11th November 2012
View Replies!

Prevent time span overlap

Given a table with StartDate and EndDate, I need to be able to determine if any given timespan (@StartDate and @EndDate) overlaps with any of the records in the table. I'd like something short and concise. I could make a big huge if/else structure, but there must be a better way. It would be nice and easy using BETWEENs, but I need to allow @StartDate = EndDate Some examples: Table has one record: StartDate = 10/1/07 7:00 AM__________EndDate = 10/1/07 12:00 PM The following entries (taken individually) would be considered OK: A) @StartDate = 10/1/07 4:00AM__________@EndDate = 10/1/07 6:00AM B) @StartDate = 10/1/07 4:00AM__________@EndDate = 10/1/07 7:00AM C) @StartDate = 10/1/07 12:00PM_________@EndDate = 10/1/07 3:00PM D) @StartDate = 10/1/07 1:00PM__________@EndDate = 10/1/07 3:00PM The following entries (taken individually) would be considered NOT OK: 1) @StartDate = 10/1/07 4:00AM__________@EndDate = 10/1/07 3:00PM 2) @StartDate = 10/1/07 4:00AM__________@EndDate = 10/1/07 10:00AM 3) @StartDate = 10/1/07 8:00AM__________@EndDate = 10/1/07 10:00AM 4) @StartDate = 10/1/07 10:00AM_________@EndDate = 10/1/07 3:00PM 5) @StartDate = 10/1/07 7:00AM__________@EndDate = 10/1/07 12:00PM Anyone got any ideas
VIEWS ON THIS POST

124

Posted on:

Sunday 11th November 2012
View Replies!

Query

hi below is my sql query..if my coulms value has null or blank than i don't want to add this coulmns in my select query.. how to do this.near future new coulmn can also add to table. Code: SELECT [ApplicationNo], [ProductCode], [RiskCommencementDate], [ProposalDate], [BillingFrequency], [ProposalReceiptDate], [MethodOfPayment], [SourceOfBusiness], [Agency], [StatisticalCode], [CaseClassification], [CUW], [DirectDebit], [MandateNo], [BankCode], [Account], [FollowUps], [DoctorCode], [FollowUpType1], [FollowUpLifeNo1], [FollowUpJLNo1], [FollowUpStatus1], [FollowUpRemDate1], [FollowUpType2], [FollowUpLifeNo2], [FollowUpJLNo2], [FollowUpStatus2], [FollowUpRemDate2], [FollowUpType3], [FollowUpLifeNo3], [FollowUpJLNo3], [FollowUpStatus3], [FollowUpRemDate3], [FollowUpType4], [FollowUpLifeNo4], [FollowUpJLNo4], [FollowUpStatus4], [FollowUpRemDate4], [FollowUpType5], [FollowUpLifeNo5], [FollowUpJLNo5], [FollowUpStatus5], [FollowUpRemDate5], [FollowUpType6], [FollowUpLifeNo6], [FollowUpJLNo6], [FollowUpStatus6], [FollowUpRemDate6], [FollowUpType7], [FollowUpLifeNo7], [FollowUpJLNo7], [FollowUpStatus7], [FollowUpRemDate7], [FollowUpType8], [FollowUpLifeNo8], [FollowUpJLNo8], [FollowUpStatus8], [FollowUpRemDate8], [FollowUpType9], [FollowUpLifeNo9], [FollowUpJLNo9], [FollowUpStatus9], [FollowUpRemDate9], [FollowUpType10], [FollowUpLifeNo10], [FollowUpJLNo10], [FollowUpStatus10], [FollowUpRemDate10], [Beneficiaries], [Relation1], [Share1], [EffectiveDate1], [Relation2], [Share2], [EffectiveDate2], [Relation3], [Share3], [EffectiveDate3], [Relation4], [Share4], [EffectiveDate4], [MedicalEvidence], [Smoking], [Occupation], [ProductSumAssured], [ProductTerm], [ProductPremium], [ReserveUnits], [ReserveUnitsDate], [ULIPAP], [SpecialTerm], [LoadPercent], [LoadReason], [FundBalancer], [FundFlexiBalancer], [FundMaximiser], [FundFlexiMaximiser], [FundProtector], [FundPreserver], [ADBR], [ADBRSumAssured], [ADBRTerm], [ADBRSpecialTerm], [ADBRReason], [ADBRLoad], [ABR], [ABRSumAssured], [ABRTerm], [ABRSpecialTerm], [ABRReason], [ABRLoad], [IBR], [IBRSumAssured], [IBRTerm], [IBRSpecialTerm], [IBRReason], [IBRLoad], [WOP], [WOPSumAssured], [WOPTerm], [WOPSpecialTerm], [WOPReason], [WOPLoad], [GISA], [CIBR], [CIBRSumAssured], [CIBRTerm], [CIBRSpecialTerm], [CIBRReason], [CIBRLoad], [GISASumAssured], [GISATerm], [MSAR], [MSARSumAssured], [MSARTerm], [MSARSpecialTerm], [MSARReason], [MSARLoad], [NSAP], [JLIndicator], [SmokingJL], [MedicalEvidenceJL], [OccupationJL], [RelationJL], [UWDate], [UWName], [UWDecision1], [UWDecision2], [LAAge], [ECS], [BenefitStructure], [HLInterest], [UCPeriod], [PremiumPayTerm], [CustomerPreference], [OpportunityId], [HGDR], [HGDRSumAssured], [HGDRTerm], [HGDRSpecialTerm], [HGDRReason], [HGDRLoad], [LanNo], [ChequeDrawnPremium] FROM [Tbl_ATS_Proposal] WHERE APPLICATIONNO= @STRAPPLICATIONNO thanx
VIEWS ON THIS POST

35

Posted on:

Sunday 11th November 2012
View Replies!

Connecting to databse problem

, I'm fairly new to web programming...I'm using MySQL, ASP, And VBScript...NOT PHP!! I have set up a connection between the database and dreamweaver...the database has 1 table with 1 field that is just a string named "message." I want to do is connect to this database with VBScript and have it output the text in the message field...can someone PLEASE help me!
VIEWS ON THIS POST

252

Posted on:

Sunday 11th November 2012
View Replies!

Problem with Microsoft SQL Server Database Publishing Wizard

hi, i m trying to retrive scripts using Microsoft SQL Server Database Publishing Wizard but its get fail can any noe suggest me why it so. i ave sql 2005 / 08 both install any problem then
VIEWS ON THIS POST

117

Posted on:

Monday 12th November 2012
View Replies!

Customizable Events-database

, We have a CMS-system based on the .net-platform 3.5 an a ms-sql-server. We want to create an event-database that allows multiple users to create/change events. Our needs are: - Preferable MS-SQL and ASP.NET (MySQL and PHP is possible too though not preferable) - Create our own database-structures - Add a full customizable interface to our data-structure where we can change the order of elements, type of fields, controls, ... and import e.g. google maps - different user-access-levels (where one person can approve the events for publication) - Version-administration: users provide the input on e.g. a copy-version or offline version and input will only be visible online after approval of the administrator Does anyone have any idea about an existing similar interface we can use Thank you in advance for your help and efforts!!! Sincerely, William.
VIEWS ON THIS POST

90

Posted on:

Monday 12th November 2012
View Replies!

How to increase SQL 2000 Per Seat User License?

Hi Guys, When I installed my SQL 2000 Server I had entered 50 Per Seat User License. Now I wish to increase another 50 license, how to do that And also how to check I got how many user license in my SQL 2000 Server
VIEWS ON THIS POST

73

Posted on:

Thursday 15th November 2012
View Replies!

Delete from two tables

These are my selcet and delete commands, however I am trying to delete from two tables both Assets and AssetAttribute. The both have related colums AssetTypeId, AssetAttributeId. I need to slect them first then delete them. I need the one action delete to delete int eh same information from both tables. Help. I only have the one written for Asst table, I need to include the Assetattribute table. Please help me. SelectCommand="SELECT AssetId, AssetTypeId, Description, AssetAttributeId, SKU, Barcode, GovBarcode, WarehouseId, IsVehicle, DeploymentStatus FROM Asset, AssetAttribute WHERE AssetId = @AssetId" DeleteCommand="DELETE FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
VIEWS ON THIS POST

166

Posted on:

Thursday 15th November 2012
View Replies!

Variable problem

Never mind - I figured it out. Here is a portion of the query I am using. When I run it using the stmt: EXEC dbo.spL2GVolumeConvFeeEntry @Report_Date I get an error message that says: Implicit conversion from data type sql_variant to smalldatetime not allowed. Use the CONVERT function to run this query. What am I missing here Code: ALTER PROCEDURE dbo.spL2GVolumeConvFeeEntry (@Report_Date smalldatetime = Null) AS DECLARE @Now smalldatetime DECLARE @RanOnDay smalldatetime DECLARE @StartDate smalldatetime DECLARE @EndDate smalldatetime IF @Report_Date IS NULL BEGIN SET @Now = GETDATE() SET @RanOnDay = @Now SET @EndDate = CONVERT(smalldatetime,CONVERT(varchar,DATEPART(month,@Now)) + '/1/' + CONVERT(varchar,DATEPART(year,@Now))) SET @Now = DATEADD(mm, -1, @Now) SET @StartDate = CONVERT(smalldatetime,CONVERT(varchar,DATEPART(month,@Now)) + '/1/' + CONVERT(varchar,DATEPART(year,@Now))) END ELSE BEGIN SET @RanOnDay = CONVERT(smalldatetime, @Report_Date) SET @EndDate = CONVERT(smalldatetime,CONVERT(varchar,DATEPART(month,@RanOnDay)) + '/1/' + CONVERT(varchar,DATEPART(year,@RanOnDay))) SET @RanOnDay = DATEADD(mm, -1, @RanOnDay) SET @StartDate = CONVERT(smalldatetime,CONVERT(varchar,DATEPART(month,@RanOnDay)) + '/1/' + CONVERT(varchar,DATEPART(year,@RanOnDay))) END
VIEWS ON THIS POST

178

Posted on:

Thursday 15th November 2012
View Replies!

Calculation errors or MS bug in recursive functions ??

are there know problems with calculation errors in recursive function in sql server 2000 if not, please point out the error in the following: PHP Code: CREATEFUNCTIONdbo.func_RecVerkoopprijs_basis(@Parentbigint,@Versie_IDbigint)RETURNSdecimal(10,2)ASBEGINDECLARE@Valuedecimal(10,2)IF(@ParentISnull)OR(@ParentIN(SELECTParent_IDFROMOfferte_LijnenWHEREVersie_ID=@Versie_ID))BEGIN\tIF(@ParentISnull)\tBEGIN\tSET@Value=(SELECTSUM(dbo.func_RecVerkoopprijs_basis(Lijn_ID,Versie_ID)*(1-Discount))FROMview_Full_LijnenWHEREParent_IDISnullANDVersie_ID=@Versie_ID)\tEND\tELSE\tBEGIN\tSET@Value=(SELECTSUM(dbo.func_RecVerkoopprijs_basis(Lijn_ID,Versie_ID)*(1-Discount))FROMview_Full_LijnenWHEREParent_ID=@Parent)\tENDENDELSEBEGIN\tSET@Value=(SELECT(Aantal*Actuele_Verkoopprijs)asprijsFROMview_Full_LijnenWHERELijn_ID=@Parentandoptioneel=0)ENDRETURN@ValueEND select dbo.func_recverkoopprijs_basis(25,5) as p this returns 4193 (wrong because this is supose to be 4220) Reason why: select Actuele_Verkoopprijs * Aantal from view_Full_Lijnen where Parent_ID=25 AND Versie_ID=5 returns: 400.0000 270.0000 250.0000 3300.0000 THEN: select sum(Actuele_Verkoopprijs * Aantal) from view_Full_Lijnen where Parent_ID=25 AND Versie_ID=5 returns: 4220 (this is right) EVEN: select sum(Actuele_Verkoopprijs * Aantal)*(1-(select discount from view_Full_Lijnen where lijn_ID=25 AND Versie_ID=5)) from view_Full_Lijnen where Parent_ID=25 AND Versie_ID=5 returns: 4220 (still right) And yet the salst statement should do the exact same thing the function does anyone any clue
VIEWS ON THIS POST

189

Posted on:

Thursday 15th November 2012
View Replies!

Problem with custom paging, maybe in my Stored Proc?

I have a working example of how to do custom paging. I can get the custom paging to work, I can get it working with sorting, but when I try to get it working with sorting and filtering I run into problems. I want to have a gridview control that displays a few of the record's fields. I want each row sortable, and I want to be able to filter it by state. I actually have three filters, but the user can only user one at a time, and the state is the simplest. If I can get that working I think I can get the others working as well. My objectdatasource looks like this: Code: GetInmatesSubsetByStateSorted and GetInmatesByStateRowCount are datasets built on stored procedures: [highlight="sql"] ALTER PROCEDURE GetInmatesSubsetByStateSorted ( @State nvarchar(2), @sortExpression nvarchar(50), @startRowIndex int, @maximumRows int ) AS If @State IS NULL EXEC dbo.GetInmatesSubsetSorted @SortExpression, @startRowIndex, @maximumRows ELSE BEGIN -- Otherwise we want to get just those employees in the specified department IF LEN(@sortExpression) = 0 SET @sortExpression = 'ID' -- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment SET @startRowIndex = @startRowIndex + 1 -- Issue query DECLARE @sql nvarchar(4000) SET @sql = 'SELECT ID, FILE2, [LAST NAME], [FIRST NAME], [DOC NUMBER], [CORR FACILITY], ADDRESS, CITY, STATE, Country, [Zip Codes], RACE, [RELIGION 1], [EARLIEST RELEASE DATE], [DATE OF BIRTH], SIGN, [EYE COLOR], [HAIR COLOR], [SEXUAL ORIENTATION], Overseas, [SEEKING LEGAL], [DEATH ROW], LIFER, DONATIONS, PHOTO, POETRY, ARTWORK, [HOME TOWN], [MARITAL STATUS], [LATEST RELEASE DATE], [INCARCERATED SINCE], [AD TYPE], [MALE OR FEMALE], [INCARCERATED FOR], [REFERRED BY], [AD STARTED], [AD ENDS], PageViews, COMMENTS, Ad, NewAd, ReNewAd, SeekEmploy, EmploySkills, LiveOnRelease, NeedsMail, NeedsMailDate, Suspend FROM (SELECT ID, FILE2, [LAST NAME], [FIRST NAME], [DOC NUMBER], [CORR FACILITY], ADDRESS, CITY, STATE, Country, [Zip Codes], RACE, [RELIGION 1], [EARLIEST RELEASE DATE], [DATE OF BIRTH], SIGN, [EYE COLOR], [HAIR COLOR], [SEXUAL ORIENTATION], Overseas, [SEEKING LEGAL], [DEATH ROW], LIFER, DONATIONS, PHOTO, POETRY, ARTWORK, [HOME TOWN], [MARITAL STATUS], [LATEST RELEASE DATE], [INCARCERATED SINCE], [AD TYPE], [MALE OR FEMALE], [INCARCERATED FOR], [REFERRED BY], [AD STARTED], [AD ENDS], PageViews, COMMENTS, Ad, NewAd, ReNewAd, SeekEmploy, EmploySkills, LiveOnRelease, NeedsMail, NeedsMailDate, Suspend, ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM prisonmembers WHERE State = @State ) as InmateInfo WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' -- Execute the SQL query EXEC sp_executesql @sql END and ALTER PROCEDURE dbo.GetInmatesByStateRowCount ( @State nvarchar(2) ) AS IF @State IS NULL EXEC GetInmatesRowCount ELSE SELECT COUNT(*) FROM prisonmembers WHERE State = @State RETURN [/highlight] I'm getting the following error: Must declare the scalar variable "@State". The source is code generated by Visual Studio. Is my problem in one of the the stored procedures I don't see how it can be in the Dataset, I used the Designer. I can get rid of the error by removing the line in blue above in the stored procedure, but then no records at all are returned. I've been struggling with this for a couple of weeks, and am no closer to a solution, despite a working example! Diane
VIEWS ON THIS POST

127

Posted on:

Tuesday 20th November 2012
View Replies!

Syntax error converting datetime from character string

I urgently need some help I have written a .NET windows service that processes some XML's and updates some fields in an SQL Server 2000 DB. It works ok on my workstation. But at the Client's site it causes an exception with the following error: "Syntax error converting datetime from character string" I heard it might have something to do with regional settings for the workstation and SQL Server Language Settings but im not sure. Could somebody please help with this issure!
VIEWS ON THIS POST

288

Posted on:

Tuesday 20th November 2012
View Replies!

calcuation problem

I need some help into how to approach this problem I have 4 tables and based on the results in table A and B I need to do a query that will first take the name of the hobby in table A and match them with each customerid hobby in Table C. The table above is the Hobbies table A, Hobbyid Clientid hobbyname statename 1 1 swimming GA 2 1 jogging CO 3 1 dancing NY 4 1 swimming SC 5 2 swimming NY 6 3 swimming NY table B is just a sample of the income table B hobbyid minsalary(10) expectedsal(10) 1 20000 100000 2 30000 400000 3 40000 500000 4 50000 600000 5 60000 200000 6 70000 700000 C customerid State(10) cminsalary(5) cexpsalary(10) hobby 1 GA 20000 100000 swimming 2 NY 20000 30000 jogging 3 SC 30000 10000 dancing 4 CO 10000 30000 swimming D customerid stateSchools (10) StateBirth (20) PreferredState (10) 1 GA NY SC 2 NY NC GA 3 SC GA CO 4 CO CO GA The tables already have the data and i would like to compare tables A and b with tables C and D so anything that is a state in table C and D is compared to the statename in table A and if the states match they will be given points I would then like the sum of the points , this category will be called geography. Table B minsalary and expectedsalary will be compared to table C CSalary and cexpsalary and put in a category called income a match will be given points. The points allocated are in brackets next to each field. Once I have a total points for each category I want to add them together and then add them to the table below. TABLE E customerid hobbyid finalscore so if customerid 1 filled out a questionare the hobby is swimming so the first thing will be to select all the hobbies that equal swimming from table A once we have all the hobbies that = swimming ie( hobbyid 1,4,5,6) then the statename for ( hobbyid 1,4,5,6) will be compared to the values that have state in them in tables C AND D e.g stateSchools, StateBirth , PreferredState,etc and if the statename matches any of these feilds e.g table A Statename for hobbyid 1 GA is a match for customerid 1 stateschools GA so points will be allocated for a match and no points if no match. i then want to get the total points from all the questions and then put them in a table called total score (Table D) which will hold the custermerid, the hobbid and TOTAL score so the results will look like this based on the mactches for each categorgy TABLE D customerid hobbyid score 1 1 40 1 4 10 1 5 10 1 6 10 the clientid and customerid are different.
VIEWS ON THIS POST

110

Posted on:

Tuesday 20th November 2012
View Replies!

Insert into multiple tables

hi i am using dreamweaver to create a insert record in to a table. but i need to insert the data into two tables. i am a newbie so is there a way to acheive this
VIEWS ON THIS POST

438

Posted on:

Tuesday 20th November 2012
View Replies!

DTS - SQL Server

I am having a problem with my DTS package. I want to move old backup files that are 10 days old. However, it is not passing the file name. Prior to adding the date as a condition, it moved all the bak files within the folder to the other folder. Any suggestions/ ' Move File Option Explicit Function Main() Dim oFSO Dim sSourceFile Dim sDestinationFile Dim oFile Set oFSO = CreateObject("Scripting.FileSystemObject") sSourceFile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Data\*.bak" sDestinationFile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Oldbakfiles\" Set oFile = oFSO.GetFile(sSourceFile) If oFile.DateCreated < Date Then oFSO.MoveFile sSourceFile, sDestinationFile Else Main = DTSTaskExecResult_Failure End If ' Clean Up Set oFSO = Nothing Set oFile = Nothing Main = DTSTaskExecResult_Success End Function
VIEWS ON THIS POST

43

Posted on:

Monday 26th November 2012
View Replies!