StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » How to use parameters rather string statement

How to use parameters rather string statement

Juan Carlos Pazos — Wed, 07 Oct 2009 12:17:32 GMT

I have this:

[codesnippet]

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 & "")

[/codesnippet]

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.

RE: How to use parameters rather string statement

Greg McGuffey — Wed, 07 Oct 2009 12:17:32 GMT

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

RE: How to use parameters rather string statement

Juan Carlos Pazos — Wed, 07 Oct 2009 01:15:49 GMT

Thanks Greg

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

[codesnippet]

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

[/codesnippet]

Thanks for your help

Regards

RE: How to use parameters rather string statement

Greg McGuffey — Tue, 06 Oct 2009 23:57:24 GMT

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:

[codesnippet]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"[/codesnippet]

This will end up as the following SQL statement:

[codesnippet]SELECT *
FROM Sales
WHERE Payment = 1
AND Month(DatePay) = @pCurrentMonth -- parameter
AND Year(DatePay) = @pCurrentYear -- parameter[/codesnippet]

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.
[codesnippet]
  '-- 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[/codesnippet]

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:

[codesnippet]Declare @pCurrentMonth int
Declare @pCurrentYear int
Set @pCurrentMonth = 10
Set @pCurrentYear = 2009[/codesnippet]

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)").

RE: How to use parameters rather string statement

Edhy Rijo — Tue, 06 Oct 2009 20:25:44 GMT

[quote][b]Juan Carlos Pazos (10/06/2009)[/b][hr]Where does @pCurrentMonth and @pCurrentYear come from?[/quote]

It is coming from your code in [url=]http://forum.strataframe.net/FindPost24872.aspx[/url] in your sample you declare those variables, I simply use it as a sample, but you have to provide their values.

RE: How to use parameters rather string statement

Juan Carlos Pazos — Tue, 06 Oct 2009 19:43:05 GMT

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

RE: How to use parameters rather string statement

Edhy Rijo — Tue, 06 Oct 2009 19:27:04 GMT

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?

RE: How to use parameters rather string statement

Juan Carlos Pazos — Tue, 06 Oct 2009 18:13:32 GMT

Hi Edhy and Greg

I will use this:

[codesnippet]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[/codesnippet]

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

RE: How to use parameters rather string statement

Greg McGuffey — Tue, 06 Oct 2009 14:49:20 GMT

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, [b]all[/b] of my sql statements are parametrized.

RE: How to use parameters rather string statement

Edhy Rijo — Tue, 06 Oct 2009 12:26:17 GMT

Hi Juan Carlos,

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

[quote]
'-- 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
[/quote]