Member Avatar for Xiao_1

in existing project of combobox not just show the the project No. and also show the textbox of the project name

this is my code is it corrct:

  Private Sub DataOfProject_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb"


        If conn.State = ConnectionState.Open Then conn.Close()
        conn.Open()
        Dim sql As String = "Select [ProjectNo] From tbl_assignment "
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql, conn)
        sqlCom.Connection = conn
        Dim sqlReader As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

        While sqlReader.Read = True

            cbbproject.Items.Add(sqlReader("ProjectNo").ToString)
        End While

        sqlReader.Close()
        sqlCom.Dispose()
        conn.Close()
    End Sub

    Private Sub cbbproject_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbbproject.SelectedIndexChanged

        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb"


        If conn.State = ConnectionState.Open Then conn.Close()
        conn.Open()
        Dim sql As String = "Select * From tbl_assignment Where ProjectNo ='" & cbbproject.SelectedItem.ToString & "' AND Projcet Name'" & txtpn.Text & "' "
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql, conn)
        sqlCom.Connection = conn
        Dim sqlReader As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader

        While sqlReader.Read = True

            txtpn.Text = (sqlReader("Project Name").ToString)



        End While
        sqlReader.Close()
        sqlCom.Dispose()
        conn.Close()
    End Sub
2.jpg 72.83 KB
Edited by Reverend Jim because: Corrected formatting
  • 4 Contributors
  • 8 Replies
  • 231 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Mr.M

Dani AI

Generated

Short summary and a clean, reliable fix.

The original loop adds only project numbers to the ComboBox and then tries to re-query for the project name; that produced a malformed SQL string and a SelectedItem/SelectedIndex misuse. and correctly warned about field names with spaces (use brackets or, better, rename the column). 's reminder about naming conflicts is also useful—avoid naming controls or variables that shadow framework types.

A simpler, safer pattern is to load both ProjectNo and ProjectName once, bind the results to the ComboBox, and read the project name from the bound value. This avoids string-concatenation SQL, extra readers on selection, and trouble when the stored column has a space (you can alias it to a safe name).

' Load and bind once (e.g. Form.Load)
Dim connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb"
Using conn As New OleDb.OleDbConnection(connStr)
    conn.Open()
    Dim dt As New DataTable()
    Using da As New OleDb.OleDbDataAdapter("SELECT ProjectNo, [Project Name] AS ProjectName FROM tbl_assignment", conn)
        da.Fill(dt)
    End Using
    cbbproject.DisplayMember = "ProjectNo"
    cbbproject.ValueMember = "ProjectName"
    cbbproject.DataSource = dt
End Using
' ComboBox.SelectedIndexChanged
Dim v = cbbproject.SelectedValue
txtpn.Text = If(v IsNot Nothing AndAlso Not Convert.IsDBNull(v), v.ToString(), String.Empty)

Troubleshooting notes: if the ComboBox shows "System.Data.DataRowView", set DisplayMember/ValueMember correctly; if SelectedValue is Nothing check DataSource and SelectedIndex; avoid hard-coded paths (move connection string to config); wrap DB calls in Using and Try/Catch; if you must query per-selection, use parameterized commands (Access OleDb uses positional parameters).

Recommended Answers

All 8 Replies

Member Avatar for Mr.M

Well by the look at that snap shoot it indicate that the word "Collection" is already reserved so you can't use that if you want to continue with it you can do like "cCollection" just like the word "Name" is reserved on a database so to use it I normally do like this "nName"

Member Avatar for Mr.M

Ow sorry about that the error says "ObjectCollection" meaning one of you control collection's I think its your ComboBox's Collection items is using the reserved name so try putting one by one and test to see which collection item is reserved.

Member Avatar for Minimalist

Field names with spaces in them will create problems as in "Projcet Name"
better to use Projcet_Name or brackets "[Projcet Name]"

Member Avatar for Santanu.Das

better to use Projcet_Name or brackets "[Projcet Name]"

Yes, This is right.
If a Field Name contains one or multiple space, in Sql Statement cover it withen sqare brackets, like [Projcet Name].
If possible don't use space between two words to create a field. Always use underscore "_" in between two words of the field name.

No your codes are not correct. there are some mistakes.

At line No 15 you write cbbproject.Items.Add(sqlReader("ProjectNo").ToString). It is incorrect. It should be

cbbproject.Items.Add(sqlReader.Item("ProjectNo").ToString)

At line 31 you write

Dim sql As String = "Select * From tbl_assignment Where ProjectNo ='" & cbbproject.SelectedItem.ToString & "' AND Projcet Name'" & txtpn.Text & "' "

In this line cbbproject.SelectedItem.ToString is incorrect. through Combobox or ListBox SelectedItem property we can Get or Set currently selected item. It never returns the Text Value of currently selected item.
It should be

cbbproject.Items(cbbproject.SelectedIndex)

THe portion of that line i.e. AND Projcet Name'" & txtpn.Text & "' " is needless.
As per your codes, when combox selected index changes, the Datareader tries to show the "Project Name" matching with the ProjectNo in the textbox "txtpn".
So your SQL Statement should be

Dim sql As String = "Select * From tbl_assignment Where ProjectNo ='" & cbbproject.Items(cbbproject.SelectedIndex) & "'"

At line 38 you write txtpn.Text = (sqlReader("Project Name").ToString). It is also incorrect. It should be

txtpn.Text = (sqlReader.Item("[Project Name]").ToString)

Repeating again,
Make a condition checking before initiating the loop for data reading, unless it could produce an error. The checking is like

If sqlReader.HasRows then
    While sqlReader.Read
    'Process data here
    End While
End If
Edited by Santanu.Das
Member Avatar for Xiao_1

your this have error

If sqlReader.HasRows then
While sqlReader.Read
'Process data here
End While
End If

Member Avatar for Santanu.Das

Pls. post the picture of the error.

Member Avatar for Xiao_1

is ok have fix it

Member Avatar for Mr.M

If your question has been answered don't forget to mark it as Solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.