I made a code to save data from TextBox to Access 2003 Database . When compiling there is a lot of errors related to DataSet like "Identifier expected","> expected", "declaration expected  " etc...
My code for Saving is :

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim objConnection As OleDb.OleDbConnection
        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\Pfectrol\My Documents\Visual Studio 2008\db\CoolMate.mdb;"
        Try
            Dim strSQL As String = "INSERT INTO Equipment ([Port Number], [Display Name], Equipment, Tonnage, [User Privilage]) VALUES (trim(txtPort.Text), trim(txtDisplay.Text), trim(txtCA.Text), trim(txtTonnage), trim(cmbUser.Text));"
            Dim OleDbCommand As New OleDb.OleDbCommand(strSQL)
            objConnection = New OleDb.OleDbConnection(strConnection)
            OleDbCommand.Connection = objConnection
            objConnection.Open()
            OleDbCommand.ExecuteNonQuery()
            objConnection.Close()

        Catch ex As Exception
            Trace.WriteLine(ex.ToString)
        End Try
        OleDbCommand.Dispose()
        objConnection = Nothing
        objConnection.Dispose()
        objConnection = Nothing
    End Sub


I dont no what is the problem

Any one Help me Please  ......
Thanks in Advance .....


____________________________
from "god's own country"

Your SQL string is not properly constructed. You have to insert the values from your textboxes into the string so that you pass the actual value - not the reference to the source of the value. Also, if the value expected by the database is a String then it should be enclosed in single quotes ( the ' character) so assuming that [Port Number] and [Tonnage] are numeric values and all the others are strings it should read :-

Dim strSQL As String = "INSERT INTO Equipment ([Port Number], [Display Name], Equipment, Tonnage, [User Privilage]) VALUES (" & trim(txtPort.Text) & ", '" & trim(txtDisplay.Text) & "', '" & trim(txtCA.Text) & "', " & trim(txtTonnage) & ", '" &  trim(cmbUser.Text) & "');"


[Edited by GeoffS on 08-01-2010 at 01:12 PM GMT]

____________________________
multi-tasking - the ability to hang more than one app. at the same time.

quote:

---

tashaheer wrote:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

---

Well if you want to get sniffy about it Keith then technically it should be in the "Database" forum, but does it really matter? The most important thing is that we try to help tashaheer with his problem - not criticise him for putting it in the wrong forum. Such an unfriendly approach is not going to help this site attract more users, and without the users then we lose the opportunity to help each other and to benefit from the exchange of knowledge between us all.



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

quote:

---

GeoffS wrote:
Well if you want to get sniffy about it Keith then technically it should be in the "Database"

---

Dear GeoffS
I got your code. But still problems there
I rewrote SQL as per your direction Now its like
INSERT INTO Equipment
                      (port_number, display_name, equipment, tonnage, user_privilage)
VALUES     ('&trim(txtPort.Text)&', '"&trim(txtDisplay.Text)&"', '"&trim(txtCA.Text)&"', '&trim(txtTonnage.Text)&', '"&trim(cmbUser.Text)&"')
and Error message is "Data type mismatch in Criteria expression".
data type of port_number and tonnage are number(long integer) and others are Memo
I am using VS2008 and MS ACCESS 2003
I am SORRY I know nothing about database so you have to hard work to help me    

____________________________
from "god's own country"

Some problem I found is like this.
My code is:
INSERT INTO Equipment (port_number,display_name,equipment,tonnage,user_privilage) VALUES ("&txtPort.Text&",'"&txtDisplay.Text&"','"&txtCA.Text &"',"&txtTonnage.Text&",'"&cmbUser.Text&"')
but when execute query button pressed then it will be
INSERT INTO Equipment
                      (port_number, display_name, equipment, tonnage, user_privilage)
VALUES     ('&txtPort.Text&', '"&txtDisplay.Text&"', '"&txtCA.Text &"', '&txtTonnage.Text&', '"&cmbUser.Text&"')
What it means
and Error message is "Data type mismatch in Criteria expression".    

____________________________
from "god's own country"

Your SQL Statement is still not properly constructed.
You need to be more carefull in typing out the statement. Everything that you surround in the double quote character ( " ) will be passed to the database exactly. The single quote character ( ' ) within your string will be interpreted by the database as surrounding a String Data TYpe - so if you were to pass a persons name that contains the single quote as an apostrophe (eg the name  O'Niell) then the database engine will stop reading the string after the letter O - since the word Niell would not correspond to a field or a SQL Command the database engine would terminate the action and throw an exception.
The Comma character ( , ) is used to seperate fields. So if this is passed in a String input by your users it can cause complications if it is not properly surrounded by the String identifier ( ' )
For these reason you should always examine any text input by your users before placing it into your SQL String.
You also need to take care with inserting spaces in your SQL String - for example txtPort.Text& - the ampersand character ( & ) is used to concatenate strings (join together the actual text that you want to pass like "INSERT" with the value of a variable like txtPort.Text to create one string of text) but it is also used by VB as a Type Identifier for the Long Data Type, so by placing it immediately after the value of txtPort.Text that value is treated as a Long Number instead of a String.
Your use of the Trim Function is OK if you think that the user input is likely to contain superfluous spaces at the beginning and end of the string, in the main this is not likely to be the case, but using it is not a bad habit to get into. The trim function is usually only really needed when handling fixed length strings returned from functions that will pad the end of the string with spaces if the return string is shorter than the fixed length.
When passing values to a database you must ensure that they are of the type expected. Generally speaking the database can do a certain amount of thinking for itself in deciding what you have sent it, but to ensure that the type of value passed to the  database is exactly what it expects you can use VB functions to "coerce" the data into a particular type - CLng for example will take any numeric value that you pass to it and convert it to a whole number of the Long data type. So if we include type conversion functions in your SQL String then we know we are sending the right type of data.
Your string should then read :-

Dim strSQL As String = "INSERT INTO Equipment (port_number,display_name,equipment,tonnage,user_privilage) VALUES (" & Clng(txtPort.Text) & ", '" & CStr(txtDisplay.Text) & "', '" & CStr(txtCA.Text) & "', " & CLng(txtTonnage.Text) & ", '" & CStr(cmbUser.Text) & "')"

To check that it all goes together correctly you can either output the constructed SQL String to the immediate window by placing :-
Debug.Print strSQL
immediatly after the Dim strSQL As String = "INSERT .... line of code
OR pop it up in a Message Box by inserting:- MsgBox strSQL

One final point. When designing a database think carefully about the data type that you select for each field. The database will reserve space based upon your selection, and since a small database will work faster than a large one it is best to select the smallest data type. For example, you are using the "Memo" data type to store your String values. This type allows for the saving of 65,535 characters. I am sure that none of your fields will ever need to hold that much data, so it would be better to set the data type to "Text" - this will allow a maximum of 255 characters to be saved, you must set a value in the "Field Size" at the bottom part of your table designer in Access - this can be anything up to the 255 so if you are certain that the input is never going to be more than 20 characters then you could enter 20 and keep your database really small - but beware, if you pass 21 characters to the database then an error will arise, so add a bit extra and set it to 30. The same goes with numbers, if you are only ever going to store small whole numbers use Integer rather than Long - but again beware, if you pass a number greater than 32,767 then an error will be raised, so use Long if you are storing things like Order Numbers - it may be a long time before your user gets to Order 32,768 but one day he will and then your program will stop working. The "Currency" data type in Access is very useful, it allows you to store decimal numbers with up to 4 decimal places, much better than using Single or Double types where you do not need any more decimal precision. The number does not actually have to be representing money, any number can be stored using "Currency" data type - it is only the "Currency" Format type that places a Currency sign (e.g. £ or $) in front of the number for display purposes.



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thank you very much for your regards - I think you take time to write all this answer-
I will check it and write to you when it work      

____________________________
from "god's own country"

Dear GeoffS
I already told you that  suggested code:
Dim strSQL As String = "INSERT INTO Equipment (port_number,display_name,equipment,tonnage,user_privilage) VALUES (" & Clng(txtPort.Text) & ", '" & CStr(txtDisplay.Text) & "', '" & CStr(txtCA.Text) & "', " & CLng(txtTonnage.Text) & ", '" & CStr(cmbUser.Text) & "')"
is become after runquery to
INSERT INTO Equipment
                      (port_number, display_name, equipment, tonnage, user_privilage)
VALUES     (' & Clng(txtPort.[Text]) & ', '" & CStr(txtDisplay.Text) & "', '" & CStr(txtCA.Text) & "', ' & CLng(txtTonnage.[Text]) & ', '" & CStr(cmbUser.Text) & "')
so I made instead of (") to (' " ') for " & Clng(txtPort.[Text]) &"
is it ok ?
When run the query still there is an error which is:
" No value given for one or more required parameters "
Please help me
Anyway again thank you for your efforts and I expecting it further
  

____________________________
from "god's own country"

I'm not quite sure what you mean when you say that variable strSQL "is become after runquery to .."
If you mean that you did as I suggested and output the variable to the immediate window using Debug Print, then if it is showing the textbox names in the string you are still not typing it out like I suggested. If you were typing it out acurately you should see the actual values of the textboxes, not their names. So it would look like :-

INSERT INTO Equipment (port_number,display_name,equipment,tonnage,user_privilage) VALUES (81,'my display name', 'my equipment name', 200, 'my privilage')

Check again the construction of your SQL String, and check that the textboxes contain valid information.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Now all are working and there is no error message!!
But sql command itself is saving on db i.e;
' & txtPort.Text & ' in port_number field and so on I want to insert the data typed in text box is to saved in db Can you Help me ?  

____________________________
from "god's own country"

You obviously still have errors in your SQL String.
Read my previous posts about how to see what the resulting SQL String looks like before you try sending it to the database, and take more care with your typing - you only need a " or a ' or a , or a space in the wrong place and it will completely change the resulting string.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.