R
rasol
Guest
I am newer in visual basic.net.
I have a problem.
I have a simple windows forms application with four tables ,this is called customers, order_details, order_header and product
I want to insert into order_details table with(serial nvarchar (50) not null,
ord_id fk int not null,
prod_id fk int not null,
prod_name nvarchar(60)not null,
quantity int not null,
fee money not null,
total_amount computed money null)fields so that ,the serial number in serial field with each time click ,insert and
move forward one record and with changing the number of ord_id field, serial number begins again from number one(1).
my code is this :
Imports System.Data
Imports System.Data.SqlClient
Dim conn As SqlConnection
Dim s1, s2, s3, s4 As String
Dim ds As New DataSet
Dim da As SqlDataAdapter
Dim cmd As SqlCommand
Dim tb As DataTable
Dim temp As String
Public Sub newcode ()
Dim conn1 As SqlConnection
Dim cmd1 As SqlCommand
Dim myreader As SqlDataReader c
onn1 = New SqlConnection("Data Source=Accounting-pc;Initial Catalog=rasol;Integrated Security=sspi;")
conn1.Open()
cmd1 = New SqlCommand("select max(serial) from order_details", conn1)
cmd1.CommandType = CommandType.Text
myreader = cmd1.ExecuteReader()
If myreader.HasRows Then
While myreader.Read()
temp = myreader.Item("serial") + 1
End While
Else
temp = 1
End If
myreader.Close()
conn1.Close()
End Sub
Private Sub frm_order_details_Load (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
s1 = "Data Source=Accounting-pc;Initial Catalog=Rasol;Integrated Security=true;"
conn = New SqlConnection(s1)
conn.Open()
s2 = "SELECT dbo.order_details.serial,dbo.order_header.ord_id ,dbo.order_header.cust_id ,dbo.order_header.ord_date ,dbo.product.prod_id,
dbo.order_details.prod_name ,dbo.order_details.quantity,dbo.order_details.fee ,dbo.order_details.total_amount FROM dbo.order_header INNER JOIN dbo.order_details ON dbo.order_header.ord_id = dbo.order_details.ord_id INNER JOIN dbo.product ON dbo.order_details.prod_id = dbo.product.prod_id"
da = New SqlDataAdapter(s2, conn)
da.Fill(ds, "order_details")
DataGridView1.DataSource = ds.Tables("order_details")
s3 = "select ord_id,ord_date,cust_id from order_header"
da = New SqlDataAdapter(s3, conn)
da.Fill(ds, "order_header")
With ComboBox1
.DataSource = ds.Tables("order_header")
.DisplayMember = "ord_id"
.ValueMember = "cust_id"
.SelectedIndex = 0
End With
s4 = "select prod_id,prod_name,fee from product"
da = New SqlDataAdapter(s4, conn)
da.Fill(ds, "product")
With ComboBox2
.DataSource = ds.Tables("product")
.DisplayMember = "prod_id"
.ValueMember = "prod_name"
.SelectedIndex = 0
End With
With ComboBox3
.DataSource = ds.Tables("product")
.DisplayMember = "prod_name"
.ValueMember = "prod_id"
.SelectedIndex = 0
End With
With ComboBox4
.DataSource = ds.Tables("product")
.DisplayMember = "fee"
.ValueMember = "prod_id"
.SelectedIndex = 0
End With
End Sub
I am using stored procedure with table_valued parameter as follows :
USE [rasol] GO
/** Object: StoredProcedure [dbo].[order_details_insert] Script Date: 08/04/2013 16:25:45 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter proc [dbo].[order_details_insert] (@bb order_details_infoo readonly)
as
set nocount on
insert into order_details(serial,ord_id,prod_id,prod_name,quantity,fee)
select serial,ord_id,prod_id,prod_name,quantity,fee from @bb
GO
USE [rasol] GO
/** Object: UserDefinedTableType [dbo].[order_details_info] Script Date: 08/04/2013 16:27:44 **/
CREATE TYPE [dbo].[order_details_info] AS TABLE
( [serial] nvarchar NOT NULL, [ord_id] [int] NOT NULL, [prod_id] [int] NOT NULL, [prod_name] nvarchar NOT NULL, [quantity] [int] NOT NULL, [fee] [money] NOT NULL )
GO
when I run my program, it dose not work properly and makes There are not enough fields in the Structured type.Structured types must have at least one field. error
please correct my code.
please help me.
thanks everybody
Continue reading...
I have a problem.
I have a simple windows forms application with four tables ,this is called customers, order_details, order_header and product
I want to insert into order_details table with(serial nvarchar (50) not null,
ord_id fk int not null,
prod_id fk int not null,
prod_name nvarchar(60)not null,
quantity int not null,
fee money not null,
total_amount computed money null)fields so that ,the serial number in serial field with each time click ,insert and
move forward one record and with changing the number of ord_id field, serial number begins again from number one(1).
my code is this :
Imports System.Data
Imports System.Data.SqlClient
Dim conn As SqlConnection
Dim s1, s2, s3, s4 As String
Dim ds As New DataSet
Dim da As SqlDataAdapter
Dim cmd As SqlCommand
Dim tb As DataTable
Dim temp As String
Public Sub newcode ()
Dim conn1 As SqlConnection
Dim cmd1 As SqlCommand
Dim myreader As SqlDataReader c
onn1 = New SqlConnection("Data Source=Accounting-pc;Initial Catalog=rasol;Integrated Security=sspi;")
conn1.Open()
cmd1 = New SqlCommand("select max(serial) from order_details", conn1)
cmd1.CommandType = CommandType.Text
myreader = cmd1.ExecuteReader()
If myreader.HasRows Then
While myreader.Read()
temp = myreader.Item("serial") + 1
End While
Else
temp = 1
End If
myreader.Close()
conn1.Close()
End Sub
Private Sub frm_order_details_Load (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
s1 = "Data Source=Accounting-pc;Initial Catalog=Rasol;Integrated Security=true;"
conn = New SqlConnection(s1)
conn.Open()
s2 = "SELECT dbo.order_details.serial,dbo.order_header.ord_id ,dbo.order_header.cust_id ,dbo.order_header.ord_date ,dbo.product.prod_id,
dbo.order_details.prod_name ,dbo.order_details.quantity,dbo.order_details.fee ,dbo.order_details.total_amount FROM dbo.order_header INNER JOIN dbo.order_details ON dbo.order_header.ord_id = dbo.order_details.ord_id INNER JOIN dbo.product ON dbo.order_details.prod_id = dbo.product.prod_id"
da = New SqlDataAdapter(s2, conn)
da.Fill(ds, "order_details")
DataGridView1.DataSource = ds.Tables("order_details")
s3 = "select ord_id,ord_date,cust_id from order_header"
da = New SqlDataAdapter(s3, conn)
da.Fill(ds, "order_header")
With ComboBox1
.DataSource = ds.Tables("order_header")
.DisplayMember = "ord_id"
.ValueMember = "cust_id"
.SelectedIndex = 0
End With
s4 = "select prod_id,prod_name,fee from product"
da = New SqlDataAdapter(s4, conn)
da.Fill(ds, "product")
With ComboBox2
.DataSource = ds.Tables("product")
.DisplayMember = "prod_id"
.ValueMember = "prod_name"
.SelectedIndex = 0
End With
With ComboBox3
.DataSource = ds.Tables("product")
.DisplayMember = "prod_name"
.ValueMember = "prod_id"
.SelectedIndex = 0
End With
With ComboBox4
.DataSource = ds.Tables("product")
.DisplayMember = "fee"
.ValueMember = "prod_id"
.SelectedIndex = 0
End With
End Sub
I am using stored procedure with table_valued parameter as follows :
USE [rasol] GO
/** Object: StoredProcedure [dbo].[order_details_insert] Script Date: 08/04/2013 16:25:45 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter proc [dbo].[order_details_insert] (@bb order_details_infoo readonly)
as
set nocount on
insert into order_details(serial,ord_id,prod_id,prod_name,quantity,fee)
select serial,ord_id,prod_id,prod_name,quantity,fee from @bb
GO
USE [rasol] GO
/** Object: UserDefinedTableType [dbo].[order_details_info] Script Date: 08/04/2013 16:27:44 **/
CREATE TYPE [dbo].[order_details_info] AS TABLE
( [serial] nvarchar NOT NULL, [ord_id] [int] NOT NULL, [prod_id] [int] NOT NULL, [prod_name] nvarchar NOT NULL, [quantity] [int] NOT NULL, [fee] [money] NOT NULL )
GO
when I run my program, it dose not work properly and makes There are not enough fields in the Structured type.Structured types must have at least one field. error
please correct my code.
please help me.
thanks everybody
Continue reading...
