By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

String or binary data would be truncated. The statement has been terminated

P: 22
I'll trying to update some information, the data that i want to update its also came from same sql database, and that error showing . thanks in advance
Attached Files
File Type: txt help.txt (596 Bytes, 188 views)
Apr 2 '14 #1

✓ answered by mcupito

You really haven't given us much to go on. You should really add Parameters when dealing with a database.

See my code for an example:

Expand|Select|Wrap|Line Numbers
  1.         Dim conn As New SqlConnection("Data Source=SQLTEST_HR,4000\SQLEXPRESS;Integrated Security=True")
  2.         Dim cmd As New SqlCommand
  3.  
  4.         'insert statement
  5.         cmd.CommandText &= " INSERT INTO ELECT_SALARY_DFRL (ELECT_XREF_SEQ_ID, EMPL_SALARY_DFRL_AMT, EMPL_SALARY_DFRL_FREQ_CD, "
  6.         cmd.CommandText &= "  FUND_SEQ_ID, ELECT_SALARY_DFRL_FUND_PCT_RT, ELECT_SALARY_DFRL_ELECT_DT, BEG_DT, END_DT)"
  7.         cmd.CommandText &= " VALUES (@SeqID, @DefAmt, @Freq, @FundID, @FundPct, @Today, @BegDT, @EndDT); "
  8.  
  9.         ' parameters for sql insert, from DGV
  10.         cmd.Parameters.AddWithValue("@SeqID", empseqId)
  11.         cmd.Parameters.Add("@DefAmt", SqlDbType.VarChar)
  12.         cmd.Parameters.Add("@Freq", SqlDbType.VarChar)
  13.         cmd.Parameters.Add("@FundID", SqlDbType.Int)
  14.         cmd.Parameters.Add("@FundPct", SqlDbType.VarChar)
  15.         cmd.Parameters.AddWithValue("@Today", Date.Now())
  16.         cmd.Parameters.AddWithValue("@BegDT", "19000101")
  17.         cmd.Parameters.AddWithValue("@EndDT", "24000101")
  18.  
  19.         conn.Open()
  20.         cmd.Connection = conn
  21.         cmd.ExecuteNonQuery()
  22.  
I put the cmd.ExecuteNonQuery() in a Try Catch block to throw a meaningful exception, however that might be out of the scope for your sole inquiry.

Hope this helps.

Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Please post your code using code tags. As a general rule, I do not download attachments.
Apr 2 '14 #2

P: 22
Expand|Select|Wrap|Line Numbers
  1.  conn = GetConnect()
  2.  
  3.  
  4.  
  5.  
  6.  
  7.  
  8.             MySqlCommand = New SqlCommand("Update Tbl_Member_Profile set F_Name='" & txt_F_Name.Text & 
  9.  
  10. "', M_Name='" & txt_M_Name.Text & "', L_Name='" & txt_L_Name.Text & "', Credit_Line='" & txt_cre_line.Text 
  11.  
  12. & "', Rec_Last_Update='" & Date.Today & "', Age='" & txt_age.Text & "', Date_Of_Birth='" & datepicker.Value 
  13.  
  14. & "', Credit_Rating='" & txt_ranking.Text & "', Membership_Category='" & txt_membership.Text & "' WHERE 
  15.  
  16. P_Member_Code='" & txt_Cus_No.Text & "'", conn)
  17.  
  18.  
  19.             conn.Open()
  20.  
  21.  
  22.  
  23.  
  24.  
  25.             MySqlCommand.ExecuteNonQuery()
  26.  
  27.  
''like this one sir? im sorry this is my first time to join this kind of forum
Apr 3 '14 #3

Rabbit
Expert Mod 10K+
P: 12,366
That error means that one of the values you are inserting is too long for the field.
Apr 3 '14 #4

100+
P: 294
You really haven't given us much to go on. You should really add Parameters when dealing with a database.

See my code for an example:

Expand|Select|Wrap|Line Numbers
  1.         Dim conn As New SqlConnection("Data Source=SQLTEST_HR,4000\SQLEXPRESS;Integrated Security=True")
  2.         Dim cmd As New SqlCommand
  3.  
  4.         'insert statement
  5.         cmd.CommandText &= " INSERT INTO ELECT_SALARY_DFRL (ELECT_XREF_SEQ_ID, EMPL_SALARY_DFRL_AMT, EMPL_SALARY_DFRL_FREQ_CD, "
  6.         cmd.CommandText &= "  FUND_SEQ_ID, ELECT_SALARY_DFRL_FUND_PCT_RT, ELECT_SALARY_DFRL_ELECT_DT, BEG_DT, END_DT)"
  7.         cmd.CommandText &= " VALUES (@SeqID, @DefAmt, @Freq, @FundID, @FundPct, @Today, @BegDT, @EndDT); "
  8.  
  9.         ' parameters for sql insert, from DGV
  10.         cmd.Parameters.AddWithValue("@SeqID", empseqId)
  11.         cmd.Parameters.Add("@DefAmt", SqlDbType.VarChar)
  12.         cmd.Parameters.Add("@Freq", SqlDbType.VarChar)
  13.         cmd.Parameters.Add("@FundID", SqlDbType.Int)
  14.         cmd.Parameters.Add("@FundPct", SqlDbType.VarChar)
  15.         cmd.Parameters.AddWithValue("@Today", Date.Now())
  16.         cmd.Parameters.AddWithValue("@BegDT", "19000101")
  17.         cmd.Parameters.AddWithValue("@EndDT", "24000101")
  18.  
  19.         conn.Open()
  20.         cmd.Connection = conn
  21.         cmd.ExecuteNonQuery()
  22.  
I put the cmd.ExecuteNonQuery() in a Try Catch block to throw a meaningful exception, however that might be out of the scope for your sole inquiry.

Hope this helps.
Apr 3 '14 #5

P: 22
Thank you so much Mr.mcupito, it's really works. .
also Mr.Rabbit . .
Apr 4 '14 #6

100+
P: 294
No problem. Hope it helps
Apr 4 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.