asked on

Retrieving and Entering data through the VB.NET Datagrid

hi, i'm learning how to use the vb.net datagrid. i'm from the school of vb6, so using all the data-adaptors, data-sets etc can get pretty confusing. Basically, if someone can demonstrate how to complete the following simple task for me, it would give me a giant leap in my understanding. This example may seem trivial but it is strategic and will answer quite a few questions that i have.

Tools:
VB.Net
SQL-Server 2000
VB.Net Datagrid
SqlDataAdaptor
a DataSet
SQLConnection

Task:
I'd like someone to show me code that will accomplish the following.

1.Connect to the sample SQL-Server Pubs database
2.Display the contents of the 'authors' table in a datagrid using the SQLDataAdaptor and a Dataset
3.On the 'double-click' of any cell in the datagrid (excluding the au_id column) i would like 2 actions
-a messagebox appears showing the au_id of the double-clicked row
-the text of the double clicked cell becomes the current system time and is immediately saved to the pubs database
4.this must all be done with code, not using wizards or design-view clicking/dragging.

i will give the points to anyone whose post leads me to an answer, even if i have to split the points up. the ideal answer would be a code segment that does exactly what i asked.

thanks everybody!

-dwops

rhys_kirk

I am from VB6 school also.......but I have tonnes of VB.NET equivalent examples now. One of which is using sql server to populate datagrid in vb.net(ASP.NET page)

Its a start!

**CODE

<%@ Page Language="VB" ClassName="SendingPage" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Public ReadOnly Property GridData() As System.Object
Get
' Datasource of Datagrid1 is a data table inside a dataset
Return DataGrid1.DataSource
End Get
End Property

Sub Button_Clicked (sender As Object, e As EventArgs)
Server.Transfer("ReceivingPage.aspx")
End Sub

Sub Page_Load(sender As Object, e As EventArgs)
'bind some relevant data to the grid at page load
GetData()
End Sub

Sub GetData()
Dim DS As System.Data.DataSet
Dim Con As System.Data.SqlClient.SqlConnection
Dim Ada As System.Data.SqlClient.SqlDataAdapter
Dim connStr as String
conStr = "server=localhost;uid=sa;pwd=;initial catalog=pubs"

Con = New System.Data.SqlClient.SqlConnection(connStr)

Ada = New System.Data.SqlClient.SqlDataAdapter(_
"select * from authors", Con)

DS = New System.Data.DataSet()
Ada.Fill(DS, "Table1")

'Fill the DataGrid
DataGrid1.DataSource = DS.Tables("Table1").DefaultView
DataGrid1.DataBind()
End Sub
</script>

<html>
<body>
<form runat="server">
<b>Visiting SendingPage.aspx</b>
<asp:datagrid id=DataGrid1 runat=server />

<asp:Button id="myButton" OnClick="Button_Clicked"
Text="Send Grid Data" runat=server />
</form>
</body>
</html>

morphinex

1.Connect to the sample SQL-Server Pubs database

Imports System.Data.SqlClient

Public Class Sample

      Public Shared Sub Main()
      Dim nwindConn As SqlConnection = New SqlConnection("data source= YourServerName ;initial catalog= YourDataBaseName ;persist security info=False;user id=YourUserName;packet size=4096;password= YourPassword")

       Dim cmdSQL as New SqlClient.SqlCommand
      Dim SqlParam as New SqlClient.SqlParameter
       Dim sqlDA As New SqlClient.SqlDataAdapter

       nwindConn.Open()

       cmdSQL.Connection = nwindConn
       cmdSQL.CommandType = CommandType.Text
       cmdSQL.CommandText = "select * from authors"

       sqlDA.SelectCommand = cmdSQL

       Dim dsDataSet As New DataSet
            sqlDA.Fill(dsDataSet, "YourDataSet")

            '2.Display the contents of the 'authors' table in a datagrid using the SQLDataAdaptor and a Dataset
            'Make a datagrid on the designer view

            datagrid1.DataSource = dsDataSet
            datagrid1.DataMember = "YourDataSet"
      End Sub
End Class

3.On the 'double-click' of any cell in the datagrid (excluding the au_id column) i would like 2 actions
'In order for the datagrid to respond to a double click well, the cells shouldn't be editable
Goto here to find out how --

http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q1008q

Or, you can use the method shown to catch double click

http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q869q

... actually, the above site would show you everything you'd ever want to do with a datagrid and more. That'd
get you really started =)

dwops

ASKER

thanks morphinex,

i've read over that list of questions before but i still can't seem to answer a few questions.

How do i retrieve and set the value of a given cell? All i'm able to do is determine the row and column that was clicked. How do i now get the data that is in that cell? How do I change the value in that cell programmatically and then save the dataset back to the database?

thanks
-dwops

ASKER CERTIFIED SOLUTION

morphinex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dwops

ASKER

how do i update the data grid and the appropriate table in MS Access...

I am allowing the user to edit the data in the data grid itself and also i am allowing them to edit more than one row at a time....

as of now i am doing it the old fashioned way of using a recordset and a connection string and a sql string....

i want to know if i can use the UPDATE method for the dataset to do the same...

If any one has the clue please let me know...