How to insert all characters in database

Beiniam

hello guys i have doing some project and since i have new for vb.net 2010. my project is coded in vb.net 2010 and ms access 2007 mdb database. i have DocId, description, date and other fileds in the table.. my querry works for all but. i can't add all characters except integers in the database column DocId. it accepts only integer values. so how can i insert characters in the DocId filed. here is the detail tables: DocId======Textbox name RefferencNoTxt.Text Table name = EraDms database name= data

cmd.CommandText = "INSERT INTO EraDms(DocId,Description,Address,NoOfPages, SendOrReceived, SentDate,ReceivedDate,ForwardedWorkingUnit,Attachements,ForwardedDate,ReceivedBy,DocPath) " & _
" VALUES(" & Me.RefferencNoTxt.Text.ToString & ",'" & Me.DescriptionTextBox.Text & "','" & Me.AddressTextBox.Text & "','" & Me.NoOfPagesTextBox.Text & "','" & Me.SendOrReceievedComboBox.Text & "','" & _
Me.SentDateTextBox.Text & "','" & Me.receivedDate.Text & "','" & Me.ForwardedWorkingUnitTextBox.Text & "','" & Me.AttachementsTextBox.Text & "','" & Me.TextBox6.Text & "','" & Me.ReceivedByTextBox.Text & "','" & Me.DocPathTextBox.Text & "')"
cmd.ExecuteNonQuery()
MessageBox.Show("File Upload Successfull", "ERA-DMS-File Uploader", MessageBoxButtons.OK, MessageBoxIcon.Information)
RefreshData()

       '  With ERA\_DMS\_HOME

       ' End With
       cnn.Close()
   Else
       'update data in table
       cmd.CommandText = "UPDATE Eradms " & \_
                   " SET DocId=" & Me.RefferencNoTxt.Text.ToString & \_
                   ", Description='" & Me.DescriptionTextBox.Text & "'" & \_
                   ", Address='" & Me.AddressTextBox.Text & "'" & \_
                   ", NoOfPages='" & Me.NoOfPagesTextBox.Text & "'" & \_
                   ", SendOrReceived='" & Me.SendOrReceievedComboBox.Text & "'" & \_

Bernhard Hiller

Change the definition of DocID from Integer to Text in the database.

Beiniam

DocId data type is text in my database

Lost User

What is the actual definition for DocId in your database schema? Also why are you calling ToString on the Me.RefferencNoTxt.Text object; is it not already text?

Use the best guess

Simon_Whale

Beiniam wrote:

i can't add all characters except integers in the database column DocId. it accepts only integer values. so how can i insert characters in the DocId filed.

From that it makes me ask the question is the column type numeric? (integer, decimal etc.) you will need to convert RefferencNoTxt.Text to the correct numeric type that your database needs. i.e.

dim value as integer = Convert.ToInt32(RefferencNoTxt.Text)

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

Richard Deeming

Your code is a classic example of SQL Injection[^]. One mis-placed ' in a text box, and your entire database could be corrupted. Change the code to use parametereized queries instead:

cmd.CommandText = _
"INSERT INTO EraDms(DocId, Description, Address, NoOfPages, SendOrReceived, SentDate, ReceivedDate, ForwardedWorkingUnit, Attachements, ForwardedDate, ReceivedBy, DocPath) " & _
" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

' Parameter names don't matter; OleDbCommand uses positional parameters:
cmd.Parameters.AddWithValue("@p0", Me.RefferencNoTxt.Text)
cmd.Parameters.AddWithValue("@p1", Me.DescriptionTextBox.Text)
cmd.Parameters.AddWithValue("@p2", Me.AddressTextBox.Text)
cmd.Parameters.AddWithValue("@p3", Me.NoOfPagesTextBox.Text)
cmd.Parameters.AddWithValue("@p4", Me.SendOrReceievedComboBox.Text)
cmd.Parameters.AddWithValue("@p5", Me.SentDateTextBox.Text)
cmd.Parameters.AddWithValue("@p6", Me.receivedDate.Text)
cmd.Parameters.AddWithValue("@p7", Me.ForwardedWorkingUnitTextBox.Text)
cmd.Parameters.AddWithValue("@p8", Me.AttachementsTextBox.Text)
cmd.Parameters.AddWithValue("@p9", Me.TextBox6.Text)
cmd.Parameters.AddWithValue("@p10", Me.ReceivedByTextBox.Text)
cmd.Parameters.AddWithValue("@p11", Me.DocPathTextBox.Text)

And:

cmd.CommandText = _
"UPDATE Eradms " & _
" SET Description = ?, " & _
" Address = ?, " & _
" NoOfPages = ?, " & _
" SendOrReceived = ?, " & _
" SentDate = ?, " & _
" ReceivedDate = ?, " & _
" ForwardedWorkingUnit = ?, " & _
" Attachements = ?, " & _
" ForwardedDate = ?, " & _
" ReceivedBy = ?, " & _
" DocPath = ? " & _
" WHERE DocId = ?"

' Parameter names don't matter; OleDbCommand uses positional parameters:
cmd.Parameters.AddWithValue("@p0", Me.DescriptionTextBox.Text)
cmd.Parameters.AddWithValue("@p1", Me.AddressTextBox.Text)
cmd.Parameters.AddWithValue("@p2", Me.NoOfPagesTextBox.Text)
cmd.Parameters.AddWithValue("@p3", Me

Beiniam

thank you a lot

Richard Deeming

eming for your help. it works