• (5 Pages)
• 1
• 2
• 3
• 4
• Last »

## 62 Replies - 17350 Views - Last Post: 28 July 2009 - 01:12 PMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'https://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=60053&amp;s=538babf6ff1559df787bc72fc8535bf1&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #16 sansclue

• D.I.C Regular

Reputation: 29
• Posts: 316
• Joined: 21-November 07

Posted 14 August 2008 - 11:16 AM

bonneylake, on 14 Aug, 2008 - 06:28 AM, said:

but if you wouldn't mind putting up with me a little bit more could you explain how i could do the rename part of this?i have looked at examples online but i am still baffled on how to do this the way i am trying to accomplish this.

here is what i got right now for the rename an was going to put this under the cffile upload.
<CFFILE ACTION="RENAME" SOURCE="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#CFFILE.ServerFile#" destination="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\">


but thank you so much for all the help you have given me ,
Rach

Good to hear it was just an oversight. Those are easy to fix ... once you find them ;-)

In the code above the destination is a directory, which won't work. For rename, you have to supply two full file paths. Example

source="c:\fullpath\to\oldFileName.txt" (ie original file name)
destination="c:\fullpath\to\newFileName.txt" (ie new file name)

You already know the directory path. So you can use the CFFILE variables to construct the new file name. Since you are not using CF8, the unique file names are random strings like ACF39DD. So try using the CFFILE.ClientFileName variable to get the original file name. Then concatenate it with the ID, attachment number and file extension to create the new file name. As I mentioned before, I do not normally use the "Client..." variables but they are supposed to contain information about the original file name, etc...

<cfloop ....>
<!--- construct the new file name --->
<cfset newFileName = CFFILE.ClientFileName &"_"& FORM.ID &"_"& counter &"."& CFFILE.ServerFileExt>
<!--- rename the uploaded file --->
<cffile action="rename" ...>
...
</cfloop>



Then use the old (CFFILE.ServerFile) and new file names to perform the rename. But be sure your file naming convention really is unique. IIRC rename will overwrite existing files. So you can easily wipe out files by accident.

This post has been edited by sansclue: 14 August 2008 - 11:19 AM

### #17 bonneylake

Reputation: 0
• Posts: 37
• Joined: 22-July 08

Posted 14 August 2008 - 11:33 AM

Hey Sansclue,

I figured out how to do the rename an it all works great. Here is what the finished code looks like

<cfif structKeyExists(FORM, "totalAttachments")>
<cfset currentDirectory = GetDirectoryFromPath(GetTemplatePath()) & "uploaded">
<cfparam name="FORM.totalAttachments" default="0">
<cfloop from="1" to="#form.totalAttachments#" index="counter">
verify the form field exists
<cfif structKeyExists(FORM, "attachment"& counter)>
<cfset filename = cffile.ClientFileName & "_" & form.id & "_" & counter & "." & cffile.ClientFileExt>
<CFFILE ACTION="RENAME" SOURCE="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#CFFILE.ServerFile#" destination="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#filename#">
<cfquery name="attachment" datasource="CustomerSupport">
exec usp_CS_Insertattachments
</cfquery>
</cfif>
</cfloop>
</cfif>


but i wanted to ask you one more question, if you don't mind answering it. I don't know if i need to write this into the ajax/javascript part of the site or not but figure i ask you before i do that.

Well obviously what i been doing is trying to upload multiple files. But anyway i need to be able to add a description to every file i add. I have figured out the Javascript part of this an i think almost most of the Coldfusion part. Right now the way it is working is the first description i fill out it will apply it to every file i upload rather then the first description go with first file an 2nd description go with 2nd file. I am thinking i either need to put a counter in there or a cfloop somewhere for the description part, but not sure. But here is what i got so far, the action page for this is above.

