Can't update a table 1

[joecdn]

OK, hair pulling here ...

I'm just trying to insert a record into a database and when I do it, I get so many different errors. The main one was the problem with the locking. Here's the code I have.

Set adoCon = Server.CreateObject("ADODB.Connection&quot
adoCon.ConnectionString = "Driver=Microsoft Visual FoxPro Driver;"_
&"SourceType=DBF;SourceDB=C:\Inetpub\

http://wwwroot\Nu-Leaf\nl_staff;Exclusive=NO;Deleted=YES;"_

& "NULL=No;BackgroundFetch=No;"

adoCon.Open

SQLQuery="Select * from C:\Inetpub\

http://wwwroot\Nu-Leaf\nl_staff"

Set RS=Server.CreateObject("ADODB.Recordset&quot
RS.Open SQLQuery, , adOpenKeySet, adLockOptimistic
RS.AddNew
RS.Fields("cnumber&quot = request.form("txtrepnum&quot
RS.Fields("sname&quot = request.form("txtlast&quot
RS.Fields("sfname&quot = request.form("txtfirst&quot
RS.Fields("webpass&quot = pv
RS.Update

I'm sure this is all mixed up because I've changed bits and pieces, so I've confused myself even more. There's also some other things I don't understand like what is adOpenKeySet and adLockOptimistic.

Anyway, hopefully this makes sense, any insight would be great.

Thanks in advance.

 

[LV]

You can do it without retrieving records(assuming that the cnumber field is integer data type in the database and the rest are strings):

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.ConnectionString = "Driver=Microsoft Visual FoxPro Driver;"_
&"SourceType=DBF;SourceDB=C:\Inetpub\[URL unfurl="true"]wwwroot\Nu-Leaf\nl_staff;Exclusive=NO;Deleted=YES;"_[/URL]
& "NULL=No;BackgroundFetch=No;"

adoCon.Open 

sql = "INSERT INTO tablename(cnumber,sname,sfname,webpass) VALUES(" & request.form("txtrepnum") & ",'" & request.form("txtlast") & "','" & request.form("txtfirst") & "','" & pv & "')"

adoCon.Execute sql
adoCon.Close
set adoCon = nothing

 

[onpnt]

lots a questions!

lets run through it and maybe clear a few things up.
first thing to check is if the database is being opened

so, do you know if this is successful or not?


Set adoCon = Server.CreateObject("ADODB.Connection&quot
adoCon.ConnectionString = "Driver=Microsoft Visual FoxPro Driver;"_
&"SourceType=DBF;SourceDB=C:\Inetpub\

http://wwwroot\Nu-Leaf\nl_staff;Exclusive=NO;Deleted=YES;"_

& "NULL=No;BackgroundFetch=No;"

adoCon.Open


my personal views included on how you should format this build is something like this.
it may makes things a bit easier for you 1) to debug 2) to read


Dim ConString
Dim adoCon
Dim DataBase

DataBase = "C:\Inetpub\

http://wwwroot\Nu-Leaf\nl_staff"

ConString = "Driver=Microsoft Visual FoxPro Driver;"
ConString = ConString & "SourceType=DBF;SourceDB=" & DataBase
ConString = ConString & ";Exclusive=NO;Deleted=YES;NULL=No;BackgroundFetch=No;"

Set adoCon = Server.CreateObject("ADODB.Connection&quot

adoCon.Open(ConString)


first, you can debug pieces of this entity.
1) test the database and set paths to it. "write it to the screen"
2) quick changes for maintenance

so at this point a debug statement would be

Response.write &quot;The database has been opened<br>&quot;


The is On Error Resume Next. but at this point a response.write is
something quick and nothing to learn for you along with needing to debug
the error handling that is in all trying to debug your code.

So the next statement contains you SQL. I'm (no offense) am going to kill
the entire portion of code after this point. The way you're doing it
is resource ungry and just hard to debug.

so the easy and more efficient way would be
1) declare the var's you need
you don't need a recordset for UPDATE's or INSERT's
it's a waist of the server and time no matter what the reasoning

Can you explain this
C:\Inetpub\

http://wwwroot\Nu-Leaf\nl_staff

is nl_staff te table name. SQL doesn't get paths. just the table
the database does the finding in the access paths.


so, is nl_staff is the table per say

Dim SQLQuery

SQLQuery = &quot;INSERT INTO nl_staff &quot;
'that initialted our statement and now we concate the column values in there
' first set the columns

SQLQuery = SQLQuery & &quot;(cnumber, sname, sfname, webpass) &quot;
'now the values

SQLQuery = SQLQuery & &quot;VALUES (&quot; & request.form(&quot;txtrepnum&quot & &quot;,&quot;
SQLQuery = SQLQuery & &quot;'&quot; & request.form(&quot;txtlast&quot & &quot;',&quot;
SQLQuery = SQLQuery & &quot;'&quot; & request.form(&quot;txtfirst&quot & &quot;',&quot;
SQLQuery = SQLQuery & &quot;'&quot; & request.form(&quot;pv&quot & &quot;')&quot;


this should output this statement
INSERT INTO nl_staff (cnumber, sname, sfname, webpass) VALUES (txtrepnum,'txtlast','txtfirst','pv')

now the easy part. all you need to do is Execute this statement
so literally all you need is


adoCon.Execute(SQLQuery)


here's a good tutorial as well as adding the RS with the same methods
for SELECT statements

http://www.domaindlx.com/asp_db.asp

for the Cursor and Lock type questions.
This is how the database is opened and how the cursor is set on the records
It will give you the ability to move forward and back in the tables.
It gives you the ability to get values like recordcounts of the tables
and allows for locking for no mess of two instances trying to update
insert etc. at the same time.

Take a look at these tables to venture into which is the best for your situation

http://www.w3schools.com/ado/prop_rs_cursortype.asp

http://www.w3schools.com/ado/prop_rs_locktype.asp

_____________________________________________________________________

_{Hakuna matata!!}

 

[onpnt]

see what happens when you babble

also check

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForVisualFoxPro

for the proper syntax to your connection string

LV

has to of the most important factors I didn't mention in the way you encaps your form val's and the closeing of the connections.

_____________________________________________________________________

_{Hakuna matata!!}

 

[vikramonline]

Joecdn,
Try this

rs.CursorType = 1
rs.CursorLocation = 2
rs.LockType = 3

This will help I guess

Vicky

 

[joecdn]

LV - That's actually the original way I did the coding and it still didn't work.

onpont - The nl_staff is the table database. FoxPro doesn't have tables within databases like Access. That's why I have to put the whole path, it doesn't understand that it's located there. I persoanlly don't like using Foxpro, but that's what I have to work with unfortunately. So, the insert command doesn't seem to work. I'll check out the tutorials.

Vicky - I tried your method, is this what you mean?

SQLQuery=&quot;Select * from C:\Inetpub\

http://wwwroot\Nu-Leaf\nl_staff&quot;

RS.Open SQLQuery
RS.CursorType=1
RS.CursorLocation=2
RS.LockType=3
RS.AddNew
RS.Fields(&quot;cnumber&quot = request.form(&quot;txtrepnum&quot
RS.Fields(&quot;sname&quot = request.form(&quot;txtlast&quot
RS.Fields(&quot;sfname&quot = request.form(&quot;txtfirst&quot
RS.Fields(&quot;webpass&quot = pv
RS.Update

Because I still get an error.

Thanks to everyone who's helped, but I'm still at a loss.

 

[onpnt]

joecdn, I've used FoxPro as my data source in the past with ASP and never had to use full paths in this way.

maybe at that point you may want to ask the question int he Foxpro Forum forum182

_____________________________________________________________________

_{Hakuna matata!!}

 

[shaddow]

I hope this clear things up.

<%
Set con = Server.CreateObject(&quot;ADODB.Connection&quot;)
adoCon.ConnectionString = &quot;Driver=Microsoft Visual FoxPro Driver;&quot;_
&&quot;SourceType=DBF;SourceDB=C:\Inetpub\[URL unfurl="true"]wwwroot\Nu-Leaf\nl_staff;Exclusive=NO;Deleted=YES;&quot;_[/URL]
& &quot;NULL=No;BackgroundFetch=No;&quot;

con.Open 

set rs=Server.CreateObject(&quot;ADODB.Recordset&quot;)
sql=&quot;select top 1 * from yourVFPtable&quot;
rs.Open sql,con,3,3
rs.AddNew
  rs(&quot;cnumber&quot;) = request.form(&quot;txtrepnum&quot;)
  rs(&quot;sname&quot;) = request.form(&quot;txtlast&quot;)
  rs(&quot;sfname&quot;) = request.form(&quot;txtfirst&quot;)
  rs(&quot;webpass&quot;) = pv
rs.Update
rs.Close
con.Close
%>

________
George, M

 

[joecdn]

Hey shaddow,

I tried your suggestion, but it doesn't like this line of code:

rs.Open SQLQuery,adoCon,3,3

Hey onpnt,

I actually tried changing it so, I don't include the path and it says it can't find the database. If there's a way to do it without using the path, I'd love to know. I did send a forum in the FoxPro side and haven't had a response yet.

I am now bald ...

 

[joecdn]

onpnt - OK, I figured out what you were asking regarding the table and the path. I got that part figured out where I can run the SQL command without using the path.

Anyway, I changed the SQL so that it updates a field. I got rid of the Record Set and am trying to work directly to the table.

Anyway, the error I'm still getting is &quot;Cannot Update the Cursor&quot;

It happens on the Execute command. Here's the coding I have:
Dim ConString
Dim adoCon
Dim DataBase

DataBase = &quot;C:\Inetpub\

http://wwwroot\Nu-Leaf&quot;

ConString = &quot;Driver=Microsoft Visual FoxPro Driver;&quot;
ConString = ConString & &quot;SourceType=DBF;SourceDB=&quot; & DataBase
ConString = ConString & &quot;;Exclusive=NO;Deleted=YES;NULL=No;BackgroundFetch=No;&quot;

Set adoCon = Server.CreateObject(&quot;ADODB.Connection&quot
adoCon.Open(ConString)

SQLQuery = &quot;UPDATE nl_staff SET cnumber = '&quot; & request.form(&quot;txtrepnum&quot & &quot;'&quot;
response.write(SQLQuery) ' this is for debugging
adoCon.Execute(SQLQuery) ' This is where it craps out.

adoCon.Close
set adoCon = nothing

Anyway, if you could figure that out, since you've worked with FoxPro before. Please help!!!

Thanks in advance.

 

[onpnt]

try opening as
adoCon.Open ConString,3,3

my bad. failed to re-enter in your CursorType and Lock when I wrote that bit up there.

3 = adOpenStatic
3 = adLockOptimistic


_____________________________________________________________________

_{Hakuna matata!!}

 

[joecdn]

OK, I'm getting closer, I can feel it. I downloaded a .dll file for VFP and changed the coding a little bit. Here's what I put:
DataBase = &quot;C:\Inetpub\

http://wwwroot\Nu-Leaf&quot;

ConString = &quot;Provider=vfpoledb;&quot;
ConString = ConString & &quot;Data Source=&quot; & DataBase
ConString = ConString & &quot;;Mode=ReadWrite;Collating Sequence=MACHINE;&quot;

Set adoCon = Server.CreateObject(&quot;ADODB.Connection&quot
adoCon.Open ConString,3,3

Now this time the error I get is:
Cannot update the cursor nl_staff, since it is read only.

At least it recognized the name of the table.

 

[onpnt]

I recall there being setting on the .VFP for setting the cursor to update mode along with is defaulting to read only. have you checked this yet. I'll look through my old references also and post if I find something

_____________________________________________________________________

_{Hakuna matata!!}

 

[joecdn]

OK, sorry a little confused, where do you mean?

 

[onpnt]

take a gander here faq184-2800

_____________________________________________________________________

_{Hakuna matata!!}

 

[joecdn]

Thanks for all your help, I'll see what I can do with that code.

Although, I guess my questions is, why does it think it's a cursor when I'm updating the table? Wierd.

 

[joecdn]

So I finally figured out this problem. The reason why it was coming up with the cannot update error was because the security wasn't defaulted to Read/Write. I had to physically go into the properties of the table in Explorer, select the Security tab and make sure the Read/Write was selected. Now it works fine.

Thanks for your help.