string str="create table TESTONESTR as(select '20080401-20080421'
日期,(select propertyvalue from machineinfo where
propertycode='20' and macid=PAYMENTINFO.MACID) 营业厅,
(select machinecode from machineindex where macid=PAYMENTINFO.MACID)
设备编号,(select selectitemname from dropdownlist where ddlname='缴费状态'
and selectitemvalue=PAYSTATE) 缴费状态,count(PAYMONEY) 交易笔数,
sum(PAYMONEY) 交易金额 from PAYMENTINFO where
(paydatetime between to_date(:strdate,'yyyymmdd')
and to_date(:strenddate,'yyyymmdd')) and PAYMENTINFO.MACID
in (select macid from machineinfo where PropertyCode='20'
and propertyvalue='芙蓉区营业厅') group by PAYMENTINFO.MACID,PAYSTATE)
order by 营业厅,设备编号,缴费状态"; IDataParameter[] par ={ Base.GetParameter(":strdate", OracleType.VarChar, strdate),
Base.GetParameter(":strenddate", OracleType.VarChar, strenddate)
};
Base.ExecuteDataSet(str, par, CommandType.Text); 我在程序里面执行这个SQL语句就报错,调试了下,发现把create table testonestr 去掉就能通过,但是一加上就报 非法的变量名/编号 难道是oracle里面不能这样写? 希望哪位高手帮帮忙谢啦
日期,(select propertyvalue from machineinfo where
propertycode='20' and macid=PAYMENTINFO.MACID) 营业厅,
(select machinecode from machineindex where macid=PAYMENTINFO.MACID)
设备编号,(select selectitemname from dropdownlist where ddlname='缴费状态'
and selectitemvalue=PAYSTATE) 缴费状态,count(PAYMONEY) 交易笔数,
sum(PAYMONEY) 交易金额 from PAYMENTINFO where
(paydatetime between to_date(:strdate,'yyyymmdd')
and to_date(:strenddate,'yyyymmdd')) and PAYMENTINFO.MACID
in (select macid from machineinfo where PropertyCode='20'
and propertyvalue='芙蓉区营业厅') group by PAYMENTINFO.MACID,PAYSTATE)
order by 营业厅,设备编号,缴费状态"; IDataParameter[] par ={ Base.GetParameter(":strdate", OracleType.VarChar, strdate),
Base.GetParameter(":strenddate", OracleType.VarChar, strenddate)
};
Base.ExecuteDataSet(str, par, CommandType.Text); 我在程序里面执行这个SQL语句就报错,调试了下,发现把create table testonestr 去掉就能通过,但是一加上就报 非法的变量名/编号 难道是oracle里面不能这样写? 希望哪位高手帮帮忙谢啦
string strSql = "create table TEST2 as select * from Test ";
try
{
using (OracleConnection conn = new OracleConnection(connString))
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSql;
conn.Open();
int i=cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
而使用参数时报错:非法变量名,具体方法如下:
string strSql = "create table TEST3 as select * from Test where id=:userID "; try
{
using (OracleConnection conn = new OracleConnection(connString))
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSql;
OracleParameter param = new OracleParameter(":userID", OracleType.VarChar, 30);
param.Value="yhr";
cmd.Parameters.Add(param);
conn.Open();
int i = cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}