Solved

Need help with my ADO.Net SQL query to a SQL Server

Posted on 2012-03-15
5
387 Views
Last Modified: 2012-08-13
Hi, I'm having issues getting my query to work. My update query works fine:
command.CommandText = "UPDATE [dbo].[Customers] SET [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone WHERE (([ContactName] = @Original_ContactName) AND ([ContactTitle] = @Original_ContactTitle) AND ([PostalCode] = @Original_PostalCode) AND ([Country] = @Original_Country) AND ([Phone] = @Original_Phone))";

Open in new window

However, my Insert query keeps spitting out errors:
command.CommandText = "INSERT INTO [dbo].[Customers](ContactName,ContactTitle,PostalCode,Country,Phone) VALUES ([ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone)";

Open in new window

Can someone please help me correct my insert code?
0
Comment
Question by:Eindoofus
[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
  • 3
  • 2
5 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37727852
In the VALUES clause, ditch all the "[XXX] =" bits, and just keep the "@XXX" bits.
0
 

Author Comment

by:Eindoofus
ID: 37727860
command.CommandText = "INSERT INTO [dbo].[Customers](ContactName,ContactTitle,PostalCode,Country,Phone) VALUES (@ContactName, @ContactTitle, @PostalCode, @Country, @Phone)";

Open in new window

Spits out the following error:

The parameterized query '(@ContactName nvarchar(10),@ContactTitle nvarchar(4000),@PostalC' expects the parameter '@ContactTitle', which was not supplied.

Btw, I should probably mention that I'm using the Northwind database.
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 37727864
To your SqlCommand object are you adding a SqlParameter object which has a parameter name of "@ContactTitle"? If so, make sure the variable which holds the data you want to send to the DB is not null. If you want to insert a NULL into the DB, then you will have to add logic to send DBNull.Value instead. (DBNull.Value represents the concept of database NULL, not null as in C#.)
0
 

Author Comment

by:Eindoofus
ID: 37727873
Turned out that I wasn't filling in all of the necessary textboxes. I kept on get similar errors until they were all filled, but now I get:

Cannot insert the value NULL into column 'CustomerID', table 'Northwind.dbo.Customers'; column does not allow nulls. INSERT fails.
The statement has been terminated.


Shouldn't the database create a key automatically?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37727883
You have set up the Customers column to not accept NULL as a valid value, and I'm guessing you did not set a default value for the column (if nothing is explicitly passed for this column). You must supply a value for this column, or change the column definition to have a default value.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

622 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