SQL Problem

[BigPandaCake]

I've been trying for hours to do this and can't figure it out.. I'm trying to add a new record to a table in my local database.. Heres the SQL code im using at the minute.. I keep getting this error

"The data was truncated while converting from one data type to another. [ Name of function(if known) = ]" on the line

SQLCom.ExecuteNonQuery()

Dim SQLCon As New SqlServerCe.SqlCeConnection
        Dim SQLCom As New SqlServerCe.SqlCeCommand
        SQLCon = New SqlServerCe.SqlCeConnection("Data Source = BK.sdf")

        SQLCon.Open()

        SQLCom = SQLCon.CreateCommand
        SQLCom.CommandText = "INSERT INTO [Current Rentals](MemberID,DVDID,DaysonRent,DateRented,DueDate,TotalPrice,Overdue,DaysOverdue,OverduePrice) VALUES('" _
            & Trim(tbMemberID.Text) & "','" & Trim(tbDVDID.Text) & "','" & CInt(Trim(cbDays.Text)) & "',GETDATE(),DATEADD(day," & CInt(Trim(cbDays.Text)) & ",GETDATE())" & "," & CDbl(Trim(tbPrice.Text)) & ",'No',NULL,NULL)"
        MessageBox.Show(SQLCom.CommandText)
        SQLCom.ExecuteNonQuery()

        SQLCon.Close()

Any help would be appreciated!

[pritesh2010]

try this

Dim SQLCon As New SqlServerCe.SqlCeConnection
      Dim SQLCom As New SqlServerCe.SqlCeCommand
dim str as string,i as boolean
       SQLCon = New SqlServerCe.SqlCeConnection("Data Source = BK.sdf")
SQLCon.Open()


str=""INSERT INTO [Current Rentals](MemberID,DVDID,DaysonRent,DateRented,DueDate,TotalPrice,Overdue,DaysOverdue,OverduePrice) VALUES('" _
& trim(tbmemberId.text) & "','" & Trim(tbDVDID.Text) & "','" & trim(Cint(cbdays.text)) & "'_
& trim(getdate(),Dateadd(days," & trim(Cint(cbdays.text)) & ",GetDate())) & "'," &trin(CBal(tbprice.text)) & ",'No','','')"

sqlcom=new  SqlServerCe.SqlCeCommand(str,sqlcon)
i=SQLCom.ExecuteNonQuery()
if i=true then
''msgbox value added
else 
''msgbox error in adding
end if
sqlcom=nothing
sqlcon.close()

''cbdays.text if this all are combo box then write it's selected item
''cbdays.SelectedItem

[pritesh2010]

remove fisrt qute from str""
and write

str="INSERT INTO [Current Rentals](MemberID,DVDID,DaysonRent,DateRented,DueDate,TotalPrice,Overdue,DaysOverdue,OverduePrice) VALUES('" _
& trim(tbmemberId.text) & "','" & Trim(tbDVDID.Text) & "','" & trim(Cint(cbdays.text)) & "'_
& trim(getdate(),Dateadd(days," & trim(Cint(cbdays.text)) & ",GetDate())) & "'," &trin(CBal(tbprice.text)) & ",'No','','')"

[kvprajapati]

Don't use string concatenate to form a query. Use Parameters.

Dim SQLCon As New SqlServerCe.SqlCeConnection
        Dim SQLCom As New SqlServerCe.SqlCeCommand
        SQLCon = New SqlServerCe.SqlCeConnection("Data Source = BK.sdf")
        SQLCom.Connection = SQLCon
        SQLCom.CommandText = "INSERT INTO [Current Rentals](MemberID,DVDID,DaysonRent,DateRented,DueDate,TotalPrice,Overdue,DaysOverdue,OverduePrice) VALUES (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9)"
       
        SqlCom.Parameters.Add("@p1", SqlDbType.Varchar, 4).Value=tbMemberid.text
        ......
        ......
     SqlCom.Parameters.Add("@p8", SqlDbType.Int, 4).Value=DBNull.Value
     SqlCom.Parameters.Add("@p9", SqlDbType.Int, 4).Value=DBNull.Value
 
        SQLCon.Open()
        SQLCom.ExecuteNonQuery()
        SQLCon.Close()

[BigPandaCake]

thanks for the replies,

I tried this

Dim SQLCon As New SqlServerCe.SqlCeConnection
        Dim SQLCom As New SqlServerCe.SqlCeCommand
        SQLCon = New SqlServerCe.SqlCeConnection("Data Source = BK.sdf")

        SQLCom.CommandText = "INSERT INTO [Current Rentals](MemberID,DVDID,DaysonRent,DateRented,DueDate,TotalPrice,Overdue,DaysOverdue,OverduePrice) VALUES (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9)"

        SQLCom.Parameters.Add("@p1", SqlDbType.VarChar, 4).Value = tbMemberID.Text
        SQLCom.Parameters.Add("@p2", SqlDbType.VarChar, 6).Value = tbDVDID.Text
        SQLCom.Parameters.Add("@p3", SqlDbType.Int, 9).Value = cbDays.SelectedItem
        SQLCom.Parameters.Add("@p4", SqlDbType.DateTime, 8).Value = DateTime.Today.Date
        SQLCom.Parameters.Add("@p5", SqlDbType.DateTime, 8).Value = DateTime.Today.AddDays(Int(cbDays.SelectedItem))
        SQLCom.Parameters.Add("@p6", SqlDbType.Money, 19).Value = tbPrice.Text
        SQLCom.Parameters.Add("@p7", SqlDbType.VarChar, 1).Value = "N"
        SQLCom.Parameters.Add("@p8", SqlDbType.Int, 9).Value = DBNull.Value
        SQLCom.Parameters.Add("@p9", SqlDbType.Money, 19).Value = DBNull.Value

        SQLCon.Open()
        SQLCom.ExecuteNonQuery()
        SQLCon.Close()

but I'm getting this error:

ArguemenetException was unhandeled
VChar

on the line

SQLCom.Parameters.Add("@p1", SqlDbType.VarChar, 4).Value = tbMemberID.Text

[BigPandaCake]

Never mind, got it working :)

