Please Help Me, I Am to Be Fired!

[vbgaya]

What I ask can be very familiar to most of you. So, please don't point me a URL in MSDN, I can't understand the stuff there. Instead please add the "lacking" codes and help me to complete the code.I need to store First Name and Last Name of userswhich come as user inputs through text boxes in a web form to an existing data table as a new data row.I used DataSets. I heard it is bulky. Is there any otherway of achieving it? I have included the databaseconnection in a method that is called for the clickevent of the submit button. Instead, Should I includeit in the Page load event? I also want to know weather there is a way to give relative paths for database connection instead of an absolute one which won't be practical when I move the stuff to a web server.Below I have included the complete code even withHTML. The error I get is "Update requires a valid InsertCommand when passed DataRow collection with new rows"It occurs due to the statement,objCmd.Update(ds, "contacts")Please help me to complete the code. I am a trainee and this is a practice project thatI should submit. I couldn't complete it for2 weeks. I made forum posts and got MSDNlinks. Please don't do that. Just tell me what code to where I should add. I really appreciate yourhelp. Here is the code,sub Add(obj as object, e as EventArgs)dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\users\database\users.mdb")objConn.Open()dim objCmd as new OleDbDataAdapter("select * from contacts", objConn)dim ds as DataSet = new DataSet()objCmd.Fill(ds, "contacts")dim dr as DataRow = ds.Tables("contacts").NewRow()dr(1)=txtFirstName.textdr(2)=txtLastName.Textds.Tables("contacts").Rows.Add(dr)objCmd.Update(ds, "contacts")objConn.Close()end sub First Name Last Name

[hollystyles]

You don't need all those DataSets and DataRows for a simple insert to your database. This looks very long but that's because I have left your code in but commented it out, so you can compare the differences. I have tried to explain with comments all the details. I am a C# programmer so my VB may not be perfect and you might have to fix some of the syntax. If you do make changes please post them back here for me so I can see them.

sub Add(obj as object, e as EventArgs)
	'dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\users\database\users.mdb")
         'Use Server.MapPath function to get the physical path to the .mdb database file
         dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/users/database/users.mdb"))
         objConn.Open()
         
         'dim objCmd as new OleDbDataAdapter("select * from contacts", objConn)
         'Create an SQL insert statement I don't know what the column names should be so I have guessed
         dim sql as String
         sql = "insert into contacts (firstname, lastname) values('" & txtFirstName.text & "', '" & txtLastName.Text & "')"
         
         'Create an adapter
         dim objAdapter as new OleDbDataAdapter()  
         
         'set the adapters insert command with your SQL statement and connection object
         objAdapter.InsertCommand = new OleDbCommand(sql, objCon)
         
	'dim ds as DataSet = new DataSet()objCmd.Fill(ds, "contacts")
         
         'dim dr as DataRow = ds.Tables("contacts").NewRow()dr(1)=txtFirstName.textdr(2)=
         
         'ds.Tables("contacts").Rows.Add(dr)
         
         'objCmd.Update(ds, "contacts")  
         
         'prepare a variable for the result of the databse transaction this could be used for error handling      
         dim rowsAffected as integer
         
         'Execute the SQL statement, we would expect rowsAffected to be 1 for one record inserted
         rowsAffected = objAdapter.InsertCommand.ExecuteNonQuery()
         
         'dispose of your objects, yes there is a garbage collecter but that's no excuse to be lazy
         objAdapter.Dispose()
         objConn.Close()
         objConn.Dispose()
end sub

[vbgaya]

Hi hollystyles,Thanks a lot. I will work on the code and let you know the progress.Thanks again!vbgaya.

[vbgaya]

Dear hollystyles,I adapted the code and executed. An error occurredafter clicking the submit button at the statement,rowsAffected = objAdapter.InsertCommand.ExecuteNonQuery()Error was,"Syntax error in INSERT INTO statement"Then I commented the rowsAffected variable declarationand above statement. After entering data and clicking submitbutton nothing happened and no error came.When I checked the database the data hadn't beenincluded. What can be the error? I expect your kindassistance.Thank you.vbgaya.

[hollystyles]

Well it doesn't like the SQl insert statement, please post your modified code in code tags so I can look at it.

What are the column names in the database for first name and second name?

What other colmns are there ? do they allow null values or do we need to supply values for those too ?

Hmm access database it maybe we just need a semi-colon on the end of the SQL statement replace it with this

sql = "insert into contacts (firstname, lastname) values('" & txtFirstName.text & "', '" & txtLastName.Text & "');"

[vbgaya]

What are the column names in the database for first name and second name?

What other colmns are there ? do they allow null values or do we need to supply values for those too ?

Hi hollystyles,

I changed the column names in the database so that they match with
your ones. But it still has a username field (Data type is text) and a
ID (Autonumber and the Primary key).

When trying your code I didn't fill the username field because it
should automatically come after a login (I haven't created it and therefore
I left it). I think ID field is no problem. This username field is the problem.
As you have asked, how can I configure it to get null values?

After your great help, I came home for the weekend. At the moment I
haven't installed IIS in my home machine. So, I didn't try your statement
with the semicolon. I am sure it's not the case because in the samples I
have looked in reference books there is no semicolon after SQL commands
(In VB, of course).

I will try the code with a datatable that has no username filed and let you know.
For the time being, is this info enough to you for tackling the problem?

Again, I really appreciate your help!

vbgaya.

[hollystyles]

I changed the column names in the database so that they match with
your ones.

Oh well not the conventional method, but that's ok, normally you would change the code to fit the column names. But nevermind as long as they match that's the main thing.

As for the Username column, you can supply a zero-length string ('') that's not the same as a NULL value which is literally nothing.

So if the nullability of the username column is the problem try this insert statement instead.

sql = "insert into contacts (username, firstname, lastname) values('', '" & txtFirstName.text & "', '" & txtLastName.Text & "')"

what worries me slightly though is if nullability of the username column is the problem I would not expect a syntax error

Syntax error in INSERT INTO statement

I would expect a "column 'username' cannot be NULL" or something like that.

A final word the ; is oficially the statement terminator for SQL, I know MS SQL Server is not bothered about it, but I couldn't remember if Access was so forgiving.

Another point worth making is that your 'candidate key' (the column best suited to uniquely identify each row in the table and therefore be the primary key) is the username which would typically be the users email address, the AutoID is unecessary and meaningless, also a primary key can be a combination of columns it doesn't have to be just one. But don't worry about that too much let's just get it working first.

[vbgaya]

Hi hollystyles,I gave a null value to username filed.Now it runs without errors. But still thedatabase is not updated. And it ran while thedatabase was open. As I know, the page should prompta message saying that the database is open.So, where can be the error?vbgaya

[hollystyles]

But still thedatabase is not updated.

Are you trying to UPDATE an *existing* record or INSERT a *new* record?

You will have to post your code again I'm afraid in it's current state, because now there are no syntax errors, you now have a logic error because your code is not doing what you expect.

[vbgaya]

Dear hollystyles, I couldn't find the error and for the submission I was able to do it with DataSets. I will get back to you when I continue in this aproach. Anyway, thanks a lot for being with me. Best Regards, vbgaya.

[nunnakk]

see vybaya, i clearly didnt get ur requirement.
if u have mail id so what we can contact directly. my id is [e-mail]
give me ur id so that i can help u