Insert into not exist 的问题

小发猫 2009-06-08 04:20:55
出现问题的代码:
void XEIM_AddUser::AddUser(LPCTSTR lpszUID, LPCTSTR lpszNick)
{
/*
飞鸽传书这个功能时,遇到一个问题,想添加记录到表里,并且表里不存在这条记录,如果存在则不操作。
试过许多方法,都无法实现,有人可以帮忙解决吗?
*/

/* char *sql = "INSERT INTO userinfo("
"uid,"
"unickname,"
"upassword,"
"ugroup) "
"SELECT '%s','%s','%s','%s' WHERE NOT EXISTS (SELECT * FROM userinfo WHERE uid='%s')";
*/
/* char *sql = "INSERT INTO userinfo "
"SELECT '%s','%s','%s','%s' WHERE NOT EXISTS (SELECT * FROM userinfo WHERE uid='%s')";
*/
/* char *sql = "INSERT INTO userinfo("
"uid,"
"unickname,"
"upassword,"
"ugroup) "
"VALUES('%s','%s','%s','%s') "
"WHERE NOT EXISTS(SELECT * FROM userinfo WHERE uid='%s')";*/
char *sql = "INSERT INTO userinfo("
"uid,"
"unickname,"
"upassword,"
"ugroup) "
"SELECT '%s','%s','%s','%s' "
"FROM dual "
"WHERE NOT EXISTS(SELECT * FROM userinfo WHERE uid='%s')";
/*WHERE NOT EXISTS
(SELECT *
FROM tblSQL_2
WHERE SOBN = 5 AND BN1 = 3)

*/
CString strSQL;
strSQL.Format(sql,
lpszUID,
lpszNick,
m_pMainTop->m_strPsw,
m_pMainTop->m_strGroup,
lpszUID);

AfxMessageBox(strSQL);
// return;
try
{
_variant_t vAffected;
m_pConn->CursorLocation = adUseClient;
m_pConn->Execute((LPCTSTR)strSQL, &vAffected, adExecuteNoRecords);
}
catch (_com_error e)
{
MessageBox(e.Description());
return;
}

LVITEM lvi;
int nIdx = 0;

lvi.mask = LVIF_IMAGE | LVIF_TEXT;
lvi.iItem = 0xff;
lvi.iSubItem = 0;
lvi.pszText = (LPSTR)lpszUID;
lvi.iImage = I_IMAGECALLBACK; // There are 8 images in the image list
nIdx = m_lcUsers.InsertItem(&lvi);

m_lcUsers.SetItemText(nIdx, 1, lpszNick);
m_lcUsers.SetItemText(nIdx, 2, m_pMainTop->m_strPsw);
m_lcUsers.SetItemText(nIdx, 3, m_pMainTop->m_strGroup);
}



所有代码
/////////////////////////////////////////////////////////////////////////////////

// XEIM_AddUser.cpp : implementation file
//

#include "stdafx.h"
#include "xeimUserCreator.h"
#include "XEIM_AddUser.h"
#include "xeimUserCreatorDlg.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// XEIM_AddUser dialog


XEIM_AddUser::XEIM_AddUser(CWnd* pParent /*=NULL*/)
: CDialog(XEIM_AddUser::IDD, pParent)
{
//{{AFX_DATA_INIT(XEIM_AddUser)
// NOTE: the ClassWizard will add member initialization here
//}}AFX_DATA_INIT
}


