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 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, move forward one step and with changing the number of ord_id field, serial number begins again from number one(1)
my code is this :
I was used stored procedure with table_valued parameter as follows :
USE [rasol] GO<o
></o>
/** Object: StoredProcedure [dbo].[order_details_insert]
Script Date: 08/04/2013 16:25:45 **/
SET ANSI_NULLS ON GO<o></o>
SET QUOTED_IDENTIFIER ON GO<o></o>
create 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<o></o>
GO<o></o>
USE [rasol] GO<o></o>
/** 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<o></o>
when I run this code, 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 help me.
please correct my code.
thanks everybody.
Continue reading...
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 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, move forward one step 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
Public Class frm_order_details
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
Dim BindingSource1 As New BindingSource()
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
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
tb = New DataTable("order_details")
Dim row As DataRow
Try
Dim dc0 As DataColumn = New DataColumn("serial")
dc0.DataType = Type.GetType("system.String")
tb.Columns.Add(dc0)
Dim dc1 As DataColumn = New DataColumn("ord_id")
dc1.DataType = Type.GetType("System.String")
tb.Columns.Add(dc1)
Dim dc2 As DataColumn = New DataColumn("prod_id")
dc2.DataType = Type.GetType("System.String")
tb.Columns.Add(dc2)
Dim dc3 As DataColumn = New DataColumn("prod_name")
dc3.DataType = Type.GetType("System.String")
tb.Columns.Add(dc3)
Dim dc4 As DataColumn = New DataColumn("quantity")
dc4.DataType = Type.GetType("System.String")
tb.Columns.Add(dc4)
Dim dc5 As DataColumn = New DataColumn("fee")
dc5.DataType = Type.GetType("System.String")
tb.Columns.Add(dc5)
row = tb.NewRow()
row.Item("serial") = temp
row.Item("ord_id") = CInt(ComboBox1.Text)
row.Item("prod_id") = CInt(ComboBox2.Text)
row.Item("prod_name") = CStr(ComboBox3.Text)
row.Item("quantity") = CInt(TextBox1.Text)
row.Item("fee") = CInt(ComboBox4.Text)
tb.Rows.Add(row)
Catch
End Try
Try
cmd = New SqlCommand("order_details_insert", conn)
Dim prm As SqlParameter = cmd.Parameters.Add("@bb", SqlDbType.Structured)
cmd.CommandType = CommandType.StoredProcedure
prm.Value = tb
cmd.ExecuteNonQuery()
newcode()
Catch ex As SqlException
MsgBox(ex.Message)
End Try
ComboBox2.Text = ""
ComboBox3.Text = ""
TextBox1.Text = ""
ComboBox4.Text = ""
BindingSource1.DataSource = tb
DataGridView1.DataSource = BindingSource1
End Sub
Public Sub newcode()
Dim conn1 As SqlConnection
Dim cmd1 As SqlCommand
Dim myreader As SqlDataReader
conn1 = 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
End Class
I was used stored procedure with table_valued parameter as follows :
USE [rasol] GO<o
></o>/** Object: StoredProcedure [dbo].[order_details_insert]
Script Date: 08/04/2013 16:25:45 **/
SET ANSI_NULLS ON GO<o
></o>SET QUOTED_IDENTIFIER ON GO<o
></o>create 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<o
></o>GO<o
></o>USE [rasol] GO<o
></o>/** 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<o
></o>when I run this code, 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 help me.
please correct my code.
thanks everybody.
Continue reading...
