Incorrect syntax near the keyword 'DEFAULT'

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
Sun Jul 31 23:58:11 CDT 2005

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
Mon Aug 01 08:37:44 CDT 2005

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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



"Mark Findlay" <mfindlay@speakeasy.org> wrote in message
news:eU$FdPklFHA.3544@TK2MSFTNGP15.phx.gbl...
> 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;}

Paul
Mon Aug 01 08:50:25 CDT 2005

On Sun, 31 Jul 2005 19:51:04 -0700, "Mark Findlay" <mfindlay@speakeasy.org> wrote:

¤ 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)

Franky
Mon Aug 01 09:00:01 CDT 2005


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

"Mark Findlay" wrote:

> 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;}
>

Hans
Mon Aug 01 09:39:33 CDT 2005

Franky wrote:
> 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

> "Mark Findlay" wrote:
>
>> 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;}

Patrice
Mon Aug 01 10:09:13 CDT 2005

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

--

"Mark Findlay" <mfindlay@speakeasy.org> a écrit dans le message de
news:eU$FdPklFHA.3544@TK2MSFTNGP15.phx.gbl...
> 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
Mon Aug 01 15:30:06 CDT 2005

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

Thanks,
Mark

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:O2t$z4plFHA.320@TK2MSFTNGP09.phx.gbl...
> 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,
>
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>
>
> "Mark Findlay" <mfindlay@speakeasy.org> wrote in message
> news:eU$FdPklFHA.3544@TK2MSFTNGP15.phx.gbl...
>> 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;}
>
>

Mark
Mon Aug 01 15:30:40 CDT 2005

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

Thanks,
Mark

"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
news:lm9se1583f0j62uvubo3ebflfdejqg28pr@4ax.com...
> On Sun, 31 Jul 2005 19:51:04 -0700, "Mark Findlay"
> <mfindlay@speakeasy.org> wrote:
>
> ¤ 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)

Mark
Mon Aug 01 15:31:24 CDT 2005

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

Thanks,
Mark

"Patrice" <nobody@nowhere.com> wrote in message
news:e0pZ8rqlFHA.2988@TK2MSFTNGP10.phx.gbl...
> 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
>
> --
>
> "Mark Findlay" <mfindlay@speakeasy.org> a écrit dans le message de
> news:eU$FdPklFHA.3544@TK2MSFTNGP15.phx.gbl...
>> 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;}
>
>

zdrakec
Mon Aug 01 15:40:37 CDT 2005

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
Mon Aug 01 16:15:54 CDT 2005

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



"Mark Findlay" <mfindlay@speakeasy.org> wrote in message
news:eU$FdPklFHA.3544@TK2MSFTNGP15.phx.gbl...

> 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;}