|
This works on an SQL2000 Db, but when I try to covert it to an Ole connections using an AccessDb I get the following error:
System.Data.OleDb.OleDbException: Operation must use an updateable query.
My code:
//Create the connection object
string DbPath= Server.MapPath("Player.mdb");
string ConnStrg="Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection cnn= new OleDbConnection(ConnStrg);
DataSet dsPlayer= new DataSet();
OleDbDataAdapter daPlayer=new OleDbDataAdapter();
//Create a OleDbCommand to select data
OleDbCommand cmdSelect=cnn.CreateCommand();
cmdSelect.CommandType=CommandType.Text;
cmdSelect.CommandText="SELECT FirstName, LastName FROM PlayerID";
//Create an object to insert data
OleDbCommand cmdInsert=cnn.CreateCommand();
cmdInsert.CommandType=CommandType.Text;
cmdInsert.CommandText="INSERT INTO PlayerID (FirstName, LastName) " +
"VALUES(@FirstName, @LastName)";
cmdInsert.Parameters.Add("@FirstName", OleDbType.VarChar,50, "FirstName");
cmdInsert.Parameters.Add("@LastName", OleDbType.VarChar,50, "LastName");
cmdInsert.Parameters["@FirstName"].SourceVersion=DataRowVersion.Original;
//Setup the DataAdapter and fill the DataSet
daPlayer.SelectCommand=cmdSelect;
daPlayer.InsertCommand=cmdInsert;
daPlayer.Fill(dsPlayer, "Player");
//Create a new DataRow
DataRow dr= dsPlayer.Tables["Player"].NewRow();
//Set Values
dr[0]=txtFirstName.Text;
dr[1]=txtLastName.Text;
//And append the new row to the DataTable
dsPlayer.Tables["Player"].Rows.Add(dr);
//Save back to the database
daPlayer.Update(dsPlayer, "Player");
}
Any help or suggestion are greatly appreciated.
Jerry
Most people are willing to pay more to be amused than to be educated--Robert C. Savage, Life Lessons
Toasty0.com
Ladder League (beta)
My Grandkids
|
|
|
|
|
i had the same problem some time back when working with access database. Check whether you have given only read permission in the database. If you have given only read permission then you can only use select statement, if you use insert statement. you will get this error.
leo
Kathmandu, Nepal
|
|
|
|
|
Hi all, having a very frustrating problem:
I'm reading a field from a SQL Anywhere (ODBC) database into a .Net DataSet. The type in the database is "time", but this is read into the DataSet as a .Net TimeSpan type instead of a DateTime. No big deal, except I can't figure out how to convert this within the dataset (I'm exporting straight to XML, so I really can't read the data out into .Net types and do the conversions - it has to stay in the DataSet).
I've tried:
- creating a DataColumn with type String and forcing it into that
- above, with DateTime instead of String
- using the Convert function in DataColumn.Expression; this seems like the most promising, but it says that the type doesn't implement IConvertible (even though the documentation for Convert says that it CAN convert a TimeSpan to a string)
I don't care if it's a string or a DateTime, it just can't be a TimeSpan because it prints in a really ugly fasion (11:01 is PT11H01M) and I can't figure out how to format it nicely. Any help is appreciated, thanks!
|
|
|
|
|
Hi all,
How can i generate auto Number in SQL Server 2000,like AutoNumber Datatype in MsAccess....
Thanx in advance
Sajjad
|
|
|
|
|
When you create your table set the column to be an IDENTITY column like this:
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
)
The numbers in the brackets after IDENTITY refer to the seed and increment and can be omitted. If you don't specify the numbers the default behaviour will be equivalent to IDENTITY(1,1)
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I created a user login page associated with an address book. In order to retrieve the address book the user must enter the username and password. How can i code the login button for authentication.
Below is the code that i used but there is an error message that points to ExecuteReader. Error: "ExecuteReader requires an open and available connection. The connection current state is closed."
I am using the OleDbDataAdapter, OleDbDataReader, OleDbConnection, and the OleDbCommand. What is it that I am doing wrong. I also created a MS Access table.
Dim mypath = Application.StartupPath & "\password.mdb"
Dim Password = ""
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mypath & ";Jet OLEDB:Database Password=" & Password)
Dim cmd As OleDbCommand
Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
Dim sql = "SELECT Username,Password FROM Password WHERE Username = '" & txtUsername.Text & "' AND Password = '" & txtPassword.Text & "'"
cmd = New OleDbCommand(sql, conn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Try
conn.Open()
Catch ex As InvalidOperationException
MsgBox(ex.Message)
End Try
Try
If dr.Read = False Then
MessageBox.Show("Authentication Failed...")
Else
MessageBox.Show("Login Successful...")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
Dim form As New Form2
form.Show()
bravo659
|
|
|
|
|
first of all : the actual reason of this problem that you must close the reader before you clos the connection ,
And also it seems you opened the connection twice in :
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
And :
Try
conn.Open()
Catch ex As InvalidOperationException
MsgBox(ex.Message)
End Try
hope that helps.
|
|
|
|
|
I didn't even noticed that I opened it twice, thanks for pointing it out to me. So then, it is not the ExecuteReader, is that I used the conn.Open twice that disallow the disconnect from the reader, it just tries to open twice without closing the reader.
Thanks for your response. Let me try and will post again whether it works or not. Been working on this for 3 months. LOL.
bravo659
|
|
|
|
|
Nevermind, but try also to close the reader before closing the connection .
|
|
|
|
|
I finally fixed the problem. But I still need more to learn about programming in vb.net.
Thanks for your reply.
bravo659
|
|
|
|
|
"SELECT Username,Password FROM Password WHERE Username = '" & txtUsername.Text & "' AND Password = '" & txtPassword.Text & "'"
Just as a side issue and before Colin gets you for it. Although this is the sort of code you see in many text book examples it is open to SQL injection attacks. Again it probably matters less if it is a desktop application but its still bad practice.
what this means basically is that a user can input a string of SQL into the username textbox which could easily invalidate your security. This is why paramaters in Stored Procedures is a better way to go.
Jon
|
|
|
|
|
|
Thanks for your reply.
I've been hearing about these issues. But I am still learning, and need to learn about stored procedires. I believe I am not ready for the stored procedures. But I will try to learn it. I like programming, which it is a challenge. Is it possible if you would explain more about stored procedures? I really appreciate it. And what is the best advanced books that I can learn from. By the way who is Colin?
bravo659
|
|
|
|
|
|
I need to know how many records there are in the datareader, is the some way ?
...
...
Dim conexao As New SqlConnection("Database=xonline; Server=xsamcpd05x; user id=ox_sadmilson; pwd=cchinatown")
'Dim conexao As New SqlConnection("Database=online; Server=samcpd05; user id=onlineteste; pwd=onlineteste")
Dim query As String = "select prod_date,unit_id FROM rip_producao withwhere prod_date>=" & ts_init.Text & " and prod_date<=" & ts_end.Text & ""
Dim comando As New SqlCommand(query, conexao)
Dim dr As SqlDataReader
conexao.Open()
dr = comando.ExecuteReader()
...
...
|
|
|
|
|
sadmilson wrote:
I need to know how many records there are in the datareader, is the some way ?
You cannot find this out in advance. You would need to read through all the rows first.
One possible solution, if you need to know the answer in advance of reading the rows is to sent two SQL Statements into the SqlCommand object. The first would be a SELECT COUNT(*) FROM.... Then you would have two result sets in the Data Reader, the first result set would contain the number of rows in the second result set.
Also, I point this out any time I see a potential security concern with code, your code is susceptible to a SQL Injection Attack. You may wish to read this article[^] to find out what that means and how to mitigate the risks.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks Colin,
The problem is that I need to make two selects, each select returns about 2 milions records, it take some time to return, I beleave that your solution is good, i will try.
Thanks my friend
|
|
|
|
|
Just do one SELECT statement but return a DataView instead of SqlDataReader so you can do
DataView pData.Count
Cheers
Al
|
|
|
|
|
|
No I meant DataView that also user SqlDataReader!
Al
|
|
|
|
|
|
Why top make 2 requests into the database when you can just get the container DataView with properties like DataView.Count? So you don't make 2 requests, and yes you put it into memory. GC will take care of calling Dispose or you can do it too.
Al
|
|
|
|
|
Albert Pascual wrote:
Why top make 2 requests into the database when you can just get the container DataView with properties like DataView.Count?
I wasn't advocating 2 requests. I was suggesting one request with two SQL Statements in it. Something like this:
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM MyTable; SELECT col1, col2, etc FROM MyTable");
Albert Pascual wrote:
GC will take care of calling Dispose or you can do it too.
Why make two copies in the first place. If it is a large enough set of data that's going to hit memory fairly hard.
[EDIT]
Also, if the code is already written for using a DataReader, why make more modifications than is necessary.
[/EDIT]
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I thought that was 2 requests still! coming back in one SqlDataReader, however SQL still has to make every request between ; character
Al
|
|
|
|
|
Albert Pascual wrote:
I thought that was 2 requests still!
Nope. One outward request (with two commands in it). One lump of data back. On its return the DataReader splits the lump in to two ResultSets.
Albert Pascual wrote:
however SQL still has to make every request between ; character
Nope, it just sends out the string as given. The SQL Parser on the server will sort it out. It could be further optimised by adding a BEGIN TRANSACTION at the start and an COMIT TRANSACTION at the end, but that optimisation would be on the server end and not really affect communications.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
