UPDATE and INSERT Fun

[turonah]

Alright, I've got a table in Access that will let me update anything ... except for two fields, "Min" and "Max" ... here's the sql query as I define it in VB.Net: (ASPX pages, btw)

sqlQuery = "INSERT INTO ProcessPricing ( MaterialCode, Process, Min, Max, CostPerPound, RateMeasurement, RateAmount, MinimumCharge ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? );"

I then define the parameters, etc. When I hit ExecuteNonQuery(), however, the application comes back saying that there's a syntax error in my INSERT INTO statement. I had the same problem when using UPDATE (minus the materialCode and Process fields), so I figured I could work around it by creating a new record (wrong!). I know it's the "Min" and "Max" fields through trial and error ... I basically tried updating each field separately until I hit the error again.

The problem is, these two fields are the Double data type in the database, and I can't tell what's causing the problem ... especially since RateMeasurement is in the same table, of the same data type, the whole bit!

Any ideas of what I might be overlooking?

 

Thanks in advance,

Turonah

[Joe Mamma]

Min and Max are keywords. . . not a good choice for field names. . .

min and max what????

 

to get around this put min and max in brackets. . .

[Min], [Max]

 

keep in mind the [FieldName] format is MS specific and might not work if your client chooses to go to another DB platform in the future.

[techmanbd]

the only thing I can think of is that one of your datatypes is text.

 

so just take materialcode for instance. if that was a text then look at the value I added.

 

so all text you put single quotes around it and all numbers you don't.

 

 

sqlQuery = "INSERT INTO ProcessPricing ( MaterialCode, Process, Min, Max, CostPerPound, RateMeasurement, RateAmount, MinimumCharge ) VALUES ( 'hello', ?, ?, ?, ?, ?, ?, ? );"

[turonah]

Joe Mamma, you hit the nail right on the head ... I had no idea that Min and Max were reserved SQL keywords (oops), especially since the names worked fine with ASP. Guess I'll have to change those field names :P

 

Thanks very much!