【网学网提醒】:网学会员鉴于大家对十分关注,论文会员在此为大家搜集整理了“SQL基础语法”一文,供大家参考学习
增:insertinto表名(字段名1,字段名2。。)values(值1,值2。。。。)注意:括号内字段名数量要和值数量一样insertinto表名values(值1,值2。。。)注意:表中字段数量要和值数量一样insertinto表名values(select*from另一张表)注意:2张表字段要一样删:delete表名注意:这是全部删除注意:这是有条件删除
delete表名where条件。。。
改:update表名set字段1=值1,字段2=值2。。。注意:这是根据值更新update表名set字段1=值1,字段2=值2。。from其他表。注意:这是用另一张表来更新update表名set字段1=值1,字段2=值2。。from其他表where条件。。。。注意:这是用另一张表有条件更新查:select*from表名注意:这是查询全部select*from表名where条件。。。注意:这是由条件查询
PrivateSubCommand1_Click()Fori=0To5Text1(i).Text=""NextiAdodc1.RecordSource="select*from"&;s1&;"orderby编号"Adodc1.RefreshIfAdodc1.Recordset.RecordCount>0ThenAdodc1.Recordset.MoveLastText1(0).Text="G"+Format((Val(Right(Trim(Adodc1.Recordset.Fields("编号")),4))+1),"0000")ElseText1(0).Text="G0001"EndIfEndSubPrivateSubCommand2_Click()IfAdodc1.Recordset.EOF=FalseThenc=MsgBox("您确认要删除该记录吗?",vbOKCancel,"删除提示信息")Ifc=vbOKThenAdodc1.Recordset.DeleteAdodc1.RecordSource="select*from人员表"Adodc1.RefreshEndIfElseMsgBox"当前数据库中没有可删除的数据记录",vbOKOnly,"提示信息"EndIfEndSubPrivateSubCommand3_Click()IfText1(0).Text=""OrText1(1).Text=""ThenMsgBox"请选择需要改动的记录信息!",vbOKOnly,"错误提示"Elsec=MsgBox("确定要修改该记录吗?",vbOKCancel,"提示信息")Ifc=vbOKThen'如果确认修改的话进行修改操作IfText1(1).Text=""ThenMsgBox"不能为空值!",48,"修改信息提示"Else'连接所要修改的数据库con.Open"Provider=SQLOLEDB.1;Password=2752;PersistSecurityInfo=True;UserID=sa;InitialCatalog=sample;DataSource=JAMLEEPC"'开始修改数据库con.Execute("UPDATE"&;s1&;"SET姓名='"&;Text1(1).Text&;"',年龄="&;Text1(2).Text&;",学历='"&;Text1(3).Text&;"',年级="&;Text1(4).Text&;",入学时间='"&;Text1(5).Text&;"'where编号='"&;Trim(Text1(0))&;"'")MsgBox"信息修改成功",64,"修改信息提示"
con.CloseAdodc1.RecordSource="select*from人员表"Adodc1.RefreshEndIfEndIfEndIfEndSubPrivateSubCommand4_Click()Adodc1.RecordSource="select*from人员表where编号='"+Text1(0).Text+"'"Adodc1.RefreshIfAdodc1.Recordset.RecordCount>0ThenMsgBox"该信息已存在,信息保存不成功",64,"保存信息提示"Elsecc=MsgBox("您确定要保存该信息吗?",33,"信息保存提示")Ifcc=vbOKThenIfText1(1).Text=""Or
Text1(2).Text=""OrText1(3).Text=""OrText1(4).Text=""OrText1(5).Text=""ThenMsgBox"请确认,人员的、年龄、学历、年级和入学时间不能为空",48,"保存信息提示"Elsecon.Open"Provider=SQLOLEDB.1;Password=2752;PersistSecurityInfo=True;UserID=sa;InitialCatalog=sample;DataSource=JAMLEEPC"con.Execute("insertinto人员表values('"&;Text1(0).Text&;"','"&;Text1(1).Text&;"','"&;Text1(2).Text&;"','"&;Text1(3).Text&;"','"&;Text1(4).Text&;"','"&;Text1(5).Text&;"')")MsgBox"信息修改成功",64,"修改信息提示"con.CloseAdodc1.RecordSource="select*from人员表"Adodc1.RefreshEndIfElseEndIfEndIfSetDataGrid1.DataSource=Adodc1EndSub给你段代码参考下,这个就是添加,删除,修改的代码。不过我用的是SQL2000你要吧代码稍微修改一下的,呵呵,祝你成功!
如何实现将vsflexgrid中修改的数据反馈到数据库中?PrivateSubvsflexgrid1_AfterEdit(ByValRowAsLong,ByValColAsLong)rs.MoveFirst'//rs为记录集rs.Movevsflexgrid1.Row-1rs.EditIfvsflexgrid1.text=""Thenrs.Fields(vsflexgrid1.Col-1)=NullElsers.Fields(vsflexgrid1.Col-1)=vsflexgrid1.textEndIfrs.Updateendsub
一、增加记录使用for来循环表格行。fori=1togrid1.rows-1withrs.addnew.fileds(o)=grid1.textmariy(i,0).fileds(1)=grid1.textmariy(i,1).fileds(2)=grid1.textmariy(i,2).fileds(3)=grid1.textmariy(i,3).updateendwithnext二、添加行grid1.additemrow三、删除当前行withgrid1i=.row.removeitemiendwith四、要显示下拉框,可以使用vsflexgrid中列绑定功能grid1.colcombolist(1)=grid.buildcombolist(rs,"商品名称")跟楼上的相比,仅仅是datamode不一样(2-flexDMBoundBatch)但这样做的优势是非常明显的:可以撤销包括新增删除在内的所有操作,按保存键才写入数据库PrivateSubCmdDel_Click()Iffg.Row<>0Thenfg.RemoveItem(fg.Row)fg.RefreshEndSub
PrivateSubCmdAdd_Click()
OnErrorResumeNextAdodc1.Recordset.AddNewIfErr.Number<>0ThenMsgBoxErr.DescriptionEndSub
PrivateSubCmdUpdate()Adodc1.Recordset.UpdateBatchadAffectAllChaptersEndSub
PrivateSubCmdCancel_Click()Adodc1.Recordset.CancelBatchfg.DataRefreshEndSubPrivateSubForm_Load()Adodc1.ConnectionString="FILENAME="&;App.Path&;"\conn.dsn"Adodc1.LockType=adLockBatchOptimisticAdodc1.RecordSource="Your_Tablename"Setfg.DataSource=Adodc1EndSub1、打印vsflexgrid可以使用vsprinter打印控件。跟vsflexgrid配套使用效果不错。2、导出EXECL,可以使用grid.savegrid的方法。用savegrid的方法,在导出execl时,如果碰到类似于银行帐号的列如:“6465456665”,导到EXECL中就不这样显示了,这个问题还不知道怎么解决??另外也可以写代码(这个方法比较实用,但慢一些):DimexcelAppAsExcel.Application
SetexcelApp=NewExcel.ApplicationOnErrorResumeNextIfexcelAppIsNothingThenSetexcelApp=CreateObject("Excel.application")IfexcelAppIsNothingThenExitSubEndIfEndIfexcelApp.Visible=TrueMe.MousePointer=vbHourglassexcelApp.Workbooks.AddWithexcelApp.ActiveSheetDimiAsInteger,jAsIntegerFori=1ToGrid1.rowsForj=1ToGrid1.Cols
.Cells(i,j).value="'"&;Grid1.TextMatrix((i-1),(j-1))'加上“'”号则可以解决上面savegrid中银行帐号的导出问题。NextjDoEventsNextiEndWithMe.MousePointer=vbDefaultSetexcelApp=NothingEndSubEXCEL同Vsflexgrid通过最近很多的朋友,都想知道EXCEL怎样同VSflexgrid交换数据。实际上,利用“复制”“粘贴”菜单即可实现。具体如下:、(1)在Vsflexgrid上弹出右键菜单PrivateSubgrid1_MouseDown(ButtonAsInteger,ShiftAsInteger,XAsSingle,yAsSingle)ifButton=2ThenPopupMenumnutccdEndSub(2)设置各菜单的内容A复制Clipboard.ClearClipboard.SetTextgrid1.ClipB剪切DimrowcAsLongDimrowzAsLongDimcolcAsLongdimcolzAsLongdimiaslongdimsaslongIfgrid1.Rows=1ThenExitSubClipboard.ClearClipboard.SetTextgrid1.ClipIfgrid1.RowSel>grid1.rowThenrowc=grid1.rowrowz=grid1.RowSelElserowc=grid1.RowSelrowz=grid1.rowEndIfIfgrid1.ColSel>grid1.ColThencolc=grid1.Colcolz=grid1.ColSelElsecolc=grid1.ColSelcolz=grid1.Col
EndIfFori=rowcTorowzFors=colcTocolzgrid1.TextMatrix(i,s)=""NextNextC粘贴(精华部分)DimiAsLongDimsAsLongDimmAsLongDimtAsLongIfgrid1.Rows=1ThenExitSubt=Len(Clipboard.GetText)Ift=0ThenExitSubFori=1TotIfMid(Clipboard.GetText,i,1)=Chr(9)Thens=s+1IfMid(Clipboard.GetText,i,1)=Chr(13)Thenm=m+1NextIfs/(m+1)+grid1.Col>grid1.Cols-1Thengrid1.ColSel=grid1.Cols-1Elsegrid1.ColSel=s/(m+1)+grid1.ColEndIfIfgrid1.row+m>grid1.Rows-1Thengrid1.RowSel=grid1.Rows-1Elsegrid1.RowSel=grid1.row+mEndIfgrid1.Clip=Clipboard.GetText
VSFlexGrid常用属性或方法:.FixedRows=1.FixedCols=1p;'固定几列.Editable=True.AllowUserResizing=flexResizeBoth.FocusRect=flexFocusNone.SelectionMode=flexSelectionListBox.BackColor=RGB(255,255,255).BackColorSel=vbBlue.BackColorFixed=RGB(208,192,160).BackColorAlternate=RGB(255,250,230)
'固定几行&;nbs'允许修改'可调整行/列'无虚框'焦点选中样式'单元背景色'单元选择色'固定单元色'间隔行背景色
.GridColor=RGB(245,240,210)'单元线条色.ForeColor=RGB(0,0,0)'单元前景色(字符色).RowHeightMin=260'最小行高.RowHeightMax=800'最大行高.ColHeightMin=50'最小列宽.ColHeightMax=3000'最大列宽.ColWidth(Col)=1000'指定列宽.RowHeight(Row)=260'指定行高.TextMatrix(Row,Col)="Text"'指定单元字符.Text="Text"'选
定单元字符.MergeCol(Col)=True'允许合并列.MergeRow(Row)=True'允许合并行.MergeCells=0|1|2|3|4|5|6'合并选项.Cell(选项准则,Row1,Col1,Row2,Col2)'选择部分的相应准则值.EditCell'当移动到当前单元时自动选择.EditSelStart'移动到单元时的光标位置.MousePointer'设置对象的鼠标指针样式O.A=0到15|99━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━几个特殊的属性方法的使用:FormatString属性:管道符格式化字符串示例:下面定义对齐方式同字意,列宽窄同距离VSG1.FormatString="^中|<左|>右|>右|^中"+++++++++++++++++++++++++搜索(查找)表格中符合条件的行:FindRow属性:该属性返回一个行值MsgBoxVSG1.FindRow(关键词,[指定行],[指定列],[敏感],[精度])关键词:String,表示要搜索的字符串指定行/指定列:Long,表示只在指定的行或列中找敏感:Boolean,━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━'限制只能在指定列输入(这里默认倒数第2列)PrivateSubVSG1_BeforeRowColChange(ByValOldRowAsLong,_ByValOldColAsLong,ByValNewRowAsLong,_ByValNewColAsLong,CancelAsBoolean)VSG1.Editable=flexEDKbdIfVSG1.Redraw<>flexRDNoneAndNewCol<>VSG1.Cols-2ThenCancel=TrueVSG1.SelectNewRow,VSG1.Cols-2EndIfEndSub━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━限制不能编辑某些列:(这里限制第1列和第3列)
PrivateSubVSG1_RowColChange()IfVSG1.Col=1OrVSG1.Col=3ThenVSG1.FocusRect=flexFocusNoneVSG1.Editable=flexEDNone'SendKeys"{TAB}"ElseVSG1.Editable=flexEDKbdSendKeys"{ENTER}"EndIfEndSub或:PrivateSubVSG1_RowColChange()IfVSG1.Col=1OrVSG1.Col=3ThenSendKeys"{RIGHT}"ElseSendKeys"{ENTER}"EndIfEndSub或:PrivateSubVSG1_RowColChange()IfVSG1.Col=1OrVSG1.Col=3ThenVSG1.Editable=flexEDNoneElseVSG1.Editable=flexEDKbdVSG1.EditCell'自动选择单元内容VSG1.EditSelStart=0[选到最前]|1[选到指定]|Len(VSG1.Text)[选到最后]EndIfEndSub━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━'对齐方式(-1标示所有).ColAlignment(-1)=flexAlignLeftCenter|flexAlignCenterCenter|flexAlignRightCenter示例1:(最后一行的第3列靠右对齐)VSG1.SelectVSG1.Rows-1,2VSG1.CellAlignment=flexAlignRightCenter示例2:VSG1.Row=VSG1.Rows-1:VSG1.Col=1VSG1.CellAlignment=flexAlignRightCenter示例3:VSG1.Cell(flexcpAlignment,VSG1.Rows-1,1,VSG1.Rows-1,3)=flexAlignRightCenter━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━本对象拖放:PrivateSubVSG1_MouseDown(ButtonAsInteger,_ShiftAsInteger,XAsSingle,YAsSingle)VSG1.Drag
VSG1.DragI
con=LoadPicture("D:\Icon.ico")VSG1.DragRowVSG1.RowSelEndSub或从其它对象拖:PrivateSubVSG2_MouseDown(ButtonAsInteger,_ShiftAsInteger,XAsSingle,YAsSingle)VSG2.OLEDragVSG1.OLEDropMode=flexOLEDropAutomaticEndSub━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━PublicSubVSGridCount(OptionalSelRowAsLong,OptionalSelColAsLong)DimXAsLong,iAsLongDimHj1,Hj2,Hj3AsStringConstA1=-922337203685477#,A2=922337203685477#OnErrorGoToErrTransactWithfrmFG.VSG1X=.Rows-1.MergeCells=flexMergeFree.MergeRow(X)=True.Cell(flexcpText,X,0,X,1)="合计"If.Rows=3Then.TextMatrix(X,0)=0.Cell(flexcpText,X,2,X,14)="".Cell(flexcpText,X,16,X,17)="¥0.00"ExitSubEndIf'Hj1=Val(.Aggregate(flexSTSum,2,2,X-1,2))'Hj2=Val(.Aggregate(flexSTSum,2,16,X-1,16))'Hj3=CurrencyToStr(Hj2)Fori=2ToX-2Hj1=Hj1+Val(.TextMatrix(i,15))IfVal(.TextMatrix(i,16))>0ThenHj2=Hj2+Val(.TextMatrix(i,15))*Val(.TextMatrix(i,16))EndIfNextiIfHj2<=A1OrHj2>=A2ThenGoToErrTransactEndIfHj3=CurrencyToStr(Hj2).TextMatrix(X,&;nbsp;2)=Hj1.Cell(flexcpText,X,3,X,15)=IIf(Hj3="","",Hj3).Cell(flexcpText,X,16,X,17)=Format(Hj2,"¥0.00").Cell(flexcpAlignment,X,2,X,14)=flexAlignLeftCenter
'.SelectX,3'.CellAlignment=flexAlignLeftCenterIfSelRow>1AndSelCol>0Then.SelectSelRow,SelColEndWithExitSubErrTransact:MsgBox"你输入的数字过大无法计算!请修改!!"!EndSub-----------------------------------------------将数字转换为大写金额的函数:FunctionCurrencyToStr(ByValNumberAsCurrency)AsStringNumber=Val(Trim(Number))IfNumber=0ThenCurrencyToStr="":ExitFunctionDimstr1AryAsVariant,str2AryAsVariantstr1Ary=Split("零壹贰叁肆伍陆柒捌玖")str2Ary=Split("分角元拾佰仟万拾佰仟亿拾佰仟万拾佰")DimaAsLong,bAsLong'循环基数Dimtmp1AsString'临时转换Dimtmp2AsString'临时转换结果DimPointAsLong'小数点位置IfNumber<=-922337203685477#OrNumber>=922337203685477#ThenExitFunctionEndIftmp1=Round(Number,2)tmp1=Replace(tmp1,"-","")'先去掉“-”号Point=InStr(tmp1,".")'取得小数点位置IfPoint=0Then'如果有小数点,最大佰万亿b=Len(tmp1)+2'加2位小数Elseb=Len(Left(tmp1,Point+1))'包括点加2位小数EndIf''先将所有数字替换为中文Fora=9To0Step-1tmp1=Replace(Replace(tmp1,a,str1Ary(a)),".","")NextFora=1Tobb=b-1IfMid(tmp1,a,1)<>""ThenIfb>UBound(str2Ary)ThenExitFortmp2=tmp2&;Mid(tmp1,a,1)&;str2Ary(b)EndIfNextIftmp2=""ThenCurrencyToStr="":ExitFunction''〓下面为非正式财务算法,可以去掉〓
Fora=1ToLen(tmp2)tmp2=Replace(tmp2,"零亿","亿零")tmp2=Replace(tmp2,"零万","万
零")tmp2=Replace(tmp2,"零仟","零")tmp2=Replace(tmp2,"零佰","零")tmp2=Replace(tmp2,"零拾","零")tmp2=Replace(tmp2,"零元","元")tmp2=Replace(tmp2,"零零","零")tmp2=Replace(tmp2,"亿万","亿")Next''〓上面为非正式财务算法,可以去掉〓IfPoint=1Thentmp2="零元"+tmp2IfNumber<0Thentmp2="负"+tmp2IfPoint=0Thentmp2=tmp2+"整"CurrencyToStr=tmp2EndFunction━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━Cell属性的使用:Cell的作用是给以选定区块的特定的设置值,具体值可参阅相应属性值。语法:Cell(条件准则,Row1,Col1,Row2,Col2)=相应准则值其中的“条件准则”有以下准则常数,根据准则的不同而设置相应准则的值:flexcpAlignment对齐方式flexcpBackColor背景色flexcpChecked选择框flexcpCustomFormat格式设置flexcpData日期flexcpFloodColor颜色flexcpFloodPercent背景色flexcpFont字体flexcpFontBold粗体flexcpFontItalic斜体flexcpFontName字体名flexcpFontSize字体大小flexcpFontStrikethru删除线flexcpFontUnderline下划线flexcpFontWidth字符宽flexcpForeColor字符色flexcpHeight高flexcpLeft左flexcpPicture添加图flexcpPictureAlignment图对齐flexcpRefresh刷新flexcpSort分类flexcpText字符
flexcpTextDisplay显示字符flexcpTextStyle文本样式flexcpTop返回顶端高,同RowPos和valueMatrix属性flexcpvalue返回字符值flexcpVariantvalue返回字符值flexcpWidth返回单元宽━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━flexSTSum方法:每行增加小计行[这个方法还不会用]语法:VSG.flexSTSum常数名,[GroupOnAsLong],:标签列[TotalOnAsLong],:计算列[FormatAsString],:格式,例"$0.00"[BackColorAsColor],:Color[ForeColorAsColor],:Color[FontBoldAsBoolean],:False|True[CaptionAsString],:例"数%s"[MatchFromAsInteger],:0|1|2|3[TatalOnlyAsBoolean]:False|True常数名:常数常数值说明flexSTNone0大纲唯一的,没有合计价值flexSTClear1清除全部的小计flexSTSum2总数flexSTPercent3总数的百分比flexSTCount4行数flexSTAverage5平均flexSTMax6最大的flexSTMin7最小的flexSTStd8标准偏差flexSTVar9方差━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━MousePointer、MouseIcon鼠标指针的应用:0=flexDefault1=flexArrow2=flexCross3=flexIBeam4=flexIcon5=flexSize6=flexSizeNESW7=flexSizeNS8=flexSizeNWSE9=flexSizeEW10=flexUpArrow11=flexHourglass
12=flexNoDrop13=flexArrowHourGlass14=flexArrowQuestion15=flexSizeAll50=flexPointerCopy'(&;H32)'拖动带拷贝51=flexPointerMove'(&;H33)'拖动52=flexSizeHorz'(&;H34)'左右调整53=flexSizeVert'(&;H35)'上下调整54=flexHand'(&;H36)手型99=flexCustom'自定义
ConstMA="50,51,52,53,54"DimxyAsIntegerxy=Val(Text1.Text)Ifxy>15Andxy<>99AndInStr(MA,xy)=0Thenxy=15VSG1.MousePointer=x
冒泡排序PrivateSubCommand1_Click()List1.AddItemText1.TextEndSubPrivateSubCommand2_Click()Dimi,jAsIntegerDimstrTAsStringList2.ClearFori=0ToList1.ListCount-1List2.AddItemList1.List(i)NextFori=0ToList1.ListCount-1Forj=i+1ToList2.ListCount-1IfVal(List2.List(i))>Val(List2.List(j))ThenstrT=Val(List2.List(i))List2.List(i)=Val(List2.List(j))List2.List(j)=strTEndIfNextjNextiEndSub