Nektarios Nektarios - 1 year ago 63
SQL Question

converts varchar to int while i am inserting to an sql table

I have this table, which i have named Users, and whenever the user is using the sign up form i want to insert to the Users.table all the credentials he enters. The problem is that, somehow, the database is trying to convert the Username(column) from varchar to int. This is my insert Query :

string Query = "Insert into Users(FirstName, LastName, Username,
Password, Role, Email, Class) values('" + txtfn.Text + "',
'" + txtsn.Text + "','" + txtUsername.Text + "','" + txtPassword.Text + "',
'" + cbRole.Text + "','" + txtEmail.Text + "','" + cbClass.Text + "')";

The error that pops-ups everytime a user is trying to sign up

How can i prevent this conversion from happening or is any other way to deal with this error?
Thanks In Advance.

Answer Source

Here is how to use SQL parameters and specify the types. Which is safier and more precise and also handle conversions for some types (such as DateTime):

string Query = "Insert into Users(FirstName, LastName, Username, "
            + " Password, Role, Email, Class) "
            + " values(@fn, @sm, @user, @ps, @role, @email, @class)";

SqlCommand sqlcom = new SqlCommand(Query, sqlconnection);

// change the types to match SQL Column data types
// I set all as Nvarchar as a sample
var fnParma = new SqlParameter("@fn", SqlDbType.NVarChar);
var smParma = new SqlParameter("@sm", SqlDbType.NVarChar);
var userParma = new SqlParameter("@user", SqlDbType.NVarChar);
var psParma = new SqlParameter("@ps", SqlDbType.NVarChar);
var roleParma = new SqlParameter("@role", SqlDbType.NVarChar);
var emailParma = new SqlParameter("@email", SqlDbType.NVarChar);
var classParma = new SqlParameter("@class", SqlDbType.NVarChar);

// Here set each value by converting it to the specified type.

fnParma.Value = txtfn.Text;
smParma.Value = txtsn.Text;
// Continue for all the params. If you have int column use Int32.Parse(Text);