By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

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

P: n/a
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);
}
}

Nov 16 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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 wrote:
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);
}
}

Nov 16 '05 #2

P: n/a
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!

"Eyeawanda Pondicherry" <ou***********@us.india> wrote in message
news:eN***************@newsread1.news.pas.earthlin k.net...
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 wrote:
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);
}
}


Nov 16 '05 #3

P: n/a
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
"Brian Basquille" <re**********@please.com> wrote in message
news:Or**************@TK2MSFTNGP12.phx.gbl...
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.GetExecutingAsse
mbly().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);
}
}

Nov 16 '05 #4

P: n/a
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');

"Brian Basquille" <re**********@please.com> wrote in message
news:Or**************@TK2MSFTNGP12.phx.gbl...
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);
}
}

Nov 16 '05 #5

P: n/a
Try something like:

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

P: n/a
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

"Peter Gloor" <p_*****@hotmail.com> wrote in message
news:Ot**************@tk2msftngp13.phx.gbl...
Try something like:

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

Nov 16 '05 #7

P: n/a
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...
"Peter Gloor" <p_*****@hotmail.com> wrote in message
news:Ot**************@tk2msftngp13.phx.gbl...
Try something like:

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

Nov 16 '05 #8

P: n/a
CKa wrote:
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...
"Peter Gloor" <p_*****@hotmail.com> wrote in message
news:Ot**************@tk2msftngp13.phx.gbl...
Try something like:

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


get rid of the plus sign in front of newPhotoID. Perhaps it is confused.
Nov 16 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.