how to get " in a string

[tjpowers]

I think I have been told this before, but do I add a " in a string...

example "I am trying to use an "app" by myself"

what I am doing, is trying to send a string into a MySQL database, and it needs to be in quotation marks, and unfortunately, a ' doesnt work for some reason.

 

[Solitaire]

Quotations in string

I think I have been told this before, but do I add a " in a string...

example "I am trying to use an "app" by myself"

what I am doing, is trying to send a string into a MySQL database, and it needs to be in quotation marks, and unfortunately, a ' doesnt work for some reason.

Use a double quote around the text to quote. For example:

MessageBox.Show("This is ""a quote"" in a sentence.")

 

[tjpowers]

Thanks... I did try that. I will explain the situation. Its complicated.
I have a mySQLCommandtext:

NonQueryCommand.CommandText = _
"INSERT INTO info(Date, StartTime, EndTime, TimeTaken, CompanyName, ContactName, Description, Chargeable, Rate, TotalCharges)" + _
"VALUES('?Date', '?StartTime', '?EndTime', '?TimeTaken', '?Companyname', '?ContactName', '?Description', '?Chargeable', ?Rate, ?TotalCharges)"

dont be fooled by the single ', its only to send out the names because without them i get an error. "input string is not in a correct format.
this only happens with strings right now.
this is more of the code.

NonQueryCommand.Parameters.AddWithValue("?Date", MySqlDbType.VarChar)
NonQueryCommand.Parameters.AddWithValue("?StartTime", MySqlDbType.VarChar)
NonQueryCommand.Parameters.AddWithValue("?EndTime", MySqlDbType.VarChar)
NonQueryCommand.Parameters.AddWithValue("?TimeTaken", MySqlDbType.VarChar)
NonQueryCommand.Parameters.AddWithValue("?CompanyName", MySqlDbType.VarChar)
NonQueryCommand.Parameters.AddWithValue("?ContactName", MySqlDbType.VarChar)
NonQueryCommand.Parameters.AddWithValue("?Description", MySqlDbType.Text)
NonQueryCommand.Parameters.AddWithValue("?Chargeable", MySqlDbType.VarChar)
NonQueryCommand.Parameters.AddWithValue("?Rate", MySqlDbType.Double)
NonQueryCommand.Parameters.AddWithValue("?TotalCharges", MySqlDbType.Double)


NonQueryCommand.Prepare()


For Each row As DataGridViewRow In DailyReportGrid.Rows
Dim datestring As String = row.Cells("DateColumn").Value.ToString
Dim starttimestring As String = row.Cells("StartTimeColumn").Value.ToString
Dim endtimestring As String = row.Cells("EndTimeColumn").Value.ToString
Dim timetakenstring As String = row.Cells("TimeTakenColumn").Value.ToString
Dim customernamestring As String = row.Cells("CompanyNameColumn").Value.ToString
Dim contactnamestring As String = row.Cells("ContactNameColumn").Value.ToString
Dim descriptionstring As String = row.Cells("DescriptionColumn").Value, Chr(34)
Dim chargeablestring2 As String = row.Cells("ChargeableColumn").Value.ToString
Dim ratesdecimal As Double = row.Cells("RateColumn").Value
Dim totalchargesdecimal As Double = row.Cells("TotalChargesColumn").Value




NonQueryCommand.Parameters("?Date").Value = datestring
NonQueryCommand.Parameters("?StartTime").Value = starttimestring
NonQueryCommand.Parameters("?EndTime").Value = endtimestring
NonQueryCommand.Parameters("?TimeTaken").Value = timetakenstring
NonQueryCommand.Parameters("?CompanyName").Value = customernamestring
NonQueryCommand.Parameters("?ContactName").Value = contactnamestring
NonQueryCommand.Parameters("?Description").Value = DescriptionTextBox.Text
NonQueryCommand.Parameters("?Chargeable").Value = chargeablestring
NonQueryCommand.Parameters("?Rate").Value = ratesdecimal
NonQueryCommand.Parameters("?TotalCharges").Value = totalchargesdecimal


Next


NonQueryCommand.ExecuteNonQuery()
Catch formatEx As System.FormatException
MessageBox.Show(formatEx.ToString)
Catch ex As MySqlException
MessageBox.Show(ex.ToString)
Finally
MySQLConnection.Close()


End Try

again, my code seems to be a bit much with the unnecessary variables, but i only did so to play with the strings in many different ways. the strings coming from either a textbox, or the gridcell doesnt match a proper string to enter my MySQL database. i have doubles and they go in fine, but the strings dont. any ideas?

 

[Solitaire]

