Insert into table in VISUAL BASIC


I want to insert into Oracle table values from Visual Basic. I created the like using ADODB and everything working fine.
but in my insert statment i'm recieving error

Below is the insert statment where limit is defined as integer and in the oracle table it is number.

 SSQL = "Insert into ramzi_RECORD values(limit,1520)"

If i replace limit by any actual integer like 1,2,3...., it insert it in the database table.It seems i have to do a sort of concatination or add &.

Please advice
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
Hi ramziabk,

If limit is a variable then yes you need to concatenate:

SSQL = "Insert Into ramzi_RECORD Values(" & CStr(Limit) & ",1520)"

The CStr(Limit) ensures it is converted to a string, mostly such conversion is implicit but sometimes you can get strange results so it is always safer to ensure that you are dealing with the same datatype.

Tim Cottee
ramziabkAuthor Commented:
10X MATE IT WORKED but only if the variable is number, but if the varaible is string,i'm recieving an error.
Should i concatenate it with anything???

whats your error?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

If the variable is a string and you're trying to insert it into a field that is a number you will always receive an error from Oracle.  You would want to make sure that the 'Limit' variable had a number in it and not text.  

To do this you could put code in above the SQL statement that would make sure the variable 'Limit' is in the correct data format for the field you're trying to insert it into.  To find out if the variable is a number you could either write a function or try to convert it to an number variable type and if there's an error you know it's not.
ramziabkAuthor Commented:
My database record has two fields:ID  VARCHAR2(20)
                                                  ID2  VARCHAR2(20)

What i'm trying to insert is varaible result of mid, I'm reading from textfile,using MID function to divide text and insert into table.
Whenever variable is string like "username" .I'm recieving error.

ex. text file i'm reading has this format.

first 2 characters as alphanumeric,
next 5 as number,
next 5 as number,
next 16 anouther number,
next 30 characters alphanumeric
and so on.

Numbers are inserted properly using Cstr.Alphanumeric returns error.

Please help
your insert string must be :

SSQL = "Insert Into ramzi_RECORD Values(' " & CStr(Limit) & " ','1520')"

don't look at the format of the textfile, oracle is the most important place to look for the format.  Since it's varchar2 (string) you must place your inserted values between simple quote.

if you don't insert all of the oracle field in the query, you must insert the name of your field in the query

SSQL = "Insert Into ramzi_RECORD (ID,ID2) Values(' " & CStr(Limit) & " ','1520')"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anandhi KAssociate DirectorCommented:
You have to do the concatenation when sending data thro' variables like given below

SSQL = "Insert into ramzi_RECORD values(" & limit & ",1520)"
This definatly works for you

SSQL = "Insert into ramzi_RECORD values('" & limit & "','1520')"

I'd like to know what happened to ramziabk and his project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.