how to update multiple tables simultaneously in vb.net

if i create stored procedure, can i update records from front tend side? i mean from forms?

i did not understand the link as i have never used sp beofore. can you help me more in this,plz

can you just tell me step by step what to do? first shall i create a stored procedure in sql server?

ok.but can you give any other link for vb.net?

i am checking the 1st link only as 2nd link is not required as my project is windows application not web base applicatio

another doubt i have that since my both tables have all the fields commom except 2fields, so creating only 1 sp will be suffice for 1table?

this is my 2 table fields with columns

data table fields-
lot_no,type,shape,size,place,dt(date)
sort table fields
lot_no,name,type,shape,size,place,issue_dt

hello sir, i created the sp and also the code in vb.net and its working but my datagridview is showing the old record. suppose i change the value of shape in data table,but in gridview its showing the old value,how to change that too.

i am giving here my code.

sorry the multiple tables are not getting updated, i saw it wrong. can you see and tell me where is wrong in my code?

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.CommandText = "Update data set " & "type = '" & ComboBox1.Text & "', " & "shape='" & TextBox3.Text & " '," & "size='" & TextBox4.Text & " '," & "place='" & TextBox5.Text & " ' ," & "weight='" & TextBox6.Text & " ', " & "dt = '" & Format(CDate(DateTimePicker1.Value), "MM/dd/yyyy") & "'" & "Where lot_no = '" & TextBox1.Text & "'"
cmd.Parameters.Add("@lot_no", SqlDbType.VarChar).Value = TextBox1.Text.Trim()
cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = ComboBox1.Text.Trim()
cmd.Parameters.Add("@shape", SqlDbType.VarChar).Value = TextBox3.Text.Trim()
cmd.Parameters.Add("@size", SqlDbType.VarChar).Value = TextBox4.Text.Trim()
cmd.Parameters.Add("@place", SqlDbType.VarChar).Value = TextBox5.Text.Trim()
cmd.Parameters.Add("@weight", SqlDbType.VarChar).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 = ""

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 tb As DataTable
Try
cmd.Connection = cn
cn.Open()
da = New SqlDataAdapter("select * from data", cn)
tb = New DataTable
da.Fill(tb)
DataDataGridView.DataSource = tb
cn.Close()



Catch ex As Exception
MsgBox(ex.Message)
End Try

can you just tell me that i have 2 tables with some different columns, so in my stored procedure do i have to include all the columns or only the common columns in both the tables.

please reply kindly

thanks in advance.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
USE record
GO

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


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

oh,that i forgot to mention

where do i write another update statement for the other table? after the end statement?

