Click here to Skip to main content
15,867,977 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

i have 3 tables data,sort,fullsort
the columns for data table(lot_no,type,shape,size,place,weight,dt)
sort(lot_no,name,type,shape,size,place,weight,issue_dt)
fullsort(lot_no,name,type,shape,size,place,weight,issue_dt,return_dt)

i have created the following stored procedure
Posted
Comments
sudeshna from bangkok 6-Aug-13 0:44am    
USE [record]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdatedataDetails]
@lot_no nvarchar(25),
@name nvarchar(25),
@type nvarchar(25),
@shape nvarchar(25),
@size nvarchar(25),
@place nvarchar(25),
@weight float,
@dt datetime,
@issue_dt datetime,
@return_dt datetime



AS
BEGIN
SET NOCOUNT ON;
UPDATE data SET type = @type, shape = @shape,size = @size,place = @place,weight=@weight,dt = @dt
WHERE lot_no=@lot_no

UPDATE sort SET type=@type, shape = @shape,size = @size,place = @place,weight=@weight,issue_dt=@issue_dt
WHERE lot_no=@lot_no


UPDATE fullsort SET type=@type, shape = @shape,size = @size,place = @place,weight=@weight,issue_dt=@issue_dt,return_dt=@return_dt
WHERE lot_no=@lot_no

END
sudeshna from bangkok 6-Aug-13 0:45am    
the command is getting executed here succesfully but the code which i am writing in vb.net showing error .please can anyone help me. thanks in advance
sudeshna from bangkok 6-Aug-13 0:49am    
this is my code for update in vb.net

Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
'Dim dr As SqlDataReader
cmd.Connection = cn
cn.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "UpdatedataDetails"
cmd.Parameters.Add("@lot_no", SqlDbType.NVarChar).Value = TextBox1.Text.Trim()
cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = ComboBox1.Text.Trim()
cmd.Parameters.Add("@shape", SqlDbType.NVarChar).Value = TextBox3.Text.Trim()
cmd.Parameters.Add("@size", SqlDbType.NVarChar).Value = TextBox4.Text.Trim()
cmd.Parameters.Add("@place", SqlDbType.NVarChar).Value = TextBox5.Text.Trim()
cmd.Parameters.Add("@weight", SqlDbType.Float).Value = TextBox6.Text.Trim()
cmd.Parameters.Add("@dt", SqlDbType.DateTime).Value = DateTimePicker1.Text.Trim()
cmd.ExecuteNonQuery()
MsgBox("Data successfully updated", MsgBoxStyle.Information)
'Me.DataTableAdapter.Insert(TextBox1.Text, ComboBox1.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, DateTimePicker1.Value)
Me.DataTableAdapter.Fill(Me.RecordDataSet.data)
Me.DateTimePicker1.Text = ""
TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
sudeshna from bangkok 6-Aug-13 0:50am    
here its showing the error

Procedure or Function 'UpdatedataDetails' expects parameter '@name', which was not supplied.

in my data table name column is not there,then how do i resolve this?
_Damian S_ 6-Aug-13 1:16am    
Remove @name from your stored procedure, or give it a default value of NULL.

1 solution

Your stored procedure is expecting the parameter @weight to be a float, and you are passing it a string.
 
Share this answer
 
Comments
sudeshna from bangkok 6-Aug-13 1:08am    
i have declared weight as float only in my stored procedure.

which line you are telling can you mention it please
_Damian S_ 6-Aug-13 1:17am    
These lines:
cmd.Parameters.Add("@weight", SqlDbType.Float).Value = TextBox6.Text.Trim()
cmd.Parameters.Add("@dt", SqlDbType.DateTime).Value = DateTimePicker1.Text.Trim()

@weight is expecting a float to be passed, not a string. @dt is expecting a date/time.
sudeshna from bangkok 6-Aug-13 1:17am    
which line sir?
sudeshna from bangkok 6-Aug-13 1:31am    
yes i have entered sqlDbtype.float sir, can you recorrect and send me, as mi am new to stored procedure,yeterday only i learnt a bit, so is the problem.
_Damian S_ 6-Aug-13 1:39am    
cmd.Parameters.Add("@weight", SqlDbType.Float).Value = cdbl(TextBox6.Text.Trim())

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900