none
关于VB中数据库连接问题 RRS feed

  • 问题

  • 关于VB中数据库连接问题

    同一个数据库PUBS在同一台电脑上用同一个连接字符串连接,但一个连接并显示,而另一个

    则不能连接和显示,究竟问题出在什么地方?

    不显示的程序段:

     'Declare object..
       
      Dim objConnection As New SqlConnection("Integrated Security=True;Persist Security Info=False;database = PUBS")  

       Dim objDataAdapter As New SqlDataAdapter( _
              "SELECT authors.au_lname, au_fname, title, price " & _
              "titles.title_id, title, price " & _
              "FROM authors " & _
              "JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
              "JOIN titles ON titleauthor.title_id = titles.title_id " & _
              "ORDER BY au_lname, au_fname ", objConnection)
        Dim objDataSet As DataSet
        Dim objDataView As DataView
        Dim objCurrencyManager As CurrencyManager
        Private Sub FillDataSetAndView()
            'Initialize a new instance of DataSet object..
            objDataSet = New DataSet()

            'Fill the DataSet object with data..
            objDataAdapter.Fill(objDataSet, "authors")

            'Set the DataView object to the DataSet object..
            objDataView = New DataView(objDataSet.Tables("authors"))

            'Set our CurrencyManager object to the DataView object..
            objCurrencyManager = CType(Me.BindingContext(objDataSet), CurrencyManager)

        End Sub
        Private Sub BindFields()
            'Clear any previous bindings..
            txtLastName.DataBindings.Clear()
            txtFirstName.DataBindings.Clear()
            txtBookName.DataBindings.Clear()
            txtPrice.DataBindings.Clear()

            'Add new bindings to the daytaView object..
            txtLastName.DataBindings.Add("Text", objDataView, "au_lname")
            txtFirstName.DataBindings.Add("Text", objDataView, "au_fname")
            txtBookName.DataBindings.Add("Text", objDataView, "title")
            txtPrice.DataBindings.Add("Text", objDataView, "price")

            'Display a ready status..
            ToolStripStatusLabel1.Text = "Ready"
        End Sub
        Private Sub ShowPosition()
            'Always format the number in the txtPrice field to include cents.
            Try
                txtPrice.Text = Format(CType(txtPrice.Text, Decimal), "##0.00")
            Catch ex As System.Exception
                txtPrice.Text = "0"
                txtPrice.Text = Format(CType(txtPrice.Text, Decimal), "##0.00")

            End Try
            'Display the Current position and the number of records.
            txtRecordPosition.Text = objCurrencyManager.Position + 1 & " of " & objCurrencyManager.Count()

        End Sub

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            'Add item to the combo bnox..
            cboField.Items.Add("Last Name")
            cboField.Items.Add("First Name")
            cboField.Items.Add("Book Title")
            cboField.Items.Add("Price")

            'Make the first item selected..

            cboField.SelectedIndex = 0

            'Fill the DataSet and bind the fields..
            FillDataSetAndView()
            BindFields()

            'Show the current record position..
            ShowPosition()
        End Sub


        Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
            'Set the record position to the first record..
            objCurrencyManager.Position = 0

            'Show the current record position..
            ShowPosition()

        End Sub

        Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
            'Set the record position to the Last record..
            Try
                objCurrencyManager.Position = objCurrencyManager.Count - 1
            Catch ex As System.Exception

            End Try

            'Show the current record position..
            ShowPosition()
        End Sub

     

        Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
            'Move to the previous record..
            objCurrencyManager.Position -= 1
            'Show the current record position..
            ShowPosition()
        End Sub


        Private Sub btnMoveNext_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
            'Move to the next record..
            objCurrencyManager.Position += 1

            'Show the current record position..
            ShowPosition()
        End Sub
    End Class

    能显示的程序段:

    'Import Data and SqlClient namespace..
    Imports System.Data
    Imports System.Data.SqlClient


    Public Class Form1
        Inherits System.Windows.Forms.Form

        'Dim objConnection As New SqlConnection("server=local;database=PUBS;use id=sa;password=wrox")
        Dim objConnection As New SqlConnection("Integrated Security=True;Persist Security Info=False;database = PUBS")
        Dim objDataAdapter As New SqlDataAdapter()
        Dim objDataSet As New DataSet()


        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            'Set the SelectCommand properties..
            objDataAdapter.SelectCommand = New SqlCommand()
            objDataAdapter.SelectCommand.Connection = objConnection
            objDataAdapter.SelectCommand.CommandText = _
                "SELECT au_lname, au_fname, title, price " & _
                "FROM authors " & _
                "JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
                "JOIN titles ON titleauthor.title_id = titles.title_id " & _
                "ORDER BY au_lname, au_fname "
            objDataAdapter.SelectCommand.CommandType = CommandType.Text

            'Open the database connection..
            objConnection.Open()

            'Fill the dataSet object with data..
            objDataAdapter.Fill(objDataSet, "authors")


            'Close the dataset connnection..

            objConnection.Close()

            'Set the DataGridView properties to bind it to our data..
            grdAuthorTitles.AutoGenerateColumns = True
            grdAuthorTitles.DataSource = objDataSet
            grdAuthorTitles.DataMember = "authors"


            'Declare and Set the currency header alignment property..
            Dim objAlignrightcellstyle As New DataGridViewCellStyle
            objAlignrightcellstyle.Alignment = DataGridViewContentAlignment.MiddleRight

            'Declare and set the alternating rows style..

            Dim objAcurrencyCellStyle As New DataGridViewCellStyle()
            objAcurrencyCellStyle.Format = "C"
            objAcurrencyCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            'change column name and styles using the column index
            grdAuthorTitles.Columns(0).HeaderText = "Last Name"
            grdAuthorTitles.Columns(1).HeaderText = "First Name"
            grdAuthorTitles.Columns(2).HeaderText = "Book Title"
            grdAuthorTitles.Columns(2).Width = 205

            Me.Width = 700
            Me.Height = 400

            'Me.grdAuthorTitles.Width = 675
            'Me..Width = 375
            grdAuthorTitles.Dock = DockStyle.Fill
            'change column name and styles using the column name
            grdAuthorTitles.Columns("price").HeaderCell.Value = "Retail Price "
            grdAuthorTitles.Columns("price").HeaderCell.Style = objAlignrightcellstyle
            grdAuthorTitles.Columns("price").DefaultCellStyle = objAcurrencyCellStyle

            'Clean up..
            objDataAdapter = Nothing
            objConnection = Nothing
            objAcurrencyCellStyle = Nothing
            objAcurrencyCellStyle = Nothing
            objAlignrightcellstyle = Nothing

        End Sub
    End Class



    2012年2月24日 5:56

答案