SQL SetParameter Question

[BPak]

I am creating a program using modules and find there are issues in the SetParameters I am not understanding.

Program example extracts below:

From the Import.bas file I call a Sub in DM module.bas

   TheFile = "20091206"
   TheDbDate = TheFile + 0
   
   Msgbox("TheDbDate " & TheDbDate)
   DM.WorkMeetDte(TheDbDate)

In the DM.bas module I have the Public Sub (which works as shown)

Public Sub WorkMeetDte(TheDbDate)
   ' open MeetDte Table in Data Database
   ' check if this TheDbDate exists which says files have already been imported for this date.
   ' then check if this meeting 'RceCde' are in already or can be added
   
   con.BeginTransaction
   
   cmd.AddParameter("TheDbDate")
   cmd.SetParameter("TheDbDate", TheDbDate)
   cmd.CommandText = "SELECT * FROM MeetDte WHERE TheDbDate=@TheDbDate"
   reader.Value = cmd.ExecuteReader
      
   If reader.ReadNextRow = True Then
      ' must have found the row if a record is here
      Msgbox("Exists MeetDte entry")
   Else
      ' not new row so no record found
      ' add new record to MeetDte table
      Msgbox("New MeetDte entry")
   End If
   reader.Close
   con.EndTransaction
End Sub

