I am unable to insert expirydate product ?

Question

0.00/5 (No votes)

See more:

I have PurchaseInvoice Details Table where I have pid_proexpiry column where I couldn't add expiry Date.

Here is My Table :

SQL

CREATE TABLE purchaseInvoiceDetails
(
    pid_id bigint IDENTITY NOT NULL,
    pid_purchaseID bigint NOT NULL,
    pid_proID int NOT NULL,
    pid_proquan int NOT NULL,
    pid_totprice money NOT NULL,
    pid_proexpiry date NULL
)

and this my Stored Procedure :

SQL

Create procedure [dbo].[st_insertPurchaseInvoiceDetails]
    @purchaseID bigint,
    @proID int,
    @quan int,
    @totPrice money,
    @proExpiry date
As
    insert into purchaseInvoiceDetails values (@purchaseID,@proID,@quan, 
@totPrice,@proExpiry)

What I have tried:

I used this Function :

C#

int pidcount;
public int insertPurchaseInvoiceDetails(Int64 purID,Int64 proID,int quan,float totPrice, DateTime? expiry=null)
{
    try
    {
        SqlCommand cmd = new SqlCommand("st_insertPurchaseInvoiceDetails", MainClass.con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@purchaseID",purID);
        cmd.Parameters.AddWithValue("@proID", proID);
        cmd.Parameters.AddWithValue("@quan", quan);
        cmd.Parameters.AddWithValue("@totPrice", totPrice);

        if (expiry == null)
        {
            cmd.Parameters.AddWithValue("@proExpiry", DBNull.Value);
        }
        else
        {
            cmd.Parameters.AddWithValue("@proExpiry", expiry);
        }
        MainClass.con.Open();
        pidcount = cmd.ExecuteNonQuery();            
        MainClass.con.Close();
    }
    catch (Exception ex)
    {
        MainClass.con.Close();
        MainClass.ShowMSG(ex.Message, "Error....", "Error");
    }
    return pidcount;
}

and This is How I am Calling Function :

C#

if (expiryPicker.Value.Date == DateTime.Now.Date)
{
    co += i.insertPurchaseInvoiceDetails(purchaseinvoiceID, Convert.ToInt32(row.Cells["proIDGV"].Value.ToString()), Convert.ToInt32(row.Cells["quantGV"].Value.ToString()), Convert.ToSingle(row.Cells["totalGV"].Value.ToString()));
}
else
{
    co += i.insertPurchaseInvoiceDetails(purchaseinvoiceID, Convert.ToInt32(row.Cells["proIDGV"].Value.ToString()), Convert.ToInt32(row.Cells["quantGV"].Value.ToString()), Convert.ToSingle(row.Cells["totalGV"].Value.ToString()), Convert.ToDateTime(row.Cells["expiryGV"].Value.ToString()));
}

This I Have Tried but In Database Showing Null Value In pid_ProExpiry Column. Can I Get any Solution For it.
I want to insert only those date which having Expiry Date like Food etc.

Posted 28-Sep-20 4:31am

Tejas@45

Updated 28-Sep-20 6:00am

Richard Deeming

v2

Add a Solution

Comments

ZurdoDev 28-Sep-20 10:32am

Debug your code. This is a very, very easy thing to fix.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-09-28T05:32:00

Your mistake is simple: you don't list the columns you are INSERTing into:

SQL

insert into purchaseInvoiceDetails values (@purchaseID,@proID,@quan,
@totPrice,@proExpiry)

Because of that, SQL tires to insert the data in the order that the table items are defined:

SQL

CREATE TABLE purchaseInvoiceDetails(
pid_id bigint IDENTITY NOT NULL,
pid_purchaseID bigint NOT NULL,
pid_proID int NOT NULL,
pid_proquan int NOT NULL,
pid_totprice money NOT NULL,
pid_proexpiry date NULL

The first column is an IDENTITY column, and you can't write to that at all, so the SP fails to do any INSERT operation.
List your columns, and it'll start to work:

SQL

INSERT INTO purchaseInvoiceDetails (pid_purchaseID, pid_proID, pid_proquan, pid_totprice, pid_proexpiry) VALUES (@purchaseID,@proID,@quan,
@totPrice,@proExpiry)

Always list the columns you are trying to INSERT or SELECT - it's just asking for problems if you don't!

Oshtri Deka · Answer 2 · 2020-09-28T06:01:00

I cannot help myself I must patronize a bit.
Float is a bad choice for monetary values, use decimal - underlying type in db is money. There are plenty sources on the subject online.

if (expiryPicker.Value.Date == DateTime.Now.Date)
{
    co += i.insertPurchaseInvoiceDetails(
            purchaseinvoiceID,
            Convert.ToInt32(row.Cells["proIDGV"].Value.ToString()),
            Convert.ToInt32(row.Cells["quantGV"].Value.ToString()), 
            Convert.ToSingle(row.Cells["totalGV"].Value.ToString()),
            DateTime.Now.Date // you forgot to pass date
            );
}
else
{
    // original code
}

I am unable to insert expirydate product ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0