?
Solved

Calling Stored Procedure from VB express 2008

Posted on 2009-04-21
10
Medium Priority
?
431 Views
Last Modified: 2012-05-06
I created a stored procedure that performs an update on a single field and now i want to call it from my vb application.  Below is what I have tried with some variations.  

       EXEC (StoredProcedure7)
OR
        Dim i As Int16
        i = CompanySetupDataSetTableAdapters.ExecuteSQL("StoredProcedure7")

Also, how do I rename a stored procedure?
0
Comment
Question by:GarySB
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 24198411
You can do something like this:
        Dim myConnectionString As String = _
       "Data Source=.\SQLEXPRESS;AttachDbFilename='c:\myDatabase.mdf';Integrated Security=True;User Instance=True"
 
        Using connection As New SqlClient.SqlConnection(myConnectionString)
            connection.Open()
 
            Using command As New SqlClient.SqlCommand("StoredProcedure7", connection)
                command.CommandType = CommandType.StoredProcedure
                ' If you want to pass some parameter
                'command.Parameters.Add("@codigo", SqlDbType.Int).Value = SomeValue
 
                command.ExecuteNonQuery()
            End Using
        End Using

Open in new window

0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24198454
To rename a stored procedure you can use in a function this
EXEC sp_rename 'oldname', 'newname'
0
 

Author Comment

by:GarySB
ID: 24198610
Thank you for responding so quickly.

Can the above code you wrote be referenced using one of my existing data connections?
I'm already able to read and write to the tables with the below connections.
SetupDataSet, TableAdapterManager, TableAdapter or BindingSource
Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 24198788
I believe that the TableAdapter can use a Stored Procedure as it's source.  You just have to find and populate the SelectCommand I believe.  
0
 

Author Comment

by:GarySB
ID: 24198954
Can you give me  a sample of the syntax using my names please.  I thought the GETDATA might be usefull, but does not appear to be for this.

ProjectDataTableAdapter, StoredProcedure7
0
 

Author Comment

by:GarySB
ID: 24215463
I'm trying to use your solution. How can I correct this error?. I'm trying both of these and I keep getting the same error:

Cannot open database "<C:\Documents and Settings\user\My Documents\CompanySetup.mdf>" requested by the login. The login failed. Login failed for user 'HP2\user'.

 'HP2\user' is correct because it shows in the properties of the database.

The database does not have a password and the properties shows that the bottom one below is exactly the right path.  I also tried the C: drive location. The database is on my local computer

Dim myConnectionString As String = _
                "Data Source=.\SQLEXPRESS;Database=<C:\Documents and Settings\user\My Documents\CompanySetup.mdf>;Integrated Security=SSPI"

Dim myConnectionString As String = _
                "Data Source=.\SQLEXPRESS;Database=<CompanySetup.mdf>;Integrated Security=SSPI"
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 24217422
Try this connection string instead.

Server=.\SQLExpress;AttachDbFilename=C:\Documents and Settings\user\My Documents\CompanySetup.mdf;Database=CompanySetup; Trusted_Connection=Yes;
0
 

Author Comment

by:GarySB
ID: 24218015
Hi dbaduck,  I appreciate your help, but it is still not cooperating with the open statement.  I placed the error msg below along with the exact code in my program.  

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

        Dim myConnectionString As String = _
                "Data Source=.Server=.\SQLExpress;AttachDbFilename=C:\Documents and Settings\user\My Documents\CompanySetup.mdf;Database=CompanySetup; Trusted_Connection=Yes;"

        Using connection As New SqlClient.SqlConnection(myConnectionString)
            connection.Open()  'STOPS HERE

            Using command As New SqlClient.SqlCommand("StoredProcedure7", connection)
                command.CommandType = CommandType.StoredProcedure
                ' If you want to pass some parameter
                command.Parameters.Add("@ChangeFlag", SqlDbType.Text).Value = "J"
                command.ExecuteNonQuery()
            End Using
        End Using
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 24219257
The problem maybe related to not having the SQL Express instance started on the machine.  I would move the database file because it is in the Documents and Settings folder which is your user related and the SQL Server is running probably under Network service.

Move the database .mdf file to another place and ensure that Network Service has rights to the folder.
0
 

Author Closing Comment

by:GarySB
ID: 31572981
Thank you for working this out with me.  Moving it to the local C: drive did the trick.  I also wanted to show you the final connection string that worked.
"Data Source=.\SQLExpress;AttachDbFilename=C:\CompanySetup.mdf;Integrated Security=True;User Instance=True;Trusted_Connection=Yes;"
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

839 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