javascript
<!--- Allows you to attach multiple files --->
<script type="text/javascript">
{
var d = document.createElement("div");
var file = document.createElement("input");
file.setAttribute("type", "file");
d.appendChild(file);

var text = document.createElement("input");
text.setAttribute("type", "text");
d.appendChild(text);
}
</script>


an heres html
 <input type="file" name="attachment1" id="attachment" value="" onchange="document.getElementById('moreUploadsLink').style.display = 'block';" />
<input type="text" name="description" id="description" value="" />
<input type="button" value="Attach another file"
</div>
<input type="hidden" id="totalAttachments" name="totalAttachments" value="1">


If you got any idea on how i can accomplish that, it be awesome.

But thank you for all your help ,
Rach

### #18 sansclue

• D.I.C Regular

Reputation: 29
• Posts: 316
• Joined: 21-November 07

Posted 14 August 2008 - 12:27 PM

[quote name='bonneylake' date='14 Aug, 2008 - 11:33 AM' post='400807']
<CFFILE ACTION="RENAME" SOURCE="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#CFFILE.ServerFile#" destination="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#filename#">

Right now the way it is working is the first description i fill out it will apply it to every file i upload rather then the first description go with first file an 2nd description go with 2nd file. I am thinking i either need to put a counter in there or a cfloop somewhere for the description part, but not sure.
[/quote]

You understand the rename will always wipe out any existing file by that same name,  right?  If for whatever reason the #filename# is wrong or is not unique (coding mistake, hacking attempt, ...) the existing files will be overwritten and you cannot recover them.

As far as the description, handle it the same way you do the attachments.  On the action page, use the #counter# variable and array notation to get the value.

[code]
<cfloop index="counter" ...>
<cfset currentDescription = form["description" & counter]>
.....
</cfloop>



But I think you have a typo in the html code. The first field name should be "description1" not "description".

	<input type="file" name="attachment1" id="attachment" value="" onchange="document.getElementById('moreUploadsLink').style.display = 'block';" />
<input type="text" name="description" id="description" value="" />



BTW - You didn't say.. why aren't you using cfstoredproc?

### #19 bonneylake

Reputation: 0
• Posts: 37
• Joined: 22-July 08

Posted 14 August 2008 - 02:13 PM

Hey Sansclue,

Well to be honest with you i have just always used cfquery an haven't ever used cfstoredproc. Not really sure what the difference is (read most of coldfusion book but its been over 5 months since i have), but if you could explain that to me it be very appreciated .

As of of worrying about it wipeing out existing names. The way i have it is every time someone fills out the form, they are given a id (ticket id). An in my attachments every time someone uploads a file it will have the file name the id (ticket id)and will have the count (which count starts over everytime i create a new ticket). So its next to impossible to have a duplicate file. Also in my attachment table a primary key is assigned to every attachment uploaded an a serial number. Now if someone hacks it, yeah i will probably be screwed. But i think everyone at my job would be if it did happen. But thank you for still pointing that out to me, because i miss things like that (most of the time) .

But on the description part. Yeah your right on the html it should be description1 for the name so changed that. But i am confused on the other part of this. I tried to put in what you had in your example an well i ran into the problem of it saying Error resolving parameter DESCRIPTION.ColdFusion was unable to determine the value of the parameter.i am probably just missing something simple an cant see what it is.

<cfif structKeyExists(FORM, "totalAttachments")>
<cfset currentDirectory = GetDirectoryFromPath(GetTemplatePath()) & "uploaded">
<cfparam name="FORM.totalAttachments" default="0">
<cfloop from="1" to="#form.totalAttachments#" index="counter">
<cfset currentDescription = form["description" & counter]>
<!---verify the form field exists --->
<cfif structKeyExists(FORM, "attachment"& counter)>
<!--- try and upload it ...--->
<cfset filename = cffile.ClientFileName & "_" & form.id & "_" & counter & "." & cffile.ClientFileExt>
<CFFILE ACTION="RENAME" SOURCE="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#CFFILE.ServerFile#" destination="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#filename#">
<cfquery name="attachment" datasource="CustomerSupport">
exec usp_CS_Insertattachments
</cfquery>
</cfif>
</cfloop>
</cfif>


