Run a Stored Procedure via a click of a button in .Net web page

Question

Afternoon All,

I have a stored procedure in an SQL 2005 database named GasNominationsRawData_Insert.

When executed this simply extracts some data from another database and inserts the result into a table. This query works fine.

What i need to do is enable this to be executed on the click on a button on my web page. I have had a good luck around the internet have have managed to create the following code in my .net 2008 web page. But i think im either missing something or i have have completly the wrong code. Im new to programming in .Net but i understand that i need to delclare the stored procedure, create the SQL connection, create the command line, open the conection, execute the query and then close the connection.

I dont need or have any parameters. Essentially this button is just used to push data to a table.

Imports System.Data Imports System.Data.SqlClient

Partial Class RawData Inherits System.Web.UI.Page

Protected Sub btnAddRawData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddRawData.Click

    'Declare Stored Procedure
    Dim GasNominationsRawData_Insert As String = "GasNominationsRawData_Insert"

    'Declare SQL Connection (This is the connection string located on the web.config page)
    Dim SQLConn As SqlConnection
    SQLConn = New SqlConnection("GasNominationsDataConnectionString")

    'Declare command
    Dim SqlComm As SqlCommand = New SqlCommand("GasNominationsRawData_Insert", SQLConn)
    SqlComm.CommandType = CommandType.StoredProcedure

    Try
        'Open SQL Connection
        SQLConn.Open()

        'Execute Query
        SqlComm.ExecuteNonQuery()

        'Close connection
        SQLConn.Close()

    Catch ex As Exception
        Throw (ex)

    End Try

End Sub

End Class

Any Help is much appreciated.

Regards Betty

score 0 · Answer 1 · answered Oct 27 '11 at 11:19

0

Looks fine at a first glance. So what happens on clicking the button? Have you assured that your btnAddRawData_Click is called when the button is clicked?

answered Oct 27 '11 at 11:19

Anja

324
1
2

when i click the button on the web page i get the following error message: Format of the initialization string does not conform to specification starting at index 0. and line 14 is highlighted red: SQLConn = New SqlConnection("GasNominationsDataConnectionString"). I thought that this was an issue with the connection string in the Web config file but this connection sting is used else where in the web app for a grid view and works just fine? – Betty Oct 27 '11 at 11:53

score 0 · Accepted Answer · answered Oct 27 '11 at 12:29

Upps. I overlooked that. You're trying to connect to your database with the connectionstring: "GasNominationsDataConnectionString". That's of course no correct connection string. Your connection string should look somehow like that: "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;" So you have to get your connection string first out of your web.config like Dim GasNominationsDataConnectionString As String = ConfigurationManager.ConnectionStrings("GasNominationsDataConnectionString").ConnectionString

Regards Anja

Run a Stored Procedure via a click of a button in .Net web page

2 Answers2