Display records from SQL Server in my GridView

Question

0.00/5 (No votes)

See more:

Hello everyone,

I am creating a site in VB.Net and ASP.Net that allows a user to type a code in to a text box on my form and click search.

When the user clicks search, the program goes to the backend database(SQL Server) and retrieves all records when the code matches the code typed in to the textbox.

This information is then all displayed in to a listbox on my page.

For example, CentreCode | CentreName | RegionCode | RegionName etc
Value Value Value Value

I have managed to get the connection to the database going but can only seem to display the centrecode.

This is my code as far:

<pre lang="vb">Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
            Dim sqlConn As New SqlConnection
            Dim sqlCmd As New SqlClient.SqlCommand
            Dim sqlReader As SqlDataReader
            If TextBox1.Text = "" Then
                MsgBox("A centre code needs to be provided...")
            End If
            If TextBox1.Text <> "" Then
                'Telling the system the location of the database.
                sqlConn.ConnectionString = "server=servername;Initial Catalog=dbname;Trusted_Connection=yes"
                'Here we are opening the connection to the database.
                sqlConn.Open()
                MsgBox("CONNECTION OPEN..." & sqlConn.ConnectionString)
                'This is to say that sqlCmd is a stored procedure.
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                sqlCmd = sqlConn.CreateCommand
                'The command is triggered to execute the stored procedure which grabs all information for the specific centre.
                sqlCmd.CommandText = "exec GetAllInformation '" & TextBox1.Text & "' "
                MsgBox("PROCEDURE EXECUTED...")
                sqlReader = sqlCmd.ExecuteReader()
                If (sqlReader.HasRows) Then
                    While (sqlReader.Read())
                        'This grabs the details for the specific centre from the database using the data reader.
                        GridView1.DataSource = sqlReader
                        GridView1.DataBind()
                    End While
                Else
                    MsgBox("THERE IS NO DATA TO SELECT...")
                End If
                MsgBox("INFORMATION GRABBED...")
                'This is closing the connection to the database once we have finished with it.
                sqlConn.Close()
            End If
        End Sub

Can anybody please help?

Many thanks,

Dan

Posted 13-Jul-11 5:59am

dannyboi86

Add a Solution

3 solutions

Solution 2

Try This if You Don't able to Clear your Problem Yet

Using Reader As SqlClient.SqlDataReader = sqlCmd .ExecuteReader()
                Dim dt As New DataTable
                dt.Load(Reader)
                If dt.Rows.Count > 0 Then
                    DataGridView1.DataSource = dt
                End If
            End Using

Posted 13-Jul-11 18:41pm

Karwa_Vivek

Solution 3

public void Gridview1()
{
try
{
DataSet ds = new DataSet();
ds = obj.GetSampleCode(//Pass the Parameters//)
if (ds.Tables[0].Rows.Count == 0)
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());

grd1.DataSource = ds; // grid ID name given in design
grd1.DataBind();
grd1.Rows[0].Visible = false;

}
else
{

grd1.DataSource = ds;
grd1.DataBind();
grd1.Visible = true;
}
}
catch (Exception ex)
{
throw ex;
}
}

Posted 13-Jul-11 21:20pm

dhakshan dev

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2011-07-13T06:55:00

Solution 1

Your sqlReader code is screwy. You're binding the grid on every record read in the returned data. You should just set the DataSource property ONCE.

If (sqlReader.HasRows) Then
GridView1.DataSource = sqlReader
GridView1.DataBind()

Posted 13-Jul-11 6:55am

Dave Kreskowiak

Comments

dannyboi86 14-Jul-11 5:23am

Thank you Dave Kreskowiak, my program worked fine after I edited it as per your suggestion.

Many thanks again.

Dan