ADO.NET in C# - statement problem, please help?

[jez]

I've been stuck with this stupid problem for the past two days.. I give up
now, please help me?

Here's the code :

OleDbCommand updatedb = conn.CreateCommand();
updatedb.CommandText = "INSERT INTO maintable (name, number) VALUES ('John
Smith', '01215489746')";
updatedb.ExecuteNonQuery();


When I release/debug my app in VS.NET I get a succeeded build. However when
I want to run the app I get on the screen :
" OleDbException : Syntax Error in INSERT INTO statement. "

Obviously there's something wrong with the SQL command I'm writing, but
what? I really don't get it and I've honestly tried everything.

For the info, I'm trying to connect to an Access DB. Please share you
knowledge with me.. thanks so much!
By the way, if anyone's got a good website with an ADO.NET tutorial (for
beginners) please let me know..

thanks

jez

 

[Carl Fenley]

Wrap the words name and number in brackets ([]). Your new command would
look like this:

"INSERT INTO maintable ([name], [number])
VALUES ('John Smith', '01215489746')";

 

[jez]

Carl,

thanks for your reply. I made the change, here's the error I get now :

* Additional information: Specified cast is not valid. *

It's doesn't even give a line number..

Am I missing something out ?

 

[One Handed Man]

sounds like your datatypes are inconsistent with the table, maybe your
trying to write a number to a string type or visa versa

--
Regards - One Handed Man

Author : Fish .NET & Keep .NET

==============================

Carl,

thanks for your reply. I made the change, here's the error I get now :

* Additional information: Specified cast is not valid. *

It's doesn't even give a line number..

Am I missing something out ?

Wrap the words name and number in brackets ([]). Your new command would
look like this:

"INSERT INTO maintable ([name], [number])
VALUES ('John Smith', '01215489746')";

 

[jez]

It all looks ok. I just had a look at my DB. All integers are being showed
in C# as ints and all "text" as string. I also checked the database
security, but the problem doesn't seem to be on that side.

Worth to note : I am able to delete records in the DB.

 

[sam]

Can you update the db via an update command?

The problem with the Insert command will probably be in the datatypes of the
paramters, not the query string itself. Instead of hardcoding the parameters
into the sql string, use question marks and add parameters to the command.
Make sure you are adding your parameters in the correct order in which the
query string expects them.

Make sure the oleDbType datatypes for the parameters are correct. Don't
specify Text parameters as oleDbType.WChar datatypes, instead use
OleDbType.VarWChar. Don't specify Integers as OleDbType.Double, instead use
OleDbType.Integer. This all counts.

It all looks ok. I just had a look at my DB. All integers are being showed
in C# as ints and all "text" as string. I also checked the database
security, but the problem doesn't seem to be on that side.

Worth to note : I am able to delete records in the DB.

 

[Michael Lang]

sam,

thanks for your reply. I don't quite understand what you mean by the
datatypes parameters. As you could see from the update command I was
trying to execute there are no other parameters given but the ones
there.

Please can you clarify what you mean by using question marks ? Could
you perhaps give an example ?

Again, i don't see where I should use oleDbType.WChar. I'd appreciate
an example on that one as well.. If it helps I could give the entire
code.. although it could get a little long (130 lines).

thanks
jez

by using question marks he means this:

OleDbCommand updatedb = conn.CreateCommand();
updatedb.CommandText = "INSERT INTO maintable (name, number) "
+ "VALUES (?, ?)";
OleDbParameter prmName = new OldDbParameter();
prmName.SourceColumn = "name";
prmName.DataType = OleDBType.WChar;
prmName.Value = "John Smith";
updatedb.Parameters.Add(prmName);

OleDbParameter prmNumber = new OldDbParameter();
prmNumber.SourceColumn = "number";
prmNumber.DataType = OleDBType.Double;
prmNumber.Value = 1215489746;
updatedb.Parameters.Add(prmNumber);

// Note it is important the parameters get added in the
// order they appear in the command text!!!

updatedb.ExecuteNonQuery();

 

[jez]

Jeff,

I had already tried that. My number field used to be numeric but then I
decided to change it to text just for the purpose of testing it..

I even tried to add a number as the ID field cos the ID is the primary key
in my DB, so I thought it's a requirement when I add a row to the DB - still
no result.

