1

Upon clicking the add button in vb, it works if you dont leave textbox fields null or empty the code executes well

but when leaving some fields blank it returns error

Failed to convert parameter value from a String to a Int32

STORED PROC

CREATE PROCEDURE AddOfficeEquipmentProfile
(
@OE_ID      varchar(11),    
@OE_Category        char(3) =NULL,      
@OE_SubCategory char(3)=    NULL,       
@OE_Name        varchar(35)=NULL,       
@OE_User        varchar(35)=NULL,   
@OE_Brand       varchar(15)=NULL,   
@OE_Model       varchar(35)=NULL,   
@OE_Specs       varchar(1000)=NULL,     
@OE_SerialNo        varchar(35)=NULL,   
@OE_PropertyNo  varchar(35)=NULL,   
@OE_MacAddress  varchar(100)=NULL,      
@OE_Static_IP       varchar(15)=NULL,   
@OE_Vendor      varchar(35)=NULL,   
@OE_PurchaseDate    smalldatetime,      
@OE_WarrantyInclusiveYear   int=NULL,   
@OE_WarrantyStatus  char(2)=    NULL,   
@OE_Status      varchar(15)=NULL,       
@OE_Dept_Code   char(3)=    NULL,   
@OE_Location_Code   char(8)=    NULL,       
@OE_Remarks     varchar(1000)=  NULL
)
AS

INSERT INTO tblOfficeEquipmentProfile (OE_ID, OE_Category, OE_SubCategory, OE_Name, OE_User, OE_Brand, OE_Model, OE_Specs, OE_SerialNo,
OE_PropertyNo, OE_MacAddress, OE_Static_IP, OE_Vendor, OE_PurchaseDate, OE_WarrantyInclusiveYear, OE_WarrantyStatus, OE_Status, OE_Dept_Code,
OE_Location_Code, OE_Remarks ) 
VALUES (@OE_ID, @OE_Category, @OE_SubCategory, @OE_Name, @OE_User, @OE_Brand, @OE_Model, 
@OE_Specs, @OE_SerialNo, @OE_PropertyNo, @OE_MacAddress, @OE_Static_IP, @OE_Vendor, @OE_PurchaseDate, @OE_WarrantyInclusiveYear, @OE_WarrantyStatus,
@OE_Status, @OE_Dept_Code, @OE_Location_Code, @OE_Remarks)

GO

