SQL Inset Sub, having problem's please help

[TrAcER]

    Sub insCard(ByVal strSet As String, ByVal strName As String, ByVal strColor As String, ByVal strCost As String, ByVal strType As String, ByVal strPowtgh As String, ByVal strRules As String, ByVal strFlavor As String, ByVal strRarity As String, ByVal strArtist As String)
        Dim connectionString As String = "server='(local)'; trusted_connection=true; database='DNScards'"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

        Dim queryString As String = "INSERT INTO [tblCardlist] ([strSet], [strName], [strColor], [strCost], [strType]," & _
            " [strPowtgh], [strRules], [strFlavor], [strRarity], [strArtist]) VALUES (@strSet" & _
            ", @strName, @strColor, @strCost, @strType, @strPowtgh, @strRules, @strFlavor, @s" & _
            "trRarity, @strArtist)"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_strSet As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strSet.ParameterName = "@strSet"
        dbParam_strSet.Value = strSet
        dbParam_strSet.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strSet)
        Dim dbParam_strName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strName.ParameterName = "@strName"
        dbParam_strName.Value = strName
        dbParam_strName.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strName)
        Dim dbParam_strColor As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strColor.ParameterName = "@strColor"
        dbParam_strColor.Value = strColor
        dbParam_strColor.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strColor)
        Dim dbParam_strCost As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strCost.ParameterName = "@strCost"
        dbParam_strCost.Value = strCost
        dbParam_strCost.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strCost)
        Dim dbParam_strType As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strType.ParameterName = "@strType"
        dbParam_strType.Value = strType
        dbParam_strType.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strType)
        Dim dbParam_strPowtgh As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strPowtgh.ParameterName = "@strPowtgh"
        dbParam_strPowtgh.Value = strPowtgh
        dbParam_strPowtgh.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strPowtgh)
        Dim dbParam_strRules As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strRules.ParameterName = "@strRules"
        dbParam_strRules.Value = strRules
        dbParam_strRules.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strRules)
        Dim dbParam_strFlavor As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strFlavor.ParameterName = "@strFlavor"
        dbParam_strFlavor.Value = strFlavor
        dbParam_strFlavor.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strFlavor)
        Dim dbParam_strRarity As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strRarity.ParameterName = "@strRarity"
        dbParam_strRarity.Value = strRarity
        dbParam_strRarity.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strRarity)
        Dim dbParam_strArtist As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_strArtist.ParameterName = "@strArtist"
        dbParam_strArtist.Value = strArtist
        dbParam_strArtist.DbType = System.Data.DbType.StringFixedLength
        dbCommand.Parameters.Add(dbParam_strArtist)

        dbConnection.Open()
        Try
            dbCommand.ExecuteNonQuery()
        Finally
            dbConnection.Close()
        End Try
    End Sub

So im passing this all the right variables, but its falling over any ideas why guys? please help this is almost mission critical

[plenderj]

Where is it falling over?

[TrAcER]

when it comes to executenoquery

just dies for no reson, i used web matrix to generate the code and just slapped it into VB.net and changed it into a sub coz i dont need the postbk stuff, but it just will not dump into the database, i dunno if im doing anything wrong?

[plenderj]

Okay well in your Try..Catch statement you're not actually catching any exceptions. Hang on I'll post a sample here ...

[plenderj]

Try
            mySQLCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message & vbCrLf & ex.StackTrace & vbCrLf & vbCrLf & vbCrLf & strSQLStatement)
        End Try

[TrAcER]

ok i'll add it in now 1 sec

[TrAcER]

String of binary data would be truncated
the statment will be terminated
do you need the rest?

[TrAcER]

String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at CardList.Form1.insCard(String strSet, String strName, String strColor, String strCost, String strType, String strPowtgh, String strRules, String strFlavor, String strRarity, String strArtist) in C:\Documents and Settings\tracer\My Documents\Visual Studio Projects\CardList\Form1.vb:line 465


INSERT INTO [tblCardlist] ([strSet], [strName], [strColor], [strCost], [strType], [strPowtgh], [strRules], [strFlavor], [strRarity], [strArtist]) VALUES (@strSet, @strName, @strColor, @strCost, @strType, @strPowtgh, @strRules, @strFlavor, @strRarity, @strArtist)

[TrAcER]

anygood? had to change msgbox coz it woulnt let me copy and paste ahahha, debug console rules, so i take it in my database one of the field lengh's is too small ? and it dosent know how to deal with it?

[plenderj]

That's what it sounds like all right. How long are your fields in the DB?

[TrAcER]

now they are 100 char in lengh
so shoulnt be a problem here is a samle of what im dumping


Card Name: Anodet Lurker
Color: A
Cost: 5
Type: Artifact Creature
Pow/Tgh: 3/3
Rules Text: When Anodet Lurker is put into a graveyard from play, you gain 3 life.
Flavor Text: These machines build fearsome visages out of scrap
metal to scare off predators who would otherwise be
eager to consume them.
Rarity: C
Artist: Jeff Easley

there are some leading and trainling sapce im getting rid of them im getting rid of everything upto and including the : so its just the infomation

and thats what im dumping into that sql statment? now the field lenghs are 100 and its still falling over

[plenderj]

Try setting the fields to length of 512.

[TrAcER]

ok that worked good catch, but why did it do that? the stuff is no where near 512 in lengh is it?

[TrAcER]

hmm maybe its just enterprise manager but there seems to be alot of trailing spaces not sure gonna investigate, thats strange, the data as you can see is quite small, hmmm any ideas buddy, btw many thanks forthe help, total life saver

[plenderj]

Try using Trim() on your strings first. Also check out the actual data in the DB - check is any of it longer than 100 characters...

[TrAcER]

they are all trimed befor i pass them, thats what i said before, nothing but the data left ???

[plenderj]

Okay well what's the actual length of the data?
From enterprise manager, try this :
SELECT *
FROM tblProducts
WHERE (LEN(strDescription) > 100)

(that works for me on my database, change the table name and the field name)

[TrAcER]

so yeah they are over 100 char for some reson but, the shoulnt be coz they have been trimmed and i have checked that in VS.net debugger, i watched my variables and there are no spaces ahhh what do do

[plenderj]

Well perhaps its just simply the text you're adding happens to be longer than 100 chars?

[TrAcER]

nope, its like there are loads of space at the end, very strange, but they are trimmed twice before the variables are passed so i dunno, i guess i'll have to live with it lol

[plenderj]

Are you sure they're spaces and not just nulls or some other non-printable character?

[TrAcER]

im gonna re write the cod into asp.net now so i'll see if i still get te issue there

[Mike Hildner]

Do you think that setting your dbtype to System.Data.DbType.StringFixedLength has anything to do with it? Would make sense that it's being padded if you have a fixed length. Quick look and I could not find the default length for System.Data.DbType.StringFixedLength

Mike

[TrAcER]

cheers mike, i'll take alook