Input string not in correct format

[Mark A. Sam]

Hello,

I am having a problem with imputting into a string variable:

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDeale r,
txtOtherCustTyp e, chkStandardBags , chkCustomBags,t xtOtherBags) " + _

"VALUES ('" + txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text
+ "','" + txtEmail.Text + "','" + txtComment.Text + "','" +
chkGrower.Check ed + "','" + chkProduceDeale r.Checked + _

"','" + txtOtherCustTyp e.Text + "','" + chkStandardBags .Checked + "','" +
chkCustomBags.C hecked + "','" + txtOtherBags.Te xt + "');"
I am running this procedure in a Visual Web Developer Webform. I am kind of
thinking the problem may be the contiuation characters (+ _) on the end of
the second line: If the line wasn't split there it would read,

chkProduceDeale r.Checked + "','" + txtOtherCustTyp e.Text

So I added the underscore next to an existing concatination rather than a
new pair of symbols, + _. I tried breaking the line somewhere else, but
that didn't work.

Thanks for any help.

God Bless,

Mark A. Sam

[Stephany Young]

If, as I suspect, the reason for the lines being split with the continuation
character is purely to save you having to scroll sideways to see what you
have written, then my advice is to take the continuation characters out, at
least until you have it working properly.

Have you got Option Strict turned on? I suspect you haven't, otherwise you
would get some compiler errors and/or warnings.

Are the names on the columns in your database table really the same as the
names of the controls name on your form?

Are the datatypes of the database table columns declared as they should be?

What is the value of the resulting string? CLUE: Use Console.Writeli ne to
see it.

In the VALUES clause, I would expect that the value for the chkGrower column
to be either ...,'True',... or ...,'False',... and the same applies to the
other chk... columns, the source of which syuspect to be CheckBoxes.

I would be surprised if the corresponding columns in the database table are
not declared as whatever flavour of boolean that your database engine
supports.

If this is the case then passing the string 'True' or 'False' is your
problem. You need to be passing True or False boolean values.

"Mark A. Sam" <ms**@Plan-It-Earth.Net> wrote in message
news:u9******** ******@TK2MSFTN GP12.phx.gbl...

Hello,

I am having a problem with imputting into a string variable:

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDeale r,
txtOtherCustTyp e, chkStandardBags , chkCustomBags,t xtOtherBags) " + _

"VALUES ('" + txtName.Text + "','" + txtCompany.Text + "','" +
txtPhone.Text + "','" + txtEmail.Text + "','" + txtComment.Text + "','" +
chkGrower.Check ed + "','" + chkProduceDeale r.Checked + _

"','" + txtOtherCustTyp e.Text + "','" + chkStandardBags .Checked + "','" +
chkCustomBags.C hecked + "','" + txtOtherBags.Te xt + "');"
I am running this procedure in a Visual Web Developer Webform. I am kind
of thinking the problem may be the contiuation characters (+ _) on the end
of the second line: If the line wasn't split there it would read,

chkProduceDeale r.Checked + "','" + txtOtherCustTyp e.Text

So I added the underscore next to an existing concatination rather than a
new pair of symbols, + _. I tried breaking the line somewhere else, but
that didn't work.

Thanks for any help.

God Bless,

Mark A. Sam

[Mark A. Sam]

Hello Stephany,