However if I want to use a different name in the SetParameter It gives an error message. Below in the example I have changes the Parameter name in the sub to DbDate and the SetParameter to reflect that name (cmd.SetParameter("TheDbDate", DbDate), and so on

Code such as this:

Public Sub WorkMeetDte(DbDate)
   ' open MeetDte Table in Data Database
   ' check if this TheDbDate exists which says files have already been imported for this date.
   ' then check if this meeting 'RceCde' are in already or can be added
   
   con.BeginTransaction
   
   cmd.AddParameter("TheDbDate")
   cmd.SetParameter("TheDbDate", DbDate)
   cmd.CommandText = "SELECT * FROM MeetDte WHERE TheDbDate=@DbDate"
   reader.Value = cmd.ExecuteReader
      
   If reader.ReadNextRow = True Then
      ' must have found the row if a record is here
      Msgbox("Exists MeetDte entry")
   Else
      ' not new row so no record found
      ' add new record to MeetDte table
      Msgbox("New MeetDte entry")
   End If
   reader.Close
   con.EndTransaction
End Sub

WHY DOES THE ERROR SHOW UP From a Simple Variable change?

 

[Erel]

The parameter name should match the name specified in the query.
In this case you specified @DbDate in the query but set the value of TheDbData.

 

[mjcoon]

WHY DOES THE ERROR SHOW UP From a Simple Variable change?

Because as well as changing the variable name in the SetParameter() (which you say you wished to do) you have also changed the name of the parameter from

   cmd.CommandText = "SELECT * FROM MeetDte WHERE TheDbDate=@TheDbDate"

to

   cmd.CommandText = "SELECT * FROM MeetDte WHERE TheDbDate=@DbDate"

which presumably you did not intend to do.

Mike.

 

[BPak]

TheDbDate is the Field in the database

DbDate is the variable I want to use in the search for the correct Record in the database.

In the help file it is that way

cmd.AddParameter("[B]value1[/B]")
cmd.AddParameter("[B]value2[/B]")
cmd.CommandText = "INSERT INTO [table1] (col1,col2) VALUES (@value1,@value2)"
Con.BeginTransaction
cmd.SetParameter("[B]value2[/B]","SomeValue")
For i = 0 To 10
      cmd.SetParameter("[B]value1[/B]",i * 2)
      cmd.ExecuteNonQuery
Next
Con.EndTransaction

I dont see where I am wrong??

cmd.AddParameter("TheDbDate")
cmd.SetParameter(DbDate, "TheDbDate")

Should I swap the values around in the SetParameter?

 

[BPak]

mjcoon:

Yes did meant to change it..

DbDate is passed into the SUB and it is the variable that contains the data that is to be found in the Query.

So in the SetParameter i used DbDate and in the query i used DbDate so that they matched.

Could you explain where the variable can be different to the FIELD name.

field name is TheDbDate

Thanks for help?

 

[Erel]

Instead of:

    cmd.AddParameter("TheDbDate")
    cmd.SetParameter("TheDbDate", DbDate)
cmd.CommandText = "SELECT * FROM MeetDte WHERE TheDbDate=@DbDate"

You should write:

    cmd.AddParameter("DbDate") 'should match the value after @...
    cmd.SetParameter("DbDate", DbDate)
    cmd.CommandText = "SELECT * FROM MeetDte WHERE TheDbDate=@DbDate"

 

[BPak]

Thank you Erel. That explains it clearly!!

 

[mjcoon]

Thank you Erel. That explains it clearly!!

To be honest, I don't think that Erel really explained it!

I'm certain that if you undid the change I thought you didn't mean to make it would also work. That was the cause of your error message.

What you are ignoring is that the SetParameter() merely moves the value of the variable into the parameter within SQLite. There does not need to be any match between the name of the variable, which could be called subParameter, or whatever, and the name of the SQL parameter. But the name of the SQL parameter must match in all three of AddParameter(), SetParameter(), and following the "@" in the command text.

I agree that it makes sense to have names for entities that show what they refer to, but it may also help to make them more distinct. E.g. add a prefix "sqlp" to the names of SQL parameters to identify that is their context.

Using a prefix like that also helps to ensure that a repeated replace-all only changes the names that you wish to change, and not other names that just happen to be the same...

HTH, Mike.

 

[BPak]

Hi Mike,

I'm certain that if you undid the change I thought you didn't mean to make it would also work. That was the cause of your error message.

Looking back at the code I put up I can see that you were correct. I only needed to restore the @TheDbDate from @DbDate in the SQL statement.

That is an excellent suggestion of naming the Parameters uniquely with a Suffix.

    cmd.AddParameter("sqlpDbDate") 'should match the value after @...
    cmd.SetParameter("sqlpDbDate", DbDate)
    cmd.CommandText = "SELECT * FROM MeetDte WHERE TheDbDate=@sqlpDbDate"

Like that?

 

[mjcoon]

Hi Mike,
...
Like that?

That is what I had in mind. I have to admit I haven't done that myself, but I might do in future!

Mike.

 

[BPak]

Thanks for the help Erel and Mike.

Now I have to add Indexes to my Tables, which I have found out how to do, but will have to go find out how to apply them into the SELECT * FROM

WHERE ....

And then to see if I can use TWO different DATABASES in the one program. Dont need Joins - but like to keep some data seperate as some are more LOOKUP tables.

Good Help file comes with the SQLITE Dowload for NET but not a lot of example in it.

There forum may have something.

So thanks again for help with the SetParameters.

 

[Saj]

Hi

I seem to be having a spot trouble with SetParameter. I'm attempting to add a JGP file:

DBcommand.AddParameter("tname1")
DBcommand.AddParameter("JPG")
DBcommand.CommandText  = "INSERT INTO [" &newTableName& "] (tilename, JPG) VALUES (@tname1,@JPG)"   

DBcommand.SetParameter("tname1",1)
DBcommand.SetParameter("JPG",DBcommand.FileToBLOB(AppPath & "\tile1.jpg")& ")")
DBcommand.ExecuteNonQuery

This results in a new row being added, but without the image. I get no error messages.

 

[mjcoon]

This results in a new row being added, but without the image. I get no error messages.

Well, if it were me writing this, I would assume that trying to add a blob via the parameter mechanism is one step too difficult, so I would write it like the FileToBLOB() example direct into the command string and not via a parameter.

(The difficulty lies in representing an unstructured item like a blob within a parameter.)

I do not see any advantage in using a parameter in this sort of code in any case.

Mike.

 

[Saj]

Yes, thats done the trick. Thnx.