怎么在使用ADO建立数据库中表时判断该表是否存在

2012-01-12

如何在使用ADO建立数据库中表时判断该表是否存在。创建我知道是g_pConn- Execute( CREATETABLEaccount(ID

如何在使用ADO建立数据库中表时判断该表是否存在。
创建我知道是
g_pConn-> Execute( "CREATE TABLE account(ID INTEGER,username TEXT,old INTEGER,birthday DATETIME) ",&RecordsAffected,adCmdText);

但是如果这个表已经有了。就不要建立了。。怎么弄呢？
还有动态建立数据库的时候也是
m_pCatalog-> Create(_bstr_t(strcnn)); //Create MDBk可以建立数据库。
但是数据库建立的话就会转到catch去啦！虽然没什么影响

[解决办法]
BOOL CDlgTest::CreateAccessTable()
{
_bstr_t strSQL;
CString strTemSQL;
CString str, str1, str2, str3;
CString strTabName,strName;

CStringArray stt;
CStringArray stTabName;

int i,n;
HRESULT hr(-1);

UpdateData(TRUE);
//OnSelchangeComboType();

n=m_List.GetItemCount();
stt.SetSize(n);
for (i=0;i <n;i++)
{
if(m_List.GetItemText(i,1)== "Text ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + "( " +
m_List.GetItemText(i,2) + ") " + ", ";

}
else if (m_List.GetItemText(i,1)== "Autoincremnet ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + ", ";
}
else if (m_List.GetItemText(i,1)== "Currency ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + ", ";

}
else if(m_List.GetItemText(i,1)== "DateTime ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + ", ";
}
else if(m_List.GetItemText(i,1)== "Text ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + "( " +
m_List.GetItemText(i,2) + ") " + ", ";
}
else if(m_List.GetItemText(i,1)== "Double ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + ", ";
}
else if(m_List.GetItemText(i,1)== "Decimal ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + "( " +
m_List.GetItemText(i,2) + ", " +
m_List.GetItemText(i,3) + ") " + ", ";
}
else if(m_List.GetItemText(i,1)== "Single ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + ", ";
}
else if(m_List.GetItemText(i,1)== "Integer ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + ", ";

}
/*else if (m_List.GetItemText(i,1)== "Yes ")
{
strTemSQL=m_List.GetItemText(i,0) + " " +
m_List.GetItemText(i,1) + ", ";
}*/
stt[i] = strTemSQL;

}
str2 = m_List.GetItemText(0,0);
str3 = " ";
for(i=0;i <n;i++)
{
if(ListView_GetCheckState(m_List,i))
{

if(i> 0)
{
str1 = m_List.GetItemText(i,0);

str.Format( "%s,%s ",str2,str1);
str2 = str;
}
str.Format( "%s%s ",str3,stt[i]);
str3 = str;
}
}

//BOOL bOpenFileDialog= false;
//CString filepath;
//CString m_strResult;
//CString strName;
//LPCTSTR lpszDefExt= _T( " ");
//LPCTSTR lpszFileName= _T( "tabAccess ");

////DWORD dwFlags= OFN_HIDEREADONLY;
//DWORD dwFlags= OFN_OVERWRITEPROMPT;
//
//
//LPCTSTR lpszFilter= _T( "(*.*)|*.*| ");
//
//CFileDialog dlg(bOpenFileDialog,
//lpszDefExt,lpszFileName,
//dwFlags,lpszFilter,CWnd::FromHandle(hMainWnd));
// dlg.m_ofn.lpstrTitle = _T( "Êý¾Ý¿âÎÄ¼þ ");
//if(dlg.DoModal()==IDOK)
//{
//filepath=dlg.GetPathName();
//strName=dlg.GetFileName();
//
//UpdateData(FALSE);
//}
try
{
m_pRecordset=theApp.m_pConnection-> OpenSchema(adSchemaTables);
while(!(m_pRecordset-> adoEOF))
{
//»ñÈ¡±í¸ñ
_bstr_t table_name = m_pRecordset-> Fields-> GetItem( "TABLE_NAME ")-> Value;
//»ñÈ¡±í¸ñÀàÐÍ
_bstr_t table_type = m_pRecordset-> Fields-> GetItem( "TABLE_TYPE ")-> Value;

//¹ýÂËÒ»ÏÂ£¬Ö»Êä³ö±í¸ñÃû³Æ£¬ÆäËûµÄÊ¡ÂÔ

if ( strcmp(((LPCSTR)table_type), "TABLE ")==0)
{
strTabName=(LPCSTR)table_name;
if(m_strTabName== " ")
{
::MessageBox(NULL, "±íÃû²»ÄÜÎª¿Õ,ÇëÊäÈë±íÃû ", "ÌáÊ¾ ",MB_OK);

return false;
}

else if(strTabName==m_strTabName)
{
strName.Format( "±íÃû%sÒÑ¾´æÔÚ,ÇëÖØÐÂÊäÈë! ",m_strTabName);
::MessageBox(NULL,strName, "ÌáÊ¾ ",MB_OK);
return false;
}

}
m_pRecordset-> MoveNext();
}

strName=m_strTabName;
}
catch(_com_error e)///²¶×½Òì³£
{
::MessageBox(NULL,e.Description(), "ÌáÊ¾ ",MB_OK);
return FALSE;
}

str.Format( "CREATE TABLE %s (ID INTEGER,X decimal(10,2),Y decimal(10,2),Z decimal(10,2),%s CONSTRAINT MyTableConstraint PRIMARY KEY(ID),UNIQUE(X,Y,Z,%s)) ", strName,str3, str2);
//str = "CREATE TABLE tabAccess (ID INTEGER,X F(10,2),Y F(10,2),Z F(10,2), CONSTRAINT MyTableConstraint PRIMARY KEY(ID), UNIQUE(X,Y,Z)) ";
//str = "CREATE TABLE %s (ID INTEGER,X Number,Y Double,Z Double, CONSTRAINT MyTableConstraint PRIMARY KEY(ID), UNIQUE(X,Y,Z)) ";

strSQL= str;
try
{

theApp.m_pConnection-> Execute(strSQL,NULL,adCmdText);
//theApp.m_pConnection-> Execute(strTemSQL,NULL,adCmdText);

}
//hr = (HRESULT)0;
catch (_com_error &e)
{
::MessageBox(NULL,e.Description(), "ÌáÊ¾ ",MB_OK);

//hr = e.WCodeToHRESULT(e.WCode());

return FALSE;

}

//::MessageBox(NULL, "´´½¨±í³É¹¦£¡ ", "ÌáÊ¾ ",MB_OK);
UINT j=::MessageBox(NULL, "´´½¨±í³É¹¦£¡ ", "ÌáÊ¾ ",MB_OK);;
if(j==IDOK)
{
UINT i=::MessageBox(NULL, "ÊÇ·ñÏÖÔÚ²é¿´±í½á¹¹ ", "ÌáÊ¾ ",MB_YESNO);
if(i==IDYES)
{
UpdateData(true);
CString str= " ";
/*int ncount=rstab.m_cmbTable.GetCount();
for(int num=0;num <ncount;num++)
{
rstab.m_cmbTable.GetLBText(num,m_strTabName);
}*/
CDlgRecordset dlg(this);
dlg.m_strCMBTable = m_strTabName;
/*int ncount=dlg.m_cmbTable.GetCount();
for(int num=0;num <ncount;num++)
{
dlg.m_cmbTable.GetLBText(num,str);
if(str==m_strTabName)
{
str=m_strTabName;
break;
}
}*/
///dlg.m_cmbTable.SetCurSel(num);
dlg.DoModal();
}
else
{
CDlgTest dlg(this);
dlg.DoModal();
}
}
return TRUE;

}

[解决办法]
CString strCretabase = _T( "if not exists(select name from sysobjects where name = 'GTALARMTEST ') create database GTALARMTEST ");
pAdoDb-> Execute(_T( "use gtalarmtest "));

CString strCreateOptInfo( "create table mis_operater_info(operater_name char(20) NOT NULL, ");
strCreateOptInfo += _T( "operater_num char(20) NOT NULL primary key, ");
strCreateOptInfo += _T( "log_name char(20) NOT NULL, ");
strCreateOptInfo += _T( "log_password char(20), ");
strCreateOptInfo += _T( "date_create_time DATETIME default getdate()) ");
CString strCreateTable;
strCreateTable = _T( "if not exists(select 1 from sysobjects where id=object_id( 'mis_operater_info ') and xtype= 'U ') ");
strCreateTable += strCreateOptInfo;
pAdoDb-> Execute(strCreateTable);
[解决办法]
但是如果这个表已经有了。就不要建立了。。怎么弄呢？

其实可以完全在SQL里面完成这样的工作。

示例：

--如果在TestDB数据库中不存在account表，则创建表。
--反之不创建

IF NOT EXISTS
(SELECT * FROM TestDB.dbo.sysobjects WHERE name= 'account ')
CREATE TABLE account
(ID INTEGER,username TEXT,old INTEGER,birthday DATETIME)

热点排行

VC/MFC

怎么在使用ADO建立数据库中表时判断该表是否存在