Zugg Software :: View topic - How to quote variable in SQL select in CMUD

Novice Joined: 06 Jun 2007 Posts: 30

Per http://www.zuggsoft.com/zmud/comado.htm, I can #call the following command sucessfully

#VAR rs @Conn.Execute( "SELECT * FROM Mudlist WHERE Title = 'Realms of Despair'")

But I failed to quote variable into the above command, such as

#VAR rs @Conn.Execute( "SELECT * FROM Mudlist WHERE Title = '%1'") ------------provided %1= Realms of Despair")

OR

#VAR rs @Conn.Execute( "SELECT * FROM Mudlist WHERE Title = '@a'") --------------provided #val a Realms of Despair

Novice Joined: 06 Jun 2007 Posts: 30

Could someone give me a hand?

How to use variable in ADO mode of CMUD?

Such as

#trig {you are using (*)}

{

#VAR Conn %comcreate( "ADODB.Connection");
#CALL @Conn.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=weapon.mdb");
#VAR rs @Conn.Execute( "SELECT action FROM weapon WHERE weapon = '%1'");
@rs.GetString

}

Thanks in advance.

GURU Joined: 23 Dec 2000 Posts: 4834 Location: USA

anything in double quotes never gets expanded, parsed, or otherwise messed with. In your above examples, you are telling SQL to look for the literal string of %1. If you want to use a variable's value, you must preform the string using %concat():

$searchstring = %concat("SELECT action FROM weapon WHERE weapon='",%1,"'")

Novice Joined: 06 Jun 2007 Posts: 30

Thanks very much for your help. It settles!

I change the code as following, it works.

#trig {you are using (*)}

{

#VAR Conn %comcreate( "ADODB.Connection");
#CALL @Conn.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=weapon.mdb");
$searchstring = %concat("SELECT action FROM weapon WHERE weapon='",%1,"'")
#VAR rs @Conn.Execute( $searchstring);
#echo @rs.GetString;

}

Novice Joined: 06 Jun 2007 Posts: 30

Hi, I met another problem. When database serach return NULL(no result in access database), the cmud 2.37 would pop up a dialogue with some words meaning " you did not find anything from the database". But I do not want to pop up this dialogue, no result is normal result as well.

Could I set cmud not to pop up this dialogue if it selects nothing from the database?

Posted: Thu May 06, 2010 5:57 pm

Posted: Thu May 06, 2010 7:41 pm

CMUD is just displaying the errors generated by the ADODB Windows object. If selecting nothing from the database causes ADODB to generate an error exception, then CMUD is going to display it in a popup window. No way around that, sorry.

Posted: Thu May 06, 2010 7:46 pm

Indeed! Which is why I said no, ok not a very informative answer. I also looked into connection properties and found nothing that would help either :(

Novice Joined: 06 Jun 2007 Posts: 30

I find a way to block the dialogue.

Under Recordset mode, check both BOF and EOF, if both of them are true, which means no record found in the database, do not get the result from database.

The following code is an example.

#VAR rs %comcreate( "ADODB.Recordset")
#VAR ConnStr "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\CMUDPro\weapon.mdb"
$Sql = %concat("SELECT action FROM weapon WHERE weapon='",%1,"'")
#CALL @rs.Open( $Sql, @ConnStr,2)
#if (@rs.bof AND @rs.eof) {#beep} {action=%trim(@rs.GetString)}

Posted: Sat May 08, 2010 10:25 am

Yep that's a very good solution, well done!