You could also try concatenating the ASCII character for the double quotation symbol:

"This includes a " & Chr(34) & "quote" & Chr(34) & " in the sentence."

 

[JohnH]

You don't quote parameters in a query, that would turn them into literal strings rather than parameters.

 

[Gopher2011]

Will not a single quote work?
MessageBox.Show("This is 'a quote' in a sentence.")

Double the quote
MessageBox.Show("This is ""a quote"" in a sentence.")

PS I am not sure you should do this in SQL Strings.

 

[tjpowers]

Well, JohnH u seem to know your VB very well... most likely better than myself, in my code above, other than the single quotes on the variables (cause i meant to do that for now since I keep getting errors on the strings the variables holds) do you see what I may be doing wrong. It constantly tells me the input string is of invalid format. Ive been trying to finish this software for a few weeks now, switched from VS 2010 Express to VS 2008 due to the constraints in ADO.NET functions (Cannot add MySQL connector), and i have been losing my mind over it. Any suggestions?

 

[jmcilhinney]

Now you basically have two threads for the same topic.

http://www.vbdotnetforums.com/mysql/48635-inserting-into-database-using-mysql.html

You are trying to solve the wrong problem. From the error message, you are almost certainly passing an invalid String for a date or number. The simple solution is to not use Strings for values that aren't text. If the value isa date then use a Date. If the value is a number then use the appropriate numeric type. You just have to use them properly. If it wasn't working for you then you should have posted that problem and we could have addressed the real issue rather than trying to help you coblle an ineffective workaround.

 

[tjpowers]

any string wont pass... and yes at the beginning, i had two different threads because i thought the issues were somewhat seperate but it lead to the same thing. for now, i do not want to focus on the dates. both the database, and the values are all in strings. it should not have an issue. nothing is in a date format or time formats... once i can figure out how to put in a strign in the database, then i will try to change the types.

 

[Gopher2011]

Why not do a test, and change the types now? Then if that does not work explore other solutions?

 

[tjpowers]

because at the moment i am not even bothering with the dates and the times. if you look at my 4 last variables, two should be decimals. i tried as strings and wont work, i put them as decimals, and the numbers are truncated to .0; so i would rather have them as strings. then the other two, one was a boolean which is not a valid mysql type, so i changed them to string, and no good. the other one, description, is an actual text. it was a strnig to begin with. but it is still not sending to the database. once i can get them moving, i can move along further to the more difficult ones such as the dates and the times. right now, i am sending ?Date to my database. i hope you understand what i mean now. its not about the dates or the times, its just about the strings.

 

[Gopher2011]

MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character) types.

Booleans are easy - numeric 1 or 0, convert them and store them as such.

Dim obj As Boolean
obj = True
MsgBox(Convert.ToInt32(obj))
obj = False
MsgBox(Convert.ToInt32(obj))


However in fairness I am using varchar also.. and it works but I is on SQL Svr 2010 not MySql

Main Class part
Private GFI As Database

sP_1 As String = "SELECT DISTINCT FROM...etc"
sP_2 = "WHERE (Data.DT BETWEEN @pFr AND @pTo) "
sP_3 As String = "AND blah blah ORDER BY blah blah"

GFI.sP_Final = sP_1 & sP_2 & sP_3



Call GFI.PopulateTable_FrTo(GFI.sP_Final)

  Public Sub PopulateTable_FrTo(ByVal QueryStr As String)
        DC.Open()
        SC = New SqlCommand(QueryStr, DC)


        SC.Parameters.Add(New SqlParameter("@pFr", SqlDbType.VarChar, 10)).Value = frm_Main.s_Fr
        SC.Parameters.Add(New SqlParameter("@pTo", SqlDbType.VarChar, 10)).Value = frm_Main.s_To


        Using SR As SqlDataReader = SC.ExecuteReader()
            Application.DoEvents()
            DS.Tables.Clear()
            DS.Tables.Add("Main")
            DS.Tables("Main").Load(SR)
            frm_Main.dgv_Table.DataSource = DS.Tables("Main")
        End Using
        SC.Dispose()
        DC.Close()
    End Sub

 

[tjpowers]

Yeah i get what your trying to say, but my command is not what is having the problem.
my command line goes threw, its the values entered in teh parameters that it doesnt like when its a string. i dont know if its the " it doesnt like, or the fact that a visual basic string conflicts with a mysql varchar. im sure dates and times and such would work... but i have fields i would prefer in string, and its giving me this issue.

 

[tjpowers]

HA!! You know what I finally figured it all out. before entering a value in my parameter, I must use .resetDBType
it worked like a charm