Link to home
Start Free TrialLog in
Avatar of npbaker1
npbaker1

asked on

Problem creating Insert Update Delete statements with a data adapter

I have just installed SQL Server 2005 developement edition on my computer.  I am also using Visual Studio.Net 2003.  When I create a data adapter to connect to one of my database tables I can create a select statement but it will not create any of the other statements.  The select statement is a simple Select *
From tblname
so I am unsure what the problem is.  I am thinking maybe I need to change the permissions in SQL Server but am not exactly sure how to go about doint that.  Any help would be appreciated and if this should go more in the DB category I apologize.

Thanks
Nate
Avatar of ibost
ibost

Use specific column names like "SELECT OrderID, ProductID, Quantity FROM MYTABLE"

Then use a command builder object

assuming you have your dataadapter set up with a select command:

            Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(da)

            Label1.Text = cb.GetInsertCommand().CommandText


To use a commandbuilder:
1)  query must return data from only one table
2)  that table must have a primary key
3)  primary key must be included in the results of the query
Avatar of YZlat
try using Command Builder:

Dim cbuild As SqlCommandBuilder = New SqlCommandBuilder(DataAdapter1)
Avatar of npbaker1

ASKER

Well I know thats an option but I am trying to figure out why it cant generate the update statements on its own.  Before I was using access and it generated all the statements fine so what is the difference with SQL Server.
If you drag a DataAdapter to your page and use the wizard, it will build them.  If you try and do it programatically you need to define them yourself.  I'm not sure how it would know to build the update logic (even for access) if you didn't use the wizard or a command builder.
I am using the wizard and it wont generate anything but the select statement.  I can manually go into the properties of each one and make the statement but it is rather time consuming when you have a large amount of data adapters
Does the base table have a primary key?  I believe that is a requirement if you want it to build the update command automatically.  Does it give you a reason why it can't build it?
Yes it has a primary key and no it just says that there is an error generating the statements.  Since I am trying to avoid hardcoding all of my commands I would really like to understand where the problem is.  I am unsure if it is a Visual Studio problem or a SQL Server problem
does the select statement itself work?  Can you fill a dataset with the data adapter?
Yes the select statement works.  I can view the data in a dataset.  I hope this is not a problem because I am using sql server 2005 and visual studio 2003.  I know that I could use command builder to make the commands manually but I am trying to avoid the extra coding and I would also like to know why the wizard is having an error generating the statements just so that I know.
Just wanted to add that I just discovered that if I uncheck refresh dataset in the advanced options of the wizard the data adapter will create the insert statement but still no update or delete.

If you have created a basic database in SQL, and the only action you have taken is to add a couple tables, then it is likely the account you are connecting to the SQL server with from VB.net does not have update/insert/delete/etc access defined on the tables/database.

Go into SQL server, right click on the database you have created, click on properties.

Once this window has opened up, go to the permissions tab and there you will see all of the accounts that have "access" to this database.

From here you will need to add/update/change the accounts you want to have be able to perform certain functions in this database.

Are you connecting to the SQL server (FROM vb.net) with the 'SA' account, or are you using windows-authentication login, or are you using another named SQL account?
I am using windows authentication right now but I also have the same problem when I tried using the sa account on SQL server.
When I went into the database properties and then permissions like you said the only 2 users I could see were guest and public.  So if this is my problem then if you could explain how to add a user as I have been trying to do that for the past 2 days and nothing I do seems to add a user to that database.  Ultimately if there is a way to create a system admin account for that database or add the sa account to the database that is what I would like as I need to have as much access as possible while I am developing my application.
ASKER CERTIFIED SOLUTION
Avatar of revroblong
revroblong

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Does anyone know of any workarounds for this problem.  I would like to avoid paying 700 bucks for VS 2005 and since Microsoft charges you to downgrade I would like to avoid that as well.

Hrm, I am able to re-create your exact problem....

I have a newly installed SQL 2005 server, and used VB.NET2003 to try to connect. When I created the adapter, it exactly dupliacated your issue.

I and a couple of co-workers are experimenting with this.
I just ordered VS 2005 but I will leave the question open so that if someone does find a solution it can be posted so that other people with the same problem can figure it out in less time than it took me.