access与mssql通用写法总结(2018更新)

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/coolwu/article/details/70153521
首先说一下心得:


一、在处理数据库的时候一定要按标准的写法,否则在转库的时候会带来很大的问题
二、尽量不要用默认值,写入时传值,默认值在sql里占用一定的空间,而且在转换时会很麻烦,需要一个个设置默认值,要养成一个好习惯


注意事项:
一、acc的是/否型和sql的bit型
    判断为真:where isok<>0
    判断为假:where isok=0
    ps:isok=1可用在sql但不能用在acc中,acc用的是isok=-1或者isok (判断为假是 not isok,但不适用于sql)
    
二、access的文本型和sql的nvarchar型
    判断为空:where title='' or title is null
    判断非空:where title<>'' and title is not null
    ps:只所以用到了null是因为,如果在数据库中复制记录,那么空的文本会是null的无效值,而并非空的字符串
    
三、时间方面
    1、查询或更新
    查询通用写法where regtime='"&now()&"'"   如果是acc也可以这个where regtime=#"&now()&"#"
    注意,时间一定要包含到外面去,内部时间acc用now(),而sql用getdate()
    2、datediff时间比较
     ACC:datediff('d',regtime,'"&now()&"')<10
     MSSQL(去掉了单引号):datediff(d,regtime,'"&now()&"')<10
     注意:这里要重点说一下,按"小时"比较,在acc里是"h",而在mssql里用hh,所以为了避开这个问题,尽量转成n(分钟)来处理
    3、between时间范围
     acc用#包含,mssql用'包含
     where addtime between #"&sj1&" 00:00:01# and #"&sj2&" 23:59:59#
     where addtime between '"&sj1&" 00:00:01' and '"&sj2&" 23:59:59'
     
四、数值与字符型转换问题
      数值转字符:access用cstr,mssql中用convert(nvarchar(20),uid)
      字符转数值(首先要保证该列记录里的内容是纯数值的):access用val(id),mssql中用convert(int,id)


五、备注类型要通过cast(column as varchar)或convert(nvarchar(500),username)来使用


六、查询问题
    通常小数据使用like来查询,但在mssql中的ntext字段不能使用replace和like函数,通用的方法是用instr来判断
    access可以使用instr,但在mssql中不存在instr,需要改用charindex,注意instr和charindex的使用方法是相反的


七、无效值null运算的问题
     有时候数据库内部需要合并字串统一取出来,但遇到null内容时会报错,这时候就需要使用以下的方法转换
     access可以用 iif(name,name,'') ,意思是如果name的内容为null时,转换成空值
     mssql可以用 isnull(name,'') ,意思同上


总结:在acc和sql并存情况下,要避免时间字段上的问题
展开阅读全文

通用写法

07-23

请看我的某一个文件.aspx.cs的代码rnrn//省略USING部分rnpublic partial class _Default : System.Web.UI.Pagernrn protected void Page_Load(object sender, EventArgs e)rn rn rn rn public string show1()rn rnrn string strConnection = ConfigurationSettings.AppSettings["dns"];rn SqlConnection objConnection = new SqlConnection(strConnection);rn objConnection.Open();rn SqlCommand cmd = new SqlCommand("SQL语句1", objConnection);rn SqlDataReader dr = cmd.ExecuteReader();rn string strBody = null;rn dr.Read();rn if (条件)rn rn strBody += "内容1";rn rn elsern rn strBody += "内容2";rn rn dr.Close();rn objConnection.Close();rn return strBody;rn rnrnrn public string show2()rn rnrn string strConnection = ConfigurationSettings.AppSettings["dns"];rn SqlConnection objConnection = new SqlConnection(strConnection);rn objConnection.Open();rn SqlCommand cmd = new SqlCommand("SQL语句2", objConnection);rn SqlDataReader dr = cmd.ExecuteReader();rn string strBody = null;rn while (dr.Read())rn rn strBody += "内容3";rn rn dr.Close();rn objConnection.Close();rn return strBody;rn rnrnrn public string show3()rn rnrn string strConnection = ConfigurationSettings.AppSettings["dns"];rn SqlConnection objConnection = new SqlConnection(strConnection);rn objConnection.Open();rn SqlCommand cmd = new SqlCommand("SQL语句3", objConnection);rn SqlDataReader dr = cmd.ExecuteReader();rn string strBody = null;rn while (dr.Read())rn rn strBody += "内容5";rn rn dr.Close();rn objConnection.Close();rn return strBody;rn rnrn rn public string show4(string getString)rn rn string strConnection = ConfigurationSettings.AppSettings["dns"];rn SqlConnection objConnection = new SqlConnection(strConnection);rn objConnection.Open();rn SqlCommand cmd = new SqlCommand("SQL语句4", objConnection);rn SqlDataReader dr = cmd.ExecuteReader();rn string strBody = null;rn dr.Read();rn strBody += "" + dr[getString] + "";rn dr.Close();rn objConnection.Close();rn return strBody;rn rnrnrnrnrn所有的asp.cs文件都是这样的方式写出来的,您可以看到里面的show1 - show4 有的是相同的,只是所查询的条件不一样,所有在.aspx页面中,都是通过<%=show1()%>这样的方式读出来,我想请问下,有没有什么方法让相同部分封装成.DLL文件,然后使用,该怎么使用,请指导下,谢谢 论坛

