CDMeyer
Nuke Pro Posts:63
|
01/07/2011 3:17 PM |
|
Hi there,
I'm using Dynamic Forms to capture some data and I'm trying to set up a conditional data capture. I have the user entering a zip code and if the zip is in my table, then the data gets populated in table_X, but if it's not in the zip table, then it gets populated in table_Y.
I'm no longer getting errors, but the data does not seem to be going into the table. Any help with this is appreciated. Here is my code:
SELECT zip = CASE WHEN zip = 12345 THEN 'INSERT INTO table_X (name,email,zip) VALUES ($(name),$(email),$(zipcode))' ELSE 'INSERT INTO table_Y (name,email,zip)
VALUES ($(name),$(email),$(zipcode))'
END FROM zipcodes;
Thanks much!
|
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/07/2011 3:41 PM |
|
I haven't checked the CASE Statement but, the I think that the WHEN condition should say WHEN zip = '12345' |
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
Joseph Craig DNN MVP Posts:11667
|
01/07/2011 3:42 PM |
|
Oh ... you have single quotes around the inserts. |
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
CDMeyer
Nuke Pro Posts:63
|
01/07/2011 6:22 PM |
|
I formerly had single quotes around '12345' and did not have single quotes around the INSERT statements, but I got errors back when I had it like this:
This was my code progression with the respective errors below each:
Try #1: SELECT zip FROM zipcodes CASE WHEN zip = '12345' THEN INSERT INTO table_X (name,email,zip) VALUES ('$(name)','$(email)','$(zipcode)') ELSE INSERT INTO TESTtable_Y (name,email,zip) VALUES ('$(name)','$(email)','$(zipcode)') END ;
Incorrect syntax near the keyword 'CASE'. Incorrect syntax near the keyword 'ELSE'..
Try #2: SELECT zip = CASE WHEN zip = 12345 THEN INSERT INTO table_X (name,email,zip) VALUES ('$(name)','$(email)','$(zipcode)') ELSE INSERT INTO table_Y (name,email,zip) VALUES ('$(name)','$(email)','$(zipcode)') END FROM zipcodes;
Incorrect syntax near the keyword 'INSERT'. Incorrect syntax near the keyword 'ELSE'. Incorrect syntax near the keyword 'FROM'.
Try #3: SELECT zip = CASE WHEN zip = 12345 THEN 'INSERT INTO table_X (name,email,zip) VALUES ('$(name)','$(email)','$(zipcode)')' ELSE 'INSERT INTO table_Y (name,email,zip) VALUES ('$(name)','$(email)','$(zipcode)')' END FROM zipcodes;
Incorrect syntax near '$(name)'.
|
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/07/2011 6:39 PM |
|
Here is an example of a CASE statement: SELECT Category = CASE ProductLine WHEN 'R' THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END FROM Production.Product
Re-writing your example:
SELECT myzip = CASE zip WHEN '12345' THEN INSERT INTO table_X (name,email,zip) VALUES ('$(name)','$(email)','$(zipcode)') ELSE INSERT INTO table_Y (name,email,zip) VALUES ('$(name)','$(email)','$(zipcode)') END FROM zipcodes
(Note: I haven't tested this ...)
My new New Year's resolution is not to post answers without thinking.
|
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
CDMeyer
Nuke Pro Posts:63
|
01/07/2011 6:53 PM |
|
Unfortunately, this threw back some errors. Incorrect syntax near the keyword 'INSERT'. Incorrect syntax near the keyword
'ELSE'. Incorrect syntax near the keyword 'FROM'. The reason I had changed the quotes around is because I was wondering if SQL was getting confused and closing the statement (and reopening it) every time it saw a quote. Does that make any sense? -Jahn |
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/07/2011 7:17 PM |
|
I should ask about the definitions of the three tables.
The single quotes are used to denote character strings. They have nothing to do with the end of a statement.
As the errors are related to the INSERT and ELSE statement, I would look there. Can you simply execute the two insert statements?
If you'll post create SQL for the three tables, it would be easier to test.
|
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
CDMeyer
Nuke Pro Posts:63
|
01/07/2011 7:34 PM |
|
I was unable to run the INSERT by itself, but after playing a bit, I realized that the ID field was not populating automatically, like I had thought I had set the table up to do. So, because the ID field was null, it was throwing back an error, and that's perhaps where my problem is.
That being said, am I able to change the table to automatically generate the ID (incremental) or should I delete the table and recreate it correctly? Are you even able to delete a table?
Thanks,
-Jahn
|
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/08/2011 3:11 AM |
|
You can change the table. It should be fine. If you are using SQL Server Management Studio (or Express), right click on the table and choose the Modify option.
Make a backup first just in case something goes awry.
|
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
CDMeyer
Nuke Pro Posts:63
|
01/09/2011 11:00 PM |
|
Hi Joe, I'm actually working in a test environment, so if things go wrong, it's ok. However, still not working. :-( I actually recreated the table and tested to make sure it could capture data with the INSERT statement that's in my code, and it does. But when I use the entire statement I still get errors. Here's the latest code I'm using: SELECT zip = CASE zip WHEN '$(zipcode)' THEN INSERT INTO table_X (thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)') ELSE INSERT INTO table_Y (thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)') END FROM zipcodes; Errors: Incorrect syntax near the keyword 'INSERT'. Incorrect syntax near the keyword 'ELSE'. Incorrect syntax near the keyword 'FROM'. Any other ideas you may have are greatly appreciated. Thanks, -Jahn |
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/10/2011 7:49 AM |
|
Jahn,
If you could post the SQL to create your tables, I would be able to check your code. It doesn't make sense to do it without your tables, as I might do something different.
|
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
CDMeyer
Nuke Pro Posts:63
|
01/10/2011 10:43 AM |
|
All tables are created with the code below (just changed the table name): CREATE TABLE table_X (id INT IDENTITY (1,1) PRIMARY KEY, thedate datetime DEFAULT(getdate()), thename VARCHAR(50), theemail VARCHAR(150), thezip VARCHAR (20) ); |
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/10/2011 6:57 PM |
|
OK, my brain seems to be working better today.
You can't have an insert as part of a select statement they way that you
were doing it. But, lets go back to one of your earlier posts. I
think that what you are want to do is this:
IF zip = '12345'
THEN INSERT
INTO table_X (thename,theemail,thezip) VALUES
('$(name)','$(email)','$(zipcode)')
ELSE INSERT INTO TESTtable_Y
(thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)')
So something like that.
But, I'm not sure why you would have two tables. Why not just have one
table and do: INSERT
INTO table_X (name,email,zip) VALUES
('$(name)','$(email)','$(zipcode)')?
You can handle the zip code when you need it:
SELECT * from table_X where LTRIM(RTRIM(thezip))='12345'
The reason for the trims is to get rid of leading or trailing blanks because thezip is 20 characters.
|
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
CDMeyer
Nuke Pro Posts:63
|
01/10/2011 10:04 PM |
|
Laughing out loud right now, really. :-) Every time I looked for the proper syntax for an IF statement I was shown CASE, so I thought the IF statement was not possible. Lots of things are clearing up now. Ok, I tried the IF statement and it didn't populate the table (I would prefer this method as it's exactly what I need as far as logic is concerned). As far as having one table vs. two, it's a client request thing. The trim statement - should I use that whenever I retrieve the data only? Or should I use it when I'm entering data into the table? I'm assuming SQL doesn't put blank spaces in a table just because the column is set to a certain amount of characters. If it displays that way, I'll just have to see if it looks funky or not. Almost there? :-) Thanks very much for all your help so far. -Jahn |
|
|
|
|
CDMeyer
Nuke Pro Posts:63
|
01/11/2011 9:32 AM |
|
Joe, I had another thought. What if I populated the data into one database, but then when I extract the data I do a cross check to create two different results? Over time, as more and more data is in the table, do you think that would create a problem in terms of bogging down the system whenever I make my extraction? If this would work, then I would assume that I would use a similar CASE statement as I was trying to use earlier, except this time I would only display the data and not need an INSERT. If I could do it this way, that would buy me a ton of time and I'd be able to move on with my project. Your thoughts? -Jahn |
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/11/2011 5:54 PM |
|
I had some more things wrong. I think that this is closer to correct:
IF '$(zipcode)' = '12345' INSERT INTO table_X (thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)') ELSE INSERT INTO table_Y (thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)')
But, as I said I think that you are making this way too complicated. Just do this:
INSERT INTO table_X (thename,theemail,thezip) VALUES
('$(name)','$(email)','$(zipcode)')
When you need to extract data, so a SELECT with a WHERE clause. And, my use of the TRIMs is probably overkill. Try it without those.
You should NEVER put the same data into two tables. That's just asking for trouble and breaking the fundamental rules of good database design.
|
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
CDMeyer
Nuke Pro Posts:63
|
01/11/2011 8:21 PM |
|
Unfortunately I can't use your code suggestion because I only have 1 of the 2 variables. The $(zipcode) variable is being passed from the form. However, '12345' is something that would come from the zipcodes table. So, I would need to use a SELECT statement to get that information. Which brings us back to the previous coding suggestions and such as: SELECT $(zipcode) IF '$(zipcode)' = 'zip' INSERT INTO table_X (thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)') ELSE INSERT INTO table_Y (thename,theemail,thezip) VALUES ('$(name)','$(email)','$(zipcode)') FROM zipcodes I did try the above and it didn't work. Same errors as before. Also, I think I need to clarify a little. My apologies. There is a table full of zip codes called zipcodes. If a user enters a zip code that matches a zip code in the zipcodes table, then put the data in table_X. If there is no match, then put the data into table_Y. There should never be the same data in both tables because of this. However, I have no problem putting any and all data into a single table as you suggest. But when I do an extraction I need to be able to show if any of the zip codes entered match those contained in the zipcodes table and show me 2 sets of results. 1) data with matching zip codes and 2) data with non-matching zip codes. My only concern is that with this method, if down the road there are thousands upon thousands of records that doing the extraction will bog down the server because every record needs to be verified against the zipcodes table. Given the information I've given you in this reply do think there might even be a 3rd possibility/solution? Thanks again for your help. I'm sorry that I'm causing all this trouble. :-\ -Jahn |
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/11/2011 11:38 PM |
|
We're getting closer!
(I'm going to assume that the field in the zipcodes table is zip) This part creates and integer variable and sets it to the number of times the form variable $(zipcode) is in the zipcodes table. It will be zero if there is no match.
DECLARE @Z INT SET @Z = SELECT COUNT(ZIP) FROM ZIPCODES WHERE ZIP='$(zipcode)'
This part puts data in the correct table
IF @Z = 0 INSERT INTO table_Y (thename,theemail,thezip) VALUES
('$(name)','$(email)','$(zipcode)') ELSE INTO table_X (thename,theemail,thezip) VALUES
('$(name)','$(email)','$(zipcode)')
---------- There are some decent tutorials on T-SQL on the web. Google around ...
|
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|
CDMeyer
Nuke Pro Posts:63
|
01/12/2011 4:05 PM |
|
Hi Joe, I tried your suggestion and it didn't work. Any variations I tried (either from my brain or from what I read online) didn't work either. The errors are: Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near the keyword 'INTO'. Now I do have to mention that in the tables, I did not make the zip code column/field an Integer, I made it VARCHAR. Does that make a difference since you are setting the SET variable to be an INT? I tried making it a VARCHAR, but that didn't work either. What is the @ sign for? I looked for an explanation for that as well and couldn't figure that out. Thanks, -Jahn |
|
|
|
|
Joseph Craig DNN MVP Posts:11667
|
01/12/2011 5:48 PM |
|
Jahn,
Yes, all of those things to make a difference. I actually tested the code using table_x and table_y per the code you had previously posted.
As you can tell, it's tough programming into the dark. The best thing for you to do is to try testing code using SQL Server Management Studio Express. But, since you are not familiar with it or with SQL, this might be a case of shooting into the dark and hoping to hit a target.
If you could give me access to your site, this would be easy to do. You can send me email using the contact form at patapscorg.com.
|
|
Joe Craig, Patapsco Research Group Complete DNN Support |
|
|