okay, I thought I had the return value from a statement figured out...

So, Jimmy G helped me out with it in showing a little bit how to do it.

SqlCommand command = new SqlCommand(, object>)SqlParameter param = new SqlParameter();param.ParameterName ="@.return";param.Direction = ParameterDirection.ReturnValue;command.Parameters.Add(param);command.ExecuteNonQuery();//get the return valueint val = int.Parse(command.Parameters[0].ToString());

Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following

Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs)
dim returnValue as integer

'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable?

InsertIssue.Insert()

Response.Redirect("/addarticletoissue")

End Sub

again, thank you for your help and patience with such a beginner =)

I haven't seen your original post where jimmyg helped you out, so I am going to guess that you are trying to retrieve the autoincrement identity value of a newly added record from a SqlDataSource Try the approach detailed here:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54 about 2/3rds of the way down.

|||

ahh! thank you so very much. This helps a ton!

Odd Unicode problem

I use Server2000 and ADO connection and Unicode.
I have fields type of nvarchar.
And when I do the search ADO creates the SQL statement like this:
exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
nvarchar(120),@.P3 smallint', 0, N'?', 7
(Caught from Profiler)
And this result nothing.
but when I delete the N from the second parameter:
..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, '?', 7 <-- no N
before the Unicode variable, it works (work - means result the correct
records)!
and to make it more odd
..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, N'John', 7 -- with N
works too.
I have tested those statements on S2k Query Analyzer and S2k5 SQL Server
Management Studio Express and there are same results.
Have anyone any idea what is wrong?
Regards,
Tomasz RutkowskiHi Tom
My guess is that your data is not what you expect
If you update the data
BEGIN TRANSACTION
UPDATE dbo.AB_EntityTreeNodeLink
SET EntityTitle = N'?'
WHERE EntityTitle = '?'
exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
nvarchar(120),@.P3 smallint', 0, N'?', 7
ROLLBACK TRANSACTION
Does it work?
John
"TomX" wrote:
> I use Server2000 and ADO connection and Unicode.
> I have fields type of nvarchar.
> And when I do the search ADO creates the SQL statement like this:
> exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
> dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
> ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
> ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
> nvarchar(120),@.P3 smallint', 0, N'?', 7
> (Caught from Profiler)
> And this result nothing.
> but when I delete the N from the second parameter:
> ...., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, '?', 7 <-- no N
> before the Unicode variable, it works (work - means result the correct
> records)!
> and to make it more odd
> ..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, N'John', 7 -- with N
> works too.
> I have tested those statements on S2k Query Analyzer and S2k5 SQL Server
> Management Studio Express and there are same results.
> Have anyone any idea what is wrong?
> Regards,
> Tomasz Rutkowski
>
>
>

odd issue with NOT IN statement

Some info

We recently moved a database to SQL2005 (start of year) a program I wrote is having a small issue(not rejecting unmatched orders) and I narrowed it to one command statement (that I tried directly in the management studio)

I have two databases on the server (a live and a test DB) on the test DB (same tables and views as live but the data is a bit different) side there is no issue, while on the live side it is not working.

the command is

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

on the test database if I add in the wip table a bad order number [order xref] it won't find it and it flags the haserr column and a reject statement is created for that order.

On the live database the haserr is not changed and so the filling of the order is not rejected when it needs to. It looks to be an issue with the "not in" command

PS at first I thought it was an update issue, but if I do

select * form wip where [order xref] not in (select [order xref] from m_orders)

edit: did some more testing

update wip set haserr=[order xref] where [order xref] in (select [order xref] from m_orders)

works

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

does not

I get null

My knee-jerk reaction to this is to change NOT IN syntax into NOT EXISTS syntax.

update wip

set haserr=[order xref]

from wip a

inner join m_orders b

on a.[order xref] = b.[order xref]

There are issues with NOT IN when your field is null that can cause the results to be different than the way you might intuitively expect. When the result is a NULL it cannot be determined whether the record is IN or NOT IN the set because the NULL means unknown. There have been previous threads that discuss this; I will try to find a couple.

Here are a couple of posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299702&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1

|||

It is bcs of your null value on m_orders table..

(Ex)

Select * From MYTable Where Id in (1,3,4,null)

Will return 1,3,4