but any suggestions on what i did wrong? i was thinking that maybe i needed to make it look like the cfif strucktkeyexists one, the one under verify the form field exits (because your example looks almost identical to it)and tried a few different ways but i didn't get any further.

But again thank you for all your help
Rach

### #20 sansclue

• D.I.C Regular

Reputation: 29
• Posts: 316
• Joined: 21-November 07

Posted 14 August 2008 - 03:32 PM

bonneylake, on 14 Aug, 2008 - 02:13 PM, said:

Well to be honest with you i have just always used cfquery an haven't ever used cfstoredproc. Not really sure what the difference is (read most of coldfusion book but its been over 5 months since i have), but if you could explain that to me it be very appreciated .

As of of worrying about it wipeing out existing names. The way i have it is every time someone fills out the form, they are given a id (ticket id). An in my attachments every time someone uploads a file it will have the file name the id (ticket id)and will have the count (which count starts over everytime i create a new ticket). So its next to impossible to have a duplicate file. Also in my attachment table a primary key is assigned to every attachment uploaded an a serial number. Now if someone hacks it, yeah i will probably be screwed. But i think everyone at my job would be if it did happen. But thank you for still pointing that out to me, because i miss things like that (most of the time) .

But on the description part. Yeah your right on the html it should be description1 for the name so changed that. But i am confused on the other part of this. I tried to put in what you had in your example an well i ran into the problem of it saying Error resolving parameter DESCRIPTION.ColdFusion was unable to determine the value of the parameter.i am probably just missing something simple an cant see what it is.

<cfif structKeyExists(FORM, "totalAttachments")>
<cfset currentDirectory = GetDirectoryFromPath(GetTemplatePath()) & "uploaded">
<cfparam name="FORM.totalAttachments" default="0">
<cfloop from="1" to="#form.totalAttachments#" index="counter">
<cfset currentDescription = form["description" & counter]>
<!---verify the form field exists --->
<cfif structKeyExists(FORM, "attachment"& counter)>
<!--- try and upload it ...--->
<cfset filename = cffile.ClientFileName & "_" & form.id & "_" & counter & "." & cffile.ClientFileExt>
<CFFILE ACTION="RENAME" SOURCE="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#CFFILE.ServerFile#" destination="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#filename#">
<cfquery name="attachment" datasource="CustomerSupport">
exec usp_CS_Insertattachments
</cfquery>
</cfif>
</cfloop>
</cfif>


but any suggestions on what i did wrong? i was thinking that maybe i needed to make it look like the cfif strucktkeyexists one, the one under verify the form field exits (because your example looks almost identical to it)and tried a few different ways but i didn't get any further.

But again thank you for all your help
Rach

CFSTOREDPROC:
Well, since stored procedures can do a lot more than simple queries, cfstoredproc gives you access a wider range of features. For example, you can return multiple query results from a stored procedure. You can't do that with cfquery. You can also use output variables, return status codes, etc. Another nice feature is that you never have to worry about quoting all of your string values. cfprocparam handles that automatically.

But even if you are not using the more advanced features of stored procedures one large benefit is that cfstoredproc uses what is called "bind variables". Bind variables have several advantages. They help validate data types and protection against sql injection. However, the biggest benefit here is performance. When you execute sql statements your database usually performs a certain amount of compilation or processing to determine the best query plan (or way to execute the statement). That adds overhead and extra time to the query. Bind variables essentially help the database to cache query plans. So if you are run the same sql statement 10 times, the database can reuse the query plan, and the sql statements will execute faster. Since that is exactly what you are doing (ie running the same statement in a loop) you should take advantage of this feature.

I know it is a lot to take in, but trust me there is a benefit ;-)

