[RESOLVED] ODBC SQL Parameters

[Strider]

Hello,

im need to write data to a 3rd party database in which i cannot rename columns etc...
many of the column names contain a "-" in them eg: prod-name

im trying to contruct a parameterised query but im having problems

"INSERT INTO Products (""prod-name"") VALUES (?)"

im using the double quotes so the database will accept the "-"

Dim l_sqlCmd = l_sqlConnection.CreateCommand()
 
l_sqlCmd.Parameters.Add("@prod-name", "My Product")
 
l_sqlCmd.Prepare()
l_sqlCmd.ExecuteNonQuery()

will the parameters accept the parameter name as in the SQL statement there are the double quotes

[sevenhalo]

Can you use "[" and "]" instead of the double quotes, or are you stuck with the query as-is?

[Strider]

im stuck with it as it. The database is a progress one so its very very very picky about what you can do.

im just wondering will it ignore the inner quotation marks

[sevenhalo]

I don't think so. I think it's still going to try and calculate the column names. You aren't able to do something like this in your code to INSERT?

"INSERT INTO Products ([prod-name], [prod-desc]) VALUES (@prodname, @proddesc)"

l_sqlCmd.Parameters.Add("@prodname", "My Product")
l_sqlCmd.Parameters.Add("@proddesc", "My Product Description")

[Strider]

no it doesnt recognise the [] it gives a syntax error.
it worked for the double quotes """" except for when trying to insert a date
hmmmm

[sevenhalo]

Hmmmm; what kind of database is the 3rd party using?

(I ran the statement in my analyzer and it worked for me)

[Strider]

its a Progress Database....

[Dnereb]

Try using single quotes around the string you will send

" ' INSERT INTO Products ("prod-name") VALUES (?)' "
or

"INSERT INTO Products ('prod-name' ) VALUES (?)"

a lot of SQL interpreters use single quotes for string that shoudn't be proccessed before interpreting.

fiddle around a bit maybe you wil get lucky.

Groeten Berend

[sevenhalo]

I did some looking around about progress. From the looks of it, it handles column names with '-' as if it was just another character. In the examples, you don't need to do anything with it:

Look under "Data Manipulation Language" --> "Inserting Rows"
http://www.progress.com/progress/pro...oa/s89/s89.pdf

When you give the date parameter to the statement, how are you doing it?

I've been trying to figure out the statement, but it looks a little too pseudo to really understand why only a date wouldn't work.