Select * From MyTable Where Id not in (1,3,4,null)

will not return any value

Why?

The not in (1,3,4,null) = not (Id =1 or Id =3 or Id=4 or Id=null)

= id <> 1 and Id <> 3 and Id <> 4 And id <> null

= True And True And True And NULL

= True And NULL

= FALSE

ie, ALWAYS FALSE

The in (1,3,4) = (Id =1 or Id =3 or Id=4 or Id=null)

= True or False Or null

= True Or NULL

= True

So change your query as follow as,

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders Where [order xref] is NOT NULL)

|||NULL fails it, NULL values cant be compared.........sql

odd issue with IN statement

Some info

We recently moved a database to SQL2005 (start of year) a program I wrote is having a small issue(not rejecting unmatched orders) and I narrowed it to one command statement (that I tried directly in the management studio)

I have two databases on the server (a live and a test DB) on the test DB (same tables and views as live but the data is a bit different) side there is no issue, while on the live side it is not working.

the command is

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

on the test database if I add in the wip table a bad order number [order xref] it won't find it and it flags the haserr column and a reject statement is created for that order.

On the live database the haserr is not changed and so the filling of the order is not rejected when it needs to. It looks to be an issue with the "not in" command

PS at first I thought it was an update issue, but if I do

select * form wip where [order xref] not in (select [order xref] from m_orders)

edit: did some more testing

update wip set haserr=[order xref] where [order xref] in (select [order xref] from m_orders)

works

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

does not

I get null

My knee-jerk reaction to this is to change NOT IN syntax into NOT EXISTS syntax.

update wip

set haserr=[order xref]

from wip a

inner join m_orders b

on a.[order xref] = b.[order xref]

There are issues with NOT IN when your field is null that can cause the results to be different than the way you might intuitively expect. When the result is a NULL it cannot be determined whether the record is IN or NOT IN the set because the NULL means unknown. There have been previous threads that discuss this; I will try to find a couple.

Here are a couple of posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299702&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1

|||

It is bcs of your null value on m_orders table..

(Ex)

Select * From MYTable Where Id in (1,3,4,null)

Will return 1,3,4

Select * From MyTable Where Id not in (1,3,4,null)

will not return any value

Why?

The not in (1,3,4,null) = not (Id =1 or Id =3 or Id=4 or Id=null)

= id <> 1 and Id <> 3 and Id <> 4 And id <> null

= True And True And True And NULL

= True And NULL

= FALSE

ie, ALWAYS FALSE

The in (1,3,4) = (Id =1 or Id =3 or Id=4 or Id=null)

= True or False Or null

= True Or NULL

= True

So change your query as follow as,

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders Where [order xref] is NOT NULL)

|||NULL fails it, NULL values cant be compared.........

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

ODBC LIKE clause does not work - SQL Server *BUG*

Hi All:
I think there is a problem with the LIKE clause in a
SELECT statement. I am using the latest SQL Server 2000
(ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
Windows XP.
I created an ODBC connection and tied into a database
containing a customer table. The following piece of code
returns a record count of 0 (zero) when, in fact, the
record count should be over 2000.
=========================
#include <afxwin.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define DATABASE_CLAUSE "MPOS_SQLSERVER"
#define USERNAME_CLAUSE ""
#define PASSWORD_CLAUSE ""
#define SELECT_CLAUSE "SELECT COUNT(*) from Customers
WHERE LastName LIKE ? "
#define LIKE_CLAUSE "A%"
void main( void )
{
HENV hEnv = SQL_NULL_HENV;
HDBC hDbc = SQL_NULL_HDBC;
HSTMT hStmt = SQL_NULL_HSTMT;
long lValue = 0;
SQLINTEGER sqlNull = 0;
SQLINTEGER sqlStrLen = SQL_NTS;
SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlValue = 0;
if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
printf( "Error in SQLAllocEnv()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
&hDbc )))
printf( "Error in SQLAllocConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
(SQLCHAR *)
DATABASE_CLAUSE, SQL_NTS,
(SQLCHAR *)
USERNAME_CLAUSE, SQL_NTS,
(SQLCHAR *)
PASSWORD_CLAUSE, SQL_NTS )))
printf( "Error in SQLConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
&hStmt )))
printf( "Error in SQLAllocStmt()\n" );
else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
(SQLCHAR *)
SELECT_CLAUSE,
SQL_NTS )))
printf( "Error in SQLPrepare()\n" );
else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
0,
0,
LIKE_CLAUSE,
sqlBufferLen,
&sqlStrLen )))
printf( "Error in SQLBindParameter()\n" );
else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
printf( "Error in SQLExecute()\n" );
else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
printf( "Error in SQLFetch()\n" );
else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
1,
SQL_C_LONG,
&sqlValue,
sizeof(
sqlValue ),
&sqlNull )))
printf( "Error in SQLGetData()\n" );
else
printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
sqlNull );
}
=========================
When using the Query Analyzer and Enterprise Manager it
works. This is because, in the above code, I use the
SQLBindParameter() function to do the binding.
I've tried using the SQL Trace facility but it only shows
me the SQL statement being executed but does not indicate
any translations with the binded parameter.
Any suggestions or solutions would be greatly appreciated.
Regards,
AngeloWhen using SQL Trace, what is the text content of the query being executed?
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:06b101c3c712$93afb310$a401280a@.phx.gbl...

