Incorrect syntax near the keyword 'DEFAULT'

[Mark Findlay]

Hello Experts!

I am attempting to use the OleDbCommand.ExecuteScaler() function within my
ASP.NET C# web page to perform a simple validation, but receive the
following error:

"Incorrect syntax near the keyword 'DEFAULT'"

The form has 2 fields on it, called tb_username and tb_password. (see code
snippet below).
The SQL Server table is called _users and has a number of columns, 2 of
which I am reading: username and password.

I've searched the net for any clues on this but so far none of the items
I've found were relavent to what I was doing. Can anyone shed any light on
this?

Many thanks!

=== here is my code ====

private bool ValidateLogin()
{
// set the connection string
oleDbConnection_login.ConnectionString = C_Constants.g_sDSN;

// add parameters to the query
oleDbCommand_login.Parameters.Add("@user",System.Data.OleDb.OleDbType.VarWChar,25,tb_username.Text); oleDbCommand_login.Parameters.Add("@pass",System.Data.OleDb.OleDbType.VarWChar,25,tb_password.Text); oleDbCommand_login.CommandText = "Select username from _users whereusername=? AND password=?"; // open the connection oleDbConnection_login.Open(); // execute the query oleDbCommand_login.Connection = oleDbConnection_login; object objResults = oleDbCommand_login.ExecuteScalar(); <--- errormessage occurs here // close the connection oleDbConnection_login.Close(); if (objResults==null) { return false; } return true;}

 

[Cor Ligthert [MVP]]

Mark,

I would set in your place your command statement in a try block than you get
probably more information.

I had once this behaviour as well and than there was asked for 3 parameters.

I added a dummy one at the end and my problem was gone.
Until now I don't know the reason for this behaviour.

Cor

 

[Steven Cheng[MSFT]]

Thanks for Cor's inputs.

Hi Mark, if it's the sql statement that cause the error, I'll suggest you
turn on the sql profiler at the sqlserver side to monitor the actually
executed sqlstatement. And you can compare it with the correct one (which
you can get executed correctly in SQL Query Analyzer).

Hope also helps. If still anything unclear or need any further assistance,
please feel free to post here.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--------------------
| From: "Cor Ligthert [MVP]" <[email protected]>
| References: <[email protected]>
| Subject: Re: Incorrect syntax near the keyword 'DEFAULT'
| Date: Mon, 1 Aug 2005 06:58:11 +0200
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Response
| Message-ID: <[email protected]>
| Newsgroups:
microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.c
sharp
| NNTP-Posting-Host: ip3e830773.speed.planet.nl 62.131.7.115
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.dotnet.languages.csharp:113390
microsoft.public.dotnet.framework.adonet:33238
| X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
|
| Mark,
|
| I would set in your place your command statement in a try block than you
get
| probably more information.
|
| I had once this behaviour as well and than there was asked for 3
parameters.
|
| I added a dummy one at the end and my problem was gone.
| Until now I don't know the reason for this behaviour.
|
| Cor
|
|
|

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Where is "DEFAULT" ? I see it nowhere in your code

Are you using a trigger?

what if you run this in the SQL query analyser?

cheers,

 

[Paul Clement]

¤ Hello Experts!
¤
¤ I am attempting to use the OleDbCommand.ExecuteScaler() function within my
¤ ASP.NET C# web page to perform a simple validation, but receive the
¤ following error:
¤
¤ "Incorrect syntax near the keyword 'DEFAULT'"
¤
¤ The form has 2 fields on it, called tb_username and tb_password. (see code
¤ snippet below).
¤ The SQL Server table is called _users and has a number of columns, 2 of
¤ which I am reading: username and password.
¤
¤ I've searched the net for any clues on this but so far none of the items
¤ I've found were relavent to what I was doing. Can anyone shed any light on
¤ this?
¤
¤ Many thanks!
¤
¤ === here is my code ====
¤
¤ private bool ValidateLogin()
¤ {
¤ // set the connection string
¤ oleDbConnection_login.ConnectionString = C_Constants.g_sDSN;
¤
¤ // add parameters to the query
¤ oleDbCommand_login.Parameters.Add("@user",System.Data.OleDb.OleDbType.VarWChar,25,tb_username.Text); oleDbCommand_login.Parameters.Add("@pass",System.Data.OleDb.OleDbType.VarWChar,25,tb_password.Text); oleDbCommand_login.CommandText = "Select username from _users whereusername=? AND password=?"; // open the connection oleDbConnection_login.Open(); // execute the query oleDbCommand_login.Connection = oleDbConnection_login; object objResults = oleDbCommand_login.ExecuteScalar(); <--- errormessage occurs here // close the connection oleDbConnection_login.Close(); if (objResults==null) { return false; } return true;}

Unless that's a typo in your post, a blank space is omitted between the WHERE keyword and username
column name. I don't see the keyword DEFAULT in that statement.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

username and password need single quotes around values, something like this:

oleDbCommand_login.CommandText = "Select username from _users where
username='myusername' AND password='mypwd'"; // open the connection

 

[Hans Kesting]

username and password need single quotes around values, something
like this:

oleDbCommand_login.CommandText = "Select username from _users where
username='myusername' AND password='mypwd'"; // open the connection

Not if you use parameters, which is preferred and which the OP does.

Hans Kesting

 

[Patrice]

AFAIK it uses the default keyword when it finds no mapping. Usually I set
the parameters after the commandtext. Are you sure it doesn't reset the
collection ?

Patrice

--

Hello Experts!

I am attempting to use the OleDbCommand.ExecuteScaler() function within my
ASP.NET C# web page to perform a simple validation, but receive the
following error:

"Incorrect syntax near the keyword 'DEFAULT'"

The form has 2 fields on it, called tb_username and tb_password. (see code
snippet below).
The SQL Server table is called _users and has a number of columns, 2 of
which I am reading: username and password.

I've searched the net for any clues on this but so far none of the items
I've found were relavent to what I was doing. Can anyone shed any light on
this?

Many thanks!

=== here is my code ====

private bool ValidateLogin()
{
// set the connection string
oleDbConnection_login.ConnectionString = C_Constants.g_sDSN;

// add parameters to the query

oleDbCommand_login.Parameters.Add("@user",System.Data.OleDb.OleDbType.VarWCh
ar,25,tb_username.Text);
oleDbCommand_login.Parameters.Add("@pass",System.Data.OleDb.OleDbType.VarWCh
ar,25,tb_password.Text); oleDbCommand_login.CommandText = "Select username
from _users whereusername=? AND password=?"; // open the connection
oleDbConnection_login.Open(); // execute the query
oleDbCommand_login.Connection = oleDbConnection_login; object objResults =
oleDbCommand_login.ExecuteScalar(); <--- errormessage occurs here //
close the connection oleDbConnection_login.Close(); if (objResults==null)
{ return false; } return true;}

 

[Mark Findlay]

The "DEFAULT" is not part of my code, it is only contained in the error
message.

Thanks,
Mark

 

[Mark Findlay]

Sorry Paul, that's just a typo in my message. There is in fact a space
between WHERE and the where clause.

Thanks,
Mark

 

[Mark Findlay]

I tried moving the parameters after the commandtext and still receive the
same error.

Thanks,
Mark

 

[zdrakec]

Try using the parameter names in your command, e.g. "where username =
@user AND password = @pass", rather than the placeholders, "where
username = ? AND password = ?". I have found that using the question
mark placeholders works for me with ODBC, not OLEDB, and that named
parameters have worked for me with OLEDB, but not ODBC.

Hope this is helpful...

zdrakec

 

[Mark Findlay]

I believe I found the solution:

For a select query, I am not allowed to use the format of the
Parameters.Add() function I am currently using:
oleDbCommand_login.Parameters.Add("@user",System.Data.OleDb.OleDbType.VarWChar,25,tb_username.Text);

Instead, when I used the following format, it all worked fine:
oleDbCommand_login.Parameters.Add("@user",tb_username.Text);

I believe the explanation lies in the help screen for the Parameters.Add()
function from the Dynamic Help in Visual Studio.NET. It states:
"Adds an OleDbParameter to the OleDbParameterCollection given the
parameter name, data type, column length, and source column name."

Notice the end of the text: "source column name". In my usage, I am not
providing the source column name, I am providing the textbox from my webpage
where the parm value is to be read. I'm definitely no expert on VS.NET etc.,
but it would appear from the dynamic help text above that using this form of
Parameters.Add() is not appropriate for a select statement. Odd though,
since I pulled my usage out of a textbook.

Thanks for everyone's input. I hope this answer helps anyone else looking
in.

Mark