Change column to autonumber (MSSQL)


After six years of constant nagging from me, my office has finally implemented a copy of MSSQL to replace Access (Woohoo!), so I am now going through and making changes to my queries, which is going quite well.

The one problem I noticed was that when the server people imported my databases, they did not always preserve the column properties properly. I'm getting "cannot insert null value" errors for columns that should be autonumber and primary key.

Is there:

1) A query I can issue to check the column properties, and

2) A query I can issue to change an existing column to autonumber and make it a primary key
Posted On: Monday 31st of December 2012 01:44:45 AM Total Views:  354
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Keeping track of record changes

Hi all! Okay, I am new to PHP and MySQL, and here is an embarrassingly simple question since I am very rusty on database stuff... I want to keep track of when a record was created and updated. I thought I read somewhere that if you use a certain Data-Type that MySQL will automatically put a TimeStamp in a row anytime something changes. Is this true What is the best way to set things up in PHP (i.e. code) and MySQL (i.e. Data-Types and SQL) to keep track of Row Created and Row Changed info\t
VIEWS ON THIS POST

154

Posted on:

Thursday 25th October 2012
View Replies!

deploy new changes to existing database

, Several days ago, I needed to upgrade one application by deploying some changes to one SQL server database. I tried to use ApexSQL diff tool but I got many error messages due to the dependencies (PKs and FKs). What's the best way to generate SQL scripts that upgrades my old database schema to match the new schema without data loss.
VIEWS ON THIS POST

235

Posted on:

Thursday 25th October 2012
View Replies!

DATE_ADD PROBLEM USING >

