VisualBasic數據庫開發疑難問題解
［前言：］我始終認為Visual Basic是數據庫開發最便捷的工具，我在論壇中看到不少朋友提出的關于VB數據庫開發方面的問題，有許多問題是反復提出，也有些我認為是比較典型的問題，我將這些問題收錄在一起同時提出答案，便于網友們查閱，如果你還有社呢們提可以給我寫信。問：如何顯示格式為03-3-13的日期？
解決的方法：
| 1 Cmd.CommandText = "select * from 支出 where 日期=03-3-13" 中 03-3-13=-13。 日期實際上是Double型數字。0 是 1899-12-30，-13 是 1899-12-17。你當然沒有這樣日期的記錄，所以只有大于才行。 2 Cmd.CommandText = "select * from 支出 where 日期=#03-3-13#" 凡是沒有明示，文字型日期是按美國習慣解釋的，#03-3-13# 是 0013-03-03。 或者使用長日期格式： Cmd.CommandText = "select * from 支出 where 日期=#2003-3-13#" 用格式化函數： Cmd.CommandText = "select * from 支出 where 日期=#" & format(mydate,"yyyy-mm-dd") & "#" |
問： 如何判斷DNS是否存在？怎樣才能列舉出所有的DNS?
解決方法：
| 1、通過利用ODBC API中的SQLDataSource函數可以取得ODBC API中數據源的列表。 判斷DNS是否存在： 2、使用API函數Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv As Long, ByVal fDirection As Integer, ByVal szDSN As String, ByVal cbDSNMax As Integer, pcbDSN As Integer, ByVal szDescription As String, ByVal cbDescriptionMax As Integer, pcbDescription As Integer) As Integer Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (ByRef env As Long) As Long 列舉出所有DNS。 |
問：處理文本文件是導入數據庫還是直接讀寫文件呢？
解決方法：
| Set main = bumony.OpenRecordset("main") Open App.Path & "sources" & Text1.Text & "´úÀíÒµÎñ" & Text1.Text & ".txt" For Input As #1 Do While Not EOF(1) Line Input #1, str1 With main .AddNew !code = Mid(str1, 1, 5) !date = Text1.Text If Mid(str1, 1, 5) = "21310" Or Mid(str1, 1, 5) = "21311" Or Mid(str1, 1, 5) = "21410" Or Mid(str1, 1, 5) = "21411" Then !Money = Trim(Mid(str1, 7, 10)) Else !Money = Trim(Mid(str1, 7, 10)) & "0000" End If !whao = "1102" !ywhao = "1102" .Update End With Loop Close #1 main.Close |
問：調用SQL存儲后有參數返回,應該怎么賦值?
解決方法：
Dim ADOCmd As New ADODB.Command Dim ADOPrm As New ADODB.Parameter Dim ADORs As ADODB.Recordset '.... Set ADOCmd.ActiveConnection = ADOCon With ADOCmd .CommandType = adCmdStoredProc .CommandText = "ADOTestRPE" End With sParmName = "Output" Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, adParamOutput) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = 999 Set ADORs = ADOCmd.Execute '..... Debug.Print "Output: " & ADOCmd.Parameters("Output").Value |
問： SQL Server 2000中如何存取圖片信息？
解決方法:
新建一個工程,添加 ado 控件,2個 Command ,1個 Picture,1個 Image Dim Chunk() As Byte Dim lngLengh As Long Dim intChunks As Integer Dim intFragment As Integer Const ChunkSize = 1000 Const lngDataFile = 1 Private Sub cmdBrowse_Click() On Error Resume Next With cmdlFilePath .Filter = "JPG Files|*.JPG|Bitmaps|*.BMP" .ShowOpen txtFilePath.Text = .filename End With End Sub Private Sub Savepic() Open "c:colordraw0094_m.jpg" For Binary Access Read As lngDataFile lngLengh = LOF(lngDataFile) If lngLengh = 0 Then Close lngDatafile: Exit Sub intChunks = lngLengh ChunkSize intFragment = lngLengh Mod ChunkSize 'OpenData 打開數據庫 Dim i As Integer Dim rs As New ADODB.Recordset Dim strQ As String If rs.State = adStateOpen Then rs.Close strQ = "Select * From [mydata]" rs.Open strQ, conn, adOpenStatic, adLockOptimistic On Error Resume Next rs.AddNew ReDim Chunk(intFragment) Get lngDataFile, , Chunk() rs.Fields("rs_photo1").AppendChunk Chunk() ReDim Chunk(ChunkSize) For i = 1 To intChunks Get lngDataFile, , Chunk() rs.Fields("rs_photo1").AppendChunk Chunk() Next i rs.Update rs.Close Close lngDataFile Call ShowPic End Sub Public Sub ShowPic() 'OpenData 打開數據庫 Dim i As Integer Dim rs As New ADODB.Recordset Dim strQ, filename As String If rs.State = adStateOpen Then rs.Close strQ = "Select * From [mydata]" rs.Open strQ, conn, adOpenStatic, adLockOptimistic If rs.EOF <> True Then rs.MoveLast Else Exit Sub End If On Error Resume Next Open "pictemp" For Binary Access Write As lngDataFile lngLengh = rs.Fields("rs_photo1").ActualSize intChunks = lngLengh ChunkSize intFragment = lngLengh Mod ChunkSize ReDim Chunk(intFragment) Chunk() = rs.Fields("rs_photo1").GetChunk(intFragment) Put lngDataFile, , Chunk() For i = 1 To intChunks ReDim Buffer(ChunkSize) Chunk() = rs.Fields("rs_photo1").GetChunk(ChunkSize) Put lngDataFile, , Chunk() Next i Close lngDataFile filename = "pictemp" Picture1.Picture = LoadPicture(filename) Image1.Stretch = True Image1.Picture = Picture1.Picture Kill filename End Sub Private Sub Command1_Click() Savepic End Sub Private Sub Command2_Click() ShowPic End Sub 上面寫的是acess的代碼!樓主可以改一下連接數據庫的設置代碼用于sql server! |