StrataFrame Forum

How to use parameters rather string statement

http://forum.strataframe.net/Topic24872.aspx

By Juan Carlos Pazos - 10/6/2009

Hi

I have this:

Dim currenthmonth As Integer

Dim currentyear As Integer

currenthmonth = Date.Now.Month

currentyear = Date.Now.Year

Me.FillDataTable("SELECT * FROM Sales WHERE Payment = 1 AND Month(DatePay) =" & currenthmonth & " AND Year(DatePay)=" & currentyear & "")

But I read that in SF help that is best use paramaters, I have used parameters in other queries and not problem with boolean fields, but I can not make it work using the Month function. I need this for recover only sales orders for current month and year.

Kindest regards.

By Edhy Rijo - 10/6/2009

Hi Juan Carlos,



Here are 2 ways of getting the same result. I prefer the 2nd one.





'-- Sample 1

Dim currenthmonth As Integer = Date.Now.Month

Dim currentyear As Integer = Date.Now.Year

Dim strSQLString = String.Format("SELECT * FROM Sales WHERE Payment = 1 AND Month(DatePay) = {0} AND Year(DatePay)= {1}", currenthmonth.ToString, currentyear.ToString)

Me.FillDataTable(strSQLString)



'-- Sample 2

Using cmd As New SqlCommand

Dim strSQLStringWithParameters = "SELECT * FROM Sales WHERE Payment = 1 AND Month(DatePay) = @pCurrenthMonth AND Year(DatePay)= @pCurrentYear"

cmd.Parameters.AddWithValue("@pCurrenthMonth", currenthmonth).SqlDbType = SqlDbType.Int

cmd.Parameters.AddWithValue("@pCurrentYear", currentyear).SqlDbType = SqlDbType.Int

Me.FillDataTable(strSQLStringWithParameters)

End Using
By Greg McGuffey - 10/6/2009

If the data comes from a user, you want to use the second option (with parameters). The method you initially showed and the first one Edhy showed are both vulnerable to SQL Injection attacks.



Also, parametrized queries I believe allow for the query plan to be cached, thus they are faster.



Mostly for the first reason, all of my sql statements are parametrized.
By Juan Carlos Pazos - 10/6/2009

Hi Edhy and Greg

I will use this:

Using cmd As New SqlCommand
Dim strSQLStringWithParameters = "SELECT * FROM Sales WHERE Payment = 1 AND Month(DatePay) = @pCurrenthMonth AND Year(DatePay)= @pCurrentYear"
cmd.Parameters.AddWithValue("@pCurrenthMonth", currenthmonth).SqlDbType = SqlDbType.Int
cmd.Parameters.AddWithValue("@pCurrentYear", currentyear).SqlDbType = SqlDbType.Int
Me.FillDataTable(strSQLStringWithParameters)
End Using

But where comes this: @pCurrenthMonth  and "@pCurrentYear?

I think they should be here:

Public Sub FillMonthSales()

But I can figure it out, because og this:  FillMonthSales(ByVal DatePay as ....)

I litle more help please.

Regards
By Edhy Rijo - 10/6/2009

Hi Juan,



I have no idea what are you asking form. Please re-read your post and re-phrase it.



By the way, did you try the code?
By Juan Carlos Pazos - 10/6/2009

Hi Edhy

This is the code you give as sample:

Using cmd As New SqlCommand
Dim strSQLStringWithParameters = "SELECT * FROM Sales WHERE Payment = 1 AND Month(DatePay) = @pCurrenthMonth AND Year(DatePay)= @pCurrentYear"
cmd.Parameters.AddWithValue("@pCurrenthMonth", currenthmonth).SqlDbType = SqlDbType.Int
cmd.Parameters.AddWithValue("@pCurrentYear", currentyear).SqlDbType = SqlDbType.Int
Me.FillDataTable(strSQLStringWithParameters)
End Using

My question is this:

Where does @pCurrentMonth and @pCurrentYear come from?

Sales is the table, Payment is a boolean field, Month(DatePay) and Year(DatePay) are functions to retrieve the month and year from the DatePay field in the sales table.

