SQL problem in updating table

[Stogie03]

I am trying to save some records to a sql table. I am trying to save a string for the Market segment. But i get a problem, "Cannot set column 'Market Seg'. The value violates the MaxLength limit of this column."

The datatype set for that column is text, and the value trying to be stored in it is "Magazine - B2B". Im new to SQL, can someone point out what is going on?

If saverow = 0 Then
            saverow2 = MainDBDataSet.Assesments.NewRow()
            saverow2.Item("Customer") = CustomerName
            saverow2.Item("Cust ID") = CustomerNum
            saverow2.Item("Title") = title
            saverow2.Item("Title ID") = title
            saverow2.Item("Market Seg") = marketSeg
            MainDBDataSet.Tables("assesments").Rows.Add(saverow2)
            AssesmentsTableAdapter.Update(MainDBDataSet.Assesments)

[Clanguage]

It is very bad practice to name table fields with space in them. If you cannot avoid it then at least surround them with brackets although I do not see any valid cases where you would need to use spaces. You will encounter all kinds of unwanted behavior when you use spaces.

If saverow = 0 Then
            saverow2 = MainDBDataSet.Assesments.NewRow()
            saverow2.Item("Customer") = CustomerName
            saverow2.Item("[Cust ID]") = CustomerNum
            saverow2.Item("Title") = title
            saverow2.Item("[Title ID]") = title
            saverow2.Item("[Market Seg]") = marketSeg
            MainDBDataSet.Tables("assesments").Rows.Add(saverow2)
            AssesmentsTableAdapter.Update(MainDBDataSet.Assesments)

[Stogie03]

Thanks, that seemed to solve the problem. However the values are not staying in the database, when the program ends the SQL database is not updated. The method im using worked for Access does it not update a SQL database? When the program starts over the values in the DB are gone.

[Clanguage]

You need to show us the code if you want to know why the data does not persist.

[Stogie03]

saverow2 = MainDBDataSet.Assesments.NewRow()
            saverow2.Item("Customer") = CustomerName
            saverow2.Item("CustID") = CustomerNum
            saverow2.Item("Title") = title
            saverow2.Item("TitleID") = title
            saverow2.Item("MarketSeg") = marketSeg
            saverow2.Item("Frequency") = frequency
            saverow2.Item("Reason") = reasonForAssess
            saverow2.Item("term") = termofquote
            saverow2.Item("prepress") = prepressTotal
            saverow2.Item("press") = CoverTotal + BF1Total + BF2Total + BF3Total + BF4Total + coatingtotal
            saverow2.Item("bind") = bindTotal
            saverow2.Item("ancillary") = 1
            saverow2.Item("paper") = PaperTotPrice
            saverow2.Item("prepressBM") = prepressTotalBM
            saverow2.Item("pressBM") = pressbm
            saverow2.Item("bindBM") = bindTotalBM
            saverow2.Item("ancillaryBM") = 1
            saverow2.Item("paperBM") = PaperTotPrice

            MainDBDataSet.Tables("assesments").Rows.Add(saverow2)
            AssesmentsTableAdapter.Update(MainDBDataSet.Assesments)
            MsgBox("Assesment has been saved to database")

[GaryMazzone]

Are you using SQL Exress or SQL Server (the full version)? You might have set the application to overwrite the database file on application start.

[Clanguage]

Hi Stogie03,
I am glad to see that you corrected the names to not include spaces. Great move.
Do you have this code in a loop and an error control block? Do you get any error? Can you step into the code and see what your update is doing?

[Stogie03]

Its the full version i believe, developer edition.

How would i have set the application to overwrite the DB on startup. This is the only place the code appears. In visual studio the database appears as all null's when i view the table.

This method works fine on my Access DB's, im just trying to switch things over to SQL.