Multiple Tables SELECT command with Microsoft Access 2003

[matthew.macdonald-wallace]

Hi all,

For various reasons I am accessing data from an access database with
multiple tables. I am unable to upgrade to SQL Server (even the
express edition), so I have to do this in access.

At the moment, my sql statement is as follows:

"SELECT * FROM software,vendors WHERE vendors.vendorId =
software.vendorId AND software.softwareId = " +
cmbExisitingSoftware.SelectedValue.ToString();

Where cmbExisitingSoftware.SelectedValue.ToString() is the software ID
from a combobox that has been populated from the database.

The issue I have is that when I try and execute this sql statement use
the code provided below, I get an error stating "No value given for one
or more parameters" when it tries to execute the datareader. the code
is as follows:

// create the connection
OleDbConnection dbcx = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=installTracker.mdb");
// open the connection
dbcx.Open();

// setup SQL statement
string sqlSoftwareDetails = "SELECT * FROM software,vendors
WHERE vendors.vendorId = software.vendorId AND software.softwareId = "
+ cmbExisitingSoftware.SelectedValue.ToString();
MessageBox.Show(sqlSoftwareDetails);
OleDbCommand cmdGetSoftware = new
OleDbCommand(sqlSoftwareDetails, dbcx);
OleDbDataReader drGetSoftware =
cmdGetSoftware.ExecuteReader();
while (drGetSoftware.Read())
{
txtDocumentationPath.Text =
drGetSoftware["softwareInstallDocumentationPath"].ToString();
}
dbcx.Close();


I anyone can help, that would be great!

Cheers,

Matt

 

[Marina Levit [MVP]]

Is the ID a numeric, or a string? If it is a string, at the very least you
need to delimit it with single quotes so it can be treated as a string
literal. The better solution would be to use a parameterized query.

 

[Matt]

Marina,

Thanks for the response, the ID is a string in the code but a number in
the table. I'll try and convert it and see if that does anything, then
I'll try the parameters.

Thanks again,

Is the ID a numeric, or a string? If it is a string, at the very least you
need to delimit it with single quotes so it can be treated as a string
literal. The better solution would be to use a parameterized query.

Hi all,

For various reasons I am accessing data from an access database with
multiple tables. I am unable to upgrade to SQL Server (even the
express edition), so I have to do this in access.

At the moment, my sql statement is as follows:

"SELECT * FROM software,vendors WHERE vendors.vendorId =
software.vendorId AND software.softwareId = " +
cmbExisitingSoftware.SelectedValue.ToString();

Where cmbExisitingSoftware.SelectedValue.ToString() is the software ID
from a combobox that has been populated from the database.

The issue I have is that when I try and execute this sql statement use
the code provided below, I get an error stating "No value given for one
or more parameters" when it tries to execute the datareader. the code
is as follows:

// create the connection
OleDbConnection dbcx = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=installTracker.mdb");
// open the connection
dbcx.Open();

// setup SQL statement
string sqlSoftwareDetails = "SELECT * FROM software,vendors
WHERE vendors.vendorId = software.vendorId AND software.softwareId = "
+ cmbExisitingSoftware.SelectedValue.ToString();
MessageBox.Show(sqlSoftwareDetails);
OleDbCommand cmdGetSoftware = new
OleDbCommand(sqlSoftwareDetails, dbcx);
OleDbDataReader drGetSoftware =
cmdGetSoftware.ExecuteReader();
while (drGetSoftware.Read())
{
txtDocumentationPath.Text =
drGetSoftware["softwareInstallDocumentationPath"].ToString();
}
dbcx.Close();


I anyone can help, that would be great!

Cheers,

Matt