I am writing an application that keeps inventory of engine parts. One of my forms allows the user to add a new parts manufacturer record to a SQL Server Express database named "Inventory.mdb" by gathering information from 3 textboxes on the form and calculating the next available "ManufacturerID". These 4 pieces of information are then passed as parameters to a stored procedure called "InsertManufacturer" which contains the following code:
ALTER PROCEDURE
dbo.InsertManufacturer@ManID
int,@ManName varchar (50),
@ContactName varchar (50),
@ContactNumber varchar (20)
AS
INSERT INTO Manufacturer
VALUES (@ManID, @ManName, @ContactName, @ContactNumber) RETURN
private void btnAdd_Click(object sender, EventArgs e)
{
string myManName = txtManName.Text; string myContact = txtContactName.Text; string myNumber = txtPhoneNumber.Text; SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = StationInventorySystem.Properties.Settings.Default.InventoryConnectionString; SqlCommand myInsert = new SqlCommand("dbo.InsertManufacturer", myConnection);myInsert.CommandType =
CommandType.StoredProcedure;//I wasn't sure if the commandtext was "sticking" above so I added the following line to make sure.
myInsert.CommandText = "dbo.InsertManufacturer"; SqlParameter pManID = myInsert.Parameters.Add("@ManID", SqlDbType.Int, 10); SqlParameter pManName = myInsert.Parameters.Add("@ManName", SqlDbType.VarChar, 50); SqlParameter pContactName = myInsert.Parameters.Add("@ContactName", SqlDbType.VarChar, 50); SqlParameter pContactNumber = myInsert.Parameters.Add("@ContactNumber", SqlDbType.VarChar, 50);
pManID.Value = myNewManID;
pManName.Value = myManName;
pContactName.Value = myContact;
pContactNumber.Value = myNumber;
try{
myConnection.Open();
myInsert.ExecuteNonQuery();
myConnection.Close();
}
{
MessageBox.Show(ex.Message);
}
this.Close();
}
-----------------------end code--------------------
For some reason when I execute the InsertManufacturer stored procedure from within the designer window in visual studio and supply the parameters, the record is inserted. However, when I build my project and run the code above, the record will not be inserted. No exception is being thrown, but for some reason the values simply are not inserted and the application continues as if nothing is wrong. This problem is driving me nuts, so any advice you can give (even another / different way that might work) will be extremely appreciated. I'm using Visual C# Express and SQL Server Express.
S.O.S
Bevin MarwaPosted Mar 30, 2006, 8:55 AM
ALTER PROCEDURE dbo.InsertManufacturer
@ManName varchar (50),
@ContactName varchar (50),
@ContactNumber varchar (20)
AS
INSERT INTO Manufacturer
VALUES (@ManName, @ContactName, @ContactNumber)
RETURN
private void btnAdd_Click(object sender, EventArgs e)
{
string myManName = txtManName.Text;
string myContact = txtContactName.Text;
string myNumber = txtPhoneNumber.Text;
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = StationInventorySystem.Properties.Settings.Default.InventoryConnectionString;
SqlCommand myInsert = new SqlCommand("dbo.InsertManufacturer", myConnection);
myInsert.CommandType = CommandType.StoredProcedure;
//I wasn't sure if the commandtext was "sticking" above so I added the following line to make sure.
myInsert.CommandText = "dbo.InsertManufacturer";
myInsert.Parameters.Add("@ManName", SqlDbType.VarChar, 50).Value = myManName;
myInsert.Parameters.Add("@ContactName", SqlDbType.VarChar, 50).Value = myContact;
myInsert.Parameters.Add("@ContactNumber", SqlDbType.VarChar, 50).Value = myNumber;
try
{
myConnection.Open();
myInsert.ExecuteNonQuery();
myConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Close();
}
Try this code, make sure that the first column in the table has the identity set to yes and increament. So you don't have to insert anything in that column.
mark doxtaterPosted Mar 20, 2006, 11:10 PM
Additionally: I added a new form to my app to take the place of this form. On the blank form I dragged the Manufacturer table from my DataSet (which was defined in Designer by dropping the Manufacturer table from my SQL Server Express Database) and set its default "associated control" to Detail. The designer added the labels, text boxes and a toolbar for navigating/deleting/saving the records. Interestingly enough, after compiling and running - when I entered valid data in the textboxes and pushed the save button the record was still navigable on the screen, but it was not saved to my underlying database. Do I need to do additional coding, here? I was under the impression that the IDE provided the plumbing based on the table's schema. Has anyone ever seen something like this? I know my app is connecting to the database at runtime - my connection is opening and closing without any errors at least - but I haven't been able to commit any records. If I execute the "InsertManufacturer" stored procedure from the designer it works though.
Binu SubiPosted Mar 20, 2006, 3:18 PM
ex:
myParam.Direction = ParameterDirection.Input;
Hope it works!
Cheers!