access 升级到mssql问题

12-08

我在access中的查询 导出成sql语句后在sqlserver 查询分析器中运行结果不一致,会有哪些方面原因呢 ?rn语句如下:rnSELECT BTS.OMCID, BTS.bscId, BTS.btsSiteManagerId, BTSSM.siteName, BTS.btsId, BTS.cellName, Table_standardIndicator.standardIndicator, BTS.msTxPwrMax, BTS.btsTimeBetweenHoConfiguration, BTS.runHandOver, HANDOCM.averagingPeriod, HANDOCM.itemboundary0, HANDOCM.itemboundary1, HANDOCM.itemboundary2, HANDOCM.itemboundary3, HANDOCM.msRangeMax, HANDOCM.distHreqt, HANDOCM.rxNCellHreqave, HANDOCM.rxLevHreqave, HANDOCM.rxLevHreqt, HANDOCM.missRxLevWt, HANDOCM.rxQualHreqave, HANDOCM.rxQualHreqt, HANDOCM.missRxQualWt, HANDOCM.incomingHandOver AS HOEnabled, IIf([HANDOCM]![intraCell]=1,"enable","disable") AS intraCell, IIf([HANDOCM]![intraCellSDCCH]=1,"enable","disable") AS intraCellSDCCH, Table_rxlevDLIH.rxLevDLIH, Table_rxLevULIH.rxLevULIH, IIf([HANDOCM]![msBtsDistanceInterCell]=1,"enable","disable") AS DisE, Table_PowerBudgetE.PowerBudgetE, Table_SDCCHHOE.SDCCHHOE, Table_lrxLevDLH.lrxLevDLH, Table_lrxLevULH.lrxLevULH, Table_lrxQualDLH.lrxQualDLH, Table_lrxQualULH.lrxQualULH, HANDOCM.smallToLargeZoneHOPriority, BSC.HOSecondBestCellConfiguration, Table_directedRetryModeUsed.directedRetryModeUsed, IIf([BTS]![intraBtsDirectedRetryFromCell]=1,"allowed","not allowed") AS intraBtsDirectedRetryFromCell, IIf([BTS]![interBtsDirectedRetryFromCell]=1,"allowed","not allowed") AS interBtsDirectedRetryFromCell, IIf([BSC]![intraBscDirectedRetry]=1,"allowed","not allowed") AS intraBscDirectedRetry, IIf([BSC]![interBscDirectedRetry]=1,"allowed","not allowed") AS interBscDirectedRetry, BSC.modeModifyMandatory, HANDOCM.concentAlgoIntRxLev, HANDOCM.concentAlgoExtRxLev, HANDOCM.concentAlgoIntMsRange, HANDOCM.concentAlgoExtMsRange, HANDOCM.rxLevHreqaveBeg, HANDOCM.rxLevNCellHreqaveBegrnFROM (Table_directRetryAllowed INNER JOIN (Table_directedRetryModeUsed INNER JOIN (BSC INNER JOIN ((Table_lrxLevULH INNER JOIN ((Table_SDCCHHOE INNER JOIN (Table_PowerBudgetE INNER JOIN ((Table_rxlevDLIH INNER JOIN (Table_incomingHandOver INNER JOIN (Table_standardIndicator INNER JOIN ((BTS INNER JOIN HANDOCM ON (HANDOCM.btsId = BTS.btsId) AND (BTS.btsSiteManagerId = HANDOCM.btsSiteManagerId) AND (HANDOCM.bscId = BTS.bscId) AND (BTS.OMCID = HANDOCM.OMCID)) INNER JOIN BTSSM ON (BTS.OMCID = BTSSM.OMCID) AND (BTSSM.bscId = BTS.bscId) AND (BTS.btsSiteManagerId = BTSSM.btsSiteManagerId)) ON Table_standardIndicator.number7 = BTS.standardIndicator) ON Table_incomingHandOver.number = HANDOCM.incomingHandOver) ON Table_rxlevDLIH.numero = HANDOCM.rxLevDLIH) INNER JOIN Table_rxLevULIH ON HANDOCM.rxLevULIH = Table_rxLevULIH.numero) ON Table_PowerBudgetE.numero = HANDOCM.powerBudgetInterCell) ON Table_SDCCHHOE.number = HANDOCM.sDCCHandHOver) INNER JOIN Table_lrxLevDLH ON HANDOCM.lRxLevDLH = Table_lrxLevDLH.number) ON Table_lrxLevULH.number = HANDOCM.lRxLevULH) INNER JOIN Table_lrxQualDLH ON HANDOCM.lRxQualDLH = Table_lrxQualDLH.numero) ON (BTSSM.bscId = BSC.bscId) AND (BSC.OmcId = BTSSM.OMCID)) ON Table_directedRetryModeUsed.number = BTS.directedRetryModeUsed) ON Table_directRetryAllowed.number = BTS.intraBtsDirectedRetryFromCell) INNER JOIN Table_lrxQualULH ON HANDOCM.lRxQualULH = Table_lrxQualULH.numerornWHERE (((BTSSM.administrativeState)=1))rnORDER BY BTS.bscId, BTSSM.siteName, BTS.btsId, BTS.cellName; 论坛

