Retrieve fields from stored procedures in vb.net

Question

0.00/5 (No votes)

See more:

XML

I have a stored procedure "Get_Emp_No" which retrieves all records from database. I have a code that call the stored procedure mentioned above. I want to display only few fields in datagrid. But I am not getting it. Below is the code of stored procedure and code in which the procedure is called.
Also I am getting Error-"Object Reference not set to an instance of object"
------------------------------------------------------------------
stored procedure
-----------------------------------------------------------------
<pre lang="SQL"><pre lang="sql">CREATE PROCEDURE Get_Emp_No

AS
    SELECT * from emp_master
RETURN 0</pre>

------------------------------------------------------------------
vb code
------------------------------------------------------------------

VB

Try
            con.Open()
            com.Connection = con
            com = New SqlCommand("Get_Emp_No", con)
            ' com.CommandText = "Get_Emp_No"
            com.CommandType = CommandType.StoredProcedure
            dr = com.ExecuteReader
            While dr.Read
                i = DGV_empno.Rows.Add(+1)
                DGV_empno.Rows(i).Cells(0).Value = dr("empid")
                DGV_empno.Rows(i).Cells(1).Value = dr("empname")
                DGV_empno.Rows(i).Cells(2).Value = dr("dob")
                DGV_empno.Rows(i).Cells(3).Value = dr("doj")
                DGV_empno.Rows(i).Cells(4).Value = dr("dept")
                DGV_empno.Rows(i).Cells(5).Value = dr("desig")
                DGV_empno.Rows(i).Cells(6).Value = dr("exp")
                DGV_empno.Rows(i).Cells(7).Value = dr("salary")
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

Posted 16-Mar-15 0:29am

A94

Updated 16-Mar-15 0:31am

v2

Add a Solution

Comments

[no name] 16-Mar-15 6:33am

Do you know at which line the error occurs?

A94 16-Mar-15 6:35am

Yes It is occurring at "com.Connection=con"

[no name] 16-Mar-15 6:47am

Oh right, I could have spotted that - Solution below :-)

2 solutions

Solution 3

You use "com" before you have created it:

VB

com.Connection = con
com = New SqlCommand("Get_Emp_No", con)

If you wouldn't pass the SqlConnection as an argument to the SqlCommand-Constructor, you would simply have to reverse these two lines. But since you do, you can just remove the first of these two lines.
It should work then - if there are no other bugs ;)

I would suggest to use a different identifier for the SqlCommand: cmd - that way it's easier to distinguish from con.

Posted 16-Mar-15 0:46am

manchanx

Comments

Maciej Los 16-Mar-15 7:13am

Hawk eye! +5!

[no name] 16-Mar-15 7:19am

I would like to keep the impression of a hawk eye up but in this case I know because I asked him for the error-line in a comment to his question. .....Maybe I just shouldn't have told you *g*
But I take the +5, thank you! :-)

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)

A94 · Accepted Answer · 2015-03-16T00:43:00

VB

Try
          con.Open()
          com = New SqlCommand("Get_Emp_No", con)
        
          com.CommandType = CommandType.StoredProcedure
          dr = com.ExecuteReader
          While dr.Read
              i = DGV_empno.Rows.Add(+1)
              DGV_empno.Rows(i).Cells(0).Value = dr("empid")
              DGV_empno.Rows(i).Cells(1).Value = dr("empname")
              DGV_empno.Rows(i).Cells(2).Value = dr("dob")
              DGV_empno.Rows(i).Cells(3).Value = dr("doj")
              DGV_empno.Rows(i).Cells(4).Value = dr("dept")
              DGV_empno.Rows(i).Cells(5).Value = dr("desig")
              DGV_empno.Rows(i).Cells(6).Value = dr("exp")
              DGV_empno.Rows(i).Cells(7).Value = dr("salary")
          End While
      Catch ex As Exception
          MsgBox(ex.Message)
      Finally
          con.Close()
      End Try
  End Sub