Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

stored procedure?

Posted on 2006-05-05
7
Medium Priority
?
221 Views
Last Modified: 2010-04-23
Why does it keep saying "Could not find stored procedure"?


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Try

Dim con As SqlConnection = New SqlConnection("workstation id=""Myname"";packet size=1000;user id=Myname;data source=Test" & _

"ist security info=True;initial catalog=MyResults;password=mypassword")

Dim cmd As SqlCommand = New SqlCommand("SELECT Address, CityState FROM Results", con)

Dim reader As SqlDataReader

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@TransNo", Me.txtTrannumber.Text)

con.Open()

reader = cmd.ExecuteReader      ----- error

con.Close()



reader.Read()

txtTrannumber.Text = reader("txttransno")

txtAddress.Text = reader("txtaddress")

txtCity.Text = reader("txtcitystate")

con = Nothing

Catch ex As SqlException

MsgBox(ex.ToString)



End Try




End Sub

0
Comment
Question by:VBdotnet2005
  • 4
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16618444
because you ask to consider the command text as Stored Procedure, but actually the text is simple command text.
Also, you want to pass a argument:

Dim cmd As SqlCommand = New SqlCommand("SELECT Address, CityState FROM Results WHERE TransNo = @TransNo", con)
Dim reader As SqlDataReader
cmd.Parameters.Add("@TransNo", Me.txtTrannumber.Text)
0
 

Author Comment

by:VBdotnet2005
ID: 16618812
Correct?

Dim con As SqlConnection = New SqlConnection("workstation id=""Myname"";packet size=1000;user id=Myname;data source=Test" & _

"ist security info=True;initial catalog=MyResults;password=mypassword")
 Dim cmd As SqlCommand = New SqlCommand("SELECT Address, CityState FROM Results WHERE TransNo = @TransNo", con)
            Dim reader As SqlDataReader
            cmd.Parameters.Add("@TransNo", Me.txtTrannumber.Text)

            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@TransNo", Me.txtTrannumber.Text)
            con.Open()

            reader = cmd.ExecuteReader

            con.Close()


            reader.Read()

            txtTrannumber.Text = reader("txttransno")
            txtAddress.Text = reader("txtaddress")
            txtCity.Text = reader("txtcitystate")

            con = Nothing

        Catch ex As SqlException

            MsgBox(ex.ToString)


        End Try
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16620201
no. as I said, leave the line away that sets the commandtype to storedprocedure as it is not a stored procedure:

"ist security info=True;initial catalog=MyResults;password=mypassword")
 Dim cmd As SqlCommand = New SqlCommand("SELECT Address, CityState FROM Results WHERE TransNo = @TransNo", con)
            Dim reader As SqlDataReader
            cmd.Parameters.Add("@TransNo", Me.txtTrannumber.Text)

            con.Open()

            reader = cmd.ExecuteReader

            con.Close()


            reader.Read()

            txtTrannumber.Text = reader("txttransno")
            txtAddress.Text = reader("txtaddress")
            txtCity.Text = reader("txtcitystate")

            con = Nothing

        Catch ex As SqlException

            MsgBox(ex.ToString)


        End Try
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:VBdotnet2005
ID: 16633807
I'm confused. Remove this line?

"ist security info=True;initial catalog=MyResults;password=mypassword")

0
 

Author Comment

by:VBdotnet2005
ID: 16633816

Dim Conn as SqlConnection ...?
Dim cmd As SqlCommand = New SqlCommand("SELECT Address, CityState FROM Results WHERE TransNo = @TransNo", con)
            Dim reader As SqlDataReader
            cmd.Parameters.Add("@TransNo", Me.txtTrannumber.Text)

            con.Open()

            reader = cmd.ExecuteReader

            con.Close()


            reader.Read()

            txtTrannumber.Text = reader("txttransno")
            txtAddress.Text = reader("txtaddress")
            txtCity.Text = reader("txtcitystate")

            con = Nothing

        Catch ex As SqlException

            MsgBox(ex.ToString)


        End Try
0
 

Author Comment

by:VBdotnet2005
ID: 16633858
ERROR


Additional information: Invalid attempt to Read when reader is closed.
0
 
LVL 6

Accepted Solution

by:
SandeepRR earned 2000 total points
ID: 16682145
Hi VBdotnet2005,
                    Actually u have put the
con.Close()


at wrong position
it must be at the end of the  all the database oeration,

change in ur code.......

.........
.
...
 con.Open()

            reader = cmd.ExecuteReader

         


            reader.Read()

            txtTrannumber.Text = reader("txttransno")
            txtAddress.Text = reader("txtaddress")
            txtCity.Text = reader("txtcitystate")

           con.Close() ''''''''''Check Here
            con = Nothing
........
.
.
......

i have included the section of code

u have to put the  con.Close() after u finish reading the data.

SandeepRR
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Screencast - Getting to Know the Pipeline
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question