I thought about that after I posted this, so I ran it as one single line and
still have the issue.

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDeale r,
txtOtherCustTyp e, chkStandardBags , chkCustomBags,t xtOtherBags) VALUES ('" +
txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text + "','" +
txtEmail.Text + "','" + txtComment.Text + "','" + chkGrower.Check ed + "','"
+ chkProduceDeale r.Checked + "','" + txtOtherCustTyp e.Text + "','" +
chkStandardBags .Checked + "','" + chkCustomBags.C hecked + "','" +
txtOtherBags.Te xt + "');"

I don't see the problem. This format worked fine when I tested it with the
first two field, txtName and txtCompany. I don't see any syntax errors,
though they could be there.

Another question if I may. Should I remove the single quote surrouding the
boolean fields, like

+ "','" + chkStandardBags .Checked + "','" +

Thank you.

Mark

[PL]

You shouldnt concatenate your queries in the first palce since a bunch of
security issues arise with this method and it is also not very readable
code. Instead use what the framework provides you with and parameterize your
query and send in the values with the datatypes properly defined, for
example:

I'm using C# here since that is what I'm used to but the same applies to
VB.NET, I have also shortened your query just for this example:

string strSQL= "INSERT INTO tblContactForm1 (txtName, txtCompany) VALUES
(@txtName, @txtCompany)";

SqlConnection objconn = new SqlConnection(s ConnStr);
objconn.Open();

SqlCommand objcommand = new SqlCommand(strS QL,objconn);

// Set the parameter values
objcommand.Para meters.Add("(@t xtName", SqlDbType.VarCh ar).Value =
txtName.Text;
objcommand.Para meters.Add("(@t xtCompany", SqlDbType.VarCh ar).Value =
txtCompany.Text ;

// ... rest of code

PL.

[Stephany Young]

Another point, as you are using VB.NET I STRONGLY recommend that you use the
String concatenation operator (&) instead of (+). The (+) operator for
string concatenation is a legacy from very old versions of BASIC and use of
it, especially with Option Strict Off, can lead to very nasty and difficult
to find problems. E,g: You might expect "1" + "2" to yield "21" but it will,
most likely yield 3.

It still appears that you haven't tyurned Option Strict On, otherwise the
code you just posted wouldn't have compiled.

Did you dump the reult of the concatenation? If so, what is it?

It would have worked perfectly when you tested it with the first two fields
(txtName and txtCompany) because, I suspect, the corresponding database
colums are of the 'text' or similar type.

Removing the single quotes surrouding the boolean fields may do the trick,
depending on what the database engine expects for boolean values.

If you do remove them then your code should look like:

... & "'," & chkStandardBags .Checked.ToStri ng & ",'" & ...

The fragment you posted still has the single quotes in it.
"Mark A. Sam" <ms**@Plan-It-Earth.Net> wrote in message
news:uH******** ******@tk2msftn gp13.phx.gbl...

Hello Stephany,

I thought about that after I posted this, so I ran it as one single line
and still have the issue.

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDeale r,
txtOtherCustTyp e, chkStandardBags , chkCustomBags,t xtOtherBags) VALUES ('"
+ txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text + "','" +
txtEmail.Text + "','" + txtComment.Text + "','" + chkGrower.Check ed +
"','" + chkProduceDeale r.Checked + "','" + txtOtherCustTyp e.Text + "','" +
chkStandardBags .Checked + "','" + chkCustomBags.C hecked + "','" +
txtOtherBags.Te xt + "');"

I don't see the problem. This format worked fine when I tested it with
the first two field, txtName and txtCompany. I don't see any syntax
errors, though they could be there.

Another question if I may. Should I remove the single quote surrouding
the boolean fields, like

+ "','" + chkStandardBags .Checked + "','" +

Thank you.

Mark

[Cor Ligthert [MVP]]

Mark,

Just try parameters, it seems more work, however you will see that as soon
as you are used to them, don't want to do it in another way anymore.

Have a look at our website for this simple sample.

http://www.vb-tips.com/default.aspx?...a-ae802ee71c86

I hope this helps,

Cor

"Mark A. Sam" <ms**@Plan-It-Earth.Net> schreef in bericht
news:uH******** ******@tk2msftn gp13.phx.gbl...

Hello Stephany,

I thought about that after I posted this, so I ran it as one single line
and still have the issue.

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDeale r,
txtOtherCustTyp e, chkStandardBags , chkCustomBags,t xtOtherBags) VALUES ('"
+ txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text + "','" +
txtEmail.Text + "','" + txtComment.Text + "','" + chkGrower.Check ed +
"','" + chkProduceDeale r.Checked + "','" + txtOtherCustTyp e.Text + "','" +
chkStandardBags .Checked + "','" + chkCustomBags.C hecked + "','" +
txtOtherBags.Te xt + "');"

I don't see the problem. This format worked fine when I tested it with
the first two field, txtName and txtCompany. I don't see any syntax
errors, though they could be there.

Another question if I may. Should I remove the single quote surrouding
the boolean fields, like

+ "','" + chkStandardBags .Checked + "','" +

Thank you.

Mark

[Cor Ligthert [MVP]]

Stephany,

I was writing to you that it was C# because of that semicolon at the end.

Now I see that it is extra good. It start with a DIM.

:-)

