read a row from database

[met0555]

hi, well i want to know how can i read from a datagrid view a row and show it in a richtext box???


tanks

[Kimmy4]

Hi there,
How many rows does your dgv contain? And do you just want to read the first row?

[Kimmy4]

I've just tested this code to a messagebox and it works

Dim sb As New StringBuilder()
        Dim row As DataGridViewRow = Me.DataGridView1.Rows(0) 'Reads from the first row
        For Each column As DataGridViewColumn In DataGridView1.Columns
            sb.Append(row.Cells(column.Index).Value.ToString)
            sb.Append(ControlChars.NewLine)
        Next
        RichTextBox1.Text = sb.Tostring

So i'm guessing it will work with a richtextbox. If you need to read from everyrow in your dgv then you'll need to replace the line

Dim row as DatagridviewRow = datagridview1.rows(0)

with the code

For each row as Datagridviewrow in datagridview1.rows
next

and place it around the for loop for the columns.
Hope this helps.

________________________________________________________________________
If i've helped then please rate this post! Thanx

[Kimmy4]

if this has solved your problem please don't forget to mark the thread [RESOLVED]!

[met0555]

i think it's what i needed it. but u see what i wanna do is kind of library so when the user click on a row ex. the music title i want to show the lyrics on the rich text box. and also when he click on other music title i want to show the music lyrics of that music on richtext box so i think that i have to put this

For each row as Datagridviewrow in datagridview1.rows
next

???

[Kimmy4]

Hi, sorry about the long delay in replying.
So just to clear things up. The user will click on a row. Will this be the row header or just a cell in that row? Then you want a richtextbox to display all the text from the rest of the columns in that row? When the user clicks on another row do you want it to replace the text currently in the richtextbox or display that text in a new richtextbox?
Also could you just explain to me the layout of your dgv....for example how many columns and rows it has (if known) and whether you know the names of the columns and rows or whether you need to use the index of them?
If you could just give me as much detail and information as possible because i dont quite understand what exactly you need. Thanks

[met0555]

ok, here it is how it will work : it's a lyrics library that i'm making it have 5 coulmn Artist, Title, Genre, Album, Lyrics . the form will be divided into two , with a datagrid and with a richtextbox and some buttons to go back... . on the datagrid view we will only see the Artist, Title Genre and Album the lyrics will be shown on the datagrid view. so when the user change the artist name the lyrics will have to match with the artist and music title name. i think i'm clear but if u need addtional info just ask me.

tanks

[stanav]

If you're not already loaded your data table at form_load, it's just a mater of creating a select statement with appropriate criteria (artist name, song title...) and query the database to get the lyrics. If the lyrics is already in your data table, then just load it to the text box base on the selected row on the datagridview.

[met0555]

----If the lyrics is already in your data table, then just load it to the text box base on the selected row on the datagridview. ---- i'm on this case, well, my problem is i don't know the code to retrive a specific data from the database and show it in the rich text box. the lyrics is in comlumn 5

[Kimmy4]

If the lyrics are already in your data table then you could use the code

Dim rowIndex as integer = DataGridView1.CurrentCellAddress.Y 'This will get the row index of the current selected cell
Dim row as datagridviewrow = datagridview1.rows(rowIndex)
dim lyrics as string = row.cells(4).value.tostring 'Get the value of the fifth column on the row selected
RichTextBox1.clear()
Richtextbox1.text = lyrics

In order to query the database you would need to use the select statement:

select lyrics from tablename where artist = 'cellvalue in artist column' and title = 'cellvalue in title column' and album = 'cellvalue in album column'

and then display the results in the richtextbox. You can get the values of each cell by using the same method as above but changing the column index from 5 to whatever column index is relevant. Don't forget the column indices start from 0 so the 1st column has an index of 0, the 2nd column has an index of 1 etc....

Hope this helps

[met0555]

ok, tanks, but there is something that i didn't understand. if i only put this code will it work Dim rowIndex as integer = DataGridView1.CurrentCellAddress.Y 'This will get the row index of the current selected cell

Dim row as datagridviewrow = datagridview1.rows(rowIndex)
dim lyrics as string = row.cells(4).value.tostring 'Get the value of the fifth column on the row selected
RichTextBox1.clear()
Richtextbox1.text = lyrics

or i have to put the select.... ? and if i need a select statment , do i only need one or more?

(The lyrics are allready stored i the database)

[Kimmy4]

Do you actually import the lyrics to the dgv or do u need to get them from the database? and if you do import them to the dgv then are the visible?

[met0555]

actualy i import it from the database

[Kimmy4]

so are the lyrics visible in the dgv?

[met0555]

nop, they are not visible. on the datagrid view only the title ... are visible . but if we can make it visible but hide it to the user i think it will make simple the code.

[Kimmy4]

is there any chance you could post part of your code that imports to the dgv so i can see how you are doing it. Then i could advise you on how to do display them in the richtextbox. Because if the lyrics can be accessed without having to do a select statement then that would be easier.

[met0555]

