help!!! its about saving data using codes
-
in saving data in my module, instead of using connection wizard, i use this sql codes:
Dim str As String dbconnect = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Rara's project\grading.mdb") dbconnect.Open() str = "select count(*) from students where studname = '" & txtname.Text & "'" dbcommand = New OleDbCommand(str, dbconnect) If dbcommand.ExecuteScalar <> 0 Then MessageBox.Show("Already exists.", "Result") cleartxt() Else str = "Insert into students(studname, year, course) values ('" & txtname.Text & "', " & "'" & cmbyear.Text & "', " & "'" & cmbcourse.Text & "')" dbcommand = New OleDbCommand(str, dbconnect) **dbcommand.ExecuteNonQuery()** MessageBox.Show("Record Added.", "Result") dbconnect.Close() cleartxt() End Ifbut i have an error in the part there -"dbcommand.ExecuteNonQuery()" a dialog box appear and it says: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll i guess the error there is the combobox that i used? because when i try to erase the "cmbcourse.text" and "cmbyear.text", it runs..but when i put it, there's an error.... how can i fix it??? please, help me!!! -
in saving data in my module, instead of using connection wizard, i use this sql codes:
Dim str As String dbconnect = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Rara's project\grading.mdb") dbconnect.Open() str = "select count(*) from students where studname = '" & txtname.Text & "'" dbcommand = New OleDbCommand(str, dbconnect) If dbcommand.ExecuteScalar <> 0 Then MessageBox.Show("Already exists.", "Result") cleartxt() Else str = "Insert into students(studname, year, course) values ('" & txtname.Text & "', " & "'" & cmbyear.Text & "', " & "'" & cmbcourse.Text & "')" dbcommand = New OleDbCommand(str, dbconnect) **dbcommand.ExecuteNonQuery()** MessageBox.Show("Record Added.", "Result") dbconnect.Close() cleartxt() End Ifbut i have an error in the part there -"dbcommand.ExecuteNonQuery()" a dialog box appear and it says: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll i guess the error there is the combobox that i used? because when i try to erase the "cmbcourse.text" and "cmbyear.text", it runs..but when i put it, there's an error.... how can i fix it??? please, help me!!!Is the year filed in your database a text field, or a nyumeric one? If the latter, you need to remove the apostrophes around it in your sql statement. If any of your courses have apostrophes in them, this too will break the code - use the replace function to replace all occurrences of apostrophes with double-apostrophes.
-
Is the year filed in your database a text field, or a nyumeric one? If the latter, you need to remove the apostrophes around it in your sql statement. If any of your courses have apostrophes in them, this too will break the code - use the replace function to replace all occurrences of apostrophes with double-apostrophes.
all my database field are text.... the apostrophes that are you saying are in my codes? So i need to remove it? This is my code before:
str = "Insert into students(studname, year, course) values ('" & txtname.Text & "', " & "'" & txtyear.Text & "', " & "'" & txtcourse.Text & "')"how can i remove it? Like this?str = "Insert into students(studname, year, course) values ('" & txtname.Text & "', " & " " & txtyear.Text & ", " & " " & txtcourse.Text & ")"but when i do it, it is still dont running? Can u please write the codes for that, so i can know which one is to be removed? -
all my database field are text.... the apostrophes that are you saying are in my codes? So i need to remove it? This is my code before:
str = "Insert into students(studname, year, course) values ('" & txtname.Text & "', " & "'" & txtyear.Text & "', " & "'" & txtcourse.Text & "')"how can i remove it? Like this?str = "Insert into students(studname, year, course) values ('" & txtname.Text & "', " & " " & txtyear.Text & ", " & " " & txtcourse.Text & ")"but when i do it, it is still dont running? Can u please write the codes for that, so i can know which one is to be removed?No - your original SQL statement is correct in as far as it goes, but will crash if any of your text inputs include apostrophes. Because SQL uses apostrophes to mark the start and end of text fields, another one occuring within a field causes problems. To get round this, SQL accepts two consecutive apostrophes as marking not the end of the text field but an apostrophy within it - thus if your txtcourse.text = "smith's" the SQL statemt would fail, "smith''s" would be accepted. To take acount for this in all cases, simply use the replace method to replace all apostrpohes within text fields with double apostrophes - thus: str = "Insert into students (studname, year, course) values ('" & txtname.Text.Replace("'", "''") & "', '" & txtyear.Text.Replace("'", "''") & "', '" & txtcourse.Text & "')" cheers Fred