Need help with a VB insert command-VBForums
Results 1 to 10 of 10

Thread: Need help with a VB insert command

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Need help with a VB insert command

    Hello

    Consider the following: i have 4 textboxes which i want to use to insert data in my sql database.
    My sql table is composed of 4 fields: name-varchar, description-varchar,year-integer,grade-varchar.
    I have a button that will determine the new row to be added. But it doesn't work. I keep getting an error. Here is the code that i execute using the 5 texboxes:

    conn.open()
    com.connection=conn
    com.commandtype=commandtype.text
    com.CommandText = "insert into Table (name,description,year,grade) values( '"+textbox1.text.tostring+"','"+textbox2.text.tostring+ "',"+var(textbox3.text)+",'"textbox4.text.tostring"')

    I guess the problem is when i try adding the value of textbox3, which has to be of type integer.

    Trying to execute this command with a integer value for the year field inserted manually by me work just fine.

    Therefore, how can i work this problem out? I need to insert values from textboxes, getting into the code for each new entry is not an option.
    Thank!

  2. #2
    Loquacious User Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    23,887

    Re: Need help with a VB insert command

    Integers are not wrapped in single quotes as you have them. However, a better solution would be to use parameters. Appending in textbox contents like that can result in some type conversion issues, and will certainly leave you open to SQL Injection attacks.
    My usual boring signature: Nothing

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    7,740

    Re: Need help with a VB insert command

    When building strings you should be using & rather than + if for no reason other than it is easier to read the code.

    Your problem however is here
    Code:
    var(textbox3.text)
    Should be
    Code:
    Val(textbox3.text)

  4. #4
    Loquacious User Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    23,887

    Re: Need help with a VB insert command

    I missed that. However, the Val is actually a mistake, too. Val will return the string as a number (after a fashion), but you are concatenating in a string, so chaning it into a number won't even work if Option Strict is ON, which it should be.

    If you want to ensure that the contents of the textbox are a number, you should use Integer.TryParse (assuming that it really is an integer). Using Val will cause you to get incorrect data in some cases. Still, you shouldn't be concatenating in the textboxes anyways.
    My usual boring signature: Nothing

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    7,740

    Re: Need help with a VB insert command

    True I stopped paying attention once I saw the r in there.

    Changing it to an l should allow the code to run but as you said before it really should be done differently using parameters.

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Re: Need help with a VB insert command

    Thank you guys for everything so far.
    Unfortunately it doesnt work.
    I've corrected the mistake from VAR to VAL but it didnt do it..
    Also, i've tested the content of the textbox in which im supposed to insert an integere using :
    dim year as integer
    year= val(textbox.text)
    if(isnumeric(year)) then....

    The test works, the content of the variable year is numeric.

    I think the problem is in the insert clause... i know that for inserting string values, for those particular fields i have to use ' " + textbox.text.tostring + " ' or & & as you suggested, but what is the proper way to go about doing this when i want to insert integer values?
    A statement like : "insert into table (ID) values(3)" works just fine...whereas
    using "+ +" makes it wrong...
    Thanks again

  7. #7
    Addicted Member
    Join Date
    Jan 2012
    Location
    Athens, Greece
    Posts
    143

    Re: Need help with a VB insert command

    What about

    Code:
    "insert into Table (name,description,year,grade) values( '" & TextBox1.Text & "','" & TextBox2.Text & "'," & Val(TextBox3.Text) & ",'" & TextBox4.Text & "')"

  8. #8

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Re: Need help with a VB insert command

    Thats it!

    Thanks pkarvou, it works!

  9. #9
    Addicted Member
    Join Date
    Jan 2012
    Location
    Athens, Greece
    Posts
    143

    Re: Need help with a VB insert command

    Using & non-string values get converted to strings...with + you should do this using cast functions like cstr

  10. #10
    Loquacious User Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    23,887

    Re: Need help with a VB insert command

    Quote Originally Posted by mirceam91 View Post
    Also, i've tested the content of the textbox in which im supposed to insert an integere using :
    dim year as integer
    year= val(textbox.text)
    if(isnumeric(year)) then....

    The test works, the content of the variable year is numeric.
    That test is meaningless. Of COURSE year is numeric, you declared it to be an Integer type, and an Integer type can't be anything BUT numeric. The problem is with how Val works. It will ALWAYS return a number. In fact, it will return a Double, that you are implicitly converting to an Integer (bad and slow). The problem is that it always returns a number, and the number is often not what you want. Consider this:

    Val("123") Returns 123 (as a double)
    Val("$123") Returns 0
    Val("Blue") Returns 0
    Val("123,456") Returns 123 (probably depending on localization settings)
    Val(" 123") Returns 0

    What Val() is doing is converting the string to a number until it finds a character that it can't convert, then it stops. Therefore, in the last example, a leading space, which won't be noticeable in a textbox, means that Val will return 0 because it will hit that space and return. In the penultimate example, Val will hit the , and return just the 123, unless you have localization settings where a comma is used as a decimal place.

    Val is just a risky way to convert values. Aside from that, concatenating in textbox code leaves you wide open to SQL Injection attacks. If this is a program that no malicious person will ever use, then that's fine, but it's a bad practice to get into. Over in the Database section, there are some sticky threads, such as the Database FAQs that show how to use parameters to do that correctly. It isn't hard, it's safer, and it would have removed your issue with Val....mostly (an invalid string will still be an invalid string).
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.