VB.NET CODE

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "AddOfficeEquipmentProfile"

    cmd.Parameters.Add("@OE_ID", SqlDbType.VarChar, 11, "oeq-su-999")
    cmd.Parameters.Add("@OE_Category", SqlDbType.Char, 3, "COM").Value = DBNull.Value
    cmd.Parameters.Add("@OE_SubCategory", SqlDbType.Char, 3, "SU").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Name", SqlDbType.VarChar, 35, "adminpmis01").Value = DBNull.Value
    cmd.Parameters.Add("@OE_User", SqlDbType.VarChar, 35, "Ivan").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Brand", SqlDbType.VarChar, 15, "DELL").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Model", SqlDbType.VarChar, 35, "optiplex").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Specs", SqlDbType.VarChar, 1000, "dualcore").Value = DBNull.Value
    cmd.Parameters.Add("@OE_SerialNo", SqlDbType.VarChar, 35, "sgh5960").Value = DBNull.Value
    cmd.Parameters.Add("@OE_PropertyNo", SqlDbType.VarChar, 35, "j7h7h6g6f2").Value = DBNull.Value
    cmd.Parameters.Add("@OE_MacAddress", SqlDbType.VarChar, 100, "j7h7:h6g6f2").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Static_IP", SqlDbType.VarChar, 15, "192.168.1.5").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Vendor", SqlDbType.VarChar, 35, "ADWAYS").Value = DBNull.Value
    cmd.Parameters.Add("@OE_PurchaseDate", SqlDbType.DateTime)
    cmd.Parameters.Add("@OE_WarrantyInclusiveYear", SqlDbType.Int).Value = DBNull.Value
    cmd.Parameters.Add("@OE_WarrantyStatus", SqlDbType.Char, 2, "IN").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Status", SqlDbType.VarChar, 15, "Good").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Dept_Code", SqlDbType.Char, 3, "ADM").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Location_Code", SqlDbType.Char, 8, "ADM_OFC").Value = DBNull.Value
    cmd.Parameters.Add("@OE_Remarks", SqlDbType.VarChar, 1000, "ACTIVE").Value = DBNull.Value

    cmd.Parameters("@OE_ID").Value = txtOEID.Text
    cmd.Parameters("@OE_Category").Value = cmbCategory.Text
    cmd.Parameters("@OE_SubCategory").Value = cmbSubCategory.Text
    cmd.Parameters("@OE_Name").Value = txtName.Text
    cmd.Parameters("@OE_User").Value = txtUser.Text
    cmd.Parameters("@OE_Brand").Value = cmbBrand.Text
    cmd.Parameters("@OE_Model").Value = cmbModel.Text
    cmd.Parameters("@OE_Specs").Value = txtSpecs.Text
    cmd.Parameters("@OE_SerialNo").Value = txtSerialNo.Text
    cmd.Parameters("@OE_PropertyNo").Value = txtPropertyNo.Text
    cmd.Parameters("@OE_MacAddress").Value = txtMacAddress.Text
    cmd.Parameters("@OE_Static_IP").Value = txtStaticIP.Text
    cmd.Parameters("@OE_Vendor").Value = txtVendor.Text
    cmd.Parameters("@OE_PurchaseDate").Value = dtpPurchaseDate.Value
    cmd.Parameters("@OE_WarrantyInclusiveYear").Value = txtWarrantyInclusiveYear.Text
    cmd.Parameters("@OE_WarrantyStatus").Value = txtWarrantyStatus.Text
    cmd.Parameters("@OE_Status").Value = txtStatus.Text
    cmd.Parameters("@OE_Dept_Code").Value = cmbDeptCode.Text
    cmd.Parameters("@OE_Location_Code").Value = cmbLocationCode.Text
    cmd.Parameters("@OE_Remarks").Value = txtRemarks.Text
    cmd.ExecuteNonQuery()
    sqlconn.Close()
Improve this question

4 Answers 4

Reset to default
2

