ODBC Error while trying to update a table

[paulfla]

I sure hope I can explain this well:

I am displaying a form on an ASP page that allows a user to enter an invoice number, Date and Job Jacket number. The entered info is used in a Access query that has two tables and a couple of sub queries to come up with the totals and updates a FoxPro table. Works fine in Access

I am getting the following Error:
=============
Microsoft JET Database Engine (0x80004005)
ODBC--connection to 'GalC01' failed.
=============
Here is my code (bolded line is what throws up the error)

	dim oConn, x, rs
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Mode = adModeReadWrite
oConn.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\inetpub\[URL unfurl="true"]wwwroot\xyz\ABC_V1.mdb")[/URL]

s_invoice_nbr = Trim(Request.form("f_invoice_nbr"))
s_date = Trim(Request.form("f_date"))
s_jobjacket_nbr = Trim(Request.form("f_jobjacket_nbr"))

[b][COLOR=blue]Set rs = oConn.Execute ("exec sp_upd_gadocard_oi " & s_invoice_nbr & ", '" & s_date & "', " & s_jobjacket_nbr & "")[/color][/b]

oConn.Close
Set oConn = Nothing

I am connecting to an Access DB and I am trying to update a FoxPro DB that is linked via ODBC DSN GalC01. This works when I update a regular test table in the Access db, but when I try to update the production table which is a ODBC DSN linked table, I get the error noted above.

Thank you in advance for your time.

 

[mwolf00]

IN access, dates need "#" signs rather than single quotes - just a possibile cause.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[ahmun]

I'm not too familiar with how FoxPro works, but if you have set up the ADODB connection properly, you may try writing a properly formed Jet SQL Query and then executing that on the connection opject:

(replace tblMyTable with the table name you are trying to update)

[COLOR=gray]
dim oConn, x, rs
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Mode = adModeReadWrite
oConn.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\inetpub\[URL unfurl="true"]wwwroot\xyz\ABC_V1.mdb")[/URL]

s_invoice_nbr = Trim(Request.form("f_invoice_nbr"))
s_date = Trim(Request.form("f_date"))
s_jobjacket_nbr = Trim(Request.form("f_jobjacket_nbr"))

Set rs = oConn.Execute ("exec sp_upd_gadocard_oi " & s_invoice_nbr & ", '" & s_date & "', " & s_jobjacket_nbr & "")[/color]
[COLOR=blue]
sSQL_UPD = "UPDATE tblMyTable "
sSQL_UPD = sSQL_UPD & "SET date = #" & s_date & " "
sSQL_UPD = sSQL_UPD &     "jobjacket_nbr = " & s_jobjacket_nbr & " "
sSQL_UPD = sSQL_UPD & "WHERE invoice_nbr = " & s_invoice_nbr & ";"
[b]oConn.execute sSQL_UPD[/b]
[/color][COLOR=gray]
oConn.Close
Set oConn = Nothing [/color]

That is assuming that you are updating properties of an invoice_nbr in your FoxPro table, and assuming that the field names in your FoxPro table are "invoice_nbr", "date", and "jobjacket_nbr"

Earnie Eng

 

[Genimuse]

I suspect it's your path to your database. Try replacing this:

oConn.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\inetpub\[URL unfurl="true"]wwwroot\xyz\ABC_V1.mdb")[/URL]

with this:

oConn.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("xyz/ABC_V1.mdb"))

 

[paulfla]

Thanks so much for your replys.

I don't think it's a problem with the path

oConn.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\inetpub\wwwroot\xyz\ABC_V1.mdb")

When I change the query sp_upd_gadocard_oi to update a regular access table, it works fine. When I change query sp_upd_gadocard_oi back to updating the table that is linked to FoxPro table via ODBC DSN GalC01 I get the above mentioned error.

I thought about building the SQL in the ASP page, but the
sp_upd_gadocard_oi query is a little involved and I'm not that familiar with SQL.

Once again, thanks for your help.

 

[ahmun]

I'm not too familiar with executing queries defined in access since I normally write everthing in SQL... but what you can do is edit the query in Access, then under the toolbar item tnat allows you to view the resluts of the query, you can click for a pull-down menu and select an SQL view. If your Access query doesn't contain any parameters, and references to macros, you can pretty much copy/paste that query into a string in your ASP page and use the query execution method I mentioned above.

Earnie Eng

 

[paulfla]

Here is the sp_upd_gadocard_oi query in Access. As you will notice, it is referencing two othe queries, sp_OrderTotal and sp_OICardQty. I'm guessing I won't be able to just paste this into my ASP page.???

INSERT INTO gadocard1 ( id, invn, rcdat, div, loc, tsell, stax, totchg, shipq, fname, formno, frt, sel, unitm )
SELECT Orders.OrderID, [@InvNumber] AS Invoice, [Enter InvoiceDate as mm/dd/yy] AS InvoiceDate, Mid(Orders!CostCode,3,2) AS Division, Mid(Orders!CostCode,6,4) AS Location, sp_OrderTotal.SumOfOrderItemPrice, sp_OrderTotal!SumOfOrderItemPrice*0.07 AS Tax, sp_OrderTotal!SumOfOrderItemPrice*1.07 AS PostTax, sp_OICardQty.SumOfOrderItemQty AS CardQty, "B-card  " & [OrderName] AS Expr1, OrderItems.OrderItemCode, 0 AS Freight, 0 AS sel, "EA" AS UnitM
FROM ((Orders LEFT JOIN OrderItems ON Orders.OrderID = OrderItems.OrderRef) LEFT JOIN sp_OrderTotal ON Orders.OrderID = sp_OrderTotal.OrderRef) LEFT JOIN sp_OICardQty ON Orders.OrderID = sp_OICardQty.OrderRef
WHERE (((OrderItems.OrderItemCode)="A111") AND ((Orders.InvNumber)=[@JobJacket]))
ORDER BY Orders.OrderID;

 

[ahmun]

are the @objects the way you named fields in the database? or does the @ character mean something?

Earnie Eng

 

[paulfla]

@ character means that information is being requested for the query.

That is why I am passing:
- s_invoice_nbr
- s_date
- s_jobjacket_nbr

in the statement
Set rs = oConn.Execute ("exec sp_upd_gadocard_oi " & s_invoice_nbr & ", '" & s_date & "', " & s_jobjacket_nbr & "")

 

[ahmun]

so it looks to me like you are running an INSERT query, where @InvNumber and @JobJacket would be values you gather from the HTML form.

Unless somebody else has a good idea, my thought is to just by brute force write out a proper JET SQL query, and nest all the sub-queries into one... This the only method I know of for handling database in ASP.

Yeah... Paula, you can look into more info on how to write a query in SQL... or post your each of your queries and I wouldn't mind taking a crack at combining them for ya, although from looking at it, it may be a bigger deal that it looks, since you have some Access-specific syntax in the query that might not work when you try to run it in ASP.

Here are some good resources:[ul]
[li]

http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html

[/li]
[li]Nesting Queries:

http://www.thunderstone.com/site/texisman/node86.html

from google search: nesting queries[/li]
[li]faq701-3510 on Access Queries and JET SQL FAQ
from google search: converting access queries to asp[/li][/ul]

Earnie Eng

 

[Genimuse]

To pass parameters to a stored procedure, you need to use the ado add parameter methods. Here's a good explanation:

http://www.developerfusion.com/show/2270/

Mind you, I've used this many times with SQL Server, but haven't tried it with Access. Still, it works great with SQL Server and should work the same way with Access.

It's a bit of a hassle to get accustomed to initially, but it becomes very straightforward after a bit.