Cor

"Stephany Young" <noone@localhos t> schreef in bericht
news:ek******** ********@TK2MSF TNGP09.phx.gbl. ..

Another point, as you are using VB.NET I STRONGLY recommend that you use
the String concatenation operator (&) instead of (+). The (+) operator for
string concatenation is a legacy from very old versions of BASIC and use
of it, especially with Option Strict Off, can lead to very nasty and
difficult to find problems. E,g: You might expect "1" + "2" to yield "21"
but it will, most likely yield 3.

It still appears that you haven't tyurned Option Strict On, otherwise the
code you just posted wouldn't have compiled.

Did you dump the reult of the concatenation? If so, what is it?

It would have worked perfectly when you tested it with the first two
fields (txtName and txtCompany) because, I suspect, the corresponding
database colums are of the 'text' or similar type.

Removing the single quotes surrouding the boolean fields may do the trick,
depending on what the database engine expects for boolean values.

If you do remove them then your code should look like:

... & "'," & chkStandardBags .Checked.ToStri ng & ",'" & ...

The fragment you posted still has the single quotes in it.
"Mark A. Sam" <ms**@Plan-It-Earth.Net> wrote in message
news:uH******** ******@tk2msftn gp13.phx.gbl...

Hello Stephany,

I thought about that after I posted this, so I ran it as one single line
and still have the issue.

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDeale r,
txtOtherCustTyp e, chkStandardBags , chkCustomBags,t xtOtherBags) VALUES ('"
+ txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text + "','"
+ txtEmail.Text + "','" + txtComment.Text + "','" + chkGrower.Check ed +
"','" + chkProduceDeale r.Checked + "','" + txtOtherCustTyp e.Text + "','"
+ chkStandardBags .Checked + "','" + chkCustomBags.C hecked + "','" +
txtOtherBags.Te xt + "');"

I don't see the problem. This format worked fine when I tested it with
the first two field, txtName and txtCompany. I don't see any syntax
errors, though they could be there.

Another question if I may. Should I remove the single quote surrouding
the boolean fields, like

+ "','" + chkStandardBags .Checked + "','" +

Thank you.

Mark

[Stephany Young]

Hiya Cor - How's things?

I suspect that the semi-colon (in the concatenated string) indicates that he
is using a Jet database. Young players often think it is needed because
Access automatically appends a semi-colon to the query when you have a look
at a querydef in SQL view in Access.

Before we get him on to a SqlCommand or OleDbCommand oobject with parameter,
I'm trying to get him to understand what the actual probelm is and how he is
causing it.

Regards.
"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:uo******** ******@tk2msftn gp13.phx.gbl...

Stephany,

I was writing to you that it was C# because of that semicolon at the end.

Now I see that it is extra good. It start with a DIM.

:-)

Cor

"Stephany Young" <noone@localhos t> schreef in bericht
news:ek******** ********@TK2MSF TNGP09.phx.gbl. ..

Another point, as you are using VB.NET I STRONGLY recommend that you use
the String concatenation operator (&) instead of (+). The (+) operator
for string concatenation is a legacy from very old versions of BASIC and
use of it, especially with Option Strict Off, can lead to very nasty and
difficult to find problems. E,g: You might expect "1" + "2" to yield "21"
but it will, most likely yield 3.

It still appears that you haven't tyurned Option Strict On, otherwise the
code you just posted wouldn't have compiled.

Did you dump the reult of the concatenation? If so, what is it?

It would have worked perfectly when you tested it with the first two
fields (txtName and txtCompany) because, I suspect, the corresponding
database colums are of the 'text' or similar type.

Removing the single quotes surrouding the boolean fields may do the
trick, depending on what the database engine expects for boolean values.

If you do remove them then your code should look like:

... & "'," & chkStandardBags .Checked.ToStri ng & ",'" & ...

The fragment you posted still has the single quotes in it.
"Mark A. Sam" <ms**@Plan-It-Earth.Net> wrote in message
news:uH******** ******@tk2msftn gp13.phx.gbl...