But in cmd.Parameters.AddWithValue("@CurrenthMonth", currenthmonth).SqlDbType = SqlDBType.Int you use this: "@pCurrentMonth" (same for the year part). I can not find where to declare this.

The error message shows this:

Debe declarar la variable escalar "@pCurrentMonth"

Hope it makes more sense.

Regards
By Edhy Rijo - 10/6/2009

Juan Carlos Pazos (10/06/2009)
Where does @pCurrentMonth and @pCurrentYear come from?




It is coming from your code in http://forum.strataframe.net/FindPost24872.aspx in your sample you declare those variables, I simply use it as a sample, but you have to provide their values.
By Greg McGuffey - 10/6/2009

I think you're just missing it by a bit here Juan.



Two things are going on:



1. You are creating a SqlCommand object, that will become a SQL statement that is executed on SQL Server:



Using cmd As New SqlCommand

  '-- This builds a sql string (see below)

  Dim strSQLStringWithParameters = "SELECT *"

  strSQLStringWithParameters &= " FROM Sales"

  strSQLStringWithParameters &= " WHERE Payment = 1"

  strSQLStringWithParameters &= " AND Month(DatePay) = @pCurrenthMonth -- parameter"

  strSQLStringWithParameters &= " AND Year(DatePay)= @pCurrentYear -- parameter"






This will end up as the following SQL statement:



SELECT *

FROM Sales

WHERE Payment = 1

AND Month(DatePay) = @pCurrentMonth -- parameter

AND Year(DatePay) = @pCurrentYear -- parameter




2. You are defining the values used by the parameters. Here you define parameters that will end up in SQL, with values you are providing via .NET variables.



  '-- This defines what the parameters are for the SQL string

  '   The parameter name, @pCurrentMonth & @pCurrentYear, must match parameter used in SQL statement above.

  cmd.Parameters.AddWithValue("@pCurrenthMonth", currenthmonth).SqlDbType = SqlDbType.Int

  cmd.Parameters.AddWithValue("@pCurrentYear", currentyear).SqlDbType = SqlDbType.Int

  '-- fill table

End Using




Note that currentmonth and currentyear are just .net variables. You can pass them into the fill method or pass in a date and then get the month/year or as you originally showed, just use the current date. They are just .NET variables, handled however makes sense in your situation.



No matter how you load up the currentmonth/currentyear variables, their values are passed along to SQL Server as parameters. The Parameters.AddWithValue methods essentially gets translated into SQL again (assuming that currentmonth was set to 10 and currentyear was 2009:



Declare @pCurrentMonth int

Declare @pCurrentYear int

Set @pCurrentMonth = 10

Set @pCurrentYear = 2009




The important thing is that you define parameters within the SQL statement (" AND Month(DatePay) = @pCurrentMonth") and then also define a parameter of the correct SQL data type with the same name ("cmd.Parameters.AddWithValue("@pCurrentMonth", currentmonth)").
By Juan Carlos Pazos - 10/6/2009

Thanks Greg

I follow your explanetion and finally get this work. Here is the final code:

Public Sub FillVentasMes()

'-- Establish locals

Dim currentmonth As Integer = Date.Now.Month

Dim currentyear As Integer = Date.Now.Year

Dim loCommand As New SqlCommand()

'-- Build the query

loCommand.CommandText = "SELECT * FROM Pedidos WHERE Pagada = 1 AND Month(FechaPago) = @pCurrentMonth AND Year(FechaPago)= @pCurrentYear"

'-- Add the parameter

loCommand.Parameters.Add("@pCurrentMonth", SqlDbType.Int)

loCommand.Parameters("@pCurrentMonth").Value = currentmonth

loCommand.Parameters.Add("@pCurrentYear", SqlDbType.Int)

loCommand.Parameters("@pCurrentYear").Value = currentyear

'-- Execute the command to fill the business object

Me.FillDataTable(loCommand)

End Sub

Thanks for your help

Regards

By Greg McGuffey - 10/7/2009

Glad you got it working. Once you understand it, this is pretty straight forward. BigGrin