adding to a table in sql server

[son]

hi i am using vb.net and my backend is sql server 2000. i am getting confused on how to send my values from my webform to my table using a stored procedure my code is as follows..

 

Dim objSQLConn As SqlConnection

       objSQLConn = New SqlConnection

       'set the connection string from the web.config file
       objSQLConn.ConnectionString = ConfigurationSettings.AppSettings.Get("ConnectionString")
       'Open a connection to the SQL database
       objSQLConn.Open()


       Dim SqlSPParam1 As String = "@customerid"
       Dim SqlSPParam2 As String = "@companyname"
       Dim SqlSPParam3 As String = "@contactname"
      

       Dim SqlParams As SqlParameter() = {New SqlParameter(SqlSPParam1, TextBox1.Text), New SqlParameter(SqlSPParam2, TextBox2.Text), New SqlParameter(SqlSPParam3, textbox3.text)}

       .ExecuteNonQuery(objSQLConn, "sp_customers", SqlParams)
      

 

can someone tell me what else i have to add infront of the .ExecuteNonQuery,

 

thanks in advance... sonia...

[PlausiblyDamp]

Try something like the following

Dim objSQLConn As SqlConnection
objSQLConn = New SqlConnection

'set the connection string from the web.config file
objSQLConn.ConnectionString = ConfigurationSettings.AppSettings.Get("ConnectionString")
'Open a connection to the SQL database
objSQLConn.Open()

Dim cmd As New SqlCommand
cmd.Connection = objSQLConn
cmd.CommandText = "sp_customers"
cmd.Parameters.Add("@customerID", textbox1.text)
cmd.Parameters.Add("@companyname", textbox2.text)
cmd.Parameters.Add("@contactname", textbox3.text)
cmd.ExecuteNonQuery(objSQLConn, "sp_customers", SqlParams)

[son]

hi i am using my code to execute a stored procedure to add to my table my code is as follows but nothing is being added to my stored procedure...can someone help please.. thanks

 

Private Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btsubmit.Click
       



       Dim conn As SqlConnection
       conn = New SqlConnection
       conn.ConnectionString = CType(configurationAppSettings.GetValue("NorthwindConnection", GetType(System.String)), String)
       conn.Open()


       Dim SqlSPParam1 As String = "@customerid"
       Dim SqlSPParam2 As String = "@companyname"
       Dim SqlSPParam3 As String = "@contactname"
       Dim SqlSPParam4 As String = "@contacttitle"
       Dim SqlSPParam5 As String = "@address"
       Dim SqlSPParam6 As String = "@city"
       Dim SqlSPParam7 As String = "@region"
       Dim SqlSPParam8 As String = "@postalcode"
       Dim SqlSPParam9 As String = "@country"
       Dim SqlSPParam10 As String = "@phone"
       Dim SqlSPParam11 As String = "@fax"

       Dim objSQLCmd As SqlCommand
       'Create the command object
       objSQLCmd = New SqlCommand

       With objSQLCmd
           .Connection = conn  ' the SQL connection
           .CommandType = CommandType.StoredProcedure
           .CommandText = "sp_customers" 'Stored Procedure to use

           'The name parameter passed into the stored procedure
           .Parameters.Add(SqlSPParam1, txtId.Text)
           .Parameters.Add(SqlSPParam2, txtCompanyName.Text)

           .Parameters.Add(SqlSPParam3, txtContactName.Text)
           .Parameters.Add(SqlSPParam4, txtContactTitle.Text)
           .Parameters.Add(SqlSPParam5, txtAddress.Text)
           .Parameters.Add(SqlSPParam6, txtCity.Text)
           .Parameters.Add(SqlSPParam7, txtRegion.Text)
           .Parameters.Add(SqlSPParam8, txtPostalCode.Text)
           .Parameters.Add(SqlSPParam9, txtCountry.Text)
           .Parameters.Add(SqlSPParam10, txtPhone.Text)
           .Parameters.Add(SqlSPParam11, txtFax.Text)
     


           objSQLCmd.ExecuteNonQuery()

       End With
     
   End Sub

[JABE]

Does an error msg pop up when you execute your code? If there is none, then the problem is most probably w/ your stored procedure.

[PlausiblyDamp]

Any chance you could also post the Stored proc's code?

[son]

hi yeah sure my stored procedure is as follows

 

CREATE PROCEDURE sp_customers @CustomerId nchar, @CompanyName nvarchar(40), 
@ContactName nvarchar(30), @ContactTitle  nvarchar(30),@Address nvarchar(60), 
@City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), 
@Phone nvarchar(24), @Fax nvarchar(24) AS


SELECT * FROM Customers
GO

 

thanks for all ur help...

[JABE]

SELECT retrieves data; use INSERT to add data to a table:

 

INSERT Customers VALUES(@CustomerId, @CompanyName, <other params...>)

[son]

thanks

 

thanks for ur reply... i feel a bit embarressed not realising my stupied mistake once again thanks... take care sonia... ;)