Hello Stephany,

I thought about that after I posted this, so I ran it as one single line
and still have the issue.

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName,
txtCompany, txtPhone, txtEmail, txtComment, chkGrower, chkProduceDeale r,
txtOtherCustTyp e, chkStandardBags , chkCustomBags,t xtOtherBags) VALUES
('" + txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text +
"','" + txtEmail.Text + "','" + txtComment.Text + "','" +
chkGrower.Check ed + "','" + chkProduceDeale r.Checked + "','" +
txtOtherCustTyp e.Text + "','" + chkStandardBags .Checked + "','" +
chkCustomBags.C hecked + "','" + txtOtherBags.Te xt + "');"

I don't see the problem. This format worked fine when I tested it with
the first two field, txtName and txtCompany. I don't see any syntax
errors, though they could be there.

Another question if I may. Should I remove the single quote surrouding
the boolean fields, like

+ "','" + chkStandardBags .Checked + "','" +

Thank you.

Mark

[Duncan Mole]

Agree with the group that you should use parameters but I also suggest that
you perform some sort of assertion that your values will actually fit into
the DB fields you are tryinh to populate, you will see this error if they
overrun.

"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:Os******** ******@TK2MSFTN GP12.phx.gbl...

Mark,

Just try parameters, it seems more work, however you will see that as soon
as you are used to them, don't want to do it in another way anymore.

Have a look at our website for this simple sample.

http://www.vb-tips.com/default.aspx?...a-ae802ee71c86

I hope this helps,

Cor

"Mark A. Sam" <ms**@Plan-It-Earth.Net> schreef in bericht
news:uH******** ******@tk2msftn gp13.phx.gbl...

Hello Stephany,

I thought about that after I posted this, so I ran it as one single line
and still have the issue.

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDeale r,
txtOtherCustTyp e, chkStandardBags , chkCustomBags,t xtOtherBags) VALUES ('"
+ txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text + "','"
+ txtEmail.Text + "','" + txtComment.Text + "','" + chkGrower.Check ed +
"','" + chkProduceDeale r.Checked + "','" + txtOtherCustTyp e.Text + "','"
+ chkStandardBags .Checked + "','" + chkCustomBags.C hecked + "','" +
txtOtherBags.Te xt + "');"

I don't see the problem. This format worked fine when I tested it with
the first two field, txtName and txtCompany. I don't see any syntax
errors, though they could be there.

Another question if I may. Should I remove the single quote surrouding
the boolean fields, like

+ "','" + chkStandardBags .Checked + "','" +

Thank you.

Mark

[Mark A. Sam]

"Stephany Young" <noone@localhos t> wrote in message
news:ek******** ********@TK2MSF TNGP09.phx.gbl. ..

Another point, as you are using VB.NET I STRONGLY recommend that you use
the String concatenation operator (&) instead of (+). The (+) operator for
string concatenation is a legacy from very old versions of BASIC and use
of it, especially with Option Strict Off, can lead to very nasty and
difficult to find problems. E,g: You might expect "1" + "2" to yield "21"
but it will, most likely yield 3.

Actually I started using the & but thought that in VB it was standard to use
the +. I'll go back to the &.

It still appears that you haven't tyurned Option Strict On, otherwise the
code you just posted wouldn't have compiled.
I didn't know that. I don't understand that term or what it is for. Why
wouldn't I want the code to be compiled?


Did you dump the reult of the concatenation? If so, what is it?
I don't know what you mean. I'm new to .Net.

It would have worked perfectly when you tested it with the first two
fields (txtName and txtCompany) because, I suspect, the corresponding
database colums are of the 'text' or similar type.
Yes, and the chk box controls correspond to Access Yes/No fields. Could
that be a problem?

Removing the single quotes surrouding the boolean fields may do the trick,
depending on what the database engine expects for boolean values.
I tried it, and it didn't help, but it really should have an effect since I
am not writing to the DB at this point, only assigning the string variable.

If you do remove them then your code should look like:

... & "'," & chkStandardBags .Checked.ToStri ng & ",'" & ...

The fragment you posted still has the single quotes in it.

I know, I put them back in after I tested this and before I posted.
God Bless,

Mark