Anyone tell me what's wrong with this SQL statement?

[Brian Basquille]

Hello all,

Bit of a change of pace now. As opposed to the typical questions regarding
my Air Hockey game, am also working on a Photo Album which uses an Access
Database to store information about photos. This information is held inside
the database (photoDB.mdb) in a table called 'photos' - information being
recorded in there are photo information (photoID, location, phDate,
category, caption).

I have inputted my own photos (not via this method) and they all load
correctly. But when adding a new photo, the user needs to clarify if the
photo information is all correct - hence pressing the OK button (see code
below).

I, however, get a 'no value given for one or more required parameters' error
below. Anyone care to share where i've gone wrong? Also, it's hard to know
if this is more relevent in the CSharp newsgroup or the Access / SQL
newsgroup.

Thanks in advance.

PS - just to cover every area, the data types in Access database are as
follows: photoID (Text), location (Text), phDate (Date/Time), category
(Text), caption (Text)

----------------------------------------------------------------------------------------

private void btnOK_Click(object sender, System.EventArgs e)
{
try
{
newPhotoID = lblPhotoID.Text;
newLocation = newFileName.ToString();
newDate = DateTime.Now.Date;
newCategory = cboCategory.Text;
newCaption = txtCaption.Text;
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
+System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)
+ @"\photoDB.MDB";
string strSQL = "INSERT INTO photos (photoID, location, phDate,
category, caption) VALUES (newPhotoID, newLocation, newDate, newCategory,
newCaption);" ;

// create Objects of ADOConnection and ADOCommand
OleDbConnection myConn = new OleDbConnection(strDSN);
OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );
myConn.Open();
myCmd.ExecuteNonQuery();
myConn.Close();
}
catch (OleDbException ex)
{
MessageBox.Show("" + ex.Message);
}
}

 

[Eyeawanda Pondicherry]

That sounds like an SQL error telling you that you are not accounting
for all the fields in your INSERT statement.

Since it's an Access database, you can always to into the Query mode and
then select SQL and then cut and paste your SQL statement there to
"test" it.

You may be:

a) Not accounting for a primary key
b) Passing a null value where nulls are not accepted
c) Spelling the name of the field wrong

 

[Brian Basquille]

Cheers also for that, Eyewanda!

Have went through the SQL statement a hundred times and still can't figure
out what's wrong with it. Field names aren't wrong. Using breakpoints, i've
figured out there's definantly no nulls. And the primary key is accounted
for.

But i'll try testing the SQL statement in Query mode.

Thanks again!

 

[John Puopolo]

hi...

if i recall correctly, i had this problem with a sql server project i was
working on. the error text is misleading. if you've checked all the values
and all the types and they are correct, make sure that the command object
and the connection object are "hooked up" as you think they are. i recall
having to do something like: sqlcommand sc = conn.createcommand(...) vs.
typing them together in some other way.

john

 

[Jim Hughes]

The variables in your VALUES section of your SQL statement are not
automatically replaced with the local variable values.

The ideal way to do it would be to use parameters with a SQL command string
like this.

INSERT INTO photos (photoID, location, phDate, category, caption)
VALUES (?, ?, ?, ?, ?);

It could also be done with string concatenation, but not recommended.

INSERT INTO photos (photoID, location, phDate, category, caption)
VALUES (3, 'Office', #1/7/2005#, 'Project', 'My Office');

 

[Peter Gloor]

Try something like:

string strSQL = "INSERT INTO photos (photoID, location, phDate,
category, caption) VALUES (" + newPhotoID + ", " + newLocation
+ ", " + newDate.ToString() + ", " + newCategory + ", " +
newCaption + ");" ;


Peter

 

[James Curran]

Not quite that easy.... (The strings would need to be quoted..)

string strSQL = "INSERT INTO photos (photoID, location, phDate,
category, caption) VALUES ('" + newPhotoID + "', '" + newLocation
+ "', #" + newDate.ToString() + "#, '" + newCategory + "', '" +
newCaption + "');" ;


--
Truth,
James Curran
[erstwhile VC++ MVP]
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com

 

[CKa]

Could it also be that the DateTime.ToString() format isn't the format that
the datetime should be in when used in the SQL Insert command?

Just a thought...

 

[MacKenzieMouse]

Could it also be that the DateTime.ToString() format isn't the format that
the datetime should be in when used in the SQL Insert command?

Just a thought...

get rid of the plus sign in front of newPhotoID. Perhaps it is confused.

 

[Ged]

It might be the semi-colon at the end of the SQL statement.
SQL doesn't need semi-colons to terminate a statement.

Hope that helps

Ged