Data Insert into MS SQL Incorrect

[m95roadster]

The problem I'm experiencing is with MS SQL. I have an Insert form with (2) drop down list that submits to the same database column. The problem is when it inserts, MS SQL puts a comma after the first drop down list data that's inputted. With the comma my recordset will not work. Is there any way to input the data into MS SQL without it adding that comma? Thanks.

[andi_g69]

This sounds to me as if your front end is constructing the insert statement like this. What kind of front end are you using?
Do you have the possibility to change the front end code?

Can you also give an example of what type of data your drop downs are inserting and into which column type?

[m95roadster]

Hi! Thanks for replying. I'm using Dreamweaver MX w/Coldfusion. Initially, I had (1) textfield for input, but want to switch to drop down due to user input error.

Below is an example of what needs to be inputed in the field.

Example: Mustang-Aerodynamics
It need to be that same way because of my recordset (URL parameter). Now since some people will enter incorrectly, it messes up the search.

What I did was remove the textfield and I'm using (2) drop down list instead. Both menu/list are named the same and input into the correct column, but after 'Mustang' or whatever model it adds the comma in MS SQL. The hyphen is part of the value of the second menu/list.

[m95roadster]

I'm using Access 2000 for the front end.

[andi_g69]

For my understanding:

You have a form in access which presents the models in one drop down and the second option in another dropdown. Users select both and you want your database to queried for the combination of both?

Usually you have a button with some code attached to the onClick event. In there you construct the SQL statement:

sSQL = SELECT * FROM yourtable where yourfield = '" & dropdown1.value & dropdown2.value & "'"

as you see you should have full controll of how the two values get concatenated.

Hope that helps

[andi_g69]

I see. I do not know Coldfsuion, but I guess it is doing the concatenation of two fields similar to access? Do you have any chance to modify the code?

Only other option I see is running an update in SQL whcih gets rid of the ",":

UPDATE yourtable SET yourfield = REPLACE (yourfield, ',-', '-')

[m95roadster]

Thanks alot! I think getting rid of it in SQL will do.

Here's the code for the form:

INSERT INTO dbo.DealerAds (Name, Brand, Description, Price, DealerID) VALUES
(

Do I add the 'UPDATE yourtable SET yourfield = REPLACE (yourfield, ',-', '-')' in the code above?

Thanks andi_g69!

[m95roadster]

The UPDATE REPLACE worked! Thanks again!