Input string not in correct format

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
M

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, chkProduceDealer,
txtOtherCustType, chkStandardBags, chkCustomBags,txtOtherBags) " + _

"VALUES ('" + txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text
+ "','" + txtEmail.Text + "','" + txtComment.Text + "','" +
chkGrower.Checked + "','" + chkProduceDealer.Checked + _

"','" + txtOtherCustType.Text + "','" + chkStandardBags.Checked + "','" +
chkCustomBags.Checked + "','" + txtOtherBags.Text + "');"


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,

chkProduceDealer.Checked + "','" + txtOtherCustType.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
 
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.Writeline 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.
 
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, chkProduceDealer,
txtOtherCustType, chkStandardBags, chkCustomBags,txtOtherBags) VALUES ('" +
txtName.Text + "','" + txtCompany.Text + "','" + txtPhone.Text + "','" +
txtEmail.Text + "','" + txtComment.Text + "','" + chkGrower.Checked + "','"
+ chkProduceDealer.Checked + "','" + txtOtherCustType.Text + "','" +
chkStandardBags.Checked + "','" + chkCustomBags.Checked + "','" +
txtOtherBags.Text + "');"

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
 
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(sConnStr);
objconn.Open();

SqlCommand objcommand = new SqlCommand(strSQL,objconn);

// Set the parameter values
objcommand.Parameters.Add("(@txtName", SqlDbType.VarChar).Value =
txtName.Text;
objcommand.Parameters.Add("(@txtCompany", SqlDbType.VarChar).Value =
txtCompany.Text;

// ... rest of code

PL.
 
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.ToString & ",'" & ...

The fragment you posted still has the single quotes in it.
 
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
 
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.
 
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.
 
Stephany Young said:
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.
Click to expand...


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.
Click to expand...

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?
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.ToString & ",'" & ...

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

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


God Bless,

Mark
 
Cor,

It isn't the work that is the issue, but rather that I am just trying to get
something to work. I'm lost following help and have no idea about common
methods. All I have done for the last 15 years or so is Access VBA and
upgrading to SQL Server, type work. I avoided ADO altogether, so I have a
lot to learn just to get going here. I'll check our the link and try to
take it where you are leading me.

God Bless,

Mark
 
It's not a matter of not wanting the code to compile. If you set the Option
Strict property of your project to ON, you will find that the compiler will
'spit it's dummy' at all the places in your code where there are problems.

Concatenate is the term for joing strings together and & is the
concatenation operator. E.g. You can read "AAA" & "BBB" as "AAA" concatenate
"BBB" - the result of the concatenation is "AAABBB".

Try this code, it works as expected. Make sure you copy and paste it exactly
as it is.

Dim txtName As New TextBox
txtName.Text = "A"
Dim txtCompany As New TextBox
txtCompany.Text = "B"
Dim txtPhone As New TextBox
txtPhone.Text = "C"
Dim txtEmail As New TextBox
txtEmail.Text = "D"
Dim txtComment As New TextBox
txtComment.Text = "E"
Dim chkGrower As New CheckBox
chkGrower.Checked = False
Dim chkProduceDealer As New CheckBox
chkProduceDealer.Checked = True
Dim txtOtherCustType As New TextBox
txtOtherCustType.Text = "F"
Dim chkStandardBags As New CheckBox
chkStandardBags.Checked = False
Dim chkCustomBags As New CheckBox
chkCustomBags.Checked = True
Dim txtOtherBags As New TextBox
txtOtherBags.Text = "G"

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, " & _
"txtCompany, txtPhone, txtEmail, txtComment, chkGrower, chkProduceDealer, "
& _
" txtOtherCustType, chkStandardBags, chkCustomBags,txtOtherBags)" & _
" VALUES ('" & txtName.Text & "','" & txtCompany.Text & "','" & _
txtPhone.Text & "','" & txtEmail.Text & "','" & txtComment.Text & _
"','" & chkGrower.Checked & "','" & chkProduceDealer.Checked & _
"','" & txtOtherCustType.Text & "','" & chkStandardBags.Checked & _
"','" & chkCustomBags.Checked & "','" & txtOtherBags.Text & "');"

Console.WriteLine(strSQL)
 
Hello Stephany,

Console.WriteLine(strSQL) ???
Here is my problem. How would I have ever known that method without coming
on the newsgroup. Where do you learn this stuff? There never seem to be
strictly working examples of methods anywhere. I mean complete examples of
common methodology. It always seems I have to spend hours sreaching and
searching for examples and hoping I can get them to work.

Before I explore your suggested method, here is what I have done, and it is
close. I parametized the SQL Statement. If it is acceptable practice, I
would like to get it working before trying something different.

Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + Server.MapPath("_database/DragonImporting.mdb")

Dim myConn As New Data.OleDb.OleDbConnection(connectString)

myConn.Open()

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDealer,
txtOtherCustType, chkStandardBags, chkCustomBags,txtOtherBags) VALUES
(@txtName, @txtCompany, @txtPhone, @txtEmail.Text, @txtComment, @chkGrower,
@chkProduceDealer, @txtOtherCustType, @chkStandardBags.Checked,
@chkCustomBags, @txtOtherBags)"

