MySQL placeholder in visual studio 2008

Hi,

I am using visual studio 2008 with mysql.

I want to include a parameter in my fill query.

When I specify

SELECT * from 'table' WHERE 'tableid' =?param1

VS tells me there's an error with my syntax.

When I specify

SELECT * from 'table' WHERE 'tableid' =@param1

then the resulting fill method expects a string.

What's the correct placeholder for an INTEGER parameter?
MDWinterAsked:
Who is Participating?
 
itsmeandnobodyelseCommented:
>>>> public virtual int FillByCompID(soccersiteDataSet.competitionbyseasonDataTable dataTable, string comp)

Here you see that the second argument is of type string. Hence you couldn't pass an int but only the number as a string.

>>>> this.Adapter.SelectCommand.Parameters[0].Value = ((string)(comp));

The next is that the 'Value' member of the structure used to build the query also is a string. The goal is to construct an SQL statement out of the settings made to 'Adapter.SelectCommand'. And cause such a select statement is pure text they also want the inputs as text what spares conversion.


>>>> Can you explain how I would add a second fillbycompID which takes an int?      

 public virtual int FillByCompID(soccersiteDataSet.competitionbyseasonDataTable dataTable, int comp) {
            this.Adapter.SelectCommand = this.CommandCollection[1];
            // assuming that -1 is not a valid ID
            if ((comp == -1)) {
                throw new global::System.ArgumentNullException("comp");
            }
            else {
                this.Adapter.SelectCommand.Parameters[0].Value = comp.ToString();
            }
            if ((this.ClearBeforeFill == true)) {
                dataTable.Clear();
            }
            int returnValue = this.Adapter.Fill(dataTable);
            return returnValue;
        }

Or shorter:

public virtual int FillByCompID(soccersiteDataSet.competitionbyseasonDataTable dataTable, int comp)
{
      return  FillByCompID(dataTable, comp.ToString());
}


>>>> So the FillbyCompID method is obviously not getting any match.

The difference between both calls probably is here

      if ((this.ClearBeforeFill == true)) {
                dataTable.Clear();
 
That statement would clear the dataTable if the ClearBeforeFill is true.


I can't see why the 'count' member should change with your calls to  FillbyCompID.

Isn't the 'count' the result of a query? The  FillbyCompID only fills the where clause but doesn't make a query, right?




0
 
Jorge PaulinoIT Pro/DeveloperCommented:
That doesn't mean that is an Integer, that indicates that you're using a parameter in the T-SQL command.
@ is used to define a parameter in SQL, mySQL, etc
? is used to define a parameter in Access (OleDb).
In Access isn't really a parameter, is just an indication that will be used a value.
0
 
MDWinterAuthor Commented:
Really?

How come when I use the code:

            myDataSet.competitionbyseasonDataTable dt = new myDataSet.competitionbyseasonDataTable();
            int x = 12;
            competitionbyseasonTableAdapter1.FillByCompID(dt, x);

I get an error:

"Argument 2 cannot change int to string"

yet the code:

            myDataSet.competitionbyseasonDataTable dt = new myDataSet.competitionbyseasonDataTable();
            string x = "12";
            competitionbyseasonTableAdapter1.FillByCompID(dt, x);

works fine?

There must be some way of passing an integer to the mySQL query?


0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
itsmeandnobodyelseCommented:
>>>> There must be some way of passing an integer to the mySQL query?

If the FillByCompID expects a string for second argument you can't pass an int variable. So you either add a second FillByCompID which takes an int or you convert the integer to a string (like you did) what is a perfect way for a sql query to pass numbers as an sql statement is pure text anyhow.

Note, if the FillByCompID was created by wizard it probably has a string for second argument because the field is a varchar and not a number.
0
 
MDWinterAuthor Commented:
The query was created with the wizzard. But when I go back to the designer and click on the properties for that field, it lists it's datatype as system.int32

I don't understand how passing it as a string is any good, since the field is an INT in the database.

Can you explain how I would add a second fillbycompID which takes an int?
0
 
itsmeandnobodyelseCommented:
>>>> how passing it as a string is any good, since the field is an INT in the database
See the following statement:

  select * from myt where id=12345

You see that the 12345 is a number but nevertheless it is a string within the above sql query.

>>>> Can you explain how I would add a second fillbycompID which takes an int?

If you post the code of the current FillByCompID I will do it. I don't know much of C# as my language is C++.
0
 
MDWinterAuthor Commented:
OK - my database table looks like this:

CompBySeasonID(int)       Competition_CompetitionID(int)       Season_SeasonID(int)
7                                      12                                                    16
6                                      3                                                      12

and my mySQL query is

SELECT     CompBySeasonID, Competition_CompetitionID, Season_SeasonID
FROM         competitionbyseason
WHERE     ('Competition_CompetitionID' = @comp)

and the code for FillByCompID is:

        public virtual int FillByCompID(soccersiteDataSet.competitionbyseasonDataTable dataTable, string comp) {
            this.Adapter.SelectCommand = this.CommandCollection[1];
            if ((comp == null)) {
                throw new global::System.ArgumentNullException("comp");
            }
            else {
                this.Adapter.SelectCommand.Parameters[0].Value = ((string)(comp));
            }
            if ((this.ClearBeforeFill == true)) {
                dataTable.Clear();
            }
            int returnValue = this.Adapter.Fill(dataTable);
            return returnValue;
        }

When I run the following code:

int x = 12
competitionbyseasonTableAdapter1.FillByCompID(dt, x.ToString());
int y = dt.count;
MessageBox.Show(y.ToString());

I get 0.

When I run
competitionbyseasonTableAdapter1.Fill(dt) I get the correct row count of 2. So the FillbyCompID method is obviously not getting any match.



0
 
MDWinterAuthor Commented:
That's done the trick - many thanks :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.