I am having a problem with my syntax. Code: DELETE * FROM BannedIPs WHERE DATE_ADD(NOW(), INTERVAL 5 MINUTE) > bannedIP_DATETIME I am trying to delete * from the BannedIPs table where the time (NOW) is greater than 5 minutes from the DATETIME value held in the bannedIP_DATETIME column, which is of type DATETIME. when I try to execute the command through phpmyadmin I get the following: Quote: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM BannedIPs WHERE DATE_ADD(CURRENT_DATE(), INTERVAL 5 ' at line 1 Can anyone help me with this , Thank you r937.
VIEWS ON THIS POST

68

Posted on:

Monday 29th October 2012
View Replies!

Query to return count(*) for 12 months of data

I'm rather new to this. What I need to do is create a query similar to the folowing: select Month(date_occurred) as n, Monthname(date_occurred) as m, count(*) as c from data_table where Year(date_occurred) = '2009' and env_location = 'JXT' GROUP BY m ORDER BY n This returns: -----+-----------+------ 1 | January | 6391 2 | February | 7391 3 | March | 7692 4 | April | 6985 5 | May | 5788 6 | June | 6456 7 | July | 5888 8 | August | 6123 9 | September | 6710 10 | October | 6681 11 | November | 5274 12 | December | 5394 This is the data that I want but I want to be able to specify a starting month/year and get the data for the next 12 months, not necassarily within the same year. For instance, I start on july 2006 and pull the data for 7/2006 to 6/2007. I am going to want to do the same thing for weeks, specify a starting week and then ppull the data for the next 26 or 52 weeks.
VIEWS ON THIS POST

90

Posted on:

Monday 29th October 2012
View Replies!

Phpmyadmin

i am using wamp and was wondering if there was a way to change the password for my individual databases as changing them through priveleges after i choose my database just seems to change the password for phpmyadmin and locks me out.
VIEWS ON THIS POST

144

Posted on:

Monday 29th October 2012
View Replies!

Handler_read_rnd_next = 13k on SMALL db

Hi! I have two very small DBs. One for a little number game, which only 6 people hit 1-3 times a day. The other is for a prototype of a game I'm working on, and there are no more than 3 users on this. Using phpmyadmin, I check the page with runtime info and I see Handler_read_rnd_next 13 k If I reload this page a couple times, it goes up to 14k. Most of the queries I use look like Code: SELECT c.color, b.percent FROM tbl_creatures a, tbl_creatures_colors b, tbl_colors c WHERE a.creature_id =1 AND a.creature_id = b.creature_id AND b.color_id = c.color_id And the EXPLAIN SQL for these comes up with Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE b ref PRIMARY,color_idPRIMARY 4 const 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 hq_proto_creatures.b.color_id 1 To me, this all looks fine. I can't tell if all the Handler_read_rnd_next is due to the phpmyadmin status checks or not. One more thing: I can't search the forum for "Handler_read_rnd_next" because no search terms larger than 12 characters are allowed... Suggestions
VIEWS ON THIS POST

94

Posted on:

Monday 29th October 2012
View Replies!

Stored procedure

Create stored procedure that will accept a series of parameters, do a lookup that will validate a user's credential and issue a boolean response. Procedure Name: Public_User_Login Parameters IN: Username varchar(255) Password varchar(255) TableName varchar(255) - table to query Fieldname varchar(255) - field to query username on Parameters OUT BoolResponse Boolean Also specify how to call ths procedure
VIEWS ON THIS POST

48

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Table join

As Rudy said, follow the advice by rabs in post #6 above. What you need is an intermediary table that will let you link a product with a range of colours.
VIEWS ON THIS POST

56

Posted on:

Monday 29th October 2012
View Replies!

Garble picture names

I have a user table with different levels of access(privileges). Users can upload photos into server. I would like to store photos the way so that users based on their photos paths would not be able easily figure out other photos paths or names (and view them) that belong to other members. e.g. I sign up as "john_wayne" I don't want to have photo link like view photo 1 I would prefer this view photo 1 How do pros go about that Do they hash photo names and store names as hash vales in database Does anybody know how to accomplish that or knows about some good article. Thank you.
VIEWS ON THIS POST

54

Posted on:

Monday 29th October 2012
View Replies!

Scalability Question: 100 MySQL Databases

I've been working on a new web application that uses PHP/MySQL combination. The site is currently only at 45 members as we just started our closed beta process. Currently, I am only using 1 database for everything. I have a table for users, comments, votes, posts, etc., and the entire site works perfectly. However, I have 100 MySQL databases at my disposal, and the hosting provider puts a 100mb cap on each. My questions is this: We're aiming to have at least 1000 users by the end of February, so is it advisable/possible to change structuring to have 10 databases for users, 10 for comments, 10 for posts, etc. I know I'll have to go back and re-code everything if this is the case, but I don't have a choice really. As a point of reference, we're only using 0.2mb of the available 100mb right now of the db, but I want to fix this now so it's not an issue if we get over 10,000 users. I can clarify if this isn't descriptive enough. I taught myself PHP/MySQL, and do not have a degree of any sort, so my conventions and concepts may be off.
VIEWS ON THIS POST

47

Posted on:

Monday 29th October 2012
View Replies!

discrepancy report, users without software (was "query question")

Greetings! Here is the jist of what I am trying to accomplish. I have a number of tables, but specifically I am talking about 2 for this issue - users and users software (usersw). In the users table I have 2 software requirements - office and msdn - each of these has several versions - designated by a number (0 none, 1 standard, 2 pro, and so on). They will be separate reports so we can probably limit it to one software for now. users contains: user_id user_name dept_id msdn office usersw contains: usersw_id user_id sw_id lic_id What I need to do is come up with some kind of discrepency report. Basically, if a software is listed in either msdn or office and they dont have the corresponding id in sw_id, I need to print their name and the software name that they should have. What I am thinking is this: List the software by username, then if it does contain either of the sw_ids then do nothing - if it does not contain either of them then print the name and software name that is required. Am I making any sense
VIEWS ON THIS POST

59

Posted on:

Monday 29th October 2012
View Replies!

Simple SELECT FROM issue

I'm trying to select from a table where that table name is stored in a variable, can anyone let me know the correct syntax for this Currently I have Code: DECLARE @SQL Varchar(2000) DECLARE @TableName Varchar(100) SET @TableName= 'SELECT tableName FROM myTable' SET @SQL = 'SELECT * FROM ' + @TableName EXEC(@SQL )
VIEWS ON THIS POST

72

Posted on:

Monday 29th October 2012
View Replies!

Get record from table1 which not exist in table2

hi to all, I have two table for example table a with one field a_id and second table b with three fields one is b_id,a_id,c_id Now i want to get all records of table a whose a_id and a constant c_id is not in table b i can get records of table a whose has a_id not in table b but i want to check the combination of a_id and c_id where c_id is any value for example constant For example table a has records a_id 1 2 3 4 table b has records b_id a_id c_id 1 1 10 2 3 10 i want to get records form a a_id 2 4 because these two records not exist in table b with c_id 10 get records from a where a.a_id not equal to b.a_id and b.c_id not equal to 10 regards.
VIEWS ON THIS POST

93

Posted on:

Monday 29th October 2012
View Replies!

Delete Duplicate Entries In Table

I added a custom input system to my user management script, and I created a table to hold each custom inputs values, holding the user id's value, field id, and the value. Each time profile information is updated, a row is inserted for each input field. This works, however it causes the table to become congested with duplicate entries pretty quickly. The table looks like this: field_values field_value_id (int, auto increment, unique) field_value_field_id (int) field_value_userid (int) field_value_value (varchar 255) DUPLICATE entries are DUPLICATE on all the columns except field_value_id. It doesn't matter which is deleted, as long as the unique rows are kept. It would be nicer though if I could delete all the DUPLICATE entries based on field_value_field_id and field_value_userid, and delete the oldest, keeping the newest.
VIEWS ON THIS POST

78

Posted on:

Monday 29th October 2012
View Replies!

Column type for php serialized array

what string column type should i use for php serialized array, i need this column to form a composite unique key as while , thanks for all u suggestions, i decide to use 2 extre table to restruct a 1NF, to illustrute what i have done, if any things done wrong plx let me kown preivous table : Code: CREATE TABLE `processes` ( id TINYINT NOT NULL AUTO_INCREMENT section CHAR (4) NOT NULL `mode` CHAR(4) NOT NULL loopLv1 TEXT NOT NULL loopLv2 TEXT NOT NULL ... PRIMARY KEY (id) UNIQUE (section, `mode`, loopLv1, loopLv2) ) INSERT IN processes (section, `mode`, looplv1, looplv2) VALUES ('page','force',serialize($array1), serialize($array2)) //properly quoted current table: Code: CREATE TABLE `processes` ( id TINYINT NOT NULL AUTO_INCREMENT section CHAR (4) NOT NULL `mode` CHAR(4) NOT NULL ... PRIMARY KEY (id) UNIQUE (section, `mode`) )ENGINE = MYSIAM CREATE TABLE `proesses_lv1` ( processId TINY NOT NULL lv1 VARCHAR(32) NOT NULL PRIMARY KEY (id, lv1) )ENGINE = MYSIAM CREATE TABLE `proesses_lv2` ( processId TINY NOT NULL lv2 VARCHAR(32) NOT NULL PRIMARY KEY (id, lv2) )ENGINE = MYSIAM the structure looks more clear with one execption. since i use MYSIAM so the is no support for declarative referential integrity, i have to manully presistant the relations of those tables,or maybe i should use InnoDB type table instead
VIEWS ON THIS POST

57

Posted on:

Monday 29th October 2012
View Replies!

Trying to Display Rows Based on Sum with a Tolerance

Everyone, I am trying to play around with a new new project and I am at a loss of where to begin, so any help or direction would be much appreciated. I have a db with a list of dates and positive or negative numbers something like what is below. Code: DATE Data 14-Apr-08 -3 -- 11-Apr-08 -2 | 10-Apr-08 +2 | - Would Match with -5 9-Apr-08 -2 -- 8-Apr-08 +2 7-Apr-08 +1 4-Apr-08 -4 3-Apr-08 +4 -- 2-Apr-08 -1 | 1-Apr-08 +1 | - Would Match with +6 31-Mar-08 +2 -- For example, I want to be able to query rows that are greater then or equal to positive or negative 5 and can have a tolerance of 1. The tolerance means if I am looking for the positive numbers I will stop when the negative numbers I have hit so far exceed my tolerance.
VIEWS ON THIS POST

46

Posted on:

Monday 29th October 2012
View Replies!

Help with mysq.

i have in my database the following tables CREATE TABLE Appointment ( ID int NOT NULL AUTO_INCREMENT, AT_cust VARCHAR(8) NOT NULL, AT_sec VARCHAR(8) NOT NULL, myTime time NOT NULL default "00:00:00", myDate DATE NOT NULL default "0000-00-00", Cause longtext NOT NULL, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=1; CREATE TABLE Secretary ( sec_AT VARCHAR(8) NOT NULL, PRIMARY KEY (sec_AT) ) ENGINE=InnoDB; CREATE TABLE Employee ( AT VARCHAR(8) NOT NULL, AFM VARCHAR(9) NOT NULL, DOY VARCHAR (20) NOT NULL default "A ATHINWN", Epitheto VARCHAR (20) NOT NULL, Onoma VARCHAR (20) NOT NULL, Hiring_Date DATE NOT NULL default "2000-01-01", Salary INT NOT NULL, Position enum('upallhlos grafeiou','upallhlos synergeiou') not null, PRIMARY KEY (AT), unique(AFM) ) ENGINE=InnoDB ; and i want to make a store procedure which returns a table with the customer name and based on the current date to add a column where it will put OK if myDate
VIEWS ON THIS POST

46

Posted on:

Monday 29th October 2012
View Replies!

Complex select

Complex select ! I have a complex select to do and i don't have any idea how to do it, maybe i will find some help here. So.. - I have a db and a table with some soccer records: 13(record) Arsenal(team) England(country) lets say this is table1and db1. (arsenal is only one example in that table i have all the england teams with different records) Second (db2)table have : 12(record) Arsenal(team) Third table have: 24(record) England (country) so i need a select to create a dynamic php table to show me this results select * from table2 where arsenal record(table2) > [(arsenal record(table1) + england record(table3) ) / 2 ]- 6 any ideas TY!
VIEWS ON THIS POST

93

Posted on:

Monday 29th October 2012
View Replies!

Need Help Grouping Query Results into 2 Different rows

I'm working on some very complicated sales reports queries and am trying to optimize them as currently they take over 2 pages to print out. I am now forced to optimze due to our recent upgrade to PHP 5.1 where the queries now time out. I'm trying to find a way to perform grouping on the results, but group the results into 2 different rows -- 1 where the results are true of a particular conditation and the other grouping consisting of the remainder of the results that didn't fit. For example, I want to be able to pull product titles, product prices, whether or not a coupon was used and the order totals from the database and group the results so that when the product price = order total AND a coupon was used, we total the item count and return the results in one row. Then for the other items where the product price did not match the order total but a coupon was used, I need the other line to sum the total items where the product price didn't match the order total. The reason I need this is that we have an agreement with our vendors that when certain digital products are downloaded and a coupon was used that matches the item price, we don't have to pay the vendor for those downloads. While it would seem easy to say, do a GROUP BY product_name, price, order_total - the problem that results is we get hundredes of rows b/c of the differring results in the order_total column as people often order multiple items. We need the results to group into just 2 rows so that we can use php and make a report out of it. Here is a more simple query that works on the same principle. Code: SELECT SUM(products_quantity), products_name, orders_id FROM `orders_products` WHERE products_name = "A Quiet Heart" GROUP BY products_name, orders_id HAVING orders_id < '500' GROUP BY products_name, orders_id HAVING orders_id > '3500' I know this query is incorrect b/c it has 2 group statements, but that's what I'm trying to have it do. If the first "having" condition is true, group all those results into one line and if the second having condition is true group those results into the other row. I need to do this without running 2 separate queries. Does anyone have any suggestions as to how I can accomplish this When I originally wrote the query, I just ended up doing a second copy of the query making the tweaks in the "Where" clause; however following that methodology resulted in a 2 page query that is now broken. If necessary I can post my actual query, but I hesitated to do that b/c of its massive size.
VIEWS ON THIS POST

44

Posted on:

Monday 29th October 2012
View Replies!

ERROR 1067 (42000) at line 2137: Invalid default value for 'AE_submitted'

hi folks, I'm trying to set up some software that uses mysql as a backend. As part of the install procedure I'm asked to run the following command Code: mysql -u me -p
VIEWS ON THIS POST

152

Posted on:

Monday 29th October 2012
View Replies!

ORDER BY created

hi, i need to know how to retrieve records in the order in which they were created. I do not want to use a date field however. Is it possible to just order the results by the order in which they were created without having to include extra fields with this information in my table looks like:- emailID, recipientID, mailStatus ------------------------------ 1 32 1 1 54 2 1 45 1 2 23 1 2 32 1 where mailID and recipientID are a composite key. I am using the following SQL statement to retrieve the records:- "SELECT DISTINCT emailID FROM tableName" I just want to get the list of emailID's that have been sent (in this example (1 and 2) but in the order in which they have been created in this table. extra note:- the emailID field does not necessarily contain numbers in sequential order. For instance it may contain emailID '36' before it contains emailID '5'.
VIEWS ON THIS POST

53

Posted on:

Monday 29th October 2012
View Replies!

Design for Energy Meters

. I need suggestions/comments on an efficient design of a database that will be in MySQL and will capture/monitor energy meters. Here is my current idea: Building Table: BID Name Desc Type WaterMeter Table: WMID BID Reading TimeStamp ElectricMeter Table: same as water w/ EMID instead and then here is where I get confused because these readings will happen every 5min by a cron/perl script parsing xml from the meter and dumping into DB. I need to do weekly monthly yearly reports blablabla. So: WeeklyWM Table: WMID Mon Tue Wed ... Sun MonthlyWM Table: Week 1 Week 2 Week 3 Week 4 YearlyWM Table Jan Feb ... Dec Hist Inserts Year every Year This seems very inefficient. DOes anyone have better sugestions
VIEWS ON THIS POST

30

Posted on:

Monday 29th October 2012
View Replies!

Configuring MySQL on Mac OS X 10.4

I am currently attempting to set up a fresh configuration on my Powerbook running OS X 10.4.4, and having some problems. I'm attempting to do this so that I can practice running Ruby on Rails. I've followed the following setup tutorial for building Ruby, Rails, LightTPD, and MySQL on Tiger: http://hivelogic.com/articles/2005/12/01/ruby_rails_lighttpd_mysql_tiger Ruby, Rails, and LightTPD seem to have followed this setup tutorial properly. However the problem that I encounter is with MySQL when following a tutorial from the book "Agile Web Development with Rails". I attempt to create the database "depot_development". When I do so I recieve the following error: "ERROR 1007 (HY000): Can't create database 'depot_development'; database exists" This would (I presume) mean that the database already exists, but when I run "mysqlshow" from the terminal, the only database that appears is "test". Despite not seeing a depot_development database, I've tried to continue with the tutorial and run the following SQL create file that should setup a table named products in depot_development: drop table if exists products; create table products ( id int not null auto_increment, title varchar(100) not null, description text not null, image_url varchar(200) not null, price decimal(10,2) not null, primary key (id) ); I am trying to run this create.sql file using the following command: mysql depot_development
VIEWS ON THIS POST

46

Posted on:

Monday 29th October 2012
View Replies!

Temporary tables problem

, I'm having a problem with using temporary tables that I can't seem to fix, and I don't know if I'm *supposed* to be able to fix it. I have read through responses to other users on this forum that touched on this issue (e.g. http://forums.devshed.com/mysql-help-4/using-temporary-table-on-different-pages-347805.html) and the PHP FAQ's which also deal with this issue, but I still have some questions. Like the user in the other thread I mentioned, I had hoped to use temporary tables as a way of "drilling down" an initial set of search results by additional criteria and/or by number of results returned, for pagination. I am unable to do that, however, because the temporary table is deleted when I change the page. As I expected, using mysql_pconnect() doesn't solve anything. Now, the example in the PHP FAQ thread suggested that one simply pass the beginning and ending record numbers through an HTML link, but that doesn't really address my problem. Unlike the other user, I am not concerned about the volume of database queries, but I do want a degree of simplicity in my code, and keeping track of the ever-increasing characteristics to search for in a drill-down sounds like a nightmare, since my users can search for up to 30 characteristics. Additionally, I am creating this result set by a join of 7 different tables. I just can't see having to do that every time a user reloads a page, in addition to searching repeatedly for the dozens of characteristics I am letting them search for. My code would be an absolute mess. Some people have claimed that the solution presented in the FAQ only requires a single query and then enables pagination through that query, however after looking at that solution I don't think that is the case. Every time the page is reloaded it will start from scratch. The concept of a temporary table that functions as a result set sounds so incredibly elegant, and it is disappointing to me that I can't seem to use it!!! Are there any other ways to do this\t
VIEWS ON THIS POST

76

Posted on:

Monday 29th October 2012
View Replies!

The Last Step - I hope

Hi , I am struggling with the last step in developing my 'clumsy idiot avoidance' code. An attempt to avoid my getting captions matched to wrong image or wrong price etc.. It's still an academic curiosity at the moment but I feel sure it will come in useful. This gives me exactly what I want to see, a formatted, prescribed table complete with content for a specific set of values for catalogue=A0002. [] [] However, I want to make it a sub procedure or function then call it in various page positions for different values of 'catalogue' in the WHERE part of my conditional SQL. Such as [] in the following code. This code does not work but I cannot see why it shouldn't. [] [ ] I am sure I've done something stupid but I just can't see it, and when I test it through the server resulting in '500 Internal Server Error', I feel so frustrated that there are no clues pointing to where the error lies.
VIEWS ON THIS POST

33

Posted on:

Wednesday 7th November 2012
View Replies!

Search for MULTIPLE words within ONE field.??

, I've created my first search page using ASP. Whilst the search function works and data can be obtained, the results will only be successful if the word i try to search is the first word in any entry in one field. For example... if i search for BALL i will get nothing... however if i search for GLOW BALL i will get a result i will receive a result... similarly if i search for GLOW i will get multiple results How can i structure the query so that i can search for any of the words within the field and be guaranteed to get a result the link for the search form is http://www.galaxyworldimports.com.au/search.html
VIEWS ON THIS POST

160

Posted on:

Wednesday 7th November 2012
View Replies!

somebody help me plzzzzzzzz

how to select a third row from a table which has 'id' as the pk, no other details about the table is given. plz hope i will get my answer sooooon,
VIEWS ON THIS POST

74

Posted on:

Wednesday 7th November 2012
View Replies!

How to search in a database

Originally posted by : HP (hpvanhagen@hotmail.com),I just have a access database connected with ODBC and IIS 4.0.My Query comes from access:SELECT im.IM, im.Betreft, ("secretariaten/im/" & [IM] & ".doc") AS [Bekijk IM]FROM imWHERE (((im.IM)=[IM nummer]) AND ((im.Betreft) Like "*"+[Name your looking for is:]+"*"));Why do i get a error\tPlease help me!
VIEWS ON THIS POST

203

Posted on:

Wednesday 7th November 2012
View Replies!

syntax of passing query to stored procedur

Originally posted by : Donna (donnaz@webportinc.com)I keep getting syntax error near the WHERE clause when passing variables to MS-SQL stored proc. Would someone like a few paying hours to help get these variables into the sp and make the results page. I must search by date, number, and varchar. I'm on server 7.0 using a temp table. I've looked at all the paging info on 4 and other sites. HELP!
VIEWS ON THIS POST

124

Posted on:

Wednesday 7th November 2012
View Replies!

Renaming selected results in a query

Im trying to select some fields from the inner join of a couple table and rename the results. After that I need to check for duplicates of some of these fields. I've tried the following as a stored procedure but get no result.help CREATE PROCEDURE dbo.PendingClaims CREATE PROCEDURE dbo.PendingClaims @StartPeriod datetime, @EndPeriod datetime AS select ClaimStatus.suffix,ClaimStatus.seqno,ClaimStatus.g roupNo,ClaimStatus.memberId,ClaimStatus.depseq,Pay mentHist.serviceFrom,PaymentHist.paidDate,Provider .firstname,BenefitLine.charge, Provider.lastname,sum(BenefitLine.majorPaid+Benefi tLine.basicPaid)as totalpaid,ClaimStatus.JulianDate into #Dup From {oj((dbo.ClaimStatus ClaimStatus Inner Join dbo.PaymentHist PaymentHist On PaymentHist.julianDate= ClaimStatus.julianDate AND PaymentHist.suffix = ClaimStatus.suffix AND PaymentHist.seqno = ClaimStatus.seqno) INNER JOIN dbo.BenefitLine BenefitLine ON PaymentHist.julianDate= BenefitLine.julianDate AND PaymentHist.suffix = BenefitLine.suffix AND PaymentHist.seqno = BenefitLine.seqno AND PaymentHist.adjNo = BenefitLine.adjNo AND PaymentHist.payNo = BenefitLine.payNo) INNER JOIN dbo.PROVIDER Provider ON PaymentHist.userId = Provider.userid AND PaymentHist.addJulianYear = Provider.addJulianYear AND PaymentHist.addJulianDay = Provider.addJulianDay AND PaymentHist.addNo = Provider.addJulianNo } where PaymentHist.paidDate >= @StartPeriod and PaymentHist.paidDate
VIEWS ON THIS POST

86

Posted on:

Saturday 10th November 2012
View Replies!

single quotes problem

Originally posted by : joe (jhandle3@ford.com)Can anybody assist my sql statement is:myFunction = "FAQ's"'SQL to open records and sort:strSql = "SELECT tblInventory.URL, tblInventory.BrandNameID, tblInventory.Description, tblInventory.Market, tblInventory.AppDevelopPartner, tblInventory.CreativePartner, tblFunction.FunctionType FROM tblInventory INNER JOIN tblFunction ON tblInventory.[FAQ''s] = tblFunction.FunctionType WHERE (((tblInventory.[FAQ''s])='"& myFunction &"'));" ... its in one long line of code on my asp page so there are no end of line problems. The problem I have is that I want my variable which contains the string -FAQ's- to find the data FAQ's in my table and it won't with the quote in it\t\t I know its a common problem and have tried the double single quotes but to no avail. Ideas
VIEWS ON THIS POST

72

Posted on:

Thursday 15th November 2012
View Replies!

Needs Help With Writing SQL Query

Here's my current SQL Query: SELECT SumOfPaymentOwed = CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0 THEN 0 ELSE CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed)) END FROM Amounts WHERE PaidDate is Null AND ClientID = '" & strClientID & "'" This works fine. However, I'd like for it to show something like: Amounts Paid Since [PaidDate]. I'd like to find the first paid date (ex: 4/05/2005) in the database and display the results like: Totals Paid: $200 since 4/05/2005. Could someone please help me with this
VIEWS ON THIS POST

32

Posted on:

Tuesday 20th November 2012
View Replies!

SQL query

Im trying to do a sql query whereby it will count the cust_id. The checking now is it will check the cust_id from 2 tables. customer and answer tbl whether the id appear in both table and whether survey_taken ='Y'. But if the cust_id did not appear in the customer tbl it will still return 0. I want to ensure that the customer appear in the customer tbl be4 he is allow to take the survey. Is there any other way I can do the query select count(O.cust_id) as cnt from customer C, answer O where C.cust_id= O.cust_id and O.survey_taken='Y' and O.cust_id=8
VIEWS ON THIS POST

80

Posted on:

Tuesday 20th November 2012
View Replies!

reply to a dumb person for a change

whats the difference b/w "inner join" and "outer join" is anything wrong with the following code code: PHP Code: ALTERPROCEDUREselectjobsearch(@emuservarchar(20)=null,@emcityvarchar(50)=null,@emprovincechar(2)=null,@emcountrychar(2)=null,@jopostedsmalldatetime=null,@jotitlevarchar(100)=null,@jodetailsvarchar(4000),@categoryvarchar(50)=null,@industryvarchar(50)=null)ASSELECTJobs.jid,Employer.emname,Jobs.jotitle,Jobs.jocutoff,Industries.industryFROM(IndustriesINNERJOIN(EmployerINNERJOINJobsONEmployer.emuser=Jobs.emuser)ONIndustries.indid=Jobs.indid)INNERJOIN(CategoriesINNERJOINJobCatONCategories.catid=JobCat.catid)ONJobs.jid=JobCat.jidWhereEmployer.emuser=@emuserorEmployer.emcity=@emcityorEmployer.emprovince=@emprovinceorEmployer.emcountry=@emcountryorJobs.joposted=@jopostedorJobs.jotitlelike'%'+@jotitle+'%'orJobs.jodetailslike'%'+@jodetails+'%'orCategories.category=@categoryorIndustries.industry=@industry i m trying to search for some job detials. when i run the SQL command i dont get any records in return what did i do wrong
VIEWS ON THIS POST

46

Posted on:

Tuesday 20th November 2012
View Replies!

Newbie ASP Programming - Help Please!

Originally posted by : nonexistant (nonexistant@usa.net)I am trying to create a web page that will allow people to vote on topics, and keep a running average score. I have created an access database with the Votes and Rating fields. What I am trying to do is find a way for the topic to be displayed, acquire the users vote, and then run a statement like: Rating = ((Uservote + Rating) / Votes) to get the average rating. I am having trouble with the variable names to perform this function. if I load in:Rating = rs("Rating")Votes = rs("Votes")and perform the calculation, I also need to be able to write the results back into the database as Votes & Rating. Please email me at nonexistant@usa.net for more info.
VIEWS ON THIS POST

53

Posted on:

Tuesday 20th November 2012
View Replies!

Validate text box entries...

My company site was hit with an SQL injection attack. Nothing major happened and we were able to clean up the mess in a couple of hours. In looking in to ways of stopping these attacks, one of the more suggested was to limit what can be passed through to the database by limiting and validating what people can put in the text boxes. The site is written in ASP, the database is a 2005 MS SQL database. Can anyone either tell me how and where to put the code for validation or point me to a tutorial with a good walk through on doing this As a complete novice to databases and only someone who tinkers with ASP programming, I need some simple straightforward answers...if there are any.
VIEWS ON THIS POST

130

Posted on:

Monday 26th November 2012
View Replies!

ORDER BY not working 100% for my numbers

I am trying to order a bunch of stuff that is returned from a SQL select using the ORDER BY clause. It's working about 90% but I have some numbers that are out of order. I am thinking that perhaps the field coming from our Informix database is not an integer. Anyone have any ideas.
VIEWS ON THIS POST

67

Posted on:

Monday 26th November 2012
View Replies!

Update limit in a stored procedure

I have a table with more then 20,000 records. this table has a record_id column. I want to change the record_id values based on a different sorting. I wrote a stored procedure using a cursor running over all the records in that table (in the new order) and then for each record assign a new record_id. when I run the stored procedure without the update, I get all 22,798 records. when I run it with the update I get only 4,838 records. Is there a limit to the amount of record a stored procedure can update can it be changed STORED PROCEDURE: CREATE PROCEDURE sft_sort_table as BEGIN DECLARE @record_ID INT DECLARE @MAX_record_ID INT DECLARE @CUR_RECORD_ID INT --Get the max rate_record_id SELECT @MAX_record_ID INT = max(record_id) FROM table DECLARE curRecord CURSOR FOR select record_id from table order by new_sort FOR UPDATE OF record_id OPEN curRecord FETCH NEXT FROM curRecord INTO @CUR_RECORD_ID WHILE @@FETCH_STATUS = 0 BEGIN SET @MAX_RECORD_ID = @MAX_RECORD_ID + 1 PRINT 'Record ' PRINT @CUR_RECORD_ID Print 'will be ' PRINT @MAX_RECORD_ID PRINT '-----------------------------' --Update the record_id UPDATE table SET record_id = @MAX_RECORD_ID WHERE current of curRecord FETCH NEXT FROM curRecord INTO @CUR_RECORD_ID END CLOSE curRecord; DEALLOCATE curRecord; END;
VIEWS ON THIS POST

58

Posted on:

Monday 26th November 2012
View Replies!