insert values into access db via asp.net

[Matar]

hi i am trying to insert a values into access database by asp.net the user will fill the field and when click add it will keep in the db but there are some errors in insert values this is the script <script runat="server"> Sub ins(ByVal obj As Object, ByVal e As EventArgs) Dim dbconn, sql, dbcomm, dbread dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("db1.mdb")) dbconn.Open() sql = "INSERT INTO vic (name, section,age,address) VALUE('name.text', 'section.text','age.text','address.text')" dbcomm = New OleDbCommand(sql, dbconn) dbread = dbcomm.ExecuteReader() vic.DataSource = dbread vic.DataBind() dbread.Close() dbconn.Close()end sub any one can slove this proplem ?

[aspnetguy]

by using VALUES('name.text' ... you are actually inserting the string name.text...you get the value from a textbox called name then do thisVALUES('" & name.Text & "',....

[Matar]

hi firstly this my code *********Dim dbconn, sql, dbread, dbcomm dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("db1.mdb")) dbconn.Open() sql = "INSERT INTO vic (user,pass,name,section,address) VALUES('" & use.Text & "','" & pass.Text & "','" & name.Text & "','" & sec.Text & "','" & adr.Text & "')" dbread = dbcomm.ExecuteReader() vic.DataSource = dbread vic.DataBind() dbread.Close() dbconn.Close()*****its still problem ,, Insert syatex error !!!!!!!***so can you slove this problem please .

[jesh]

I'm not a VB person, but it looks like you are missing something in your code there. Where are you instantiating dbcomm? You declare it in the first line, but you never instantiate it:

Dim dbconn, sql, dbread, dbcommdbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("db1.mdb"))dbconn.Open()sql = "INSERT INTO vic (user,pass,name,section,address) VALUES('" & use.Text & "','" & pass.Text & "','" & name.Text & "','" & sec.Text & "','" & adr.Text & "')"// You seem to be missing something like:dbcomm = New OleDbCommand(sql);dbcomm.Connection = dbconn;// Also, I don't think you want to run a reader on an INSERT://dbread = dbcomm.ExecuteReader()//vic.DataSource = dbread//vic.DataBind()//dbread.Close()// ----------------------------------dbcomm.ExecuteNonQuery();dbconn.Close()

[Matar]

hi thank u i miss this line dbcomm.Connection = dbconn;but i put and the script still have a proplem i donoo what is the proplem ?

[aspnetguy]

what is the full SQL error that you get?Are any of the database fields anything other than string (varchar)?

[jesh]

thank u i miss this line dbcomm.Connection = dbconn;

Did you also add this?

dbcomm = New OleDbCommand(sql);

[aspnetguy]

The Command must be working or he would get an SqlCommand error not an SQL Insert synatx error.

[jesh]

The Command must be working or he would get an SqlCommand error not an SQL Insert synatx error.

Right you are!I wonder if he also changed it from dbcomm.ExecuteReader() to dbcomm.ExecuteNonQuery()? Would a syntax error occur if you attempted to perform an INSERT with a DataReader?At any rate, it's hard to tell unless he posts his actual SQL error.

[Matar]

hi this is my code ******Sub ins(ByVal obj As Object, ByVal e As EventArgs) Dim dbconn, sql, dbcomm dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("db1.mdb")) dbconn.Open() sql = "INSERT INTO vic (user,pass,name,section,address) VALUES('" & use.Text & "','" & pass.Text & "','" & name.Text & "','" & sec.Text & "','" & adr.Text & "')" dbcomm = New OleDbCommand(sql, dbconn) dbcomm.ExecuteNonQuery() dbconn.Close()******this is the error : Syntax error in INSERT INTO statement********what is that ?

[Matar]

hi i solve this error the error was because of a resrved word in the sql command this word is resreved in the ado and they are ( name , section ,user)so to a void this duplication we must put the field name between []so the code will be like that ***Sub ins(ByVal obj As Object, ByVal e As EventArgs) Dim dbconn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("db1.mdb")) Dim cmd As New System.Data.OleDb.OleDbCommand cmd.CommandType = System.Data.CommandType.Text cmd.CommandText = "INSERT INTO vic([username],[pass],[firstname],[section],[address]) VALUES('" & username.Text & "','" & pass.Text & "','" & firstname.Text & "','" & sec.Text & "','" & adr.Text & "')" cmd.Connection = dbconn dbconn.Open() cmd.ExecuteNonQuery() dbconn.Close()****its work very will thank you frinds*****but know i am trying to make a delete button this is the code *****Sub del(ByVal obj As Object, ByVal e As EventArgs) Dim dbconn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("db1.mdb")) Dim cmd As New System.Data.OleDb.OleDbCommand cmd.CommandType = System.Data.CommandType.Text cmd.CommandText = "DELETE * FROM vic where [username] = '"&username.text&"'" cmd.Connection = dbconn dbconn.Open() cmd.ExecuteNonQuery() dbconn.Close()******in the runtime its add a recored not delete what going on ?

[aspnetguy]

lol resered words would do it! Glad it is sorted out

[pulpfiction]

About your DELETE command not working, Error in the SQL statement.[Dont use * in DELETE statement]Try this"DELETE FROM vic where [username] = '"&username.text&"'"General:DELETE FROM table_nameWHERE column_name = some_value

[Matar]

hi pulpfiction its long time ,,, i do it like your comment and still errors ****i will i ask you if you know about asp.net datagrid if you know plz post me a replay ...****best wishesMaTaR

[aspnetguy]

what do you want to know about datagrids?

[pulpfiction]

About DELETE statement, to check printout the line and see if its correct.Response.Write("DELETE FROM vic where [username] = '"&username.text&"'")I have some idea abt datagrid, whats that u wanna know??

[Matar]

hi i want to know how to display a data thats stored in access database wiht editing control add,delete,updatedo u have any idea about that ??thx

[aspnetguy]

here is everything you'll ever need to know about datagrids http://aspnet.4guysfromrolla.com/articles/122602-1.2.aspx