2) ถ้าใช้ DB เป็น SQLServer2005 ขึ้นไป (รวมถึง Express Edition) ใช้วิธีเรียก Store procedure เอา
Store procedure
USE [database name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procedure name]
(
@comsetname nvarchar(15),
@addeddate datetime,
@addedby nvarchar(8),
@comtype nvarchar(6),
@combrand nvarchar(30),
@comID int OUTPUT
)
AS
SET NOCOUNT ON
INSERT INTO tb_Comset
(ComsetName,AddedDate,AddedBy,ComType,ComBrand)
VALUES (@comsetname,@addeddate,@addedby,@comtype,@combrand)
SET @comID = scope_identity()
เวลาเรียกใช้ Code (C#)
public int InsertRequestData(string valComName,datetime valAddDate,string valAddBy,
string valComType,string valComBrand)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("procedure name", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@comsetname", SqlDbType.Text).Value = valComName;
cmd.Parameters.Add("@addeddate", SqlDbType.DateTime).Value = valAddDate;
cmd.Parameters.Add("@addedby", SqlDbType.Text).Value = valAddBy;
cmd.Parameters.Add("@comtype", SqlDbType.Text).Value = valComType;
cmd.Parameters.Add("@combrand", SqlDbType.Text).Value = valComBrand;
cmd.Parameters.Add("@comID", SqlDbType.Int).Direction = ParameterDirection.Output;
cn.Open();
int ret = ExecuteNonQuery(cmd);
return (int)cmd.Parameters["@comID"].Value;
}
throw new NotImplementedException();
}
ติดปัญหานี้เหมือนกัน ครับ ช่วยดูให้หน่อยครับ
GUES_ID(PK)ของตารางGUES ผมกำหนดเป็น Auto Number
GUES_ID(FK)ของตารางRESV
Code (VB.NET)
Dim connection As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
Dim conn As New SqlConnection(connection)
conn.Open()
Dim comm As String = "INSERT INTO GUES(GUES_NAME,GNDR,NATN,IDEN,E_MAIL,ADDR,TELP_NO)"
comm &= "VALUES('" & TextBox1.Text & "',"
comm &= "'" & TextBox2.Text & "',"
comm &= "'" & TextBox3.Text & "',"
comm &= "'" & TextBox4.Text & "',"
comm &= "'" & TextBox5.Text & "',"
comm &= "'" & TextBox6.Text & "',"
comm &= "'" & TextBox7.Text & "')"
Dim command As New SqlCommand(comm, conn)
command.ExecuteNonQuery()
comm = "INSERT INTO RESV(GUES_ID,ROOM_NAME,DATE_FROM,DATE_TO,RESV_STTS,DATE_RESV)"
comm &= "VALUES('" & .........???.......... & "',"
comm &= "'" & lblSeleceRoom.Text & "',"
comm &= "Convert(DateTime, '" & lblSeleceDate.Text & "', 103),"
comm &= "Convert(DateTime, '" & lblSeleceDate1.Text & "', 103),"
comm &= "'A',"
comm &= "'" & DateNow & "')"
Dim acommand As New SqlCommand(comm, conn)
acommand.ExecuteNonQuery()
conn.Close()
End Sub
End Class
Load balance : Server 02
