CODEWITHSUNDEEP
c#sql-server-cecompact-framework.net-1.1
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10227

Why am I getting, "cannot find table 0"?

At program startup, I'm creating a SQL Server CE table if it doesn't exist, and adding a record to it:

if (dbconn.isValidTable("hhs_settings") == -1) // <-- (IOW, it's *not* a valid table)
{
    DBUtils.CreateSettingsTable();
    AppSettings.WriteSettingsVal("beltprinter", "None");
}

public static void CreateSettingsTable()
{
    try
    {
        string sqlddl =
            "create table hhs_settings (setting_id int identity (1,1) Primary key,  setting_name varchar(40) not null, setting_value(63) varchar not null)";
        dbconn.DBCommand(sqlddl, false);        
    }
    catch (SqlCeException sqlcex)
    {
        MessageBox.Show("sqlcex - CreateSettingsTable " + sqlcex.Message);
    }
    catch (Exception ex)
    {
        MessageBox.Show("CreateSettingsTable " + ex.Message);
    }
}

...but then when I open a form that reads from the table:

lblSelectedPrinter.Text = AppSettings.ReadSettingsVal("beltprinter");

...it fails with "cannot find table 0"

public static string ReadSettingsVal(string settingName)
{
    string retVal = string.Empty;
    string sqldml = string.Format("select setting_value from hhs_settings where setting_name = {0}", " + settingName + ");
    // There should only be one value returned, but using the existing getDataSet() method for now...
    DataSet dsSettingVal = frmCentral.dbconn.getDataSet(sqldml);
    foreach (DataRow row in dsSettingVal.Tables[0].Rows)
    {
        if (retVal == string.Empty)
        {
            retVal = row["setting_value"].ToString();
        }
    }
    return retVal;
}

Am I missing a step here, so that the table (hhs_settings) is not being created? Or...???

UPDATE

I'm still getting the same err msg even after changing all of the goofily formatted strings so that they are now:

string sqldml = string.Format("insert into hhs_settings (setting_name, setting_value) values('{0}', '{1}')", settingName, settingVal);
string sqlqry = string.Format("select setting_value from hhs_settings where setting_name = '{0}'", settingName);

And, I'm still seeing the msg "about to create hhs_settings" even though, presumably, the createtable sql was already good:

string sqlddl =
    "create table hhs_settings (setting_id int identity (1,1) Primary key,  setting_name varchar(40) not null, setting_value(63) varchar not null)";

...and so, the table should have been created (which should have made the isValidTable() method return something other than -1 (the value it returns when the table is not found).

UPDATE 2

It helped (it works now) when I changed my ddl to:

string sqlddl = "create table hhs_settings (setting_id int identity (1,1) Primary key, setting_name nvarchar(40) not null, setting_value nvarchar(63) not null)";

Upvotes: 1

Views: 5392

Answers (2)

user6039531
user6039531

Reputation: 11

If you are using stored procedures, check out the name of the passing queries in it. It should exactly match with the front end and backend.

Upvotes: 1

dmay
dmay

Reputation: 1325

string.Format("select setting_value from hhs_settings where setting_name = {0}", " + settingName + ");

The result of it is 

select setting_value from hhs_settings where setting_name =  + settingName +

what is obviously not a correct SQL. You need to use something like this

string.Format("select setting_value from hhs_settings where setting_name = '{0}'", settingName);

(also note quotation marks around the parameter placeholder)

but it much more better if you use parameters instead of generating SQL with all identifiers embedded

Upvotes: 2

Related Questions