Bobski Bobski - 1 month ago 9
SQL Question

VB.Net insert/select from one table to another error

I'm trying to Select * from tbl1 and insert it into tbl1_DEL. Basically if user wants to delete a record, i will first insert it into _DEL table before I delete the actual record in case it was done in error. Here's my code so far...

queryD = "Insert Into tbl1_DEL "
queryD = queryD & " SELECT * from Tbl1 WHERE IdClient = @IdClient"

cmd.CommandText = queryD
cmd.Connection = Conn
Conn.Open()

cmd.Parameters.Add("@IDClient", SqlDbType.NChar).Value = 17 'Just testing here with hardcoded IDClient

rowAffected = cmd.ExecuteNonQuery()


I wonder How I'd be able to continue on with this and basically do something like ....

if rowAffected <>0 then
Delete * from Tbl1 where IDClient = 17 'parametized as it is with the Insert/Select above
End if


I'm wondering if it can be something to do with the parameter since the datatype for that field is Numeric(18,0).

This is the error I keep getting...


Column name or number of supplied values does not match table
definition.

Answer

I suspect the structures of tbl1_DEL and Tbl1 are different.

Either sync the structures or specify the field list (recommended)

Insert into tbl1_DEL (Fld1,Fld2...) 
 Select Fld1,Fld2...
  From  Tbl1 
  Where dClient = @IdClient

Per your comment

 Insert into tbl1_DEL
     Select *,TheOtherField = GetDate()    -- Assuming the one extra field is the last
      From  Tbl1 
      Where dClient = @IdClient