RENAME:
Well, it should not be that difficult to generate a unique name and prevent the overwrite. IMO if the information is critical, then it is something you should prevent rather than waiting for catastrophe to strike ;-) While most users probably would not bother, it is rather trivial to hack most html forms.

DESCRIPTION:
You just have to change the variable name in your query. Since my example calls the variable #currentDescription#, that is what you should use in the query, not #description#

This post has been edited by sansclue: 14 August 2008 - 03:35 PM

### #21 bonneylake

Reputation: 0
• Posts: 37
• Joined: 22-July 08

Posted 15 August 2008 - 06:48 AM

Hey Sansclue,

It works beautiful now, i almost want to cry because it is so beautiful. THANK YOU THANK YOU FOR ALL THE HELP !!!

But that is very interesting about the cfstoredproc, might take a few more times reading for it to fully sink in to me but from what i understand that is pretty awesome .

An with the rename i think it will be fine. It is only going to be used by 10 maybe 15 people at our company so i can't see them messing with it. An also everything gets backed up every week so i don't think all run into any problems, but thank you for the concern on that i do appreciate it

But i should of figured the description out, wasn't thinking clearly on it i guess. Lots of stress at home, having to buy a car and has to be by the end of this saturday so i will have it for monday so very stressed out, on a normal day probably wouldn't of been so blonde lol.

But i do got 2 questions i wanted to ask you about while i am still able to talk to you.

the first question is about my database. in the attachments table i have a field called date_added. But anyway in the default value for date_added i put (getdate()). But my question is when i put getdate in there besides just typing (getdate()) is there something else that i have to put in get date to get the current date and time? i have another table with this exact field and it works right (they both look identical) , but for some reason i can't get it to work right on this one. Another programmer created the tables for me (because i was out of town because of family death) an well i wasn't here to see him create the tables an this is the only part that didn't turn out right. So if you could let me know how to do that, it be very much appreciated because right now everytime i upload a file it says 11/1/1900 12:00:00 AM. I would ask other programmer but he is swamp with stuff he is trying to get done.

An my second question. Well theres another part to my form that i have to do where i have contact information. An well what i am going to do is, they type in a customer ID, it populars the dropdown box, they choose the customer number from the dropdown box an then it populates the rest of the form. But anyway i am trying to just get the stored procedures right (havent gotten any further then that) but i was wondering if you knew of an example or tutorial that does a stored procedure like this. If field exists don't insert, if field doesn't exist insert? i been trying to search for it for days but i think i am messing the wording up or something so was just wondering on that, or if you could tell me what the correct wording would be so i could search for it.

Rach

### #22 sansclue

• D.I.C Regular

Reputation: 29
• Posts: 316
• Joined: 21-November 07

Posted 15 August 2008 - 11:26 AM

bonneylake, on 15 Aug, 2008 - 06:48 AM, said:

Hey Sansclue,

It works beautiful now, i almost want to cry because it is so beautiful. THANK YOU THANK YOU FOR ALL THE HELP !!!
...

But i should of figured the description out, wasn't thinking clearly on it i guess. Lots of stress at home, having to buy a car and has to be by the end of this saturday so i will have it for monday so very stressed out, on a normal day probably wouldn't of been so blonde lol.

I am really glad to hear it is all working now. Nothing like the joy of buying a car on a deadline to make life interesting

DEFAULT:
As far as the default value, a simple getDate() should be enough. When you say default, do you mean there is a constraint on the table like below.. or are you inserting getDate() manually in your queries?

--- constraint example
CREATE TABLE Test (
id int identity,
Title varchar(50),
)

--- manual insert example
INSERT INTO Test (Title, Date_Added) VALUES ( 'ABC', getdate() )



If it is a constraint on the table, go into the database directly. Then run an sp_help on both tables. Maybe you can see what the difference is:

exec sp_help YourTableName1