quote:

---

> Hi All:
> I think there is a problem with the LIKE clause in a
> SELECT statement. I am using the latest SQL Server 2000
> (ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
> Windows XP.
> I created an ODBC connection and tied into a database
> containing a customer table. The following piece of code
> returns a record count of 0 (zero) when, in fact, the
> record count should be over 2000.
> =========================
> #include <afxwin.h>
> #include <stdio.h>
> #include <sql.h>
> #include <sqlext.h>
> #define DATABASE_CLAUSE "MPOS_SQLSERVER"
> #define USERNAME_CLAUSE ""
> #define PASSWORD_CLAUSE ""
> #define SELECT_CLAUSE "SELECT COUNT(*) from Customers
> WHERE LastName LIKE ? "
> #define LIKE_CLAUSE "A%"
> void main( void )
> {
> HENV hEnv = SQL_NULL_HENV;
> HDBC hDbc = SQL_NULL_HDBC;
> HSTMT hStmt = SQL_NULL_HSTMT;
> long lValue = 0;
> SQLINTEGER sqlNull = 0;
> SQLINTEGER sqlStrLen = SQL_NTS;
> SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
> SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
> SQLINTEGER sqlValue = 0;
> if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
> printf( "Error in SQLAllocEnv()\n" );
> else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
> &hDbc )))
> printf( "Error in SQLAllocConnect()\n" );
> else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
> (SQLCHAR *)
> DATABASE_CLAUSE, SQL_NTS,
> (SQLCHAR *)
> USERNAME_CLAUSE, SQL_NTS,
> (SQLCHAR *)
> PASSWORD_CLAUSE, SQL_NTS )))
> printf( "Error in SQLConnect()\n" );
> else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
> &hStmt )))
> printf( "Error in SQLAllocStmt()\n" );
> else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
> (SQLCHAR *)
> SELECT_CLAUSE,
> SQL_NTS )))
> printf( "Error in SQLPrepare()\n" );
> else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
> 1,
> SQL_PARAM_INPUT,
> SQL_C_CHAR,
> SQL_CHAR,
> 0,
> 0,
> LIKE_CLAUSE,
> sqlBufferLen,
> &sqlStrLen )))
> printf( "Error in SQLBindParameter()\n" );
> else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
> printf( "Error in SQLExecute()\n" );
> else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
> printf( "Error in SQLFetch()\n" );
> else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
> 1,
> SQL_C_LONG,
> &sqlValue,
> sizeof(
> sqlValue ),
> &sqlNull )))
> printf( "Error in SQLGetData()\n" );
> else
> printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
> ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
> sqlNull );
> }
>
> =========================
> When using the Query Analyzer and Enterprise Manager it
> works. This is because, in the above code, I use the
> SQLBindParameter() function to do the binding.
> I've tried using the SQL Trace facility but it only shows
> me the SQL statement being executed but does not indicate
> any translations with the binded parameter.
> Any suggestions or solutions would be greatly appreciated.
> Regards,
> Angelo

---