USE [record]
GO
/****** Object: StoredProcedure [dbo].[UpdatedataDetails] Script Date: 08/05/2013 19:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UpdatedataDetails]
@lot_no nvarchar(25),
@type nvarchar(25),
@shape nvarchar(25),
@size nvarchar(25),
@place nvarchar(25),
@weight float,
@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
WHERE lot_no=@lot_no

END

is it ok? and another thing i want to ask i have doubt, is that in data table i dnt have the column name and issue_dt but these columns are there in sort table, so how do i do it?

but then in the procedure do i have to decalre the column names which are not common like name and issue_dt which are present in sort column but not in data column

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



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 name=@name,type=@type, shape = @shape,size = @size,place = @place,weight=@weight
WHERE lot_no=@lot_no

END

but showing error in my vb.net code. saying that @parameter not mentioned in my data code.

sorry @name not declared

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()

this is the code for vb.net and how can i add name column here as in data table name column is not there

showing error at my vb.net code
Procedure or Function 'UpdatesortDetails' expects parameter '@dt', which was not supplied.

shall i declare name field in create procedure also?

sir please kindly help, i m stuck, s you know i am new to stored procedure.

what do i do with name field which is not commom in both the tables data and sort.

how do i write it in sp and also in vb.net?

but my data table doesnt have name column

i ams sending you the full procedure which i have created,ok,then you can tell me where i am wrong

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UpdateDetails]
@lot_no nvarchar(25),
@name nvarchar(25)=null,
@type nvarchar(25),
@shape nvarchar(25),
@size nvarchar(25),
@place nvarchar(25),
@weight float,
@dt datetime=null,
@dt1 datetime=null,
@dt2 datetime=null




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, name=@name,shape = @shape,size = @size,place = @place,weight=@weight,issue_dt=@dt1
WHERE lot_no=@lot_no


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

END

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 = "UpdateDetails"
cmd.Parameters.Add("@lot_no", SqlDbType.NVarChar).Value = TextBox1.Text.Trim()
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = ""
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 = ""

is it correct?

please help me sir, as you know i am new to sp

my 3dates that is dt,dt1 and dt2 are 3 different dates for 3 different tables, so when i am writing the statement
cmd.Parameters.Add("@dt", SqlDbType.NVarChar).Value = ""
cmd.Parameters.Add("@dt2", SqlDbType.NVarChar).Value = ""

in my sort update table in vb.net, my previous dt(datevalue) from data table is getting inserted with default value of 1 jan 1990

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 = "UpdateDetails"
cmd.Parameters.Add("@lot_no", SqlDbType.NVarChar).Value = ComboBox1.Text.Trim()
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = ComboBox2.Text.Trim()
cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = TextBox3.Text.Trim()
cmd.Parameters.Add("@shape", SqlDbType.NVarChar).Value = TextBox4.Text.Trim()
cmd.Parameters.Add("@size", SqlDbType.NVarChar).Value = TextBox5.Text.Trim()
cmd.Parameters.Add("@place", SqlDbType.NVarChar).Value = TextBox6.Text.Trim()
cmd.Parameters.Add("@weight", SqlDbType.Float).Value = TextBox7.Text.Trim()
cmd.Parameters.Add("@dt1", SqlDbType.DateTime).Value = DateTimePicker1.Text.Trim()
cmd.Parameters.Add("@dt", SqlDbType.NVarChar).Value = ""
cmd.Parameters.Add("@dt2", SqlDbType.NVarChar).Value = ""

this is the query for sort tble in vb.net

yes but what value will i send it in sort query as that dt and dt2 field are not there in sort table

sir please help. when i am executing the last code which i gave just now,then my dt column in data table is getting inserted with default value of 1st jan 1990 in spite of entering it 1st at user side with some specific value. its getting updated with the 2nd update query

no, i am explaining you.
i have 3 tables that needs to be updated with some common columns and some uncommon columns.
if i call the procedure once then how do i update the other two tables which doesnt have columns in common

i mean sir i have 3tables
data(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)


all the 3 dates dt,issue_dt,return_dt are different and name column
doesnt exist in data table

now how do i do?

sir,plz help without your help i cant proceed

no, i can get all the commom data at once,.i ll explain how my project working. from vb.net fron end side i am 1st inserting rows to data table. then in form2,i am fetching that same common rows from data table and inserting name and date at runtime and updating in sort table,then again same in form3 i am fetching rows from sort table which are common and then inserting return_dt row and updating in fullsort table.


so here in procedure when at runtime 1st time i am entering date value,its getting updated in data table,next again when i am executing the 2nd form where
cmd.Parameters.Add("@dt", SqlDbType.NVarChar).Value = ""
cmd.Parameters.Add("@dt2", SqlDbType.NVarChar).Value = ""

these two lines of code are there, then my data table which had initialy some specific value geting overwritten with default value of 1st jan 1990 and also the return_dt column in fullsort table getting inserted with default value

sir, where r u? please help me

oh ok. got it.i will do and let u know if it works

but then how changes will be reflected? i mean supose i modify 1 common column in data table(1st table) than that should also reflect in other two sort and fullsort table also

i have not implemented it as yet, after i get the solution i surely will accept the answer.

if i create 2 separate procedures 1 for common columns and another one for uncommon columns,then from the vb.net code how will i call 2separate procedures using cmd.commandtext?

cmd.CommandText = "UpdateDetails"
cmd.CommandText = "UpdatedataDetails"

you mean to say like this i will write in vb.net code?
in 1st procedure all common columns are there and in second one uncommon columns are there

Secnd one didnt understand.can you just write it for me.

do i have to write these two in every form?

showing error
Failed to convert parameter value from a String to a DateTime.

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 = "Details"

cmd.Parameters.Add("@name", SqlDbType.DateTime).Value = ComboBox2.Text.Trim()
cmd.Parameters.Add("@dt1", SqlDbType.DateTime).Value = CDate(DateTimePicker1.Text.Trim())
cmd.Parameters.Add("@dt2", SqlDbType.NVarChar).Value = ""

oh i am sorry, i made that mistake, i have rectified that.thanks

but now another problem that as i m selecting names from drop down box, only the 1st letter is showing.

e.g if name is sudeshna,its showing s only in gridview.

this is the code i have written

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "Details"
cmd.Parameters.Add("@lot_no", SqlDbType.Int).Value = ComboBox1.Text.Trim()
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = ComboBox2.Text.Trim()
cmd.Parameters.Add("@dt1", SqlDbType.DateTime).Value = DateTimePicker1.Text.Trim()
cmd.Parameters.Add("@dt2", SqlDbType.NVarChar).Value = ""

all the while it was fine,why suddenly this came? only 1st letter of the name

yes

but throughout the day it was working fine,why this truncate of it?

where will i use selected text property?

used selectedtext property but not showing anything.name is only not displaying

no other issues. i am sending you all the 3 procedures i have created and also the vb.net code,ok? then you can tell me where i am wrong

i checked with the above statement but its not working, i mean my datagridview not showing and then no value is showing from combobox1 for lot_no

no its not gettin updated in database only. i used with selectedtext property but in database blank is entering,means null no value. sorry that datagridview i wrote by mistake

but if i use normal combobox1.text.trim() then showing only 1st letter of the name and getting updated in database

oh i got it,where i had done mistake. i had not declared size

@name nvarchar(25) here i forgot to mention size in sp

thanks a lot, i learnt full stored procedure just because of your help and support.thanks a lot. a lot million.

ya i did, but without your help and all the links that you had provided, i couldnt able to proceed.

can you just tell me how to delete records from multiple tables with same common columns and uncommon columns in sp? like i did for update.

if you can again provide me some links

ya sure,give me your facebook id, i dont have twitter account.
yes i am facing problem in delete sp. i have written it, i 'll just post it here last time so that you can see and verify it.

can you just give me your email id so that i can ask my doubts over there?

and i have posted another thread for delete,can you help me as you were the one who was helping me with update stored procedure,so you know what i have done exactly.

USE [record]
GO
/****** Object: StoredProcedure [dbo].[Details] Script Date: 08/06/2013 20:01:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Details]
@lot_no int,
@name nvarchar,
@dt1 datetime





AS
BEGIN
SET NOCOUNT ON;


UPDATE sort SET name=@name,issue_dt=@dt1
WHERE lot_no=@lot_no


UPDATE fullsort SET name=@name,issue_dt=@dt1
WHERE lot_no=@lot_no

END

USE [record]
GO
/****** Object: StoredProcedure [dbo].[UpdatedataDetails] Script Date: 08/06/2013 20:01:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UpdatedataDetails]
@lot_no nvarchar(25),
@type nvarchar(25),
@shape nvarchar(25),
@size nvarchar(25),
@place nvarchar(25),
@weight float,
@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
WHERE lot_no=@lot_no


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

END

this is for the all common columns and the previous one was for the issue_dt in sort and fullsort table which needed to be updated

USE [record]
GO
/****** Object: StoredProcedure [dbo].[return] Script Date: 08/06/2013 20:03:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[return]
@lot_no int,
@dt2 datetime





AS
BEGIN
SET NOCOUNT ON;




UPDATE fullsort SET return_dt=@dt2
WHERE lot_no=@lot_no

END

this one for the return_dt for the last fullsort table which needs to be updated

cmd.Connection = cn
cn.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "Details"
cmd.Parameters.Add("@lot_no", SqlDbType.Int).Value = ComboBox1.Text.Trim()
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = ComboBox2.Text.Trim()
cmd.Parameters.Add("@dt1", SqlDbType.DateTime).Value = DateTimePicker1.Text.Trim()