ADODB & select where

[cbjax]

The commented out select statement below works fine, but I need to be able to use a variable and I've tried several versions of the uncommented out line and can't get it to work.

Any help would be greatly appreciated.

And yes I've been reading post for a day & a half concerning adodb, odbc, select, etc.

BTW: I'm fairly new to AutoIT, been a basic programmer for a long time though.

$objConn = ObjCreate("ADODB.Connection")

$objConn.Open ("Dsn=xxxxxxxxxx") ; not relevant to issue I don't think

#EndRegion

This works fine

;$rsDetails = $objConn.Execute("SELECT * FROM oddetailchg where OdOrdNo = 303451")

This doesn't work

$ord_no = 303451

$rsDetails = $objConn.Execute("SELECT * FROM oddetailchg where OdOrdNo = $ord_no ")

[DaRam]

$rsDetails = $objConn.Execute("SELECT * FROM oddetailchg where OdOrdNo = " & $ord_no)

[cbjax]

$rsDetails = $objConn.Execute("SELECT * FROM oddetailchg where OdOrdNo = " & $ord_no)

Dynamite, worked like a charm.

Thanks

Edited June 17, 2008 by cbjax

[cbjax]

Dynamite, worked like a charm.

Thanks

Can i press my luck?

I now need to delete records from the table and I've tried various versions of below with no success.

I get this message "The requested action with this object has failed" for the delete line.

I do not know if it matters, but ht_order_numb is a 32 character string

$ord_no = 123455 ! I've tried with and with out the single quotes.

Func _delHeader($ord_no)

$objConn.Execute("delete from h1lordr1 where HT_ORDER_NUMB = " &$ord_no )

EndFunc ;==>_delHeader

Thanks in advance.

[DaRam]

Try

$objConn.Execute("delete * from h1lordr1 where HT_ORDER_NUMB = " & $ord_no )

Can i press my luck?

I now need to delete records from the table and I've tried various versions of below with no success.

I get this message "The requested action with this object has failed" for the delete line.

I do not know if it matters, but ht_order_numb is a 32 character string

$ord_no = 123455 ! I've tried with and with out the single quotes.

Func _delHeader($ord_no)

$objConn.Execute("delete from h1lordr1 where HT_ORDER_NUMB = " &$ord_no )

EndFunc ;==>_delHeader

Thanks in advance.

[PsaltyDS]

Can i press my luck?

I now need to delete records from the table and I've tried various versions of below with no success.

I get this message "The requested action with this object has failed" for the delete line.

I do not know if it matters, but ht_order_numb is a 32 character string

$ord_no = 123455 ! I've tried with and with out the single quotes.

Func _delHeader($ord_no)

$objConn.Execute("delete from h1lordr1 where HT_ORDER_NUMB = " &$ord_no )

EndFunc ;==>_delHeader

Thanks in advance.

Perhaps:

Func _delHeader($ord_no)
    $objConn.Execute('delete from h1lordr1 where HT_ORDER_NUMB = "' & $ord_no & '"')
EndFunc  ;==>_delHeader

Note that trailing set of quotes is two single quotes enclosing a double quote.

[PsaltyDS]

Try

$objConn.Execute("delete * from h1lordr1 where HT_ORDER_NUMB = " & $ord_no )

I don't think you need "*" to denote fields when you want to delete the whole record.

Edit: Added reference link.

Edited June 17, 2008 by PsaltyDS

[DaRam]

True, but some file databases require it. I have no idea what db the OP is running against.

I don't think you need "*" to denote fields when you want to delete the whole record.

Edit: Added reference link.

[cbjax]

True, but some file databases require it. I have no idea what db the OP is running against.

Neither of the suggestions worked but I do appreciate the attempt anyway.

It is not a well known product, so I may be chasing my .....

Thanks

[cbjax]

Neither of the suggestions worked but I do appreciate the attempt anyway.

It is not a well known product, so I may be chasing my .....

Thanks

The uncommented line worked and the other 2 that are commented out did not.

Does anyone see something that might help delete with a variable?

Obviously for this to work I need to be able to delete by a variable.

$objConn.Execute("delete from h1lordr1 where HT_ORDER_NUMB = '468390 ' " )

;

;$objConn.Execute('select * from h1lordr1 where HT_ORDER_NUMB = "' & $ord_no & '"')

;

;$objConn.Execute("select * from h1lordr1 where HT_ORDER_NUMB = " & $ord_no )

Thanks again for listening!!!!

[PsaltyDS]

The uncommented line worked and the other 2 that are commented out did not.

Does anyone see something that might help delete with a variable?

Obviously for this to work I need to be able to delete by a variable.

$objConn.Execute("delete from h1lordr1 where HT_ORDER_NUMB = '468390 ' " )

;

;$objConn.Execute('select * from h1lordr1 where HT_ORDER_NUMB = "' & $ord_no & '"')

;

;$objConn.Execute("select * from h1lordr1 where HT_ORDER_NUMB = " & $ord_no )

Thanks again for listening!!!!

You have mixed 'select' and 'delete', but I'll assume we are still just talking about delete...

My bet is on:

$objConn.Execute("delete from h1lordr1 where HT_ORDER_NUMB = '" & $ord_no & "'")

Now there are literal single quotes enclosed in double quotes.

[cbjax]

You have mixed 'select' and 'delete', but I'll assume we are still just talking about delete...

My bet is on:

$objConn.Execute("delete from h1lordr1 where HT_ORDER_NUMB = '" & $ord_no & "'")

Now there are literal single quotes enclosed in double quotes.

You are correct I did mix it up, I was testing with select to see what would work and didn't make them all the same.

However you fixed it, your last suggestion worked.

Thanks a bunch.