SQL WHERE STATMENTS

[mpwbs]

Hi, 

I'm relatively new to CVI, but I'm in need of some help. Right now, I'm creating a UIR with a user input with a text box that is querying a database full of data. I'm wondering how do I get what I entered into the textbox into the WHERE OPERATOR = ' USERS INPUT '. Also, I'm trying to print that SQL Statement out too. So, far I'm just trying to get the output of this simple SQL statement.

hstmt = DBActivateSQL(hdbc, "SELECT * FROM REG_RESULTSTABLE");
    if(hstmt <= 0)
    {
        ShowError();
    }

    sprintf(uutNum, "%s \n",hstmt);
    while((resCode = DBFetchNext(hstmt)) == DB_SUCCESS){
    SetCtrlVal(panelHandle,PANEL_lstregulator,uutNum);
    }

[RobertoBozzolo · Accepted solution]

You should be able to get what you want by coding something along this line:

int		sts, nrec;
char	operator[32], uutNum[16], command[256];

// Retrieve user input and create the sql statement
GetCtrlVal (..., ..., operator);
sprintf (command, "SELECT * FROM REG_RESULTSTABLE WHERE OPERATOR = \'%s\', operator);

// Retrieve records
hstmt = DBActivateSQL (hdbc, command);
if (hstmt <= 0) {
    ShowError();
    goto Error;
}

// Count records found
nrec = DBNumberOfRecords (hstmt);
if (nrec < 0) {
    ShowError();
    goto Error;
}

// Map column data to an appropriate variable
if ((dberr = DBBindColChar (hstmt, colNum, 16, uutNum, &sts, "")) != DB_SUCCESS) {
    ShowError();
    goto Error;
}

// Enumerate records
while ((dberr = DBFetchNext (hstmt)) == DB_SUCCESS) {
	SetCtrlVal (..., ..., uutNum);
}

Error:

If your query is expected to retrieve more than one record, you should display results to a textbox, listbox or table indicator.

[mpwbs]

Thank You so much Robert, I would have never thought of that but now, I'm getting errors on my FetchNext Statement. Every time I run this. I get this error on the DBFetchNext(). Any suggestions or solutions to this problem. So, I have my Database connection in a function that I call, could that be the reason?

NON-FATAL RUN-TIME ERROR: "Database.c", line 324, col 30, thread id 33300: Function DBFetchNext: (return value == -10 [0xfffffff6]). Native error code -2146825023 0x800a0cc1 ADODB.Recordset: Item cannot be found in the collection corresponding to the requested name or ordinal.

[RobertoBozzolo]

A quick search on Google lists among the possible causes the reference to a non-existing column (could you possibly be misspelling it?).

[mpwbs]

Thanks! I looked at all my code and SQL statements and everything is good and in order. I seriously wonder if its because I'm using third-party stuff for the database. But, if you can think of anything else that would be great! Any help would be appreciated. I've been stuck on this going on 3 weeks now. Would like to finish this soon. Includes my hdbc and hstmt functions all spell correctly. 

[mpwbs]

Hi,

Can I ask where did you place the second double quotes in the sprintf() statement, assuming its after the last escape character?