自学c++连接SQLserver，发现进行update操作时程序无法通过输入编号和金额进行更新数据库，想了一天也试过很多方法，但是都没有找到解决办法。
#undef UNICODE
#undef _UNICODE
#include<Windows.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>
#include <iostream>
#include<string>
#include<cstring>
#include <iomanip>
#include<cstdlib>
#include<stdio.h>
using namespace std;
//定义全局变量
SQLRETURN ret = NULL;//返回信息
SQLHENV henv = NULL;//环境句柄
SQLHDBC hdbc = NULL;//连接句柄
SQLHSTMT hstmt = NULL;//语句句柄
//释放空间
void free()
{
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);//释放语句
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接
SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境
}
//连接数据库
void connect()//数据库连接函数
{
SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);//申请环境
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);//设置环境
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);//申请数据库连接
ret = SQLConnect(hdbc, (SQLTCHAR*)("Csql"), SQL_NTS, (SQLTCHAR*)("sa"), SQL_NTS, (SQLTCHAR*)("sa"), SQL_NTS);
if ((ret == SQL_SUCCESS) || (ret == SQL_SUCCESS_WITH_INFO))
{
cout << "数据库连接成功!" << endl;
}
else
{
cout << "数据库连接失败！" << endl;
}
}
//插入收入表记录
void insert_income()
{
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//申请句柄
string str1 = "use money";//第一句要执行的SQL语句
string str2 = "insert into income (income_description,income_money) values ('";//第二句要执行的SQL语句
cout << "请依次输入支出描述和支出金额: "<<endl;
string in_descr;
string in_money;
cin >> in_descr >> in_money;
string str3 = str2 + in_descr + "'," + in_money + ")";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
cout << "插记录入成功" << endl;
string str4 = "select income_no,income_description,income_money, CONVERT(varchar,GETDATE(),23) from income ";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str4.c_str(), SQL_NTS);
SQLCHAR str11[10], str12[12], str13[10], str14[15];//用来存放从数据库获取的列信息，你有几列就定义几个变量
SQLLEN len_str11, len_str12, len_str13, len_str14;//字符串对应长度，你有几列就定义几个变量
memset(str11, 0, 1);
memset(str12, 0, 1);
memset(str13, 0, 1);
memset(str14, 0, 1);
cout << "编号" << " " << "描述" << " " << "金额" << " " << "时间" << endl;
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
//你有几列就调用几次SQLGetData函数，注意找规律哦
SQLGetData(hstmt, 1, SQL_C_CHAR, str11, 10, &len_str11); //获取第一列数据
SQLGetData(hstmt, 2, SQL_C_CHAR, str12, 12, &len_str12); //获取第二列数据
SQLGetData(hstmt, 3, SQL_C_CHAR, str13, 10, &len_str13);
SQLGetData(hstmt, 4, SQL_C_CHAR, str14, 15, &len_str14); //获取第三列数据
printf("%s\t%s\t%s\t%s\n", str11, str12, str13, str14);
}
}
else {
cout << "插入记录失败！" << endl;
}
free();
}
//插入支出表记录
void insert_expend()
{
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//申请句柄
string str1 = "use money";//第一句要执行的SQL语句
string str2 = "insert into expend (expend_description,expend_money) values ('";//第二句要执行的SQL语句
cout << "请依次输入支出描述和支出金额: " << endl;
string ex_descr;
string ex_money;
cin >> ex_descr >> ex_money;
string str3 = str2 + ex_descr + "'," + ex_money + ")";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
cout << "插记录入成功" << endl;
string str4 = "select expend_no,expend_description,expend_money, CONVERT(varchar,GETDATE(),23) from expend ";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str4.c_str(), SQL_NTS);
SQLCHAR str11[10], str12[12], str13[10], str14[15];//用来存放从数据库获取的列信息，你有几列就定义几个变量
SQLLEN len_str11, len_str12, len_str13, len_str14;//字符串对应长度，你有几列就定义几个变量
memset(str11, 0, 1);
memset(str12, 0, 1);
memset(str13, 0, 1);
memset(str14, 0, 1);
cout << "编号" << " " << "描述" << " " << "金额" << " " << "时间" << endl;
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
//你有几列就调用几次SQLGetData函数，注意找规律哦
SQLGetData(hstmt, 1, SQL_C_CHAR, str11, 10, &len_str11); //获取第一列数据
SQLGetData(hstmt, 2, SQL_C_CHAR, str12, 12, &len_str12); //获取第二列数据
SQLGetData(hstmt, 3, SQL_C_CHAR, str13, 10, &len_str13);
SQLGetData(hstmt, 4, SQL_C_CHAR, str14, 15, &len_str14); //获取第三列数据
printf("%s\t%s\t%s\t%s\n", str11, str12, str13, str14);
}
}
else {
cout << "插入记录失败！" << endl;
}
free();
}
//删除收入表记录
void delete_income()
{
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//申请句柄
string str1 = "use money";
string str2 = "delete income where income_no='";
cout << "请输入要删除记录的编号：" << endl;
string in_no;
cin >> in_no;
string str3 = str2 + in_no + "'";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
cout << "删除记录成功！" << endl;
string str4 = "select income_no,income_description,income_money, CONVERT(varchar,GETDATE(),23) from income ";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str4.c_str(), SQL_NTS);
SQLCHAR str11[10], str12[12], str13[10], str14[15];//用来存放从数据库获取的列信息，你有几列就定义几个变量
SQLLEN len_str11, len_str12, len_str13, len_str14;//字符串对应长度，你有几列就定义几个变量
memset(str11, 0, 1);
memset(str12, 0, 1);
memset(str13, 0, 1);
memset(str14, 0, 1);
cout << "编号" << " " << "描述" << " " << "金额" << " " << "时间" << endl;
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
//你有几列就调用几次SQLGetData函数，注意找规律哦
SQLGetData(hstmt, 1, SQL_C_CHAR, str11, 10, &len_str11); //获取第一列数据
SQLGetData(hstmt, 2, SQL_C_CHAR, str12, 12, &len_str12); //获取第二列数据
SQLGetData(hstmt, 3, SQL_C_CHAR, str13, 10, &len_str13);
SQLGetData(hstmt, 4, SQL_C_CHAR, str14, 15, &len_str14); //获取第三列数据
printf("%s\t%s\t%s\t%s\n", str11, str12, str13, str14);
}
}
else {
cout << "删除记录失败！" << endl;
}
free();
}
//删除支出表记录
void delete_expend()
{
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//申请句柄
string str1 = "use money";
string str2 = "delete expend where expend_no='";
cout << "请输入要删除记录的编号：" << endl;
string ex_no;
cin >> ex_no;
string str3 = str2 + ex_no + "'";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
cout << "删除记录成功！" << endl;
string str4 = "select expend_no,expend_description,expend_money, CONVERT(varchar,GETDATE(),23) from expend ";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str4.c_str(), SQL_NTS);
SQLCHAR str11[10], str12[12], str13[10], str14[15];//用来存放从数据库获取的列信息，你有几列就定义几个变量
SQLLEN len_str11, len_str12, len_str13, len_str14;//字符串对应长度，你有几列就定义几个变量
memset(str11, 0, 1);
memset(str12, 0, 1);
memset(str13, 0, 1);
memset(str14, 0, 1);
cout << "编号" << " " << "描述" << " " << "金额" << " " << "时间" << endl;
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
//你有几列就调用几次SQLGetData函数，注意找规律哦
SQLGetData(hstmt, 1, SQL_C_CHAR, str11, 10, &len_str11); //获取第一列数据
SQLGetData(hstmt, 2, SQL_C_CHAR, str12, 12, &len_str12); //获取第二列数据
SQLGetData(hstmt, 3, SQL_C_CHAR, str13, 10, &len_str13);
SQLGetData(hstmt, 4, SQL_C_CHAR, str14, 15, &len_str14); //获取第三列数据
printf("%s\t%s\t%s\t%s\n", str11, str12, str13, str14);
}
}
else {
cout << "删除记录失败！" << endl;
}
free();
}
//更新收入表记录--有问题“无法更新数据”
void update_income_money()
{
cout << "请输入需要修改的记录编号；" << endl;
//string in_no;
//cin >> in_no;
string in_money;
cout << "请输入需要修改的金额：" << endl;
cin >> in_money;
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//申请句柄
string str1 = "use money";
string str2 = "update income set income_money = ' where income_no= 2";
/* cout << "请输入需要修改的记录编号；" << endl;
string in_no;
cin >> in_no;
string in_money;
cout << "请输入需要修改的金额：" << endl;
cin >> in_money;*/
string str3 = str2 + in_money + "',";//+ in_no + "'";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
ret = SQLExecDirect(hstmt, (SQLCHAR*)str2.c_str(), SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
cout << "更新记录成功！" << endl;
else
cout << "更新记录失败！" << endl;
free();
}
//查找收入表记录 --可以在上面的增删改操作中增加select功能以实现操作后显示结果
void select_income()
{
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//申请句柄
string str1 = "use money";
string str2 = "select income_no,income_description,income_money, CONVERT(varchar,GETDATE(),23) from income where income_no='";
cout << "请输入需要查找的记录编号：" << endl;
string in_no;
cin >> in_no;
string str3 = str2 + in_no + "'";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
SQLCHAR str1[10], str2[12], str3[10],str4[15];//用来存放从数据库获取的列信息，你有几列就定义几个变量
SQLLEN len_str1, len_str2, len_str3,len_str4;//字符串对应长度，你有几列就定义几个变量
memset(str1,0,1);
memset(str2, 0, 1);
memset(str3, 0, 1);
memset(str4, 0, 1);
cout << "编号" << " " << "描述" << " " << "金额" << " " <<"时间" << endl;
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
//你有几列就调用几次SQLGetData函数，注意找规律哦
SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 10, &len_str1); //获取第一列数据
SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 12, &len_str2); //获取第二列数据
SQLGetData(hstmt, 3, SQL_C_CHAR, str3, 10, &len_str3);
SQLGetData(hstmt, 4, SQL_C_CHAR, str4, 15, &len_str4); //获取第三列数据
printf("%s\t%s\t%s\t%s\n", str1, str2, str3,str4);
}
}
free();
}
//查找支出表记录
void select_expend()
{
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);//申请句柄
string str1 = "use money";
string str2 = "select expend_no,expend_description,expend_money, CONVERT(varchar,GETDATE(),23) from expend where expend_no='";
cout << "请输入需要查找的记录编号：" << endl;
string ex_no;
cin >> ex_no;
string str3 = str2 + ex_no + "'";
ret = SQLExecDirect(hstmt, (SQLCHAR*)str1.c_str(), SQL_NTS);
ret = SQLExecDirect(hstmt, (SQLCHAR*)str3.c_str(), SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
SQLCHAR str1[10], str2[12], str3[10], str4[15];//用来存放从数据库获取的列信息，你有几列就定义几个变量
SQLLEN len_str1, len_str2, len_str3, len_str4;//字符串对应长度，你有几列就定义几个变量
memset(str1, 0, 1);
memset(str2, 0, 1);
memset(str3, 0, 1);
memset(str4, 0, 1);
cout << "编号" << " " << "描述" << " " << "金额" << " " << "时间" << endl;
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
//你有几列就调用几次SQLGetData函数，注意找规律哦
SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 10, &len_str1); //获取第一列数据
SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 12, &len_str2); //获取第二列数据
SQLGetData(hstmt, 3, SQL_C_CHAR, str3, 10, &len_str3);
SQLGetData(hstmt, 4, SQL_C_CHAR, str4, 15, &len_str4); //获取第三列数据
printf("%s\t%s\t%s\t%s\n", str1, str2, str3, str4);
}
}
free();
}
int init()
{
int choice, confirm;
printf(
"\n\t|__________________________________________________________________|\t\n"
"\t|1:连接数据库 |\t\n"
"\t|2:对收入表进行插入记录 |\t\n"
"\t|3:对支出表进行插入记录 |\t\n"
"\t|4:对收入表进行删除记录 |\t\n"
"\t|5:对支出表进行删除记录 |\t\n"
"\t|6:对收入表进行查找记录(按编号查找) |\t\n"
"\t|7:对支出表进行查找记录(按编号查找) |\t\n"
"\t|8:对收入表进行更新记录 |\t\n"
"\t|9:对支出表进行更新数据 |\t\n"
"\t|10:退出程序 |\t\n"
"\t|__________________________________________________________________| \t\n\n"
);
printf("\t请输入你想进行的操作：");
scanf_s(" %d", &choice);
while ((confirm = getchar()) != '\n')
continue;
while (choice != 10 && (choice < 1 || choice > 9))
{
printf("\t您的输入: '%d' 有误!请重新输入：", choice);
scanf_s(" %d", &choice);
}
return choice;
}
int main()
{
int choice;
while ((choice = init()) && choice != 10)
{
printf("\n\t程序在进行\n");
switch (choice)
{
case 1: connect() ; break;
case 2: insert_income(); break;
case 3: insert_expend(); break;
case 4: delete_income(); break;
case 5: delete_expend(); break;
case 6: select_income(); break;
case 7: select_expend(); break;
case 8:update_income_money(); break;
//case 9:BTreeDestroy(&b); break;
}
}
printf("\n\t程序结束，感谢您的使用谢谢\n");
system("pause");;
return 0;
}
