Inserting supplier detail using save button

Question

firoz.raj -3 Posting Pro in Training

15 Years Ago

Can anybody tell me why i am getting error in the bold line.
Application uses a value of the wrong type for the current
Operation .?

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set Con = New ADODB.Connection
        Con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")

        Dim cmd As ADODB.Command
        Set cmd = New ADODB.Command

        If Not Con Is Nothing Then
            With cmd
                .ActiveConnection = Con
                .CommandType = adCmdText
               .CommandText = "INSERT INTO supplier (supplier_name,supplier_id, contact_person, contact_no, type, office_address, emails, website ) VALUES (?,?,?,?,?,?,?,?)"
                .Parameters.Append .CreateParameter("@SupplierName", adChar, adParamInput, 20, Text2.Text)
                .Parameters.Append .CreateParameter("@SupplierID", adChar, adParamInput, 20, Text1.Text)
                .Parameters.Append .CreateParameter("@Contactperson", adChar, adParamInput, 20, Text3.Text)
                .Parameters.Append .CreateParameter("@ContactNo", adChar, adParamInput, 20, Text4.Text)
                .Parameters.Append .CreateParameter("@Type", adChar, adParamInput, 20, Combo1.Text)
     [B]           .Parameters.Append .CreateParameter("@OfficeAddr", adChar,[/B] adParamInput, 50, Text5.Text)
                .Parameters.Append .CreateParameter("@Emails", adChar, adParamInput, 20, Text6.Text)
                .Parameters.Append .CreateParameter("@Website", adChar, adParamInput, 20, Text7.Text)
                .Execute
            End With
            Set Con = Nothing
        Else
            MsgBox "connection not set"
            MsgBox ("Data Saved")
        End If
        End If
       End Sub

visual-basic

2 Contributors
30 Replies
240 Views
4 Days Discussion Span
Latest Post 15 Years Ago Latest Post by debasisdas

debasisdas 580 Posting Genius

15 Years Ago

use the following code

con.BeginTrans
SQL = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values('" & Combo1.Text & "',' " & Text1.Text & "','" & Text2.Text & " ',' " & Text3.Text & " ','" & Text4.Text & "','" & Text5.Text &  "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
con.Execute SQL
con.CommitTrans

debasisdas 580 Posting Genius

15 Years Ago

use the following

SQL = "INSERT INTO Supplier(Supplier_ID, Supplier_name,Fax_No,Contact_person,Contact_No,Supp_Type,Office_Address,Emails,website) values(" & val(Combo1.Text) & ",'" & Text1.Text & "'," & val(Text2.Text) & ",'" & Text3.Text & "'," & val(Text4.Text) & ",'" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"

debasisdas 580 Posting Genius

15 Years Ago

ensure that you are passing a number for number field with out any special characters

you are getting 0 means there is something wrong with the input string through textbox or any other control

debasisdas 580 Posting Genius

15 Years Ago

you need to use proper variable type and modify the fields in database table to accomodate data that you are going to pass from application.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

debasisdas 580 Posting Genius Featured Poster · Answer 1 · 2009-02-19T17:38:57+00:00

Why you need all the lengthy code
just try this

con.begintrans       'con---ADODB connection object.
con.execute "your insert statment here"
con.committrans

firoz.raj -3 Posting Pro in Training · Answer 2 · 2009-02-21T11:22:44+00:00

right now i am getting error no value given for one or more
required parameters.here is the code what i have written

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set Con = New ADODB.Connection
        Con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")
        Con.BeginTrans
  [B]      Con.Execute "INSERT INTO Supplier(Supplier_name,Supplier_id,Contact_person,Contact_no,Type,Office_address,emails,website) values(?,?,?,?,?,?,?,?)"[/B]        Con.CommitTrans
        End If
       End Sub

debasisdas 580 Posting Genius Featured Poster · Answer 3 · 2009-02-21T17:12:55+00:00

why you have passed all those ???? marks in place of values, while not using any parameters.
values you need to capture from textboxes and those need to be properly formatted before passing to database.

firoz.raj -3 Posting Pro in Training · Answer 4 · 2009-02-21T17:19:10+00:00

it is still not working here is the code what i have written.

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set con = New ADODB.Connection
        con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")
        con.BeginTrans
        con.Execute "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values(Supplier_id,Supplier_name,Fax_no,Contact_Person,Contact_no,Type,office_address,Emails,Website)"
        con.CommitTrans
        End If
       End Sub

debasisdas 580 Posting Genius Featured Poster · Answer 5 · 2009-02-21T18:11:36+00:00

you need to frame the insert statmnet dynamicaly by passing values

firoz.raj -3 Posting Pro in Training · Answer 6 · 2009-02-21T18:13:57+00:00

can you tell me friend how should i frame .i would remain grately
appreciated to you.

debasisdas 580 Posting Genius Featured Poster · Answer 7 · 2009-02-21T18:25:31+00:00

try the following sample

dim sqlstmt as string

sqlstmt = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Typ,Office_address,emails,website) values(" & text1.text & ",'" & text2.text & "'," & text3.text & ",'" & text4.text & "','" & text5.text & "','" & text6.text & "','" & text7.text & "','" & text8.text & "','" & text9.text & "')"

