Solved

Problem creating Insert Update Delete statements with a data adapter

Posted on 2006-07-20
17
280 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 500 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Paging GridView 7 34
.net VBA word safemode 1 24
AutoHotkey ActiveX Component Error 8 55
Open a word document 23 13
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now