Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem creating Insert Update Delete statements with a data adapter

Posted on 2006-07-20
17
Medium Priority
?
292 Views
Last Modified: 2009-12-16
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
Comment
Question by:npbaker1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 2
  • +2
17 Comments
 
LVL 10

Expert Comment

by:ibost
ID: 17147452
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
 
LVL 35

Expert Comment

by:YZlat
ID: 17147460
try using Command Builder:

Dim cbuild As SqlCommandBuilder = New SqlCommandBuilder(DataAdapter1)
0
 

Author Comment

by:npbaker1
ID: 17147883
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 10

Expert Comment

by:ibost
ID: 17148040
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
 

Author Comment

by:npbaker1
ID: 17148243
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
 
LVL 10

Expert Comment

by:ibost
ID: 17148333
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
 

Author Comment

by:npbaker1
ID: 17148735
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
 
LVL 10

Expert Comment

by:ibost
ID: 17148748
does the select statement itself work?  Can you fill a dataset with the data adapter?
0
 

Author Comment

by:npbaker1
ID: 17153192
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
 

Author Comment

by:npbaker1
ID: 17153651
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
 
LVL 8

Expert Comment

by:Shakti109
ID: 17155446

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
 

Author Comment

by:npbaker1
ID: 17156072
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
 

Author Comment

by:npbaker1
ID: 17156266
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
 
LVL 1

Accepted Solution

by:
revroblong earned 2000 total points
ID: 17158736
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
 

Author Comment

by:npbaker1
ID: 17166970
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
 
LVL 8

Expert Comment

by:Shakti109
ID: 17170185

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
 

Author Comment

by:npbaker1
ID: 17170805
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question