binding input parameters leads to SQL_ERROR

I am trying to read the results from a query like
select a, b
from mytbl
where col0 = ?
The first time I run SQLExecute, everything works fine, but when I call
SQLExecute a second time with a modified input parameter, it returns
SQL_ERROR. Below is the code I'm running with the error checking removed and
the names simplified.
SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
&where_param, 0, 0);
SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)
SQLExecute(m_hstmt) //works ok
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
where_param = 10
SQLExecute(m_hstmt) //fails
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
My goal is to run execute many times with a variety of parameters. I was
trying to have the odbc driver read from the address specified in
SQLBindParameter to set the value of the input parameter. I'm further
confused by the problem because when I try to retrieve error information by
calling SQLGetDiagRec, nothing is returned. Thanks for your help.
Scott
The problem goes away when I call SQLCloseCursor after completing the fetch.
The MSDN SQLBindParameter documentation at
http://msdn.microsoft.com/library/de...dparameter.asp
gives no indication that SQLCloseCursor should be needed.
I suppose that I am now just concerned that I am introducing extra
processing overhead by calling SQLCloseCursor.
Scott
"ScottD" wrote:

> I am trying to read the results from a query like
> select a, b
> from mytbl
> where col0 = ?
> The first time I run SQLExecute, everything works fine, but when I call
> SQLExecute a second time with a modified input parameter, it returns
> SQL_ERROR. Below is the code I'm running with the error checking removed and
> the names simplified.
> SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
> SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
> SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
> &where_param, 0, 0);
> SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
> SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)
> SQLExecute(m_hstmt) //works ok
> SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
> where_param = 10
> SQLExecute(m_hstmt) //fails
> SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
>
> My goal is to run execute many times with a variety of parameters. I was
> trying to have the odbc driver read from the address specified in
> SQLBindParameter to set the value of the input parameter. I'm further
> confused by the problem because when I try to retrieve error information by
> calling SQLGetDiagRec, nothing is returned. Thanks for your help.
> Scott
|||Are you making sure that you've fetch all of the rows? Even if only
one row is returned by the query, you should call SQLFetchXXX() until
it returns SQL_NO_DATA. Otherwise the driver doesn't necessarily know
that you've finished fetching rows on the current resultset/cursor.
Alternatively, you can use SQLMoreResults() to flush the remaining data
on the wire and position on the next resultset (if there is one,
otherwise it cleans up the connection).
SQLCloseCursor shouldn't add too much overhead. I'm assuming you're
using the default (firehose) cursor. If so, SQLCloseCursor just makes
sure that all of the data from the previous statement has been
consumed, and the connection is ready for the next statement.
Brannon

Binding a SqlDataSource to a TextBox or label

I have a SQL database with 1 column in it.. it is a do not call registry for my office. I want to make a web application so our leads can go on the web and request to be put on the do not call list. problem is, if their number already exists, it gets a violation of primary key...

Normal handling for this would be to do a select query based on user input, and if it exists in the database, tell them that, else do the insert query...

Problem is, i cant seem to figure out how to bind the result of the select query to a textbox or label so i can compare the results to do that logic. Any ideas? This is in asp .net 2.0. Thanks!

-Dan

You can't directly bind to a Label or a TextBox, but you certainly can set the result of your DataSource to the Text property of your control. Here's an example:

ASPX

ProductName = <asp:label id="lblProductName" runat="server" /><asp:objectdatasource id="odsProducts" runat="server" selectmethod="GetProductByProductID"typename="NorthwindTableAdapters.ProductsTableAdapter"><selectparameters><asp:parameter defaultvalue="1" name="ProductID" type="Int32" /></selectparameters></asp:objectdatasource>

CODE-BEHIND

protected void Page_Load(object sender, EventArgs e){if (!this.IsPostBack){DataView dv = (DataView)odsProducts.Select();lblProductName.Text = dv[0]["ProductName"].ToString();}}

|||

Acctually... i figured out a way... check this out..

Dim dv As Data.DataView = SqlDataSource1.Select(DataSourceSelectArguments.Empty)

TextBox1.Text = dv.ToTable.Rows(0)("PHONE")

This works, but now i have another problem. If there is no row 0 (meaning if the number doesnt exist) the page fails. How can i check to see if the record doesnt exist, then do something else?

|||Just check to see if the DataView.Count property is greater than 0.|||Thanks, that worked great!