I think what you're missing (as you've asked a variation on this question several times over the last couple of days is an understanding of how the controls in your form map to the parameters that you associate with your SqlCommand, and in turn how those SqlCommand parameters map to the parameters of your stored procedure. That lack of understanding (and since you're learning, that lack is not a bad thing) coupled with how to handle NULL values is causing you no end of grief.

Rather than give you a single answer to a single question, I'm going to try to explain the overall concept, so you can then hopefully apply it to any number of situations you may encounter while writing code.

First, you have a collection of TextBox and ComboBox controls on your form. Both of these controls have a Text property - this property always returns a string, even if you put 12345 or 10/01/2007 in it. This means that you will have to cast (convert) the string you get from TextBox.Text or ComboBox.Text to the appropriate data type for the stored procedure parameter in question (unless its CHAR/VARCHAR or something similar).

When you create your SqlCommand object, you are adding parameters to the parameters collection. By the way, this is very good practice, and kudos to you for learning it - as it will mitigate SQL injection attacks (not always a high-risk in a WinForm app, but it's a good coding practice to get into.

The SqlParameter objects you add to your SqlCommand need to match the parameters in the stored procedure, which you also do, so that is good. However, it looks like you may be trying to pass a default value in the fourth parameter, and that's parameter is not for setting default values but for specifying the source column in an UPDATE statement.

Finally you have your stored procedure. None of the parameters are required - so you can safely admit any parameter that does not have a value in the form. Essentially, when you set a default value (NULL or anything else) on a parameter in a stored procedure you can omit that parameter from the call and SQL Server will use the default value you've specified in the declaration of the stored procedure.

You're doing extra work in your code as it is - first you're setting the parameters all to null, then you're overriding that assignment by setting the values equal to the corresponding controls on the form. Which is where you're running into most of your trouble.

As others have suggested on at least a couple of your questions, I would do a check to see if a control has a value - if it does, then add the parameter and its value to the collection (converting as needed). This will reduce the "double" assignment you have.

So it would look something like this:

If txtOEID.Text.Trim() <> "" Then
    cmd.Parameters.Add("@OE_ID", SqlDbType.VarChar, 11).Value = txtOEID.Text.Trim()
End If
If cmbCategory.Text.Trim() <> "" Then
    cmd.Parameters.Add("@OE_Category", SqlDbType.Char, 3).Value = cmbCategory.Text.Trim()
End If

And so on. When you have a non VARCHAR/CHAR parameter, you'll need to do a little additional work (since you're using a DateTimePicker for the purchase date, the Value property is already a DateTime so you don't need to worry about it):

If IsNumeric(txtWarrantyInclusiveYear.Text) Then
    cmd.Parameters.Add("@OE_WarrantyInclusiveYear", SqlDbType.Int).Value = Integer.Parse(txtWarrantyInclusiveYear.Text)
End If

In the case above, I suggest using IsNumeric to increase your chances of not accidently trying to parse a non-numeric value (since you're using VS 2003/.NET 1.1 TryParse is not available).

In a nutshell, when you creating parameters in your application, you need to be aware of the data type that is being passed in from the user, and the data type that the stored procedure is expecting. If they don't match, you'll need to convert the user input into the correct datatype for the stored procedure when you add the parameter to the collection.

If a parameter has a default value specified in the stored procedure, then you can simply skip adding the parameter if the user did not supply a value. Though in the case of NULL, make sure that the destination column you're inserting into is marked as NULLABLE.

And I strongly suggest you dump VS 2003 and avail yourself of the Express version of VS 2012, as you are missing out on a whole bunch of features that will make your life easier as a programmer.

If you need clarification on anything, please ask.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thank you very much sir, ill be experimenting and this will be the last similar question ill be asking on SO
You're quite welcome. And please don't be afraid to ask other questions as you continue to learn - there are a lot of incredibly smart and talented people here. Best of luck, and happy coding!
2

?? operator can help you to check Null value easily.

Example :

cmd.Parameters("@OE_ID").Value = txtOEID.Text ?? DBNull.Value;
cmd.Parameters("@OE_Category").Value = cmbCategory.Text ?? DBNull.Value;
Improve this answer

2 Comments

OP is using VS 2003 - ?? is not available in 1.1. I also believe it may be only for C#.
Yeah. It's only for C#. I thought he's using C#. Sorry about that.
0

Try using Inline IF for all the values.

cmd.Parameters("@OE_ID").Value = If(txtOEID.Text.Trim().Length > 0, txtOEID.Text, DBNull.Value)

What it does is it trims the string first then checks its length. If the length is greater than zero then pass the string on the true part otherwise pass DBNull.Value.

Improve this answer

1 Comment

will try sir kindly wait for feedback
0

Since issue converting string to int and the only int I see is @OE_WarrantyInclusiveYear parameter. In your code you can safely try to convert to int.

Dim year As Integer
If Integer.TryParse(txtWarrantyInclusiveYear.Text, year) Then
 cmd.Parameters("@OE_WarrantyInclusiveYear").Value = year
End If
Improve this answer

5 Comments

Just as a note, OP is using VS 2003 so unfortunately Integer.TryParse isn't available.
You can write your own TryParse, just wrap Integer.Parse into try catch.
Yes, you can...but OP appears to be quite new to VB.NET so I'd rather not overload him with too much at one time.
@tim, tomas id switched to visual studio 2008 sir
@ivandinglasan - Awesome - glad to hear it. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.