VFP 6.0 database to Sql Server database 3

[kate]

Can anyone help me with this problem? I have to create a program that transfer a .dbf to a sql database on a daily basis. I have check with all the treads available but I'm still not getting any results. I'm getting the connection to the sql server but its not writing to the sql database.

I'm getting an error on the sqlexec() saying:

"Connectivity Error [Microsoft[ODBC SQL Server Driver][SQL Server] The name 'strlname' is not permitted in this context. Only Constants, expressions, or variables allowed here. Column names are not permitted."

strlname is a variable.

my code is as follows:

select loctable
nHandle=SQLCONNECT('sqlconnection')
IF nHandle > 0
else
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ENDIF
nhandle = sqlconnect('sqlconnection')
? SQLSETPROP(nHandle, 'Transactions', 2) && Manual transactions
scan
datestring = dtos(c.bdate)
strlname = c.lname
strfname = c.fname
strmname = c.mname
strssn = c.ssn
? SQLEXEC(nHandle, "INSERT INTO sqlname VALUES ;(strlname, strfname, strmname, strssn, datestring)")
IF SQLEXEC(nhandle,"INSERT INTO sqlname VALUES ;(strlname, strfname, strmname, strssn, datestring)") < 0
AERROR(laError)
DISPLAY MEMORY LIKE laError
ENDIF
? SQLCOMMIT(nHandle) && Commit the changes
endscan

Thank you for any help you can give me.

Kate

 

[rgbean]

Kate,
A couple of comments:
1. When you repeat the line

nHandle=SQLCONNECT('sqlconnection')

After the ENDIF, you will get a different connection than the first one, and if it fails, you'll get an error when you try to use it.

2. You are mixing systax in the SQLEXEC() call. The semicolon in the middle will be picked up by VFP as a line continuation and the rest of the line will be ignored. Then VFP will add the next line on to the statement it sends to SQLEXEC(). So it would look something like:

? SQLEXEC(nHandle, "INSERT INTO sqlname VALUES ;
     IF SQLEXEC(nhandle,"INSERT INTO sqlname VALUES ;
        AERROR(laError)

which is missing a few close quotes (at the least!).

3. If the systax was right by double issuing the SQLEXEC(), you'd get two inserts.

Try something like this:

select loctable
nHandle=SQLCONNECT('sqlconnection')
IF nHandle > 0
else
   = MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
   [red]RETURN[/red]
ENDIF
[red]* -- omit [/red] nhandle = sqlconnect('sqlconnection')
   ? SQLSETPROP(nHandle, 'Transactions', 2)  && Manual transactions
   scan 
      datestring = dtos(c.bdate)
      strlname = c.lname
      strfname = c.fname
      strmname = c.mname
      strssn = c.ssn

[red]      retval = SQLEXEC(nHandle, "INSERT INTO sqlname VALUES " ;
        + "(strlname, strfname, strmname, strssn, datestring)")
      ? retval
      IF retval < 0[/red]
        AERROR(laError)
        DISPLAY MEMORY LIKE laError
      [red]  RETURN[/red]
      ENDIF
      ? SQLCOMMIT(nHandle)  && Commit the changes
   endscan

Rick

 

[Mike Lewis]

Kate,

I agree with everything Rick said, and would add that the particular error you are seeing is because strlname is a VFP variable, that is, it is visible in your VFP code, but it doesn't mean anything to SQL Server. The same applies to strfname and the others.

You'll need to apply the various suggestions that Rick made, and then change your SQLEXEC() call, like so:

SQLEXEC(nHandle, "INSERT INTO sqlname VALUES (" ;
+ strlname + ", " + strfname  + ", " + strmname ;
+ ", " + strssn  + ", " + datestring + ")" )

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[rgbean]

Mike,
Good catch. I was so much into the structural problems, I didn't even notice the obvious problem of the values.

Rick

 

[kate]

Thank you guys for your help. But I'm still getting the same error message except now instead of the variable name it has the actual data. I didn't realize that I had SQLCONNECT. so here is the updated code.

nHandle=SQLCONNECT('gunpermit')

IF nHandle < 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ENDIF
select loctable
? SQLSETPROP(nHandle, 'Transactions', 2) && Manual transactions
scan
datestring = dtos(c.bdate)
strlname = c.lname
strfname = c.fname
strmname = c.mname
strssn = c.ssn

retval = SQLEXEC(nHandle, "INSERT INTO sqlname VALUES;
(" + strlname + ", " + strfname + ", " + strmname + ", " + strssn + ", " + datestring + ")" )
? retval
IF retval < 0
AERROR(laError)
DISPLAY MEMORY LIKE laError
return
ENDIF
? SQLCOMMIT(nHandle) && Commit the changes
endscan
Now this is the error I get now.

Connectivity Error [Microsoft[ODBC SQL Server Driver][SQL Server] The name 'SMITH' is not permitted in this context. Only Constants, expressions, or variables allowed here. Column names are not permitted."

Sorry I'm a novice when it comes to this.

 

[mkrausnick]

You need to include single quotes around character values, like so:

([COLOR=red][b]'[/b][/color]" + strlname + "[COLOR=red][b]'[/b][/color],[COLOR=red][b]'[/b][/color]" + strfname  + "[COLOR=red][b]'[/b][/color],'" ...

Mike Krausnick
Dublin, California

 

[Mike Lewis]

Kate,

Yes ... Mike Krausnick has given you the right answer. My fault ... I forgot about those single quotes.

By the way, a good way of debugging this type of problem is to try running the SQL command in SQL Server's Query Analyzer window. That way, you will know if it is a VFP issue or something wrong with the actual SQL, like an invalid column name.

Ideally, construct your VFP code in such a way that the second parameter to SQLEXEC() is a simple variable containing the entire SELECT. Then, insert a temporary command to copy that variable to the clipboard, like so:

lcCommand = "INSERT INTO sqlname VALUES" + ;
"('" + strlname + "', '" + strfname  + "', '" + ;
strmname + "', '" + strssn  + "', '" + datestring + "')"

_CLIPTEXT = lcCommand

RetVal = SQLEXEC(nHandle, lcCommand)

That way, you can just paste the clipboard into the QA window. Often, you will see a syntax error just looking at the code in QA.

Mike






__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kate]

Thank you all very much it works. You guys are great.

thank you, thank you, thank you!!!!!!!!!

 

[mkrausnick]

Glad you got it working. Now that you went through all that pain (worthwhile for learning purposes) another way of connecting VFP variables to SQL Server is to use the parameter symbol "?". Your sqlexec would then look like:

IF SQLEXEC(nhandle,"INSERT INTO sqlname VALUES (?strlname, ?strfname, ?strmname, ?strssn, ?datestring)") < 0

This format is a lot easier to read.

Mike Krausnick
Dublin, California

 

[kate]

One more Question on this subject Is there a way to clear out the sql database before the program starts. this table has to be updated every night. Thank you for your help.

 

[Mike Lewis]

Kate,

What do you mean by "clear out"? Do you want to delete the entire table, or just the newly added records, or what?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kate]

Basically to zap the table and start fresh.

 

[Mike Lewis]

Kate,

OK, you have two options:

- DELETE MyTable

- TRUNCATE MyTable

The advantage of DELETE is that the deletions will be logged, which means they are recoverable in the event of a system failure.

The advantage of TRUNCATE is that it is very much faster.

Either way you will end up with an empty table. Note that DELETE will also cause any triggers to fire, but my guess is that isn't an issue in this case.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[mkrausnick]

I think the ANSI SQL standard syntax is actually:

delete [b]from[/b] mytable

but in SQL Server the FROM is optional.

Mike Krausnick
Dublin, California

 

[Mike Lewis]

Mike,

You're right on both counts. Actually, I should have included FROM, partly because it is more standard and partly because it makes the meaning of the command more obvious.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kate]

Hi Guys: Thanks for all your help with this project. I have another question or problem. I'm trying to run it at night with schedule task, but it say connection not made, but it if run from the command window in foxpro I have no problem. what am I missing so I can run this job at night?

thank you

kate

 

[rgbean]

Kate,
Does the user running the app have restricted access to the network at night? Have you tried running it at night in the command window?

Rick

 

[kate]

I'm running it from the server it has admin rights. no I haven't tried to run it from a command window. I just set up a schedule task from my pc and i have no problem with it, it must be a security issue with the server.

thank you

kate

 

[Mike Lewis]

Kate,

Not sure about the answer to that one.

But, for future reference, could I suggest that you start a new thread for a new question. That way, there's more chance of other people finding it when searching or browsing, and more chance of it being helpful to others.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)