|||When using the SQL Trace, the following is the snippet
from the log file...
=======================
rawodbc f38-f34 ENTER SQLPrepare
HSTMT 00391FE0
UCHAR * 0x00402104 [ -
3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
\ 0"
SDWORD -3
=======================
If you'd like to see the entire log file, I can post that
too.

quote:

---

>--Original Message--
>When using SQL Trace, what is the text content of the

---

query being executed?

quote:

---

>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
>"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
>news:06b101c3c712$93afb310$a401280a@.phx.gbl...
code[QUOTE]
SQL_CHAR,[QUOTE]
<%[QUOTE]
shows[QUOTE]
indicate[QUOTE]
appreciated.[QUOTE]
>
>.
>

---

|||I tried the following and it works fine:
rc = SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
2,
0,
"C%",
2,
NULL);
If I change the call to be like your example (passing 0 for the cbColDef
parameter) then I get an error trying to bind the parameter. What ODBC
version does your app set? 3.0 or earlier?
Try specifying the length of the string in the cbColDef parameter as well.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...[QUOTE]
> When using the SQL Trace, the following is the snippet
> from the log file...
> =======================
> rawodbc f38-f34 ENTER SQLPrepare
> HSTMT 00391FE0
> UCHAR * 0x00402104 [ -
> 3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
> \ 0"
> SDWORD -3
> =======================
> If you'd like to see the entire log file, I can post that
> too.
>
> query being executed?
> confers no rights.
> code
> SQL_CHAR,
> <%
> shows
> indicate
> appreciated.|||Thank you, thank you, thank you...
I am using ODBC version 2.x. The fix is the specification
of the 2 as the ColumnSize parameter.
It's funny how things work differently with Access and
SQLServer eventhough they are from the same manufacturer.
Thanks again.

quote:

---

>--Original Message--
>I tried the following and it works fine:
> rc = SQLBindParameter(hstmt,
> 1,
> SQL_PARAM_INPUT,
> SQL_C_CHAR,
> SQL_CHAR,
> 2,
> 0,
> "C%",
> 2,
> NULL);
>If I change the call to be like your example (passing 0

---

for the cbColDef

quote:

---

>parameter) then I get an error trying to bind the

---

parameter. What ODBC

quote:

---

>version does your app set? 3.0 or earlier?
>Try specifying the length of the string in the cbColDef

---

parameter as well.

quote:

---

>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
>"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
>news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...
LIKE ?[QUOTE]
that[QUOTE]
and[QUOTE]
message[QUOTE]
2000[QUOTE]
database[QUOTE]
the[QUOTE]
Customers[QUOTE]
LIKE_CLAUSE );[QUOTE]
hEnv,[QUOTE]
*)[QUOTE]
*)[QUOTE]
*)[QUOTE]
*)[QUOTE]
SQL_NTS )))[QUOTE]
hStmt,[QUOTE]
SQL_C_LONG,[QUOTE]
&sqlValue,[QUOTE]
&sqlNull )))[QUOTE]
sqlValue )/sqlNull =[QUOTE]
Manager it[QUOTE]
>
>.
>

---

|||Access and SQL Server are two completely different products (produced by
different groups here at Microsoft).
If you are using the SQL driver in 2.x mode, then it will allow you to
specify a zero-length precision, but obviously the behavior is not what you
expected. If you are using the SQL driver in 3.0 mode, then it will error
out if you specify a zero-length precision.
When binding parameters, you should always give the precision. In this
case, if you give a precision of zero, then we think the parameter (on the
server-side) has a length of zero, and so we truncate whatever data you give
us. Not the best behavior, but that's how it works. The precision (in
conjunction with the SQL data type) describes the type on the server.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0bc501c3d9e7$2fd1d7a0$a401280a@.phx.gbl...[QUOTE]
> Thank you, thank you, thank you...
> I am using ODBC version 2.x. The fix is the specification
> of the 2 as the ColumnSize parameter.
> It's funny how things work differently with Access and
> SQLServer eventhough they are from the same manufacturer.
> Thanks again.
>
> for the cbColDef
> parameter. What ODBC
> parameter as well.
> confers no rights.
> LIKE ?
> that
> and
> message
> 2000
> database
> the
> Customers
> LIKE_CLAUSE );
> hEnv,
> *)
> *)
> *)
> *)
> SQL_NTS )))
> hStmt,
> SQL_C_LONG,
> &sqlValue,
> &sqlNull )))
> sqlValue )/sqlNull =
> Manager it