Mr Pericles Mr Pericles - 5 months ago 28
Vb.net Question

Using parameterized queries in vb when using SQL Server 2012

I've got an error that says "Must declare scalar variable" when I parametize my query. I am new in using SQL Server. And the version that I'm using is SQL Server 2012. Below is the code snippet:

Protected Sub Main_Page
Dim con as OleDbConnection
Dim cmd as OleDbCommand
Dim query as String

con = New OleDbConnection("Provider=SQLNCLI11;Data Source=ARIES-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=SchoolDB")
con.Open()
query = "INSERT INTO Instructors(FirstName,LastName,Address,Contact_Number) VALUES (@fname,@lname,@address,@number)"
cmd = New OleDbCommand(query, con)
cmd.Parameters.AddWithValue("@fname", txtFirstName.Text)
cmd.Parameters.AddWithValue("@lname", txtLastName.Text)
cmd.Parameters.AddWithValue("@address", txtAddress.Text)
cmd.Parameters.AddWithValue("@number", txtContact.Text)
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
Response.Write(<script>alert('Success!')</script>)
End Sub

Answer

parameterize query for avoiding sql injuction , so for best practice use simpe store procedure with parameter , its working fine ' your C# code

  Protected Sub Main_Page()
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand

        con = New OleDbConnection("Provider=SQLNCLI11;Data Source=ARIES-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=SchoolDB")

        con.Open()
        cmd = New OleDbCommand("spSaveData", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@fname", txtFirstName.Text)
        cmd.Parameters.AddWithValue("@lname", txtLastName.Text)
        cmd.Parameters.AddWithValue("@address", txtAddress.Text)
        cmd.Parameters.AddWithValue("@number", txtContact.Text)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        con.Close()
        Response.Write(<script>alert('Success!')</script>)

    End Sub

' Your Sp code

create proc spSaveData
@fname varchar(200),
@lname varchar(200),
@address varchar(300),
@number int

AS BEGIN

INSERT INTO Instructors(FirstName,LastName,Address,Contact_Number) VALUES (@fname,@lname,@address,@number)

END
Comments