SQL problem - Stock Discrepency with less than.

Question

0.00/5 (No votes)

See more:

Hello,

I've written a program to send out an email when currentstock goes below minimumrequired.

Here's part of my code:

VB

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
 
con.ConnectionString = "Data Source=######;Initial Catalog=######;Integrated Security=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM [" + dbase + "] WHERE CurrentStock < MinimumRequired"

It displays all the ones that the current stock is below the minimum, but one or two other parts are being displayed aswell. E.G. its displaying stock of 10 and the minimum is 2, so it shouldnt be displaying.

Have i missed something out of the code above?

Cheers for any help,
Pete

Posted 30-Jul-13 23:40pm

PeterHall

Add a Solution

Comments

Mukesh Ghosh 31-Jul-13 5:43am

What is the datatype for CurrentStock & MinimumRequired?

PeterHall 31-Jul-13 6:17am

varchar(MAX),

Cheers

1 solution

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)

Adarsh chauhan · Accepted Answer · 2013-07-31T00:12:00

Solution 1

I think issue is with datatype of CurrentStock & MinimumRequired.
check for datatype.

for eg.
below case will say 2 is greater then 10

SQL

declare @a varchar(10)
declare @b varchar(10)

set @a='10'
set @b='2'

select case when @a >@b then @a +' is greater' when @a = @b then 'Both are equal'
else @b +' is greater' end as Greater

output:

Greater
2 is greater

Posted 31-Jul-13 0:12am

Adarsh chauhan

Updated 31-Jul-13 0:13am

v2

Comments

PeterHall 31-Jul-13 6:16am

ah right, just checked now my datatype in the sql table is varchar(MAX) for both current stock and minimum required, what would I need to set that to?

Thanks

Adarsh chauhan 31-Jul-13 6:17am

if your stock is in no. of pieces then you can set it to int else decimal.
I would suggest you to go for decimal as stock can be in length, weight or no. of pieces etc. so int would work right for no. of pieces only..

PeterHall 31-Jul-13 6:22am

I tried both int and decimal, it seemed to throw an exception "Conversion from string "Stock Control " to type 'Double' is not valid.

Any ideas?

Adarsh chauhan 31-Jul-13 6:25am

have you changed datatype in your database??
when this error is coming???

PeterHall 31-Jul-13 6:28am

On the dropdown box i selected "decimal(18, 0)" in visual studio.

Heres the exception it threw:

System.InvalidCastException: Conversion from string "Conformal Coating Stock Control " to type 'Double' is not valid. ---> System.FormatException: Input string was not in a correct format.
at Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat)
at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat)
--- End of inner exception stack trace ---

Adarsh chauhan 31-Jul-13 6:31am

share your piece of code where your are converting...
and also change datatype in your table structure.

PeterHall 31-Jul-13 6:38am

I changed the datatype in both currentstock and minimumrequired in my table structure to decimal(18, 0).

' Dim subject As String
Dim body As String
Dim con As New SqlConnection
Dim cmd As New SqlCommand

con.ConnectionString = "#####;Initial Catalog=#####;Integrated Security=True"
con.Open()
cmd.Connection = con
cmd.CommandText = "SELECT * FROM [" + dbase + "] WHERE CurrentStock < MinimumRequired"

Dim reader2 As SqlDataReader = cmd.ExecuteReader
body = title + " Stock Control System" + Environment.NewLine + "Time: " + Date.Now + Environment.NewLine + "##########################################" + Environment.NewLine + Environment.NewLine + "This is an automated email please do not reply!" + Environment.NewLine + Environment.NewLine + "The parts below have reached its reorder level. Please place the required orders: " + Environment.NewLine + Environment.NewLine + "Product Details are as follows:" + Environment.NewLine
If reader2.HasRows Then
While reader2.Read()
body = body + "---------------------------------" + Environment.NewLine + "Part Number: " + reader2("PartNumber") + Environment.NewLine + "Description: " + reader2("Description") + Environment.NewLine + Environment.NewLine + "Current Stock: " + reader2("CurrentStock") & Environment.NewLine + "Minimum Required: " + reader2("MinimumRequired") & Environment.NewLine
End While
body = body + Environment.NewLine + "---------------------------------" + Environment.NewLine
reader2.Close()

Dim MyMailMessage As New MailMessage()

..... Then it starts the email stuff .....

Adarsh chauhan 31-Jul-13 7:02am

Try this..
replace your while loop code with
body = body + "---------------------------------" + Environment.NewLine + "Part Number: " + reader2["PartNumber"].tostring() + Environment.NewLine + "Description: " + reader2["Description"].tostring() + Environment.NewLine + Environment.NewLine + "Current Stock: " + reader2["CurrentStock"].tostring() & Environment.NewLine + "Minimum Required: " + reader2["MinimumRequired"].tostring() & Environment.NewLine

PeterHall 31-Jul-13 7:11am

I get 2 errors,

1) I get identifier expected. at code: reader2["PartNumber"].tostring()
2) I also get Operator '+' is not defined for types 'string' and 'system.data.sqlclient.sqldatareader'

Adarsh chauhan 31-Jul-13 7:27am

i tried this code in C# and its working fine in my case..

protected void Page_Load(object sender, EventArgs e)
{
showmsg();
}

public void showmsg()
{
string body = string.Empty;

SqlConnection conn = new SqlConnection(con.getConnection());
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from temp";
cmd.Connection=conn;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
while (dr.Read())

body = body + "---------------------------------" + Environment.NewLine + "Part Number: " + dr["PartNumber"].ToString() + Environment.NewLine + "Description: " + dr["Description"].ToString() + Environment.NewLine + Environment.NewLine + "Current Stock: " + dr["CurrentStock"].ToString() + Environment.NewLine + "Minimum Required: " + dr["MinimumRequired"].ToString() + Environment.NewLine;

Response.Write(body);
}

cant say about VB.

try after replacing & to +

PeterHall 31-Jul-13 9:11am

Worked Like a Charm! - Converted it to VB,

Cheers Adarsh!

Adarsh chauhan 1-Aug-13 1:32am

Finally your prob. is solved.. :) Cheers..