i had a stored proc for which i made changes and added 2 new form fields to a stored proc and then ran it successfully now when i revoke the stored proc and run , it runs successfully but in the coldfusion it gives the error [Macromedia][SQLServer JDBC Driver][SQLServer]Insert Error: Column name or number of supplied values does not m...
I want to check for the username in the users table. If it's found, then I want to get the id. Otherwise, I want to insert the username as a new record, then get the id. Here's my code: <?PHP $sql = "SELECT id FROM users where username = '$username'"; $query = mysql_query($sql) or die(mysql_error()); $result = mysql_num_rows($query); if...
Assume that you are writing a simple app. The model is that a 'project' has a 'category'. The project's category can be edited by choosing from a drop down list that contains all possible categories. The user creates the following Categories: C1, C2, C3. The user creates the following Projects, Category associations: [P1, C1], [P2, C2]...
This is my table structure: The error message is: #1066 - Not unique table/alias: 'user' The following is my code. SELECT article.* , section.title, category.title, user.name, user.name FROM article INNER JOIN section ON article.section_id = section.id INNER JOIN category ON article.category_id = category.id INNER JOIN user ON...
Bit of a complicated SQL question here. I currently have a SELECT statement which matches several fields, like this. SELECT field1, field2, field3, field4, field5 FROM table WHERE field1 = 'variable 1' AND field2 = 'variable 2' AND field3 = 'variable 3' AND field4 = 'variable 4' AND field5 = 'variable 5' I would like to modify t...
Hey all, I'm trying to write an SQL query that would populate a dropdown box of locations. In addition, I need the query to select the location associated with a user automatically (ie: be the first in the list results.) I had the following, but recently realized that the list isn't completely alphabetized. To add another level to this...
I want to filter out several key phrases out of a dataset. Unfortunately, the only algorithm I've been able to come up with thus far is nested replace statements, such as: SELECT REPLACE( REPLACE(FIELDNAME,'</u>','') ,'<u>','') where FIELDNAME is raw HTML code stored in a table. As you can see, this is hideous. Any be...
Hi All, I have a User table where there are a Username and Application columns. Username may repeat but combination of Username + Application is unique, but I don't have the unique constraint set on the table (for performance) Question: will there be any difference (performance-wise) between : SELECT * FROM User where UserName='myuse...
I am transfering data from an old database to a new one, and I need to keep the ID from the old database when inserting them into the new database. However, it's not working. This is my Query: SET IDENTITY_INSERT RentalEase.dbo.tblTenant ON INSERT INTO RentalEase.dbo.tblTenant SELECT [ID] ,1 ,[PropertyID] ,0 ,[UnitI...
I believe that I am having a bad database design and need some help to improve it. I am having three tables for my blog posts. Posts -id -title Categories -id -name CategoryBindings -id -postId -categoryId Let say that I have this data: Posts 1 Title1 2 Title2 Categories 1 category1 2 category2 CategoryBindings 1 1 1 2 1 2 3 2 1 ...
Reason: We have a new client that wishes for the database containing all their info to be stored on their own personal database server. However the web server will be located at another location. Question How can you secure the data from the time it is inputed until the time an external database saves it? Through some reading it seems...
I have a case where Oracle data (phone numbers) was encrypted using the Oracle DBMS_OBFUSCATION_TOOLKIT DESEncrypt function with a unique user generated hash key. SQL Example: update Phone set encrypted_phone = WEBX_ENCRYPT_DECRYPT.ENCRYPT( '212-555-1201', '8IcrEuOdDjRT5iDjqHLcsA==') where person_id = 120000...
Hello, I'm trying to make a list of inventory to be counted, provided the item has had a sale in the last 2 months: I'm using Pervasive SQL and it's a BusinessVision table. This query works, but I don't know how to aggregate to have one item displayed: SELECT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", "INVENTORY"."BVSTKUOM", "I...
Hey guys, can anyone help me with a simple question. I have this SQL statement below in a sub in VB within access from a button click, and I am new to this. Here is what I typed: Private Sub Command0_Click() Dim rs As Recordset Dim sql As String Dim db As Database Set db = CurrentDb sql = "SELECT * FROM Transactions" Set...
I want to create a GUID and store it in the DB. In C# a guid can be created using Guid.NewGuid(). This creates a 128 bit integer. SQL Server has a uniqueidentifier column which holds a huge hexidecimal number. Is there a good/preferred way to make C# and SQL Server guids play well together? (i.e. create a guid using Guid.New() and ...
We are running a database system which is basically working as a pipeline flow architecture, i.e. we have lot of incoming data, process this using a variety of in-house knowledge data, and then we produce output data. Right now everything is intermingled, which makes it difficult to know what is autogenerated and what is not etc. We ar...
I have two tables products and sections in a many to many relationship and a join table products_sections. A product can be in one or more sections (new, car, airplane, old). Products id name ----------------- 1 something 2 something_else 3 other_thing Sections id name ----------------- 1 new 2 car Products_...
Given the following HBase schema scenario (from the official FAQ)... How would you design an Hbase table for many-to-many association between two entities, for example Student and Course? I would define two tables: Student: student id student data (name, address, ...) courses (use course ids as column qualifiers h...
This is a problem I often run into, and my solution has always to run numerous queries, but I feel there must be a better way. Here are my two tables for this example: Artists id name Songs id artistID name genre The table are linked as such: artists.id <-> songs.artistID I'd like to fetch a list of all artist...
Here is my query: SELECT publicationId AS PublicationID FROM dbo.PublicationOwner WHERE ownerId = 31331 UNION SELECT AreaBuy.AreaBuyID AS PublicationID FROM AreaBuy JOIN PublicationAreaBuy ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID WHERE PublicationAreaBuy.PublicationID IN (SELECT publicationId ...