PROCEDURE:
I am not sure I correctly understand this part: "If field exists don't insert, if field doesn't exist insert?" What is the procedure supposed to do?
1. Does it add new customer records and you are trying to prevent duplicates? Or ...
2. Is it a multi-purpose procedure that either "inserts a new customer record" or "updates and existing customer record" depending on whether a @customerID value exists or not?

... or something different altogether? Maybe you could post the parameters and table structure and explain what is supposed to happen.

### #23 bonneylake

Reputation: 0
• Posts: 37
• Joined: 22-July 08

Posted 15 August 2008 - 12:03 PM

Hey Sansclue,

yeah an its differently "interesting", i just wish i had more time to look at other cars.

But anyway the getdate is suppose to be in my database, not in a cfquery or anything like that. Basically if could look at my database an went to modify the table you would see getDate() as default for my date_added field. But i compared this table to the other table but i am baffled on it. They look identical in every single way. i mean this is the default value i have set for date_added is (getdate())

An the procedure. Well basically what i am trying to do (to the next part of the form). I am trying to do this.
Basically they will have a input field an start filling it out, as they fill it out in the dropdown box will populate.they will see the customer number in the dropdown box (if it exists). If it exists you choose the customer number from the drop down box and it fills in the rest of the form. if it doesn't exits then you continue to fill out the form and saves it to the database.So basically like you said to prevent duplicates. But right now i have none of the Javascript to do all that an i am just trying to get the insert information right, an right now my stored procedure is very basic because afraid to go any further based on i have no clue how to do the next part. i been searching, but i just can't figure out the right words to search for what i am doing.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_CS_Insertcontacts]
-- Add the parameters for the stored procedure here
(@fk_custNum nvarchar(50),
@cust_company nvarchar(75),
@fname nvarchar(50),
@lname nvarchar(50),
@city nvarchar(50),
@state nvarchar(5),
@zip nvarchar(15),
@email nvarchar(100),
@pri_phone nvarchar(15),
@sec_phone nvarchar(15),
@notes nvarchar(500)
)
AS
declare @pk_contactID nvarchar(55)
set @pk_contactID = (select max_num from tbl_CS_ID_HELPER where table_name='tbl_CS_contacts')
update tbl_CS_ID_helper set max_num=max_num+1 where table_name='tbl_CS_contacts'
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

insert into tbl_CS_contacts
zip,email,pri_phone,sec_phone,notes)

values
@zip,@email,@pri_phone,@sec_phone,@notes)

select @pk_contactID as contactID
END


But any suggestions on how do that would be very appreciate. I am still baffled on how all do the javascript part but hoping all find something here. But thank you for all the help
Rach

### #24 sansclue

• D.I.C Regular

Reputation: 29
• Posts: 316
• Joined: 21-November 07

Posted 15 August 2008 - 12:30 PM

bonneylake, on 15 Aug, 2008 - 12:03 PM, said:

set @pk_contactID = (select max_num from tbl_CS_ID_HELPER where table_name='tbl_CS_contacts')
update tbl_CS_ID_helper set max_num=max_num+1 where table_name='tbl_CS_contacts'

But any suggestions on how do that would be very appreciate. I am still baffled on how all do the javascript part but hoping all find something here. But thank you for all the help
Rach

There never seems to be enough time to look at cars. Not a decision that should rushed ... but it usually is ;-)

Did you run sp_help on the two tables? What does the output look like (feel free to 'xxxx' out anything confidential)?

So for the stored procedure, you only want to INSERT a record if the pk_contactID value does not already exist, right?

UPDATE What is the reason for manually generating an ID instead of just using an identity column or a constraint? Personally, I almost never use that method. With concurrent users it is error prone and can cause unintentional duplicates if not handled properly. Can you tell I have been burned by it before?

This post has been edited by sansclue: 15 August 2008 - 12:39 PM

### #25 bonneylake

Reputation: 0
• Posts: 37
• Joined: 22-July 08

Posted 15 August 2008 - 01:57 PM

