 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / ASP.NET / General / July 2007ADD RECORDS
Using ASP.net 1.1 2 QUESTIONS: 1. Why do my write commands not work? 2. This file successfully makes changes in the database record. There surely is some simple code I could add that would *add* records to this database. Who can tell me? <%@ Page Language="VB" Debug="true" %> <%@ Import Namespace="System.Data.Oledb" %> <script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim DBConnection As OledbConnection DBConnection = New OledbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _ "Data Source=C:\Inetpub\wwwroot\app_data\Acc.mdb" ) DBConnection.Open() Dim DBCommand As OledbCommand DBCommand = New OledbCommand("SELECT * FROM table1, Acc") Dim SQLString AS String SQLString = "UPDATE Table1 SET TheEmpName='POOOOO' WHERE TheDate='6/14/07'" DBCommand = New OleDBCommand(SQLString, DBConnection) DBCommand.ExecuteNonquery() Dim DBReader AS OledbDatareader DBReader = DBCommand.ExecuteReader() While DBReader.Read() Response.Write(DBReader("TheDate")) Response.Write(DBReader("TheEmpName")) End while ' MyDataGrid.DataSource = DBReader 'MyDataGrid.DataBind() DBReader.Close() DBConnection.Close() End Sub </script> </head> <body> <form id="form1" runat="server"> <asp:DataGrid id="MyDataGrid" runat="server"/> </form> </body> </html> > Using ASP.net 1.1 > 2 QUESTIONS: > 1. Why do my write commands not work? > 2. This file successfully makes changes in the database record. There > surely is some simple code I could add that would *add* records to this > database. Who can tell me? Well, firstly if you're able to edit existing records, at least *some* of your write commands are working... :-) 1) What code are you actually using to add records to the database? I see an UPDATE SQL statement for updating existing records - where is your corresponding INSERT statement for adding new records...? 2) What errors are you getting back from your database?  SignatureMark Rae ASP.NET MVP http://www.markrae.net RE:> 1) What code are you actually using to add records to the database? I see an > UPDATE SQL statement for updating existing records - where is your > corresponding INSERT statement for adding new records...? I am not using any code to add records. That was my question: What code can I add to add records? RE: 2) What errors are you getting back from your database? None. My code does exactly what it's intended to do: updates a record according to a criteria. The only part that does not work are these two lines: Response.Write(DBReader("TheDate")) Response.Write(DBReader("TheEmpName")) That's what I meant by "why does it not write?" I wanted to SEE if it updates the record without having to look at the database, therefore I included those lines, but they do not "write" so that I can see the updated field. Thank you. >> Using ASP.net 1.1 >> 2 QUESTIONS: [quoted text clipped - 11 lines] > > 2) What errors are you getting back from your database? >> RE:> 1) What code are you actually using to add records to the database? >> I see an UPDATE SQL statement for updating existing records - where is >> your corresponding INSERT statement for adding new records...? > I am not using any code to add records. That was my question: What code > can I add to add records? Oh right... The SQL syntax to add a record into a table in a Jet database is: INSERT INTO Table (field1, Field2...) VALUES (Value1, Value2...) > RE: 2) What errors are you getting back from your database? > None. [quoted text clipped - 9 lines] > included those lines, but they do not "write" so that I can see the > updated field. You're issuing your UPDATE statement correctly but, if you want to see that it has worked, you will then need to issue a SELECT statement to fetch the record you've just updated back out of the database... SignatureMark Rae ASP.NET MVP http://www.markrae.net Thank you for helping me, Mark. The INSERT INTO code works great as long as I'm inserting literals. For example, SQLString = "INSERT INTO Table1(TheEmpName, TheDate, TheNotifyDate)VALUES(""Somebody"", ""10/10/98"", ""10/11/98"")" But I need to insert variables received form an input form. When I put the variable names in place of the literals like this: SQLString = "INSERT INTO Table1(TheEmpName, TheDate, TheNotifyDate)VALUES(EmpName, DateOfAccident, NotifyDate)" I get this error message: No value given for one or more required parameters What is the syntax for using variables as the values? >>> RE:> 1) What code are you actually using to add records to the database? >>> I see an UPDATE SQL statement for updating existing records - where is [quoted text clipped - 25 lines] > that it has worked, you will then need to issue a SELECT statement to > fetch the record you've just updated back out of the database... > Thank you for helping me, Mark. > [quoted text clipped - 11 lines] > > What is the syntax for using variables as the values? An SQL statement is a string like any other string, so you concatenate your variables in the usual way: SQLString = "INSERT INTO Table1(TheEmpName, TheDate, TheNotifyDate)VALUES('" + EmpName + "' etc... However, this is an extremely dangerous practice, especially in a web application, because of something called SQL Injection, but maybe that's another story for another day... SignatureMark Rae ASP.NET MVP http://www.markrae.net Hi Mark, Can you tell me why I get a new record, but with nothing in it, even though I get a form and fill it in and click submit? Here's the code: <%@ Page Language="VB" Debug="true" %> <%@ Import Namespace="System.Data.Oledb" %> <script language= "VB" runat="server"> 'Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Sub btnSendDatabase_OnClick(Source As Object, E As EventArgs) Dim DBConnection As OledbConnection DBConnection = New OledbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _ "Data Source=C:\Inetpub\wwwroot\Acc.mdb" ) DBConnection.Open() Dim DBCommand As OledbCommand DBCommand = New OledbCommand("SELECT * FROM table1, Acc") Dim SQLString AS String Dim EmpName as String Dim DateOfAccident as string Dim NotifyDate as string SQLString = "INSERT INTO Table1(TheEmpName, TheDate, TheNotifyDate)VALUES('"+EmpName+"','"+DateOfAccident+"','"+NotifyDate+"')" DBCommand = New OleDBCommand(SQLString, DBConnection) DBCommand.ExecuteNonquery() DBConnection.Close() End Sub </script> </head> <body> <form id="form1" runat="server"> Employee's Name: <asp:textbox id="EmpName" runat=server columns="45"/> <asp:textbox id="DateofAccident" runat=server /></asp:textbox> <font face="Verdana" Size="2">Date Employer Notified <asp:textbox id="Notifydate" runat=server/> <asp:Button id="btnSendDatabase" text="Submit" OnClick="btnSendDatabase_OnClick" runat="server" /> </form> </body> </html> > Can you tell me why I get a new record, but with nothing in it, even > though I get a form and fill it in and click submit? A couple of things... Firstly: > Dim DBCommand As OledbCommand > DBCommand = New OledbCommand("SELECT * FROM table1, Acc") You're instantiating an OleDbCommand object, but never actually using it before re-instantiating it further down in the code... Secondly: > Dim SQLString AS String > Dim EmpName as String [quoted text clipped - 6 lines] > DBCommand.ExecuteNonquery() > DBConnection.Close() You're not actually populating the three data variables - therefore, you're inserting a record into your table where all the fields are blank... Dim EmpName as String = EmpName.Text Dim DateOfAccident as string = DateOfAccident.Text Dim NotifyDate as string = NotifyDate.Text SignatureMark Rae ASP.NET MVP http://www.markrae.net > Dim EmpName as String = EmpName.Text > Dim DateOfAccident as string = DateOfAccident.Text > Dim NotifyDate as string = NotifyDate.Text Actually, that might cause errors because the string variables have the same names as the webcontrols... Dim SQLString AS String Dim strEmpName As String = EmpName.Text Dim strDateOfAccident As String = DateOfAccident.Text Dim strNotifyDate As String = NotifyDate.Text SQLString = "INSERT INTO Table1(TheEmpName, TheDate, TheNotifyDate)VALUES('"+ strEmpName+"','"+ strDateOfAccident+"','"+ strNotifyDate+"')" SignatureMark Rae ASP.NET MVP http://www.markrae.net Yes, I got an error, so I put Dim TheEmpName as String = EmpName.Text, etc., which makes the names in the table the same as the variable names. It works, but will it be a problem? Thanks >> Dim EmpName as String = EmpName.Text >> Dim DateOfAccident as string = DateOfAccident.Text [quoted text clipped - 11 lines] > TheNotifyDate)VALUES('"+ strEmpName+"','"+ strDateOfAccident+"','"+ > strNotifyDate+"')" > Yes, I got an error, so I put Dim TheEmpName as String = EmpName.Text, > etc., which makes the names in the table the same as the variable names. > It works, but will it be a problem? No. SignatureMark Rae ASP.NET MVP http://www.markrae.net ok It's working now. Thanks Now - other question - I have 47 fields. Do I have to code '"+field1+"', etc. 47 TIMES?!! Is there not a way to say INSERT INTO Table 1 [all] VALUE [all] in some form? SURELY there is a way. Also, I have seen With Cmd Parameters .Add(New OleDbParameter("@field1", frmfield1.text)) etc. What is the difference in that and INSERT? Thank you. >> Can you tell me why I get a new record, but with nothing in it, even >> though I get a form and fill it in and click submit? [quoted text clipped - 27 lines] > Dim DateOfAccident as string = DateOfAccident.Text > Dim NotifyDate as string = NotifyDate.Text > Now - other question - I have 47 fields. Do I have to code '"+field1+"', > etc. 47 TIMES?!! Yes. > Is there not a way to say INSERT INTO Table 1 [all] VALUE [all] in some > form? No. > SURELY there is a way. You could play about with generics and dictionaries and goodness knows what but, at the end of the day, you can't really get away from the fact that you've to send your database a piece of SQL, so you may as well just build it... > Also, I have seen > With Cmd Parameters > .Add(New OleDbParameter("@field1", > frmfield1.text)) > etc. > What is the difference in that and INSERT? That's parameterisation - a much safer way of constructing database writes which helps to eliminate SQL Injection. You would be well advised to adopt this method. SignatureMark Rae ASP.NET MVP http://www.markrae.net RE: Cmd parameters Oh I would like to try that. But every time I try I get all kinds of errors. What does the @ mean? Do you have to declare @something or does the compiler understand the @ as something special? ("@something" , frmsomething.text)) Could you define the above? By injection you mean the user adding an ' in the data? Thank you very much. >> Now - other question - I have 47 fields. Do I have to code '"+field1+"', >> etc. 47 TIMES?!! [quoted text clipped - 23 lines] > which helps to eliminate SQL Injection. You would be well advised to adopt > this method. > RE: Cmd parameters Oh I would like to try that. But every time I try I > get all kinds of errors. http://www.java2s.com/Code/VB/Database-ADO.net/AddOleDbCommandParameter.htm > By injection you mean the user adding an ' in the data? No I don't - I mean this: http://www.google.co.uk/search?sourceid=navclient&hl=en-GB&ie=UTF-8&rlz=1T4GGIH_ en-GBGB220GB220&q=%22SQL+Injection%22 SignatureMark Rae ASP.NET MVP http://www.markrae.net Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.