Dim myCmd As New Data.OleDb.OleDbCommand(strSQL, myConn)

myCmd.Parameters.Add("txtName", Data.OleDb.OleDbType.VarChar).Value =
txtName.Text

myCmd.Parameters.Add("txtCompany", Data.OleDb.OleDbType.VarChar).Value =
txtCompany.Text

myCmd.Parameters.Add("txtPhone", Data.OleDb.OleDbType.VarChar).Value =
txtPhone.Text

myCmd.Parameters.Add("txtEmail", Data.OleDb.OleDbType.VarChar).Value =
txtEmail.Text

myCmd.Parameters.Add("txtComment", Data.OleDb.OleDbType.VarChar).Value =
txtComment.Text

myCmd.Parameters.Add("chkGrower", Data.OleDb.OleDbType.Boolean).Value =
chkGrower.Text

myCmd.Parameters.Add("chkProduceDealer", Data.OleDb.OleDbType.Boolean).Value
= chkProduceDealer.Text

myCmd.Parameters.Add("txtOtherCustType", Data.OleDb.OleDbType.VarChar).Value
= txtOtherCustType.Text

myCmd.Parameters.Add("chkStandardBags", Data.OleDb.OleDbType.Boolean).Value
= chkStandardBags.Text

myCmd.Parameters.Add("chkCustomBags", Data.OleDb.OleDbType.Boolean).Value =
chkCustomBags.Text

myCmd.Parameters.Add("txtOtherBags", Data.OleDb.OleDbType.VarChar).Value =
txtOtherBags.Text

myCmd.ExecuteNonQuery()

myConn.Close()


I get an error:

Line 125: myCmd.ExecuteNonQuery()

[FormatException: String was not recognized as a valid Boolean.]

I set Strict to True on the document, but couldn't find the same property
sheet for the project. The error result was the same.

The chk box controls are feeding Yes/No fields in and Access DB. Also the
txtComment field in Access is a memo field. I don't know if varchar is
approriate for that.

Is my methodogy ok for this or should I change it?

Thank you for the help. It is greatly appreciated.

Mark
 
I learn it chiefly py pressing the F1 key.

Using a XXCommand object with parameters is fine, but I was attempting to
get you to understand what was actually causing the problem.

I your experience of VBA in MS Access, you have been shielded from having to
understand what is actually 'happening under the hood'. Now you have to
worry about the detail and you have to get it right or it will not work.


Mark A. Sam said:
Hello Stephany,

Console.WriteLine(strSQL) ???
Here is my problem. How would I have ever known that method without
coming on the newsgroup. Where do you learn this stuff? There never seem
to be strictly working examples of methods anywhere. I mean complete
examples of common methodology. It always seems I have to spend hours
sreaching and searching for examples and hoping I can get them to work.

Before I explore your suggested method, here is what I have done, and it
is close. I parametized the SQL Statement. If it is acceptable practice,
I would like to get it working before trying something different.

Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + Server.MapPath("_database/DragonImporting.mdb")

Dim myConn As New Data.OleDb.OleDbConnection(connectString)

myConn.Open()

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, txtCompany,
txtPhone, txtEmail, txtComment, chkGrower, chkProduceDealer,
txtOtherCustType, chkStandardBags, chkCustomBags,txtOtherBags) VALUES
(@txtName, @txtCompany, @txtPhone, @txtEmail.Text, @txtComment,
@chkGrower, @chkProduceDealer, @txtOtherCustType,
@chkStandardBags.Checked, @chkCustomBags, @txtOtherBags)"