Hey Sansclue,

Yeah i know a vehicle, shouldn't. But technically the vehicle is for "one day husband to be" an right now he is driving my car an i am driving my dads truck an well i can't afford to drive that truck another day (it ate my paycheck practically) so thats the reason for the rush.

but i have never done a sp_help on it, how would i go about that? i am kinda new with coldfusion i have learned a lot in the past few months but not nearly as much as i need (atleast thats how i feel). But i can tell you that right now when i upload a file this is what i get in that field 1/1/1900 12:00:00 AM.

but for the stored procedure when i insert a record if the fk_custNum value does not exist create it, if it does exist don't create it. an as far as i understand (the database was created by someone else) the id is to keep from having duplicates and its the primary key an the custnum is the foreign key. but i think this will be ok because only 10/15 people will use it an i know that the other web developers took that into consideration.

but are you sure you don't got to type anything into getdate(). i remember the other web developers did it for me an i thought he typed in something an pressed enter an then it just looked like getDate() after he pushed enter but i can't remember. Think all look up getDate an see what i can find. But let me know how to do the sp_help an all try to do it. But wont be able to do it till monday cause wont have computer.

Thank you again,
Rach

### #26 sansclue

• D.I.C Regular

Reputation: 29
• Posts: 316
• Joined: 21-November 07

Posted 17 August 2008 - 02:53 PM

I hear you about fuel costs. Not a good thing when you end up paying out of pocket for the privilege of going to work ;-)

Yes, I am sure about getDate(). Run this example. You will see that it works correctly and automatically inserts the current date and time when you insert the two records.

declare @testTable table
(
id int identity,
title varchar(100),
)

insert into @testTable (title) values ('test one')
insert into @testTable (title) values ('test two')

select * from @testTable



sp_help it is a built in MS SQL procedure you can use to return information about objects within your database. You might be able to run it from a cfquery, but it is easier to just open a query window in your database and run it there. But are you sure your insert statement is correct? If it were accidentally trying to insert a value into your date column .. that might be what is causing the 1900 date.

aexec sp_help TheTableThatIsWorking
exec sp_help TheTableThatIsNOTWorking



bonneylake, on 15 Aug, 2008 - 01:57 PM, said:

an as far as i understand (the database was created by someone else) the id is to keep from having duplicates and its the primary key an the custnum is the foreign key. but i think this will be ok because only 10/15 people will use it an i know that the other web developers took that into consideration.

set @pk_contactID = (select max_num from tbl_CS_ID_HELPER where table_name='tbl_CS_contacts')
update tbl_CS_ID_helper set max_num=max_num+1 where table_name='tbl_CS_contacts'

Truthfully there is every possibility of two users getting the same @pk_contactID - if they hit the stored procedure at the same time. Granted it is far less likely for an application with few concurrent users, but there is absolutely nothing to prevent it from happening as far as I can see.

However, I assume there is a primary key constraint on the "pk_contactID" column in the tbl_CS_contacts table. That constraint is what would prevent duplicates. Since primary key values must be unique, if two or more users did get the same @pk_contactID the first insert would succeed and the others would fail, causing a constraint violation error.

As for creating the customer record if it does not exist, does your customer table have an identity column or does it also use a helper table like "tbl_CS_ID_HELPER"?

BTW, I am really curious why they chose that method of assigning id's because it seems like it complicates things. Since your ids seem to be simple numeric values, using identity columns is usually the simpler option.

### #27 sansclue

• D.I.C Regular

Reputation: 29
• Posts: 316
• Joined: 21-November 07

Posted 17 August 2008 - 03:16 PM

sansclue, on 17 Aug, 2008 - 02:53 PM, said:

aexec sp_help TheTableThatIsWorking
exec sp_help TheTableThatIsNOTWorking



Whoops, blatant typo there. It should be "exec" not "aexec".

### #28 bonneylake

Reputation: 0
• Posts: 37
• Joined: 22-July 08

