Banner Banner - 2 months ago 16
Vb.net Question

Must declare the scalar variable "@User_Name"

Referring to my last question: "The connection was not closed. The connection's current state is open". I am trying to do a registration form using vb.net with ASP.NET. After having solve an issue concerning 'close connection'. I am having problem to insert data to my database. When I try to validate the data it gives me an error message:


Must declare the scalar variable "@User_Name"


Can someone help me to debug this? Thanks

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Partial Class Register2

Inherits System.Web.UI.Page

'declaring connection string and command
'here we are extracting connection string from web.config file
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("MauriAuctions").ToString())
Private cmd As New SqlCommand()

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Try
cmd.Connection = con
'assigning connection to command
cmd.CommandType = CommandType.Text
'representing type of command
'cmd.CommandText = "INSERT INTO UserDetails (User_Name,Fname,Lname,Email,Password,Gender,Dob,Mobile,Address) values
' (@User_Name,@Fname,@Lname,@Email,@Password,@Gender,@Dob,@Mobile,@Address)";
cmd.CommandText = "INSERT INTO tbl_user (User_Name, Fname, Lname, Email, Pwd, Street, Town, City, Tel) values(@User_Name,@Fname,@Lname,@Email,@Pwd,@Street,@Town,@City,@Tel)"

'adding parameters with value

cmd.Parameters.AddWithValue("@User_Name", txtUser_Name.Text.ToString())
cmd.Parameters.AddWithValue("@Fname", txtFirstName.Text.ToString())
cmd.Parameters.AddWithValue("@Lname", txtLastName.Text.ToString())
cmd.Parameters.AddWithValue("@Email", txtEmail.Text.ToString())
cmd.Parameters.AddWithValue("@Pwd", txtPassword.Text.ToString())
cmd.Parameters.AddWithValue("@Street", txtStreet.Text.ToString())
cmd.Parameters.AddWithValue("@Town", txtTown.Text.ToString())
cmd.Parameters.AddWithValue("@City", txtCity.Text.ToString())
cmd.Parameters.AddWithValue("@Tel", txtTel.Text.ToString())
cmd.Parameters.Clear()

con.Open()
'opening connection
cmd.ExecuteNonQuery()
'executing query
con.Close()
'closing connection

lblMsg.Text = "Registered Successfully.."

Catch ex As Exception
lblMsg.Text = ex.Message.ToString()

Finally

con.Close()
'closing connection
End Try
End Sub

Protected Sub btnClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClear.Click
'refreshing/reloading page to clear all the controls
Page.Response.Redirect(Page.Request.Url.ToString(), True)
End Sub
End Class

Answer

You're clearing parameters after adding them. Try putting cmd.Parameters.Clear() before first .AddWithValue, not after the last one.