regds.,
rangaWhich data provider are you using?
Terrisql
Exception Details:System.Data.SqlClient.SqlException: Syntax error converting character string to smalldatetime data type.
Source Error:
An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.[SqlException: Syntax error converting character string to smalldatetime data type.]
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +180
LEInternational.admin.login.btnLogin_Click(Object sender, EventArgs e) in W:\le-international\admin\login.aspx.cs:87
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
Try to identify the T-SQL/stored proc that is causing this erros and post it here.
|||I agree, it would help to see some code... It may be that you have some bad data in your database that won't convert to a smalldatetime. But it's impossible to know if the problem is in the stored procedure or your data without seeing the SP
I'm building a form that collects some info from the user (user name, password and type ) and I need to store this info into a table called USER that I created.
I'm using 3 text boxes and a button, when the user clicks the button I need to collect this info and store it into my table
when i click the button i get an error msg in this line (comm.ExecuteNonQuery(); )
what do i need to do?
here is the code
protectedvoid Button1_Click(object sender,EventArgs e){
string str ="data source=(local);initial catalog=NH_Project1;integrated security=true;";SqlConnection conn =newSqlConnection(str);
int rows;SqlCommand comm =newSqlCommand("INSERT INTO [USER](UserId, UserName, UserPassword, Type)VALUES" +
"@.UsrName,@.UsrPass,@.UsrType", conn);comm.CommandType =CommandType.Text;
comm.Parameters.AddWithValue("@.UsrName",TextBox1.Text);comm.Parameters.AddWithValue("@.UsrPass",TextBox2.Text);
comm.Parameters.AddWithValue("@.UsrType", TextBox3.Text);conn.Open();
rows= comm.ExecuteNonQuery();
conn.Close();
comm.Parameters.AddWithValue("@.UsrName",TextBox1.Text);comm.Parameters.AddWithValue("@.UsrPass",TextBox2.Text);
louayazar:
comm.Parameters.AddWithValue("@.UsrType", TextBox3.Text);
Remove the "@." symbol from your AddWithValue calls. Also, I'd recommend enclosing your ExecuteNonQuery call within a Try/Catch block so you can capture any errors in the future.
|||Hii
I tried to remove the "@." like what you said but that doesn't work, I still have the same error MSG when clicking the button.
|||
Then make sure to put your ExecuteNonQuery call into a Try/Catch block and see what exception you get. This should help you find out what's going on.
|||that was helpful, thanks a bunch
the column type of stime and etime in Dabase both are datetime
------------------------------
public DateTime loginTime
{
get
{
//got user enter page's time
return DateTime.Now;
}
}
//record user's using time when user click exit button
protected void Button1_Click(object sender, EventArgs e)
{
int Member_No = Int32.Parse(cls_login2.GetCurrent_login_Member_no(this.Page));
int Lesson_No = Int32.Parse(Lesson_no);
int Prestep = 1;
string RemoteIp = Request.ServerVariables["REMOTE_ADDR"].ToString();
string sqlConn = "Insert into pro_Study_log (Member_no,Lesson_no,Study_log_stepid,Study_log_stime,Study_log_etime,Study_log_ip) ";
sqlConn += "values(" + Member_No + "," + Lesson_No + "," + Prestep + "," + loginTime + ", getdate(), '" + RemoteIp + "')";
SqlConnection connLog = new SqlConnection(StrConn);
SqlCommand cmdLog = new SqlCommand(sqlConn, connLog);
cmdLog.CommandType = CommandType.Text;
connLog.Open();
cmdLog.ExecuteNonQuery();
connLog.Close();
--------
and then I got error message on column Study_log_stime
I also trid set the dateimt tostring , still doesn't work..
I don't know where I did wrong... pleae help
many thanks
You need to put quotes around logintime. Also, I'd recommend using Parameterized Queries rather than concatenating strings. With what you have now there is a high potential for SQL Injection attacks.
|||it's helpless if we just put quotes around logintime ( cause another error message)
problem has been resolved by use stored procedure...
thank you very much
I make the assignment in the Variable pane. Are there any other ways of doing this?
Watching the two variables reveals that only the value for the first variable is carried over during execution. The vendorname is always blank --> {}. I'm using a SQL Task with OLEDBConnection.
What's going on here?
Carried over during execution? What do you mean?
Is this a real problem or an issue with the diagnosis method. When watching variables, you must be on a breakpoint, and you must examine them in the watch window. Looking at the variables pane or looking when not on a breakpoint may not give you the correct value, as only the watch window at a breakpoint is updated during execution. Is a bit fussy, as you can see the value in several other windows, but they just do not get updated.
|||When you create a variable and assign it a default, that value never changes. During execution it can change, but when the package is done all variables revert back to the defaults you entered when you created the package.|||"revert back to the defaults" - think of it this way, when you execute a package the execution host loads it, then executes it and then does not save it. The execution host and the designer are two different things, albeit with some communication for the debug features. This is a change to the DTS behaviour if that is in your background.|||Guys thanks so much for th replies. I'm still in the development phase obviously and I just want the flexibility of setting values to the package variables. For this, I use the Variable pane of Visual Studio where the last column - I think - is for the default value to be used on every run. I tried to use the Script task but I couldnt find my package variables. My vendorId which is initialized to -1 retains its value during the execution; at least that's what the Watch window is showing me. The other variable User::vendorName is always blank even if I set it to a certain string value prior to F5. My control flow doesnt have any event handler. Should you need more clarification guys, I'll do everything to make this clear.I created another configuration but I can't find it in the solution explorer or in the bin table? Is the configuration available only after deployment?
|||An XML configuration will be created at whatever path you specify when you create the configuration. If you open the package configurations dialog, you should see the path to the file.
Is it possible to (A) use some other xs format that changes DF Metadata to only use date and not DT_DBTIMESTAMP, or (B) is there a field type in the destination FLFF Connection that will allow a length of 10 without throwing a truncation error?The only output column date type supported by the XmlSrc is DT_DBTIMESTAMP. You could try changing the output column type to a string in the advanced editor, and set it to the length you want, and set the truncation disposition to ignore truncations.
Or, You could place a Data Conversion transform on the output of the XmlSrc, and either convert the DT_DBTIMESTAMP column to DT_DBDATE (which does not include the time), or actually convert the DT_DBTIMESTAMP to a string of the desired length and set the truncation disposition to ignore truncations.
Let me know if this solves your problem, or you need a different solution.
Mark|||Thanks Mark the feedback. Yep, I ended up implementing the first solution and works ok. The second solution is a good one as well but seems a bit too much, we will just enforce no date_type validations for now. Do you (or anyone else) know if this ability will ever be natively incorporated into SSIS?|||Do you mean, will it be possible to map the xml input to a date type other than just DT_DBTIMESTAMP directly in the XmlSrc adapter? If so, I know of no plans for that, but it would be great if you could open a DCR bug to that effect.