Posted 18 August 2008 - 07:12 AM

Hey Sansclue,

Well i feel like an idiot on the getDate(). I read what you said an well i found out i had put the date as hidden an then put it in my cfquery when it didn't need either. So now that works fine .

But for the pk_contactID i am using a helper table called dbo.tbl_CS_ID_helper, which is suppose to make each time someone enters a new record unique.

An yes gas prices make a huge difference.

Sorry this is kinda short been a long weekend (especially car hunting). But if you got any ideas on how i could do the stored procedure it be awesome.

But thank you for all the help you have given me (an sorry for so many questions i been asking) ,
Rach

### #29 sansclue

• D.I.C Regular

Reputation: 29
• Posts: 316
• Joined: 21-November 07

Posted 18 August 2008 - 08:42 AM

bonneylake, on 18 Aug, 2008 - 07:12 AM, said:

But for the pk_contactID i am using a helper table called dbo.tbl_CS_ID_helper, which is suppose to make each time someone enters a new record unique.

... if the fk_custNum value does not exist create it, if it does exist don't create it.

No, what I was asking about was the customer id: fk_custNum. Bear in mind, I can't see your schema, so I am just guessing about some of this.

It sounded as if you have two tables: one for customers and one for customer contacts. So as I understood it, before inserting a contact you want to verify the customer record (fk_custNum) exists. If it does not, you want to create a new customer record. Though I don't know what values should be inserted into that table. cust_company maybe ..?

After ensuring the customer record exists, the stored procedure should insert the contact information (name, address, etc...), using the customer id (fk_custNum) . Is that correct?

bonneylake, on 18 Aug, 2008 - 07:12 AM, said:

a helper table called dbo.tbl_CS_ID_helper, which is suppose to make each time someone enters a new record unique.

Yes ... but that is what identity columns are for They handle this automatically so you don't have to. Then you can easily retrieve the new value using 'select scope_identity()'.

### #30 bonneylake

Reputation: 0
• Posts: 37
• Joined: 22-July 08

Posted 18 August 2008 - 09:17 AM

Hey Sansclue,

Ok i get what your asking now. Well the fk_custNum is something that is assigned to each customer.
It won't be automatically generated like the pk_contactID. Its basically kinda like your social security
number.

An with the tables. There technically is 2 tables, but there separate (or at least i consider them separate).

Basically the first table is called ticketMaster. When you create a new ticket (when someone calls having a problem with say there computer) the basic information goes into it like the title, the status of the problem, an the contact information.

The contact information (an only contact information) will also go into a 2nd table called contact. What the contact table will do is basically save the person having to fill out the form time an instead of re-ask the customer there contact information every time they call. they will select a customer number from a drop down box an that will populate the rest of the form with the information already in the database.

So what i am trying to do is if the customer exist then it doesn't need to be added to the contact table . However, if the contact does not exist it needs to go into the contact table . Basically i am trying to prevent the same customer from being entered into the contacts table multiple times.I got the ticketMaster all together because no matter what the contact information is, it still needs to be inserted into it an by clicking submit it will insert that information into ticketMaster. However, i can not figure out how i would make the contact table not insert the same contact in to the contact table.An it should not insert the same customer based on the fk_custnum field.

i don't know if this will help, but here are the 2 tables an there fields.

but the fields that are in the contact table are pk_contactID(the ID_Helper table), fk_custnum(customer number),

an the fields in the ticketMaster are.
pk_ticketID(another ID_helper table), title,priority,status,submitted_by,last_edited_by,cost_center,followup_date,
fk_customer_number (this is where it starts to repeats the same information like contact table),customer_company,customer_Fname,customer_Lname,customer_add1,customer_city,
customer_state,customer_zip,cutomer_email,customer_pri_phone,customer_sec_phone,customer_notes



I hope this make a more sense. But let me know if you need any more information.

Thank you again for helping ,
Rach