参数化查询

Question

当我试图运行一个页面时，我一直收到这个错误消息。

参数化的查询'(@Medication nvarchar(14)，@Quantity nvarchar(9)，@RequestedDate n‘)期望参数'@RequestedDate'，而该参数没有提供。

这是我的代码隐藏文件：

Dim conn As SqlConnection
Dim cmd As SqlCommand

'Getting today's date to store into database to let staff know when prescription was ordered
'reference for this code : - https://msdn.microsoft.com/en-us/library/system.datetime.today(v=vs.110).aspx

Dim thisDay As DateTime = DateTime.Today
Session("DateRequested") = (thisDay.ToString())
Dim Medication As String
Dim Quantity As String
Dim RequestedDate As String
Dim Pharmacy As String
Dim PatientNumber As String

Medication = txtDrug1.Text + " " + txtDrug2.Text + " " + txtDrug3.Text + " " + txtDrug4.Text + " " + txtDrug5.Text
Quantity = txtQuant1.Text + " " + txtQuant2.Text + " " + txtQuant3.Text + " " + txtQuant4.Text + " " + txtQuant5.Text

RequestedDate = Session("Date Requested")
PatientNumber = Session("PatientNumber")
Pharmacy = txtPharmacy.Text

Dim cmdstring As String = "INSERT INTO Prescription (Medication, QntyandStrength, RequestedDate, Pharmacy, PatientNumber) Values (@Medication, @Quantity, @RequestedDate, @Pharmacy, @PatientNumber)"

conn = New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\User\Documents\Visual Studio 2015\WebSites\myAppointments\App_Data\Database.mdf;Integrated Security=True")
cmd = New SqlCommand(cmdstring, conn)

cmd.Parameters.AddWithValue("@Medication", Medication)
cmd.Parameters.AddWithValue("@Quantity", Quantity)
cmd.Parameters.AddWithValue("@RequestedDate", RequestedDate)
cmd.Parameters.AddWithValue("@Pharmacy", Pharmacy)
cmd.Parameters.AddWithValue("@PatientNumber", PatientNumber)

conn.Open()

cmd.ExecuteNonQuery()
conn.Close()

MsgBox("Your Prescription Has Been Requested!")

数据库类型为：

[PrescriptionNumber] INT          NOT NULL,
[Medication]         NVARCHAR(250) NULL,
[QntyandStrength]    NVARCHAR(250) NULL,
[RequestedDate]      NVARCHAR(70) NULL,
[Pharmacy]           VARCHAR (50) NULL,
[PatientNumber]      CHAR (10)    NULL,

关于如何改变这个问题有什么建议吗？

Answer 1

正如Chris所指出的，您对会话变量的引用是不正确的。

声明变量并赋值：

Session("DateRequested") = (thisDay.ToString())

...but然后你用不同的名字来指它.

RequestedDate = Session("Date Requested")  ''This variable name has a space.

所以，当你传递参数时.

cmd.Parameters.AddWithValue("@RequestedDate", RequestedDate)

.传递对空对象的引用.没有会议(“请求日期”)。