sql server programming: Make Labosh - Follow up -- sql server programming

Make Labosh - Follow up

Reggie
6/23/2005 11:29:28 PM

sql server programming:

Mike sorry for posting directly to you, but you gave me some good advice a
couple days back and I have a few questions for you.

Original Thread:
[quoted text, click to view]

Your resonse:
If you are in ASP.NET, the correct thing to do is to make sure your using a
server-side button. In the button's click event in your code-behind class,
you do something like this:

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Public Class MyClass

Private Sub Button1_Click(...)

' You're storing your connection string in Web.config, right?
Dim cnstr As String =
ConfigurationSettings.AppSettings("connectionString")

' Your connection
Dim cn As New SqlConnection(cnstr)

' This will help you tremendously
Dim da As New SqlDataAdapter()

' This represents your recordset. How you populate it is up to you
Dim dt As New DataTable()

' SP that knows how to insert a single record of your set
Dim cm As New SqlCommand("dbo.YourSP", cn)

' Configure your command
With cm
' Your storing a relevant timeout in Web.config, right?
.CommandTimeout = _
Convert.ToInt32(ConfigurationSettings.AppSettings("commandTimeout"))

' Don't forget this
.CommandType = CommandType.StoredProcedure

' Add one SqlParameter for each column in your DataTable
.Parameters.Add("@thisColumn")
.Parameters.Add("@thatColumn")
End With

' Give your command to the DataAdapter
da.InsertCommand = cm

' load your records from wherever they come from into your DataTable

' This will internally open the connection, and issue one call to the SP
' for each record in your data table, and then close the connection.
da.Update(dt)

End Sub

End Class

You want to build your stored procedure like this:

CREATE PROCEDURE dbo.YourSP (
@thisColumn INT, -- whatever your data types are.
@thatColumn NVARCHAR(50)
) AS
INSERT INTO YourTable
(ThisColumn, ThatColumn)
VALUES
(@thisColumn, @thatColumn)
GO

My new question/problem:
Got everything setup, but no data is being sent to my table via the
parameters. I normally set a value when adding the parameters
(Parameters.Add("@thisColumn", intRSSRID) but I don't see where the value I
want to pass in is coming from in your code. I've included my "messy code"
If you could look at it to see if I'm missing something I would appreciate
it. Also I know my datatable is being populated cause I use it to bind to a
datagrid which is displaying all the records. Also it appears this will only
append one record at a time. Is there a way to click the button once and
loop through the records? Anyway sorry for emailing you direct and
appreciate that help you've allready provided and anything more is a bonus.
(Oh yeah and you can kill bad guys :-) Take it easy!

strConString = ConfigurationSettings.AppSettings("conString")
con = New SqlConnection(strConString)
con.Open()
cmd = New SqlCommand("sp_app_RSSR1", con)
dad = New SqlDataAdapter("Select RSSR_ID, UIC_ID, NIIN From RSSR1", con)
Try
With cmd
..CommandTimeout =
Convert.ToInt32(ConfigurationSettings.AppSettings("conTimeOut"))
..CommandType = CommandType.StoredProcedure
..Parameters.Add("@RSSR_ID", SqlDbType.Int)
..Parameters.Add("@UIC_ID", SqlDbType.Int)
..Parameters.Add("@NIIN", SqlDbType.NVarChar)
End With
dad.InsertCommand = cmd
'I believe this is where you said to ' load your records from wherever they
come from into your DataTable
conn_OleDb = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
Source=" & pathandfile)
conn_OleDb.Open()
dad_OleDb = New OleDbDataAdapter("Select RSSR_ID, UIC_ID, NIIN From RSSR1",
conn_OleDb)
dst_RSSR = New DataSet
dad_OleDb.Fill(dst_RSSR, "RSSR1")
dt = dst_RSSR.Tables("RSSR1")
dgrd1.DataSource = dt
dgrd1.DataBind()
dad.Update(dt)
Catch sqlExc As SqlException
Label1.Text = sqlExc.ToString
End Try
Stored procedure:
CREATE PROCEDURE [dbo].[sp_app_RSSR1] (
@RSSR_ID INT,
@UIC_ID INT,
@NIIN NVARCHAR(10)
) AS INSERT INTO [WSMDDT].[dbo].[RSSR1]
(RSSR_ID, UIC_ID, NIIN)
VALUES (@RSSR_ID, @UIC_ID, @NIIN)
GO

--

Reggie

Re: Make Labosh - Follow up

Reggie
6/25/2005 4:00:35 AM

Mike, Disregard. You answered in the follow-up to my original post. Guess
I got a little to impatient. Thanks for all the assistance!

--

Reggie
[quoted text, click to view]