Dim myCmd As New Data.OleDb.OleDbCommand(strSQL, myConn)

myCmd.Parameters.Add("txtName", Data.OleDb.OleDbType.VarChar).Value =
txtName.Text

myCmd.Parameters.Add("txtCompany", Data.OleDb.OleDbType.VarChar).Value =
txtCompany.Text

myCmd.Parameters.Add("txtPhone", Data.OleDb.OleDbType.VarChar).Value =
txtPhone.Text

myCmd.Parameters.Add("txtEmail", Data.OleDb.OleDbType.VarChar).Value =
txtEmail.Text

myCmd.Parameters.Add("txtComment", Data.OleDb.OleDbType.VarChar).Value =
txtComment.Text

myCmd.Parameters.Add("chkGrower", Data.OleDb.OleDbType.Boolean).Value =
chkGrower.Text

myCmd.Parameters.Add("chkProduceDealer",
Data.OleDb.OleDbType.Boolean).Value = chkProduceDealer.Text

myCmd.Parameters.Add("txtOtherCustType",
Data.OleDb.OleDbType.VarChar).Value = txtOtherCustType.Text

myCmd.Parameters.Add("chkStandardBags",
Data.OleDb.OleDbType.Boolean).Value = chkStandardBags.Text

myCmd.Parameters.Add("chkCustomBags", Data.OleDb.OleDbType.Boolean).Value
= chkCustomBags.Text

myCmd.Parameters.Add("txtOtherBags", Data.OleDb.OleDbType.VarChar).Value =
txtOtherBags.Text

myCmd.ExecuteNonQuery()

myConn.Close()


I get an error:

Line 125: myCmd.ExecuteNonQuery()

[FormatException: String was not recognized as a valid Boolean.]

I set Strict to True on the document, but couldn't find the same property
sheet for the project. The error result was the same.

The chk box controls are feeding Yes/No fields in and Access DB. Also the
txtComment field in Access is a memo field. I don't know if varchar is
approriate for that.

Is my methodogy ok for this or should I change it?

Thank you for the help. It is greatly appreciated.

Mark




Stephany Young said:
It's not a matter of not wanting the code to compile. If you set the
Option Strict property of your project to ON, you will find that the
compiler will 'spit it's dummy' at all the places in your code where
there are problems.

Concatenate is the term for joing strings together and & is the
concatenation operator. E.g. You can read "AAA" & "BBB" as "AAA"
concatenate "BBB" - the result of the concatenation is "AAABBB".

Try this code, it works as expected. Make sure you copy and paste it
exactly as it is.

Dim txtName As New TextBox
txtName.Text = "A"
Dim txtCompany As New TextBox
txtCompany.Text = "B"
Dim txtPhone As New TextBox
txtPhone.Text = "C"
Dim txtEmail As New TextBox
txtEmail.Text = "D"
Dim txtComment As New TextBox
txtComment.Text = "E"
Dim chkGrower As New CheckBox
chkGrower.Checked = False
Dim chkProduceDealer As New CheckBox
chkProduceDealer.Checked = True
Dim txtOtherCustType As New TextBox
txtOtherCustType.Text = "F"
Dim chkStandardBags As New CheckBox
chkStandardBags.Checked = False
Dim chkCustomBags As New CheckBox
chkCustomBags.Checked = True
Dim txtOtherBags As New TextBox
txtOtherBags.Text = "G"

Dim strSQL As String = "INSERT INTO tblContactForm1 (txtName, " & _
"txtCompany, txtPhone, txtEmail, txtComment, chkGrower, chkProduceDealer,
" & _
" txtOtherCustType, chkStandardBags, chkCustomBags,txtOtherBags)" & _
" VALUES ('" & txtName.Text & "','" & txtCompany.Text & "','" & _
txtPhone.Text & "','" & txtEmail.Text & "','" & txtComment.Text & _
"','" & chkGrower.Checked & "','" & chkProduceDealer.Checked & _
"','" & txtOtherCustType.Text & "','" & chkStandardBags.Checked & _
"','" & chkCustomBags.Checked & "','" & txtOtherBags.Text & "');"

Console.WriteLine(strSQL)
Click to expand...
Click to expand...
 
Back
Top