access导入mssql

09-03

我想给access导入mssql中,有三个表,内容可能会一样,但是我想加入一个ID让他自动增长.rn以前是这样的:rn[code=CSS]方法一:rnEXEC sp_configure 'show advanced options', 1; rnGO rnRECONFIGURE; rnGO rnEXEC sp_configure 'Ad Hoc Distributed Queries', 1; rnGO rnRECONFIGURE; rnGO rnrnrnSELECT * rnINTO BellData rnFROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', rn 'Data Source="E:\8-longyue.mdb";User ID=Admin;Password=' )...BellDatarnrn EXEC sp_configure 'show advanced options', 0; rnGO rnRECONFIGURE; rnGO rnrn[/code]rnrn[code=CSS]方法二:rnEXEC sp_configure 'show advanced options', 1;rn  GOrn  RECONFIGURE;rn  GOrn  EXEC sp_configure 'Ad Hoc Distributed Queries', 1;rn  GOrn  RECONFIGURE;rn  GOrn  INSERT INTO 表名(字段1,字段2,字段3)rn  SELECT 字段1,字段2,字段3rn  FROM opendatasource( 'Microsoft.Jet.OLEDB.4.0',’Data Source="d:\Ilovedezai.mdb";Jetrn  OLEDB:Database Password=密码')...表名[/code]rn第二种出错我没有用rn但是现在有三个表,我必须要用第二种了,而且还要加一个ID为自己动增长形式的.rn不知道数据还是没有办法添加啊?rn有没有人知道怎么做,三个数据库的记录有很多都一样,但是我们彩铃网,所以,我们要看每个月的下载量,有以不能给以前的给更新了,只想用一个增长ID来做.rn有没有知道什么好办法,给我三个表数据库都加进去,每个ACCESS表里应该有70万数据库吧?加起来应该有200多万 论坛

没有更多推荐了,返回首页