con.Execute sqlstmt

also better change the field name Type to something more meaning ful, becuase that is a keyword.

firoz.raj -3 Posting Pro in Training · Answer 8 · 2009-02-21T18:33:52+00:00

now i have written the code what you have told me.but
still getting Invalid sql statement.here is the code what
i have written.Kindly find the attachment also.

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set con = New ADODB.Connection
        con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")
        con.BeginTrans
    '   con.Execute "insert into Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values(Supplier_id,Supplier_name,Fax_no,Contact_Person,Contact_no,Type,office_address,Emails,Website)"
        SQL = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values(" & Text1.Text & ",'" & Text2.Text & "'," & Text3.Text & ",'" & Text4.Text & "','" & Text5.Text & "','" & Typ & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
        con.Execute sqlsql = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Typ,Office_address,emails,website) values(" & Text1.Text & ",'" & Text2.Text & "'," & Text3.Text & ",'" & Text4.Text & "','" & Text5.Text & "','" & Typ & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
        con.Execute SQL
        con.CommitTrans
        End If
       End Sub

debasisdas 580 Posting Genius Featured Poster · Answer 9 · 2009-02-21T18:37:06+00:00

Ensure to pass text into text field and number into number field. extract number from textbox and pass to query after converting to number. else you might have to make some changes in the insert statement.

debasisdas 580 Posting Genius Featured Poster · Answer 10 · 2009-02-21T18:42:41+00:00

try this

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set con = New ADODB.Connection
        con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb"
        con.BeginTrans
        SQL = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values(" & Text1.Text & ",'" & Text2.Text & "'," & Text3.Text & ",'" & Text4.Text & "','" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "','" & Text9.Text & "')"
       con.Execute SQL
      con.CommitTrans
    End If
End Sub

firoz.raj -3 Posting Pro in Training · Answer 11 · 2009-02-21T18:57:28+00:00

Now i am getting error no value given for one or more required parameter.here is the following code what i have written.

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set con = New ADODB.Connection
        con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")
        con.BeginTrans
        SQL = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values(" & Text1.Text & ",'" & Text2.Text & "'," & Text3.Text & ",'" & Text4.Text & "','" & Text5.Text & "','" & Typ & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
       [B]con.Execute SQL[/B]
      con.CommitTrans
    End If
    End Sub

debasisdas 580 Posting Genius Featured Poster · Answer 12 · 2009-02-21T19:05:54+00:00

check my previous post and correct accordingly

before executing the sql , check the same using msgbox.

firoz.raj -3 Posting Pro in Training · Answer 13 · 2009-02-21T19:21:09+00:00

firoz.raj -3 Posting Pro in Training

15 Years Ago

Now i am getting syntax error (missing operator in query expression). Kindly hep me .
ERROR.zip

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

ERROR.zip (27.05 KB)

debasisdas 580 Posting Genius Featured Poster · Answer 14 · 2009-02-21T21:43:19+00:00

avoid using ' (single quote) while passing values from textbox.

post the sql insert statment that you are getting.

firoz.raj -3 Posting Pro in Training · Answer 15 · 2009-02-21T21:54:41+00:00

right now i am getting error no of query value and destination fields
are not the same.when i see the value of sql stat. in immediate window i got

?sql
INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values('Engineering Service',' 45647897','FIROZ AHMED ',' rah ','9322620576','003 b/wing golden nest phase v miraroad east thane 4000107','','firoz.raj@gmail.com','www.yahoo.com','93226205476')

and here is the complete code

Private Sub Command2_Click()
If (CheckInput) Then
End If
Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
If Y = vbYes Then
Set con = New ADODB.Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\asfserver\itp$\product_tabletest.mdb")
con.BeginTrans
SQL = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values('" & Combo1.Text & "',' " & Text1.Text & "','" & Text2.Text & " ',' " & Text3.Text & " ','" & Text4.Text & "','" & Text5.Text & "','" & Typ & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
MsgBox (Text3)
con.Execute SQL
con.CommitTrans
End If
End Sub

ERROR.zip

debasisdas 580 Posting Genius Featured Poster · Answer 16 · 2009-02-21T22:03:00+00:00

debasisdas 580 Posting Genius

15 Years Ago

why you are passing 10 values for 9 field s ???

firoz.raj -3 Posting Pro in Training · Answer 17 · 2009-02-22T10:25:31+00:00

Now i got error datatype mismatch in criteria expression.here is the
following code what i have written.

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set con = New ADODB.Connection
        con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")
        con.BeginTrans
        SQL = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Type,Office_address,emails,website) values('" & Combo1.Text & "','" & Text1.Text & "','" & Text2.Text & " ','" & Text3.Text & "','" & Text4.Text & "','" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
        [B]con.Execute SQL[/B]
        con.CommitTrans
        MsgBox (Text3)
        End If
    End Sub

debasisdas 580 Posting Genius Featured Poster · Answer 18 · 2009-02-22T19:47:17+00:00

recheck your code

rename the type field to something else more meaningful.

pass the value in proper format as per database field type.

firoz.raj -3 Posting Pro in Training · Answer 19 · 2009-02-22T19:53:40+00:00

Now i got same error datatype mismatch in criteria expression.here
is the following code what i have written.

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set con = New ADODB.Connection
        con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")
        con.BeginTrans
     [B]   SQL = "INSERT INTO Supplier(Supplier_id,Supplier_name,Fax_no,Contact_person,Contact_no,Supp_Type,Office_address,Emails,website) values('" & Combo1.Text & "','" & Text1.Text & "','" & Text2.Text & "','" & Text3.Text & "','" & Text4.Text & "','" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"[/B]        con.Execute (SQL)
        con.CommitTrans
        MsgBox (Text3)
        End If
    End Sub

debasisdas 580 Posting Genius Featured Poster · Answer 20 · 2009-02-22T20:00:32+00:00

that simply means you need to pass data of proper type to insert statement.

firoz.raj -3 Posting Pro in Training · Answer 21 · 2009-02-22T20:04:15+00:00

firoz.raj -3 Posting Pro in Training

15 Years Ago

My friend i am sending my database as well as form7 i have tried
so many times but still able to insert information of supplier detail
in a table. Kindly find the attachment.

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

Product_tabletest.zip (117.03 KB)

debasisdas 580 Posting Genius Featured Poster · Answer 22 · 2009-02-22T20:10:52+00:00

check the query that is being framed at run time and try to run the same from backend directly.

debasisdas 580 Posting Genius Featured Poster · Answer 23 · 2009-02-22T20:13:50+00:00

ensure to pass number value only (not text) for the fields

Supplier_ID
Fax_No
Contact_No

use val(te3xtboxname)

firoz.raj -3 Posting Pro in Training · Answer 24 · 2009-02-22T21:08:52+00:00

still getting same error when i use val it shows 0 in immediate
window.here is the code what i have written.

Private Sub Command2_Click()
    If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set con = New ADODB.Connection
        con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")
        con.BeginTrans
'       SQL = "INSERT INTO Supplier(Supplier_ID,Supplier_Name,Fax_No,Contact_person,Contact_No,Supp_Type,Office_Address,Emails,website) values('" & Combo1.Text & "','" & Text1.Text & "','" & Text2.Text & "','" & Text3.Text & "','" & Text4.Text & "','" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
        SQL = "INSERT INTO Supplier(Supplier_ID, Supplier_name,Fax_No,Contact_person,Contact_No,Supp_Type,Office_Address,Emails,website) values(" & Val(Combo1.Text) & ",'" & Text1.Text & "'," & Val(Text2.Text) & ",'" & Text3.Text & "'," & Val(Text4.Text) & ",'" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
        con.Execute (SQL)
        con.CommitTrans
        MsgBox (Text3)
        End If
    End Sub

firoz.raj -3 Posting Pro in Training · Answer 25 · 2009-02-22T21:57:30+00:00

Now i am getting no data type error but getting datatype overflow
error here is the code what i have written.

Private Sub Command2_Click()
      If (CheckInput) Then
       End If
    Y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
    If Y = vbYes Then
        Set con = New ADODB.Connection
        con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\product_tabletest.mdb")
        con.BeginTrans
'       SQL = "INSERT INTO Supplier(Supplier_ID,Supplier_Name,Fax_No,Contact_person,Contact_No,Supp_Type,Office_Address,Emails,website) values('" & Combo1.Text & "','" & Text1.Text & "','" & Text2.Text & "','" & Text3.Text & "','" & Text4.Text & "','" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
'       SQL = "INSERT INTO Supplier(Supplier_ID, Supplier_name,Fax_No,Contact_person,Contact_No,Supp_Type,Office_Address,Emails,website) values(" & Val(Combo1.Text) & ",'" & Text1.Text & "'," & Val(Text2.Text) & ",'" & Text3.Text & "'," & Val(Text4.Text) & ",'" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"
'       con.Execute (SQL)
       SQL = "INSERT INTO Supplier(Supplier_name,Supplier_id,Contact_person,Contact_no,Supp_Type,Office_address,emails,website,Fax_no) values('" & Combo1.Text & "','" & Text1.Text & "','" & Text2.Text & " ','" & Text4.Text & "','" & Text3.Text & "','" & Text5.Text & "','" & Text6.Text & "','" & Text7.Text & "','" & Text8.Text & "')"

       MsgBox SQL

con.Execute SQL
con.CommitTrans
MsgBox (Text3)

        con.CommitTrans
        MsgBox (Text3)
        End If
    End Sub