Problem with XBasic "cn.open" with sqlServer

[psamuels56]

Hi-

I hope someone can help...

I'm probably missing something VERY basic, but I've looked at this for so long, I just can't spot the problem.

I'm trying to do a simple "Update" sql command with a UX button. In XBasic functions, I wrote the code below.

Using my Alpha sql Connection, "payroll", I want to update all of the "Company" fields (where St_No (Store #) = 43) to "XXX". Then, I have an "alert" to tell the user that "the table has been updated."

I've done this dozens of times before, without difficulty. HOWEVER, this is the first time I'm updating a sql Server 2014 table (my previous times using this type of function for UPDATE and INSERT used "MS Access" tables... don't get mad at me, all of my new applications use "sql Server"). From what I can tell, it doesn't appear that the connection ("payroll") is opening (I AM getting the "alert" message instantly... even though the program doesn't run). There "may" also be a problem with how I'm naming the table: in sql Server, the table is called "dbo.DuplicateFilter", but in Alpha, it's just "DuplicateFilter". Of course, I've tried the code both ways (and many variations of the "UPDATE" code). I NEVER get any sql error messages... just the "alert" message at the end of the code.

I'd appreciate any help or suggestions you have:

function SetTables as c (e as p)

Dim cn as sql::connection
Dim sqlCommand as c
Dim Company as c

Company1 = "XXX"
flag = cn.open("::Name::payroll")
sqlCommand = "UPDATE DuplicateFilter SET Company = Company1 WHERE St_No = 43;"
flag = cn.execute(sqlCommand)

SetTables = "alert('Your tables have been successfully Updated!');"

end function

Thanks,
Phil

[Steve Workings]

Re: Problem with XBasic "cn.open" with sqlServer

See how an argument is used here. Also, close your connection. And there's more to be developed as you learn -- for instance if your execute Flag is false, you have no error handling. But this will help move you along.

function SetTables as c (e as p)

Dim cn as sql::connection
[COLOR="#0000FF"]Dim args as sql::arguments[/COLOR]
Dim sqlCommand as c

[COLOR="#0000FF"]args.set("Company1", "XXX")[/COLOR]
flag = cn.open("::Name::payroll")
sqlCommand = "UPDATE DuplicateFilter SET Company = [COLOR="#0000FF"]:Company1[/COLOR] WHERE St_No = 43"
flag = cn.execute(sqlCommand, [COLOR="#0000FF"]args[/COLOR])
cn.close()

SetTables = "alert('Your tables have been successfully Updated!');"


end function

[GGandhi]

Re: Problem with XBasic "cn.open" with sqlServer

question: when you set the connection string initially, did it work? ( by indicating success or succeeded )
suggestion: put a debug right after the function declaration and step thru the code and see if there is a mistake.

while you can directly include your variables in your sql statements it is recommended you use arguments as steve pointed out to prevent sql injections.

sqlCommand = "UPDATE DuplicateFilter SET Company = Company1 WHERE St_No = 43;"

in this statement company1 may not be translated to correct data as you think it would.

sqlCommand = "UPDATE DuplicateFilter SET Company =  "  + Company1  + "   WHERE St_No = 43;"

might.

[kingcarol]

Re: Problem with XBasic "cn.open" with sqlServer

Don't you have to tell it what the connection string is?

dim cn as sql::connection
dim cs as c

cs = your connection string

cn.open(cs)

[nlk10010]

Re: Problem with XBasic "cn.open" with sqlServer

Hi-

I hope someone can help...

I'm probably missing something VERY basic, but I've looked at this for so long, I just can't spot the problem.

I'm trying to do a simple "Update" sql command with a UX button. In XBasic functions, I wrote the code below.

Using my Alpha sql Connection, "payroll", I want to update all of the "Company" fields (where St_No (Store #) = 43) to "XXX". Then, I have an "alert" to tell the user that "the table has been updated."

I've done this dozens of times before, without difficulty. HOWEVER, this is the first time I'm updating a sql Server 2014 table (my previous times using this type of function for UPDATE and INSERT used "MS Access" tables... don't get mad at me, all of my new applications use "sql Server"). From what I can tell, it doesn't appear that the connection ("payroll") is opening (I AM getting the "alert" message instantly... even though the program doesn't run). There "may" also be a problem with how I'm naming the table: in sql Server, the table is called "dbo.DuplicateFilter", but in Alpha, it's just "DuplicateFilter". Of course, I've tried the code both ways (and many variations of the "UPDATE" code). I NEVER get any sql error messages... just the "alert" message at the end of the code.

I'd appreciate any help or suggestions you have:

function SetTables as c (e as p)

Dim cn as sql::connection
Dim sqlCommand as c
Dim Company as c

Company1 = "XXX"
flag = cn.open("::Name::payroll")
sqlCommand = "UPDATE DuplicateFilter SET Company = Company1 WHERE St_No = 43;"
flag = cn.execute(sqlCommand)

SetTables = "alert('Your tables have been successfully Updated!');"

end function

Thanks,
Phil

Disclaimer: I may be wrong, take the following FWIW.

Your SQL statement won't work because you either need, as Steve pointed out, an argument OR you need something like "UPDATE ......... SET Company = '" + Company1 + "' WHERE.....". You can't stick a string variable inside the quotes, you need to append it with surrounding single quotes. Also, what's with the semicolon at the end of sqlCommand? Also (for a second time) why do you Dim Company, isn't that a field name? Also (third time) put a debug after cn.execute and if flag is .F. then inspect cn.callResult.Text, it will tell you what's wrong.

[kingcarol]

Re: Problem with XBasic "cn.open" with sqlServer

I'm thinking this xbasic function from my project might give you some clues... (note that before this function is called, I start an A5 message box to tell user that change order is being updated. That's why my code has the "A5.msgBox.hide();" command in it whenever the function might be exited.)

You would need to put in proper path for connection to your database. Mine is an Access database so file name ends in .mdb...

function ChangeCOTitle as c (e as p)

'open connection	
dim cn as sql::connection
dim cs as c 
	
cs = "{A5API=Access,FileName='C:\FolderName\FileName.mdb',UserName='Admin'}"

dim flag as l 
flag = cn.open(cs)
if flag = .f. then 
	dim msg as c
	jscmd = "A5.msgBox.hide();" 
	msg = "Could not connect to database. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	ChangeCOTitle = jscmd
	exit function 
end if
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'update CO lines for the CO Number to the new group title
sql2 = "UPDATE TblChangeOrdersData SET CO_GROUP_TITLE = :whatTitle WHERE [CO-NUMBER] = :whatCONum AND JOB_CODE = :whatJob"
DIM args2 as sql::arguments
args2.add("whatJob",e.DataSubmitted.JOB_CODE)
args2.add("whatCONum",e.DataSubmitted.CO_NUMBER)
args2.add("whatTitle",e.DataSubmitted.CO_GROUP_TITLE)

flag = cn.Execute(sql2,args2)
if flag = .f. then
	cn.Close() 
	dim msg as c 
	msg = "Could not run query to change the CO Title. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c
	jscmd = "A5.msgBox.hide();"  
	jscmd = jscmd + "alert('" + msg + "');"
	ChangeCOTitle = jscmd
	exit function 
end if
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
cn.Close()

dim js as c = ""
js = js + "A5.msgBox.hide();"
js = js + "alert('The Change Order title has been updated."

ChangeCOTitle = js
	
end function
'________________________________________________________________________

[Steve Workings]

Re: Problem with XBasic "cn.open" with sqlServer

Carol - he's using a saved connection string so his syntax (and mine) work fine.

I wonder why you do not though. There are several good reasons why a saved connection string is a good idea.

[kingcarol]

Re: Problem with XBasic "cn.open" with sqlServer

Carol - he's using a saved connection string so his syntax (and mine) work fine.

I wonder why you do not though. There are several good reasons why a saved connection string is a good idea.

Steve, I actually use dynamic connection strings for my multi tenants and have a session variable for the string. I just replaced the cs with a commonly used one so as not to confuse.

[kingcarol]

Re: Problem with XBasic "cn.open" with sqlServer

Mostly thought my update query could be of use.

[GGandhi]

Re: Problem with XBasic "cn.open" with sqlServer

"UPDATE ......... SET Company = '" + Company1 + "' WHERE.....". You can't stick a string variable inside the quotes, you need to append it with surrounding single quotes.

-- normanK

yes you are correct, i have overlooked that when i copy and pasted the sql statement and edited that statement..

[psamuels56]

Re: Problem with XBasic "cn.open" with sqlServer

Steve, Carol and Govindan... You are amazing! I continue to be impressed by how giving you are with your knowledge, time and experience! You are so generous to help us as we learn. THANK YOU SO MUCH!

I tried Steve's original code, and it worked perfectly for me (no surprise). But I really like the dialogue that you had. For that same application, I am going to need to do a code for UPDATE (thanks, Carol, for sharing that) and for a DELETE. Thanks to you, I think I'm well on my way.

I wish you all the best of a Happy, Healthy and Prosperous New Year... and thanks again for all your help.

Phil

[psamuels56]

Re: Problem with XBasic "cn.open" with sqlServer

Govindan, FYI, I DID put a "debug (1)" after the function declaration, but as I stepped through the code, nothing displayed. There were no errors or messages... my code just didn't work (of course, now, I know why). I maybe I don't know how to use the debug (1) function. In one of your previous postings, I saw that you added error coding (as Steve suggested) so that if the connection wasn't opened or if the INSERT didn't work, the user would get an "aert". BUT your code was for mySQL and it didn't work when I tried it in SQL Server.

Anyway, I'm an MS Access convert, but I'm getting more and more comfortable and experienced with Alpha every day... Thanks to people like you.

Phil