When to declare variable as texbox or string?

method · May 16th, 2005, 01:36 PM

HI guys. I want to learn when to declare a variable as string and when
to declare it as TextBox like the example below?
How to use visual studio .net 2003 to construct these variables for us?

Furthermore, in constructing the sql statement like the example below, when we need to use replace andWhen not to use it.?
How to use visual studio .net 2003 to construct this sql statement for us?

I addition, when to use single ' and when not to use it?

Furthermore, why the âreplaceâ is used in this particular order in the example below?

I will be happy if some expert teaches me how to use them properly.
A reference to a complete tutorial about these topics is highly appreciated.

Code:

                Dim tbText0     As String  = E.Item.Cells(0).Text
        Dim tbText1     As String  = E.Item.Cells(1).Text
        Dim tbText2     As TextBox = E.Item.Cells(2).Controls(0)


        ' Update the appropriate record in our database.
        Dim objCommand  As SqlCommand
        Dim strSQLQuery As String

        ' Build our update command.
              strSQLQuery = "UPDATE MATCHES " _
                     & "SET teamno = '" & Replace(tbText0, "'", "''") & "', " _
                     & "PLAYERNO = '" & tbText1 & "', " _
                     & "won = '" & Replace(tbText2.Text, "'", "''") & "' " _
                     & "WHERE matchno = '" & tbText1 & "';"

planoie · May 16th, 2005, 01:51 PM

You need to declare a variable type according to what you plan to do with the variable. It's really nothing more than that. If you are going to need to work with a textbox, you need a variable of that type. That example shows how you construct the variables. You Dim them.

You need to use Replace() when you need to replace something. In this case, the example needs to replace ' with ''.

In a SQL statement you need to escape a single ' in a value with the double ' other SQL will break. Here is an example:

The program asks for your name:
Enter your name: Pat O'malley

The program constructs the SQL statement:
INSERT INTO Users(Name) VALUES('Pat O'Malley')

As you can see... the SQL value for the Name column will stop at the ' in O'Malley and SQL will break because it doesn't know what to do with the rest of the command text that it reads as "Malley')". Therefore, you need to escape the ' with '' so SQL knows that you are telling it that you want the character ' instead of telling it that you are indicating the end of the string value. Typically, you should always escape the ' in a string value that is getting written to a SQL statment.

-Peter

method · May 16th, 2005, 02:03 PM

Thank u for u reply. if i want to edit value of a textboxs and then write it to the sql server db, what data type should i declare the variables. As a string ?

planoie · May 16th, 2005, 03:16 PM

The .Text property of the textbox control is String. If you need to handle a specific datatype you might need to convert, but when you construct the SQL query all you will end up doing is feeding the SQL query the string equivalent of the variable. However, keep in mind that the table fields you deal with in your SQL query will determine what the datatype is that you are actually saving. For example, the following is perfectly valid despite the fact that the data type in the database is an integer while the server code variable is a string:

Dim strMyInteger As String = "99"
Dim strSql As String

strSql = "INSERT INTO MyTable(MyIntegerField) VALUES(" & strMyInteger & ")"

The MyIntegerField is an integer, but you handle the variable in the server code as a string which is perfectly legal.

You must keep in mind what context you are in when you work between the user interface (ASP.NET with HTML controls), the server code (VB.NET) and the database commands (SQL queries).

-Peter