void XEIM_AddUser::DoDataExchange(CDataExchange* pDX)
{
CDialog::DoDataExchange(pDX);
//{{AFX_DATA_MAP(XEIM_AddUser)
DDX_Control(pDX, IDC_LIST1, m_lcUsers);
//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(XEIM_AddUser, CDialog)
//{{AFX_MSG_MAP(XEIM_AddUser)
ON_WM_CLOSE()
ON_WM_TIMER()
//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// XEIM_AddUser message handlers

BOOL XEIM_AddUser::OnInitDialog()
{
CDialog::OnInitDialog();

// TODO: Add extra initialization here
m_pMainTop = (CXeimUserCreatorDlg*)AfxGetMainWnd();

// 初始化用户列表类名
InitListHeader();
InitDB();

// 添加并显示
int nCount = atoi(m_pMainTop->m_strCount);
CString strUID;
CString strNick;
for (int i=0; i<nCount; i++)
{
strUID.Format("%s%d", m_pMainTop->m_strUID, i+1);
strNick.Format("%s%d", m_pMainTop->m_strNickname, i+1);
AddUser(strUID, strNick);
}

SetTimer(22, 800, NULL);
return TRUE; // return TRUE unless you set the focus to a control
// EXCEPTION: OCX Property Pages should return FALSE
}

void XEIM_AddUser::InitListHeader()
{
DWORD dwStyle = m_lcUsers.GetExtendedStyle();
dwStyle |= LVS_EX_FULLROWSELECT;
m_lcUsers.SetExtendedStyle(dwStyle);

m_lcUsers.InsertColumn(0, "UID", LVCFMT_LEFT, 120);
m_lcUsers.InsertColumn(1, "显示名称", LVCFMT_LEFT, 118);
m_lcUsers.InsertColumn(2, "初始密码", LVCFMT_LEFT, 118);
m_lcUsers.InsertColumn(3, "默认分组", LVCFMT_LEFT, 118);

}

void XEIM_AddUser::OnClose()
{
// TODO: Add your message handler code here and/or call default
CDialog::OnClose();
m_pMainTop->QuitApp();
}

void XEIM_AddUser::OnCancel()
{
CDialog::OnCancel();
m_pMainTop->QuitApp();
}

void XEIM_AddUser::AddUser(LPCTSTR lpszUID, LPCTSTR lpszNick)
{
/*
做这个功能时,遇到一个问题,想添加记录到表里,并且表里不存在这条记录,如果存在则不操作。
试过许多方法,都无法实现,有人可以帮忙解决吗?
*/
/* char *sql = "INSERT INTO userinfo("
"uid,"
"unickname,"
"upassword,"
"ugroup) "
"SELECT '%s','%s','%s','%s' WHERE NOT EXISTS (SELECT * FROM userinfo WHERE uid='%s')";
*/
/* char *sql = "INSERT INTO userinfo "
"SELECT '%s','%s','%s','%s' WHERE NOT EXISTS (SELECT * FROM userinfo WHERE uid='%s')";
*/
/* char *sql = "INSERT INTO userinfo("
"uid,"
"unickname,"
"upassword,"
"ugroup) "
"VALUES('%s','%s','%s','%s') "
"WHERE NOT EXISTS(SELECT * FROM userinfo WHERE uid='%s')";*/
char *sql = "INSERT INTO userinfo("
"uid,"
"unickname,"
"upassword,"
"ugroup) "
"SELECT '%s','%s','%s','%s' "
"FROM dual "
"WHERE NOT EXISTS(SELECT * FROM userinfo WHERE uid='%s')";
/*WHERE NOT EXISTS
(SELECT *
FROM tblSQL_2
WHERE SOBN = 5 AND BN1 = 3)

*/
CString strSQL;
strSQL.Format(sql,
lpszUID,
lpszNick,
m_pMainTop->m_strPsw,
m_pMainTop->m_strGroup,
lpszUID);

AfxMessageBox(strSQL);
// return;
try
{
_variant_t vAffected;
m_pConn->CursorLocation = adUseClient;
m_pConn->Execute((LPCTSTR)strSQL, &vAffected, adExecuteNoRecords);
}
catch (_com_error e)
{
MessageBox(e.Description());
return;
}

LVITEM lvi;
int nIdx = 0;

lvi.mask = LVIF_IMAGE | LVIF_TEXT;
lvi.iItem = 0xff;
lvi.iSubItem = 0;
lvi.pszText = (LPSTR)lpszUID;
lvi.iImage = I_IMAGECALLBACK; // There are 8 images in the image list
nIdx = m_lcUsers.InsertItem(&lvi);

m_lcUsers.SetItemText(nIdx, 1, lpszNick);
m_lcUsers.SetItemText(nIdx, 2, m_pMainTop->m_strPsw);
m_lcUsers.SetItemText(nIdx, 3, m_pMainTop->m_strGroup);
}

void XEIM_AddUser::InitDB()
{
HRESULT hr;

CString strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=user.mdb";

try
{
// m_pConn 在构造函数里,必须初始化为 NULL
hr = m_pConn.CreateInstance(__uuidof(Connection));
hr = m_pConn->Open((LPCTSTR)strConn,"","",adModeUnknown);
}
catch(_com_error e)
{
AfxMessageBox("CXsvrDlg.XDB_InitUserList.catch");
// 当数据库连接失败时,直接退出系统。
// CDialog::OnCancel();
// return;
}
if (! m_pConn->State)
{
AfxMessageBox("数据库打开失败!");
}
}

void XEIM_AddUser::OnTimer(UINT nIDEvent)
{
// TODO: Add your message handler code here and/or call default
if (nIDEvent == 22)
{
KillTimer(22);
MessageBox(_T("添加完毕"));
OnCancel();
}
CDialog::OnTimer(nIDEvent);
}
...全文
970 1 打赏 收藏 转发到动态 举报
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
na_he 2009-06-08
  • 打赏
  • 举报
回复
首先用selcet筛选一下再添加了。

4,011

社区成员

发帖
与我相关
我的任务
社区描述
VC/MFC 数据库
社区管理员
  • 数据库
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