Click here to Skip to main content
15,885,365 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i am trying to populate a gridview with a stored procedure and some parameters. for some oddball reason, i get this error:

"when converting a string to datetime parse the string to take the date before putting each variable into the datetime object"

the odd thing is, i'm not trying to pass a datetime parameter, though the stored procedure does use the fn CURDATE() to filter some of the objects, but that should be taken care of in the sql right?

the error comes at the dataAdapter.Fill line.

here's the code.

SQL
ALTER PROCEDURE SearchAllPlayers
(
@personName,
@IDs
)

SELECT DISTINCT
                         person.personID, sports.sportsID, person.personName, sports.sport,
 
FROM            sports INNER JOIN
                person INNER JOIN
                personSport ON personSport.personID = person.personID
                       INNER JOIN
                personSport ON personSport.sportID = sports.sportsID
 
WHERE        (person.personName LIKE '%' + @personName + '%')
              AND
             (person.regDate >= { fn CURDATE() }
              AND
             (sports.sportID IN
                    (SELECT   ID FROM dbo.fnSplitter(@IDs) AS fnSplitter_1) OR @IDs = 0)


C#
protected void Button1_Click(object sender, EventArgs e)
   {
       searchGrid();
   }

   protected void searchGrid()
   {
       SqlConnection conn = new SqlConnection(connSTR);
       com = new SqlCommand();
       conn.Open();

       com.Connection = conn;

       com.CommandText = "SearchAllPlayers";
       com.CommandType = CommandType.StoredProcedure;

       string StringWithDelimiter = string.Empty;
       for (int i = 0; i < DropDownCheckBoxes1.Items.Count; i++)
       {
           if (DropDownCheckBoxes1.Items[i].Selected)
               StringWithDelimiter += DropDownCheckBoxes1.Items[i].Value + ";";
       }

       com.Parameters.Add("@personName", SqlDbType.VarChar).Value = personName.Text;
       com.Parameters.Add("@IDs", SqlDbType.VarChar).Value = StringWithDelimiter;

       sqlDa = new SqlDataAdapter(com);
       dS = new DataSet();
       sqlDa.Fill(dS);

       conn.Close();
       GridView1.DataSource = dS;
       GridView1.DataBind();

   }


i cannot understand why i would have this issue. please help
Posted

1 solution

Instead of (person.regDate >= { fn CURDATE() }

use

person.regDate >= GetDate()
 
Share this answer
 
Comments
memberxxxxxxxxxxxxxxxxx 6-Jun-13 1:15am    
thanks.
i added a new parameter:

com.parameters.add("@playerPay", sqldbtype.decimal).value = playerPayTextBox.Text;

this doesn't seem to work, i get the following error:
"Error converting data type nvarchar to decimal."

so then i tried this:
com.parameters.add("@playerPay", sqldbtype.decimal).value = playerPayTextBox.tostring();
i get the same error:
"Error converting data type nvarchar to decimal."

which makes sense, so then i tried:

com.parameters.add("@playerPay", sqldbtype.decimal).value = decimal.parse(playerPayTextBox.tostring());
error: "Input string was not in a correct format." - then the datetime thing...

com.parameters.add("@playerPay", sqldbtype.decimal).value = decimal.parse(playerPayTextBox.text);
error: "Input string was not in a correct format." - then the datetime thing...

not sure what to do
tumbledDown2earth 6-Jun-13 2:30am    
Please post your new parameter question as a new question

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900