Visual Basic

27-May-06 4:35

hi, can someone tell me how to insert a new record. i have 2 values and i want them to be saved in the database. i have the username and password. all i want to do is to make the user fill the textfields and the values will be saved in the database. Frown | :(

can someone help me pls with this?? Frown | :(

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

Colin Angus Mackay27-May-06 6:16

27-May-06 6:16

ADY007 wrote:
To insert a new record in asp.net

It is ADO.NET that is the part of the .NET Framework that deals with databases. ASP.NET is the part of the .NET Framework that deals with web applications.

ADY007 wrote:
can someone tell me how to insert a new record. i have 2 values and i want them to be saved in the database.

There are many ways to insert data into the database here is one:

Dim conn As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand()
cmd.CommandText = "INSERT MyTable(Column1, Column2) VALUES (@value1, @value2)"
cmd.Parameters.Add("@value1", firstValue)
cmd.Parameters.Add("@value2", secondValue)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()

Assumptions:
* You are using SQL Server
* You have a connection string already called connectionString
* You have a table called MyTable
* MyTable has two columns called Column1 and Column2
* The values you wish to insert are stored in variables called firstValue and secondValue

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

ADY00727-May-06 9:18

27-May-06 9:18

Yes i made it but it was all useless! the cmd.ExecuteNonQuery is not good i think! because the error is always in there.!! Frown | :(

i am using MS Access Frown | :(

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

Colin Angus Mackay27-May-06 9:32

Re: To insert a new record in asp.net [modified]

27-May-06 9:32

ADY007 wrote:
i am using MS Access

You should have said before.

Use OleDb classes instead of Sql classes.

ADY00727-May-06 10:22

Re: To insert a new record in asp.net [modified]

27-May-06 10:22

yes i am doing it with the oledb! but its useless.! i amm doing this right now.

Private Sub Save()
Dim daMem As OleDbDataAdapter = New OleDbDataAdapter("Insert Into tblMembers(Name,Surname) values('Hello', 'F***')", odcConnection)
daMem = New OleDbDataAdapter("Select * From tblMembers", odcConnection)
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(daMem)
Dim dsNewRow As DataRow

dsNewRow = dsMembers.Tables("tblMembers").NewRow

dsNewRow.Item("Name") = txtName.Text
dsNewRow.Item("Surname") = txtSurname.Text
dsNewRow.Item("Phone") = txtPhone.Text
dsNewRow.Item("Mobile") = txtMobile.Text
dsNewRow.Item("Username") = txtUsername.Text
dsNewRow.Item("Password") = txtPassword.Text
dsNewRow.Item("TimesEntered") = intTimesEntered

dsMembers.Tables("tblMembers").Rows.Add(dsNewRow)

daMem.Update(dsMembers, "tblMembers")
End Sub

But daMem.Update(dsMembers, "tblMembers") he is telling me that the Insert Command isn't there! Frown | :(

but i am making a new row! can someone help pls??

This is the exact error:

Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

-- modified at 16:26 Saturday 27th May, 2006

Colin Angus Mackay27-May-06 10:34

27-May-06 10:34

ADY007 wrote:
But daMem.Update(dsMembers, "tblMembers") he is telling me that the Insert Command isn't there! but i am making a new row!

That is correct you have not written an INSERT statement. The INSERT statement has nothing to do with creating a new row in a DataTable. DataTables know nothing about your database.

For such a simple action you are overcomplicating the whole thing. You do not need DataRows or DataTables.

Go back to my first response to you and see what I wrote. Change it to use the equivalent OleDb classes and change the parameters to ? (because access doesn't accept named parameters. That is a simpler and less convoluted solution.

ADY00727-May-06 11:13

27-May-06 11:13

I wrote everything you told me but here is the error i told you before about Frown | :(

:

Exception Details: System.InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

Colin Angus Mackay27-May-06 11:16

27-May-06 11:16

After creating the cmd object put the line

cmd.Connection = conn

ADY00727-May-06 11:22

27-May-06 11:22

owww noo!!! Cry | :((

another error occured! it is just the same as the one i made Frown | :(

but why is this killing me Frown | :(

this is the error!
Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

ADY00727-May-06 11:23

27-May-06 11:23

is it good if i write it like this??

cmd.CommandText = "INSERT tblMembers(Name, Surname) VALUES (@value1, @value2)"
cmd.Parameters.Add("@value1", firstValue)
cmd.Parameters.Add("@value2", secondValue)

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

ADY00727-May-06 11:57

27-May-06 11:57

Right Now I have this code:

Private Sub add()
Dim firstvalue As String = txtName.Text
Dim secondvalue As String = txtSurname.Text

Dim conn As OleDbConnection = New OleDbConnection(strConnection)
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = conn
cmd.CommandText = "INSERT Into tblMembers(Name, Surname) VALUES (?,?)"
cmd.Parameters.Add("Name", firstvalue)
cmd.Parameters.Add("Surname", secondvalue)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Sub

But an error is occuring telling me this:

Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query.

What do you thing this is??

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

Colin Angus Mackay27-May-06 13:52

27-May-06 13:52

I've never come across this exception message before, so I'm guessing that it is becuase Access does not understand named parameters. So remove the parameter name. i.e.

cmd.Parameters.Add(firstValue)
cmd.Parameters.Add(secondValue)

ADY00727-May-06 14:14

27-May-06 14:14

This comes up now:

Exception Details: System.InvalidCastException: The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects.

Cry | :((

I am so sorry i am giving you a little trouble my friend! but right now i am searching for any help i can find! because i have stucked. and this is my final project of the school Frown | :(

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

Colin Angus Mackay27-May-06 14:49

27-May-06 14:49

ADY007 wrote:
The OleDbParameterCollection only accepts non-null OleDbParameter type objects

Of course, The MSDN documentation for OleDbCommand[^] confirms that.

Because Access uses positioned parameters rather than named parameters I thought you didn't need the name. Apparently, according to the documentation I linked to above, you do. Anyway, the docs above should give you the help you need to get going again.

ADY00729-May-06 10:39

29-May-06 10:39

The error i found what was the problem!!! whoever got this error:

the apdateable error .. go to Tool, Folder Options, View Tab and then uncheck the last one which tells you: Use Simple FIle Sharing. Uncheck it. then go to database file and right click on it, properties. then go to security tab and make write allowed!!! for all users!! then you can use the database to write in it. have fun! Big Grin | :-D

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

Colin Angus Mackay29-May-06 12:19