CODEWITHSUNDEEP
asp.netsqlvb.netinsert
Jamie
Jamie

Reputation: 1679

Error when trying to INSERT INTO vb.net app

I have columns in my database called ModifyUser and ModifyDate that are supposed to show the username and date and time of the last person that has updated a specific product somehow.

I thought if I just added the second query then I would be fine, but for some reason the application wants to add ProductName into the Product table too. Why isn't the application just inserting the 2 values I told it to insert in the Product table INSERT?

Error message: Cannot insert the value NULL into column 'ProductName', table
'Products.dbo.Product'; column does not allow nulls. INSERT fails.
The statement has been terminated.

'SQL INSERT: CompanyLink Table
        Dim strSQL As String = "INSERT INTO CompanyLink (ProductID, CompanyID) 
        VALUES (@ProductID, @CompanyID);INSERT INTO Product (ModifyDate, ModifyUser)
        VALUES (getdate(), @ModifyUser)"

    Using cn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)

    Using cmd As New SqlCommand(strSQL, cn)

    cmd.Parameters.Add(New SqlParameter("@ProductID", ProductID.Value))
    cmd.Parameters.Add(New SqlParameter("@CompanyID", company.Value))
    cmd.Parameters.Add(New SqlParameter("@ModifyUser",
    System.Web.HttpContext.Current.User.Identity.Name))

    cn.Open()
    cmd.ExecuteNonQuery()

Upvotes: 0

Views: 1164

Answers (4)

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

The error message is pretty clear: your are not providing ProductName, which is a required (NOT NULL) field.

In order to fix it, you have to provide the ProductName, modifying this:

INSERT INTO Product (ModifyDate, ModifyUser, ProductName)
    VALUES (getdate(), @ModifyUser, @ProductName)

and add this:

cmd.Parameters.Add(New SqlParameter("@ProductName", SomeVariableContainingProductName))

Update

Since what you really need is an update, your statement will be:

Update product
set ModifyDate = getdate(),
    ModifyUser = @ModifyUser
where ProductID = @ProductID

Remember to provide all parameters values for the query.

Upvotes: 1

ipr101
ipr101

Reputation: 24226

If the 'ProductName' field in the 'Product' table is set to NOT NULL, the database won't accept any records into the Product table unless they contain a value for the 'ProductName' field.

Upvotes: 0

Junaid
Junaid

Reputation: 1755

ProductName in Product does not accept null. you would either have to pass in ProductName or change the column property to accept null values as below

ALTER TABLE Product ALTER COLUMN ProductName nvarchar(50) NULL

Upvotes: 0

Oded
Oded

Reputation: 498904

Looks like the ProductName column of the Product table has been defined as NOT NULL.

That means that you have to supply a value for it. This ensures that you don't have products that do not have a name.

Since you are not supplying a ProductName in this query:

INSERT INTO Product (ModifyDate, ModifyUser)
    VALUES (getdate(), @ModifyUser)

The statement fails.

I expect there are other NOT NULL columns on the same table.

Upvotes: 2

Related Questions