RUn stored Procedure using VB.net 1

[jnavarro]

I have a stored procedue that will insert new records to a table. The data is coming from a VB Form I would like to know how to pass the parameter from the form to the stored procedure. I have 5 field that are being type( Employee #, Last Name, First Name, Hire Date, Department). Can some help me on how to have the data entry to be insert sp to run in vb,

Thanks

 

[jmeckley]

take a look at the system.data.sqlclient namespace to set up your connection, data adapter, and parameters.

Jason Meckley
Database Analyst
WITF

 

[kheuber]

Here's a sample (frmEmpNum represents one of your form fields)...

Dim arParms() As SqlParameter = New SqlParameter(1) {}
arParms(0) = New SqlParameter("@EmpNum", SqlDbType.Int)
arParms(0).Value = frmEmpNum.Text

'Here's how to get an output parm back
arParms(1)=New SqlParameter("@OutPrm", SqlDbType.VarChar, 8)
arParms(1).Direction = ParameterDirection.Output

Dim result As Integer = SqlHelper.ExecuteNonQuery(yourconnectionstring, CommandType.StoredProcedure, "usp_yoursp", arParms)

sOutputParm = arParms(1).Value

Hope this helps.
-Kurt H.

 

[MFritts253]

jnavarro,
This is how i do my Inserts, Updates, and Deletes in VB.Net Code. What this code does is modifies the DataAdapter Insert Command. The Transaction part makes sure that if any Insert, Updates, or Deletes Fail they all fail. That is just good DB practice.


Dim dbtx As SqlTransaction
daTrial.InsertCommand = New SqlCommand
daTrial.DeleteCommand = New SqlCommand
daTrial.UpdateCommand = New SqlCommand

daTrial.DeleteCommand.CommandType = CommandType.StoredProcedure
daTrial.DeleteCommand.CommandText = "PKA_DeletePacking"
daTrial.DeleteCommand.Connection = conMain
daTrial.DeleteCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.DeleteCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.DeleteCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item")


daTrial.InsertCommand.CommandType = CommandType.StoredProcedure
daTrial.InsertCommand.CommandText = "PKA_InsertPacking"
daTrial.InsertCommand.Connection = conMain
daTrial.InsertCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.InsertCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.InsertCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item") '.Value = dsRow.Item(0)
daTrial.InsertCommand.Parameters.Add("@TimeEach", SqlDbType.Int, 4, "TimeEach") '.Value = dsRow.Item(2)
daTrial.InsertCommand.Parameters.Add("@QtyPacked", SqlDbType.Int, 4, "QtyPacked") '.Value = dsRow.Item(3)


daTrial.UpdateCommand.CommandType = CommandType.StoredProcedure
daTrial.UpdateCommand.CommandText = "PKA_PackingUpdate"
daTrial.UpdateCommand.Connection = conMain
daTrial.UpdateCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.UpdateCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.UpdateCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item")
daTrial.UpdateCommand.Parameters.Add("@TimeEach", SqlDbType.Int, 4, "TimeEach")
daTrial.UpdateCommand.Parameters.Add("@QtyPacked", SqlDbType.Int, 4, "QtyPacked")

conMain.Open()

dbtx = conMain.BeginTransaction
daTrial.UpdateCommand.Transaction = dbtx
daTrial.InsertCommand.Transaction = dbtx
daTrial.DeleteCommand.Transaction = dbtx

Try
daTrial.Update(dsTrial, "Trial")
dbtx.Commit()

Catch ex As Exception
dbtx.Rollback()
'dgPacking.Refresh()

MsgBox(ex.Message & ex.Source)
Finally
conMain.Close()

End Try

My Insert Stored procedure, I'm using SQL Server 2000:
CREATE PROCEDURE PKA_InsertPacking
@EmpID varchar(3),
@WorkDate datetime,
@Item varchar(35),
@TimeEach int,
@QtyPacked int

AS
INSERT INTO
PKA_Packing

(EmployeeID,WorkDate,Item,TimeEach,QtyPacked)
VALUES
(@EmpID,@WorkDate,@Item,@TimeEach,@QtyPacked)
GO

Hopefully this will help a little bit.

 

[jnavarro]

I am just a little confusion now. Let me explain what I am trying to do. I have a text file which I am stripping the parts that I need. After that data is strip I will need to update these record to a SQL Server, which holds my data.
Here is my Stored Procedure:

Use MAY
GO
Alter Procedure UpdateEmployeeInfo @EmpID int, @Firstname varchar(30),@LastName varchar(30),@Status varchar(1),
@HireDate datetime,@TermDate datetime,@Address varchar(30),@City varchar(25),@State varchar(2),@Zip varchar(5)
as
update AssocInfo
Set LastName=@LastName,FirstName=@FirstName,Status=@Status,HireDate=@HireDate,TermDate=@TermDate,Address=@Address,City=@City,Zip=@Zip
where AssocNum=@EmpID

Now what I want to do is run this SP using vb.net and populate the parameters witht the values from the text box.

I know I am overthinking this but I am also lost is what I am suppose to do.

Thanks
Everyone for helping me out.

 

[jnavarro]

Here is a copy my code. I cannot get it to update.

Private Function DeclareSQLUpdate()

Dim conMAY As New SqlConnection("workstation id=OH300337;packet size=4096;integrated security=SSPI;initial catalog=MAY;persist security info=False")
Dim cmdAssocInfo As New SqlCommand
Dim daAssocInfo As New SqlDataAdapter
Dim DBX As SqlTransaction

Try
conMAY.Open()
cmdAssocInfo = New SqlCommand
With cmdAssocInfo
.Connection = conMAY
.CommandType = CommandType.StoredProcedure
.CommandText = "UpdateEmployeeInfo"
.Parameters.Add(New SqlParameter("@EmpID", SqlDbType.Int, 4, "AssocNum")).Value = 600528
.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.Char, 30, "FirstName")).Value = "J"
.Parameters.Add(New SqlParameter("@LastName", SqlDbType.Char, 30, "LastName")).Value = "N"
.Parameters.Add(New SqlParameter("@Status", SqlDbType.Char, 1, "Status")).Value = "P"
.Parameters.Add(New SqlParameter("@HireDate", SqlDbType.DateTime, 8, "HireDate")).Value = #10/15/2000#
.Parameters.Add(New SqlParameter("@TermDate", SqlDbType.DateTime, 8, "TermDate")).Value = #10/15/2000#
.Parameters.Add(New SqlParameter("@Address", SqlDbType.Char, 30, "Address")).Value = "1111 W 9th"
.Parameters.Add(New SqlParameter("@City", SqlDbType.Char, 30, "City")).Value = "L"
.Parameters.Add(New SqlParameter("@State", SqlDbType.Char, 2, "State")).Value = "WA"
.Parameters.Add(New SqlParameter("@Zip", SqlDbType.Char, 5, "Zip")).Value = "11111"
End With
DBX = conMAY.BeginTransaction
cmdAssocInfo.Transaction = DBX
daAssocInfo.UpdateCommand = cmdAssocInfo
DBX.Commit()

Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
conMAY.Close()
End Try

End Function

I also notice that in the code MFritts put a line
daTrial.Update(dsTrial, "Trial")

but I cannot figure where and how this line impact the code. can some direct me.

 

[MFritts253]

Hey buddy,
Just wondering if you got this to work. The DataAdapter is going to read the rowstate of your dataset and then it will determine which command it needs to use. IF you set your save function up exactly like mine it should work.

just Wrap the Update in the Try Catch Blocks. don't Wrap your CmdAssocinfo in the Try Catch blocks and don't use the With write out your commands like my example.

i would put a Rollback in your Catch
Try
dataAdapter.Update(dataSet)
dbx.Commit
Catch
dbx.Rollback
End Try