well i didn't connet yet the program with the database, but the code that i will you it the one that u gave me.

Dim row as datagridviewrow = datagridview1.rows(rowIndex)
dim lyrics as string = row.cells(4).value.tostring 'Get the value of the fifth column on the row selected
RichTextBox1.clear()
Richtextbox1.text = lyrics

[Kimmy4]

the code i have supplied gets the values from the dgv AFTER all the information has been imported from the database. Could you post all of your code could i dont understand what you have already done so cant tell you what you need to do next. Where does all the information (ie the title, artist, album etc.) come from in the first place?

[met0555]

well, i did the database with 5 column , and on the program i added the richtextbox and when i connect the database i will add the datagrid view. the title genre... will come from the database and will be show on the datagrid view. there is no big code that i put in the program form1.show me.hide...)

[Kimmy4]

So is there anything currently in the datagridview? Do you currently know how to connect to the database? What database do you need to connect to (are you using oracle/excel/something different to get all the information from?)?

[met0555]

well ya i connect correctly, i just connected but, i was sking my self if i have to add the lyrics to the datagrid view or not. U see i want the lyrics to be view only on the richtextbox . i did it with acces 2003.

[Kimmy4]

OK. I think i understand now. What you can do is add the lyrics to the datagridview but then set that column's visible property to false. This way ithey will be in the dgv but the user wont be able to see them. Then you should probably create a button and call it 'Get lyrics' and inside the button place the code to get the lyrics from the dgv. This way you wont have to continually make a connection to get the lyrics because they will always be there (that just wont be apparent to the user). And because you are using a button you will only need to use the code once just make sure the user clicks on a cell in the row they require and the current row index will change accordingly. The only thing you will need to change in the code i provided above is that you will need to reference the datagridview by saying form1.datagridview1, so that it knows you are refering to that dgv.
Is this what you needed?

[met0555]

so finaly the code will be this

Dim row as datagridviewrow = datagridview1.rows(rowIndex)
dim lyrics as string = row.cells(4).value.tostring 'Get the value of the fifth column on the row selected
RichTextBox1.clear()
Richtextbox1.text = lyrics

and i will put this code under a button. and i will not need of any other code like select * from... ??

[Kimmy4]

No you wont need any sql code because you should have already put all the information into the dgv. You are just getting the code from the dgv instead. Just remember to put the name of the form everytime you use datagridview1 otherwise it wont know which dgv you are refering to.

[met0555]

ok, tanks man

[Kimmy4]

Does it work?

[met0555]

i'm adding data to the database, but i soon as i'm done i will let u know

[stanav]

Show me the code you use for the followings:
1. The select statement you use to get data
2. where and how you bind your datagridview to the data table
Also please be clear how you want to display the lyrics... Do you want to display it when the user click on any cell in the grid or only when they actually select a row?

[Kimmy4]

Hi stanav,
will it really matter whether they click on the cell or the row as the line

Dim rowIndex as integer = datagridview1.currentcelladdress.y

will give him the same index as would clicking on the row? I'm just asking out of curiosity...would the code i've suggested not work then (i admit i haven't tested it....but it should achieve the results required)

[met0555]

well i use this for searching

Private Sub butOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles but_ok.Click
 
        Dim strConnection As String = "Data Source=your SQL data source;Initial Catalog=your database; Integrated Security=True"
 
        Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection(strConnection)
 
        Dim ds As New DataSet
 
        Dim strSelect As String
 
        'strSelect As String 
 
                        strSelect = "SELECT  * FROM " & YourTable & " WHERE [Search Field] = '" & Me.txtName.Text & "'"
 
        Dim dscmd As New SqlClient.SqlDataAdapter(strSelect, cn)
 
        dscmd.Fill(ds, "your table")
 
        Me.Datagrid1.DataSource = ds
 
        Me.Datagrid1.DataMember = "your table"
 
 
 
        Dim con As Integer
 
        con = Me.BindingContext(ds, "your table").Count
 
        If con = 0 Then
 
            MessageBox.Show("Recourd could not be found")
        End If
 
 
    End Sub

2. where and how you bind your datagridview to the data table : if u mean how i connect the dgv with the database table then : i will first connect the database then i will drag and drop it to the form.

well, i want the lyrics to be viewed when a row is clicked on the dgv. the selected row artis name title .. must much with the lyrics that is going to be view on the richtextbox.

[Kimmy4]

Have you tried the code and method that i suggested earlier yet....i'm fairly confident it will give you the results you need?

[met0555]

i don't think taht i will try it son 'cuz the database will have more then 60000 data in it and to add that it will take time, and also the dtabase will be stored in my resource folder so it's betetter to add then database after i'm donw ith the datas.

[stanav]

OK... So you use "SELECT * FROM..." that means that everything is already in your data table. Now let's suppose that your table have the following fields in it: [ArtistName],[AlbumName],[SongTitle], and [SongLyrics] And you only want to show artistname, albumname, and songtitle on the datagridview. When your user click on any cell in the grid, you want to display the song lyrics on a a richtextbox for the song that is on that row. Is that correct?