Cannnot convert from String to Integer (INSERT INTO SQL)

[networkmancer]

Proper way of doing this?
Please disregard some variables.

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Public Class Addcage
    Dim conn As New OracleConnection
    Private cmd As OracleCommand
    Private da As OracleDataAdapter
    Private cb As OracleCommandBuilder
    Private ds As DataSet
    Dim roomprice As Integer
    Dim roomtitle As String
    Dim lubid As String = "User Id= PETHOTEL" & ";Password= 911" & ";Data Source = xe"
    Private Sub Addcage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        roomprice = CInt(roomcost.Text)
        roomtitle = roomname.Text
        Try
            conn.ConnectionString = lubid
            conn.Open()
            Dim sql As String = "INSERT INTO CAGETYPE (CAGECOST, CAGENAME) VALUES('" & roomprice & "'," & roomtitle & ")"
            conn.BeginTransaction(sql)
            MsgBox("adding complete")

        Catch ex As Exception
            MsgBox(ex.Message)
            conn.Close()
        End Try

        conn.Close()
    End Sub
End Class

[kingsonprisonic]

You misplace the quotes..

try this

Dim sql As String = "INSERT INTO CAGETYPE (CAGECOST, CAGENAME) VALUES(" & roomprice & ",'" & roomtitle & "')"

as CAGENAME Varchar so quote required for roomtitle .

[networkmancer]

Get this error

Conversion from string "INSERT INTO CAGETYPE(CAGECOST,"to type 'Integer' is not valid.

[M.Waqas Aslam]

Get this error

Conversion from string "INSERT INTO CAGETYPE(CAGECOST,"to type 'Integer' is not valid.

hello !
please try this code ,

Dim sql As String = "INSERT INTO CAGETYPE (CAGECOST, CAGENAME) VALUES(" & val(roomprice) & ",'" & roomtitle & "')"

hope this will solve your prob , if yes then please mark your thread solved and vote me up :)

Regards

[networkmancer]

Still the same. Getting the same error

Conversion from string "INSERT INTO CAGETYPE(CAGECOST,"to type 'Integer' is not valid.

If you can solve or someone solve it Ill vote up. :)

or any simple way of doing some sql statement in vb. The shorter the better.

[kingsonprisonic]

please post your current code.

[networkmancer]

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Public Class Addcage
    Dim conn As New OracleConnection
    Private cmd As OracleCommand
    Private da As OracleDataAdapter
    Private cb As OracleCommandBuilder
    Private ds As DataSet
    Dim roomprice As Integer
    Dim roomtitle As String
    Dim lubid As String = "User Id= PETHOTEL" & ";Password= 911" & ";Data Source = xe"
    Private Sub Addcage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        roomprice = CInt(roomcost.Text)
        roomtitle = roomname.Text
        Try
            conn.ConnectionString = lubid
            conn.Open()
            Dim sql As String = "INSERT INTO CAGETYPE (CAGECOST, CAGENAME) VALUES(" & Val(roomprice) & ",'" & roomtitle & "')"
            conn.BeginTransaction(sql)
            MsgBox("adding complete")

        Catch ex As Exception
            MsgBox(ex.Message)
            conn.Close()
        End Try

        conn.Close()
    End Sub
End Class

That is all of it in the form. I just experiment those so I am not sure on how to add items in the database through that method though I know some codes adding items in the database but those where long.

[kingsonprisonic]

will you plz provide the line number where error occurred ?

[Reverend Jim]

It would also help if you posted the structure of your table (field names and types)

[networkmancer]

Just Number and Varchar respectively.

How to find line it occurred? because I guess it occurred in line 24 due to the fact that that was the catch message reports.

Weird part is that the error started from the word INSERT up to CAGECOST. Maybe some enclosure can help?

Conversion from string "INSERT INTO CAGETYPE(CAGECOST,"to type 'Integer' is not valid.

[Reverend Jim]

I used the following query:

Dim sql As String = "INSERT INTO CAGETYPE (CAGECOST, CAGENAME) VALUES(" & roomprice & ",'" & roomtitle & "')"

with roomprice = 45.99 and roomtitle = "suite"
and the insert worked. I tried with two table definitions. In the first, CAGECOST was defined as int, and the other as numeric(18,0). Worked both times. Of course, the value for roomprice was converted to integer both times.

[networkmancer]

It still did not work. By the way here's my sql for my table

SQL

CREATE TABLE  "CAGETYPE" 
   (	"CAGEID" NUMBER, 
	"CAGENAME" VARCHAR2(50), 
	"CAGECOST" NUMBER, 
	"CAGECOUNT" NUMBER, 
	 CONSTRAINT "CAGETYPE_PK" PRIMARY KEY ("CAGEID") ENABLE
   ) ;

CREATE OR REPLACE TRIGGER  "BI_CAGETYPE" 
  before insert on "CAGETYPE"               
  for each row  
begin   
  if :NEW."CAGEID" is null then 
    select "CAGETYPE_SEQ".nextval into :NEW."CAGEID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_CAGETYPE" ENABLE;

Can I see your whole code?

[M.Waqas Aslam]

hello!
please change given code , may be this time it work fine

' roomprice = CInt(roomcost.Text) 
  roomprice = val(roomcost.Text)

Regards

[hericles]

Im thinking the real problem is this line:

conn.BeginTransaction(sql)

BeginTransaction takes an isolation level as an argument i.e. an enumeration. You are trying to feed it a string. Try:

cmd.CommandText = sql
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted) // or whatever isolation works for you

[ChrisPadgham]

at line 24 you should not have VAL function this is to convert a string to a number and room price is an integer. if anthing you shoul probably use Cstr

[networkmancer]

@hericles

How to do that? It will give me error as those code cant be red , Underlined Blue. Do I need to add or import?

Im just using these

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

[nitian]

For more information, then be patient

[hericles]

IsolationLevel comes from System.Data I believe. Try adding that if you don't have it already.

[networkmancer]

Well System.Data is default reference I think so I have it. Can you complete the code? If its ok with you because transaction cannot be found. or add any variables needed.

[networkmancer]

I got it. Thanks anyway to your help. I use Oracle Command. Actually there is nothing wrong with the sql statement.

Try
            conn.ConnectionString = lubid
            conn.Open()
            Dim sql As String = "INSERT INTO CAGETYPE (CAGECOST, CAGENAME) VALUES(" & Val(roomcost.Text) & ",'" & roomname.Text & "')"
            cmd = New OracleCommand(sql, conn)

            MessageBox.Show("Record updated", "Pet Hotel FD", MessageBoxButtons.OK, MessageBoxIcon.Information)
            conn.Close()
         
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

[Reverend Jim]

As a matter of note, it is not necessary to do the Val(roomcost.Text). When you think about it, all that is getting passed as the sql command is a string so all that is happening is

Val(roomcost.Text)

is converting the text string (which contains a number) to an actual number. Then

& Val(roomcost.Text) &

is just converting it right back to a string. You might as well be doing

Val(roomcost.Text).ToString

So the Val() is pointless (and actually a waste of CPU unless the compiler optimizes it out).

In any case, if you have what you need then please mark this thread as solved.

[networkmancer]

Yes I actually know that. Val was just added due to the face that I was searching for ways in solving it. I actually forgot to remove it. Thanks anyways to your help. I do not know where to edit and make this thread as solve.

[M.Waqas Aslam]

look where you type comments , there is a link to mark this thread solved . click it , and the thread will be mark as solved.