Try

            Dim SQLCon As New SqlServerCe.SqlCeConnection
            Dim SQLCom As New SqlServerCe.SqlCeCommand
            SQLCon = New SqlServerCe.SqlCeConnection("Data Source = BK.sdf")
            SQLCon.Open()
            SQLCom = SQLCon.CreateCommand
            SQLCom.CommandText = "INSERT INTO [Current Rentals](MemberID,DVDID,DaysonRent,DateRented,DueDate,TotalPrice,Overdue,DaysOverdue,OverduePrice) VALUES (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9)"

            SQLCom.Parameters.Add("@p1", SqlDbType.Int, 4).Value = tbMemberID.Text
            SQLCom.Parameters.Add("@p2", SqlDbType.Int, 6).Value = tbDVDID.Text
            SQLCom.Parameters.Add("@p3", SqlDbType.Int, 9).Value = cbDays.SelectedItem
            SQLCom.Parameters.Add("@p4", SqlDbType.DateTime, 8).Value = DateTime.Today.Date
            SQLCom.Parameters.Add("@p5", SqlDbType.DateTime, 8).Value = DateTime.Today.AddDays(Int(cbDays.SelectedItem))
            SQLCom.Parameters.Add("@p6", SqlDbType.Money, 19).Value = tbPrice.Text
            SQLCom.Parameters.Add("@p7", SqlDbType.NText, 1).Value = "N"
            SQLCom.Parameters.Add("@p8", SqlDbType.Int, 9).Value = DBNull.Value
            SQLCom.Parameters.Add("@p9", SqlDbType.Money, 19).Value = DBNull.Value


            SQLCom.ExecuteNonQuery()
            MessageBox.Show(SQLCom.CommandText)
            SQLCon.Close()
        Catch ex As Exception
            MessageBox.Show("Error in adding record to database!")
        End Try

for some reason VarChar wasn't working, so I used int instead.

Also forgot to use "SQLCom = SQLCon.CreateCommand"