| Author |
Topic |
|
sirmilt
Starting Member
49 Posts |
 Posted - 2010-11-20 : 13:20:41
|
| I'm working on a project using SQL Server Express 2008 R2 and Visual Basic 2008.My table has 8 text fields, each using the data type VarChar(num) with the number(num) ranging from 5 to 50. There are also number fields and a boolean field in the table.When I try to update the table in Visual Basic the program runs through to completion, but the VarChar fields only contain the first character. The other fields come out fine.Obviously I have done something wrong in defing the fields, but I can't figure out what.Can anyone point me in the right direction so that I can try and correct (and prevent for the future) this problem.Milt |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-20 : 18:54:35
|
| show us the table definition and the update statement. and as Brett said, look at the vb data types |
 |
|
|
Yalini2212
Starting Member
12 Posts |
Posted - 2010-11-21 : 00:08:40
|
| When you pass the variable to update table, use Trim(). |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-11-21 : 00:18:42
|
| ya know...people made careers out of SASI am so glad that the "hot" technologies" were given the the teachers petspests? I don't speel so well |
|
|
|
sirmilt
Starting Member
49 Posts |
Posted - 2010-11-21 : 10:12:31
|
| To complete my question, here is the stored procedure in SQL Server:# @Title varchar = 0, @Authors varchar = a, @Series varchar = 0, @Issued smallint = 0, @Price decimal =0.00, @ISBN varchar = 0, @Category varchar = 0, @Condition varchar = 0, @Type varchar = 0, @Signed bit = NO, @BookLoanStatus varchar, @Comments varchar #Here's the code block in Visual Basic 2008:# Public Sub SaveBook() Using oData As New SMABase If boNewBook = True Then 'Set Insert query... strSQL = "qryBookInsert" Else 'Set Update query strSQL = "qryBookSaveChanges" End If Try With oData .SQL = strSQL .InitializeCommand() .cmd.Parameters.AddWithValue("@Title", txtTitle.Text) .cmd.Parameters.AddWithValue("@Authors", txtAuthors.Text) .cmd.Parameters.AddWithValue("@Series", txtSeries.Text) .cmd.Parameters.AddWithValue("@Issued", lngBookIssued) .cmd.Parameters.AddWithValue("@Price", decBookprice).DbType = DbType.Decimal .cmd.Parameters.AddWithValue("@ISBN", txtISBN.Text) .cmd.Parameters.AddWithValue("@Category", cboCategory.Text) .cmd.Parameters.AddWithValue("@Condition", cboCondition.Text).DbType = DbType.String .cmd.Parameters.AddWithValue("@Type", cboType.Text) .cmd.Parameters.AddWithValue("@Signed", chkSigned.CheckState).DbType = DbType.Boolean .cmd.Parameters.AddWithValue("@BookLoanStatus", strLoanStatus) .cmd.Parameters.AddWithValue("@Comments", txtComments.Text) If boNewBook = False Then .cmd.Parameters.AddWithValue("@ID", lngBookID) End With oData.OpenConnection() intRowsAffected = oData.cmd.ExecuteNonQuery oData.CloseConnection() Catch ex As Exception MsgBox(ex.Message) End Try If intRowsAffected = 0 Then lblStatusHelp.Text = "Record was not saved." & vbNewLine & "Please check your entries." End If End Using End Sub #Until yesterday this worked fine and I made no changes to the VB code. If I create a record in the SSMS it works fine. In VB all text fields save with only one character regardless of the lenth of the TextBox entry..I'm at a loss.Milt |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-21 : 10:36:57
|
| either that isn't the proc, or it never worked fine.1. "@Signed bit = NO" -- this is going to generate a coversion error every time. bit fields expect an integer value of 1 or 0. "NO" won't implicitly cast.2. the problem is that you haven't defined lengths in your varchar parameters. there is no chance that this ever worked right, unless you only passed single characters for each param.define the variables with the same length as the corresponding columns -- for example @title varchar(32) |
|
|
|
sirmilt
Starting Member
49 Posts |
Posted - 2010-11-21 : 11:22:59
|
| russellYour solution was perfect. After a lot of tries, and even more frustration, my query works...I have read a lot of suggestions for creating a Stored Procedure, but none even hinted at the need to set the maximum character count in the parameter statement.Thanks againMiltMilt |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-21 : 11:33:46
|
| Glad you got it worked out. Keep coming back when you have more questions! |
|
|
|
|
Text Values Truncated