Here's my DDL :
ID : number
Name : text
Address : text
Number : Text
DOB : Date/Time (I'm not even touching that one at the moment).

FYI : None of them are marked as required.

Do I maybe need to add a link in my ODBC panel in XP? (like I used to do in
when I used JDBC) Could that perhaps be the problem? If that is, could you
please let me know the steps I need to take.. I would not think this is the
problem though as I can view my table and delete rows.

What's going here? I didn't think it would be _that_ hard to add data to an
Access DB.. I'm sure I must be doing something wrong here..

thanks for the help

jez

jez:

your sample code suggests the second column is numeric, but without the
ddl (table def'n), its anyone's guess.


i see you keep placing single quotes around '01215876489', creating a
string literal, instead of using the numeric value 1215876489. so if
the data type on this table's column is numeric, the insert will fail
because '01215876489' is a string, where 1215876489 is a number.


let's try this from a different angle. is the following a valid query
against your table's definition?

INSERT INTO maintable (name, number) VALUES ('Some name', 'ZaYbXcWdVe')



because this query is syntactically no different from:

INSERT INTO maintable (name, number) VALUES ('Some name', '01215876489')



but, if your ddl on the table is numeric, the query should be (note the
missing single quotes around the data for the numeric column)

INSERT INTO maintable (name, number) VALUES ('Some name', 1215876489)


just guessing,
jeff

I thought I'd just give the code out.. i'm still stuck Jeff, I tried
what you suggested, it still doesn't work though:/

Please help.. :/ Here goes the code :

using System;
using System.Globalization;
using System.Data;
using System.Data.OleDb;
class updatedb {

void showTable() {
OleDbConnection conn = null;
OleDbDataReader dbReader = null;
try
{ //creates a link to the DB (.mdb file)
conn = new OleDbConnection(@"
Provider = Microsoft.Jet.OLEDB.4.0;
User Id = ;
Password = ;
Data Source = C:\Documents and Settings\jez\My
Documents\Project\Access\csdb.mdb");
conn.Open();
//insert the required query command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM maintable";
dbReader = cmd.ExecuteReader();
//Get names of each the headers of each column and print them in
the
console
Console.Write(dbReader.GetName(0).PadRight(4, ' '));
Console.Write("{0}", dbReader.GetName(1).PadRight(20, ' '));
Console.Write("{0}", dbReader.GetName(2).PadRight(25, ' '));
Console.Write("{0}", dbReader.GetName(3).PadRight(10, ' '));
Console.Write("{0}", dbReader.GetName(4).PadRight(3, ' '));
//make a long line of 67 characters long under the names of the
columns Console.WriteLine("\n{0}", "".PadLeft(67, '-'));
int index;
string name;
string address;
string phone;
DateTime dob = new DateTime();//create a new DateTime object to be
able
to customise the format of the date
//get the values from the DB and put each of them in a variable
then
output them on the console

while (dbReader.Read())
{
index = dbReader.GetInt32(0);
name = (string)dbReader.GetValue(1);
address = (string)dbReader.GetValue(2);
phone = System.Convert.ToString(dbReader.GetValue(3));
dob = dbReader.GetDateTime(4);
string dob1 = dob.ToString("dd-MM-yy",
DateTimeFormatInfo.InvariantInfo);
Console.WriteLine("{0}{1}{2}{3}{4}",
index.ToString().PadRight(4, ' '),
name.PadRight(20, ' '),
address.PadRight(25, ' '),
phone.PadRight(10, ' '),
dob1.PadRight(20, ' '));
}
}
catch (OleDbException odbe)
{
Console.WriteLine("OleDbException: {0}", odbe.Message);
}

finally
{
if (dbReader != null)
{
dbReader.Close();//needed to ensure the DB is closed properly
conn.Close();
}
}
}

void modifyTable()
{
OleDbConnection conn=null;
try
{
conn = new OleDbConnection(@"
Provider = Microsoft.Jet.OLEDB.4.0;
User Id = ;
Password = ;
Data Source = C:\Documents and Settings\jeremyh\My
Documents\Project\Access\csdb.mdb");
conn.Open();
OleDbCommand updatedb = conn.CreateCommand();
updatedb.CommandText = "INSERT INTO maintable (name, number)
VALUES
('Some name', '01215876489')";
updatedb.ExecuteNonQuery();
}

catch (OleDbException odbe)
{
Console.WriteLine("OleDbException : {0}", odbe.Message);
}

finally
{
if (conn != null)
{
conn.Close();
}
}
}

static void Main (string[] args) {
updatedb udb = new updatedb();
udb.showTable();
udb.modifyTable();
Console.WriteLine("");
udb.showTable();
}
}