• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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
0
npbaker1
Asked:
npbaker1
  • 9
  • 4
  • 2
  • +2
1 Solution
 
ibostCommented:
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
0
 
YZlatCommented:
try using Command Builder:

Dim cbuild As SqlCommandBuilder = New SqlCommandBuilder(DataAdapter1)
0
 
npbaker1Author Commented:
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ibostCommented:
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.
0
 
npbaker1Author Commented:
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
0
 
ibostCommented:
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?
0
 
npbaker1Author Commented:
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
0
 
ibostCommented:
does the select statement itself work?  Can you fill a dataset with the data adapter?
0
 
npbaker1Author Commented:
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.
0
 
npbaker1Author Commented:
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.
0
 
Shakti109Commented:

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?
0
 
npbaker1Author Commented:
I am using windows authentication right now but I also have the same problem when I tried using the sa account on SQL server.
0
 
npbaker1Author Commented:
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.
0
 
revroblongCommented:
I ran into the same problem using the Desktop SQL 2005 engine with VB.net 2003. I found out that vb.net 2003 wasn't completely compatible with SQL 2005...so I changed out my SQL to the 2001 version, and voila...it generated all of the commands for me. Your options may be to upgrade to VB.Net 2005 or downgrade to SQL 2001.

I hope this helps out.
0
 
npbaker1Author Commented:
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.
0
 
Shakti109Commented:

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.
0
 
npbaker1Author Commented:
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.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 9
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now