Problem with Stored Procedure
-
Hi, I am using a C# code to insert a data in SQL Server. Stored Procedure which inserts data is created as : ----------------------------------------- CREATE PROCEDURE spAddNewAddress @AddressID char, @AddressType char, @HomeAddress varchar, @City varchar, @State varchar, @Zip varchar, @Country varchar AS INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress, @City, @State, @Zip, @Country) GO ----------------------------------------- Then C# code which inserts data is : public void insertNewData(Address a) { try { //create sql string sqlAddress = "spAddNewAddress"; //Open the connection scAddress.Open(); //create the cmdAddress cmdAddress = new SqlCommand(sqlAddress, scAddress); cmdAddress.CommandType = CommandType.StoredProcedure; //adding parameters for command cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); cmdAddress.Parameters.Add("@HomeAddress",SqlDbType.VarChar,100); cmdAddress.Parameters["@HomeAddress"].Value=a.getHomeAddress(); cmdAddress.Parameters.Add("@City",SqlDbType.VarChar,25); cmdAddress.Parameters["@City"].Value=a.getCity(); cmdAddress.Parameters.Add("@State",SqlDbType.VarChar,25); cmdAddress.Parameters["@State"].Value=a.getState(); cmdAddress.Parameters.Add("@Zip",SqlDbType.VarChar,25); cmdAddress.Parameters["@Zip"].Value=a.getZip(); cmdAddress.Parameters.Add("@Country",SqlDbType.VarChar,70); cmdAddress.Parameters["@Country"].Value=a.getCountry(); cmdAddress.ExecuteNonQuery(); //clean up code cmdAddress.Dispose(); scAddress.Close(); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ----------------------------------------- Code executes with out any trouble (no breaks/errors or exceptions). Only problem is when i try to view this data in SQL Server's Enterprise Manager, only first letter of the inserted data appears. e.g : If inserted AddressCode = A03C0001 then in the EnterPrise Managers it appears as : "A". Another strange thing, if try to query database with AddressCode="A", it generates error (that data don't exists). Although If you try to query database with AddressCode="A03C0001", then it extracts complete information as inserted. any suggestions!!!!!
-
Hi, I am using a C# code to insert a data in SQL Server. Stored Procedure which inserts data is created as : ----------------------------------------- CREATE PROCEDURE spAddNewAddress @AddressID char, @AddressType char, @HomeAddress varchar, @City varchar, @State varchar, @Zip varchar, @Country varchar AS INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress, @City, @State, @Zip, @Country) GO ----------------------------------------- Then C# code which inserts data is : public void insertNewData(Address a) { try { //create sql string sqlAddress = "spAddNewAddress"; //Open the connection scAddress.Open(); //create the cmdAddress cmdAddress = new SqlCommand(sqlAddress, scAddress); cmdAddress.CommandType = CommandType.StoredProcedure; //adding parameters for command cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); cmdAddress.Parameters.Add("@HomeAddress",SqlDbType.VarChar,100); cmdAddress.Parameters["@HomeAddress"].Value=a.getHomeAddress(); cmdAddress.Parameters.Add("@City",SqlDbType.VarChar,25); cmdAddress.Parameters["@City"].Value=a.getCity(); cmdAddress.Parameters.Add("@State",SqlDbType.VarChar,25); cmdAddress.Parameters["@State"].Value=a.getState(); cmdAddress.Parameters.Add("@Zip",SqlDbType.VarChar,25); cmdAddress.Parameters["@Zip"].Value=a.getZip(); cmdAddress.Parameters.Add("@Country",SqlDbType.VarChar,70); cmdAddress.Parameters["@Country"].Value=a.getCountry(); cmdAddress.ExecuteNonQuery(); //clean up code cmdAddress.Dispose(); scAddress.Close(); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ----------------------------------------- Code executes with out any trouble (no breaks/errors or exceptions). Only problem is when i try to view this data in SQL Server's Enterprise Manager, only first letter of the inserted data appears. e.g : If inserted AddressCode = A03C0001 then in the EnterPrise Managers it appears as : "A". Another strange thing, if try to query database with AddressCode="A", it generates error (that data don't exists). Although If you try to query database with AddressCode="A03C0001", then it extracts complete information as inserted. any suggestions!!!!!
ronin1770 wrote: cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); I didn't see AddressCode from your code, is it AddressID? You may have mixed up the order of the columns AddressID and AddressType. To prevent this, your insert statement should look like the following: insert into Address(FieldName1,FieldName2,...) values(FileValue1,FieldValue2,...)[
My articles and software tools
-
ronin1770 wrote: cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); I didn't see AddressCode from your code, is it AddressID? You may have mixed up the order of the columns AddressID and AddressType. To prevent this, your insert statement should look like the following: insert into Address(FieldName1,FieldName2,...) values(FileValue1,FieldValue2,...)[
My articles and software tools
-
Hi, I am using a C# code to insert a data in SQL Server. Stored Procedure which inserts data is created as : ----------------------------------------- CREATE PROCEDURE spAddNewAddress @AddressID char, @AddressType char, @HomeAddress varchar, @City varchar, @State varchar, @Zip varchar, @Country varchar AS INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress, @City, @State, @Zip, @Country) GO ----------------------------------------- Then C# code which inserts data is : public void insertNewData(Address a) { try { //create sql string sqlAddress = "spAddNewAddress"; //Open the connection scAddress.Open(); //create the cmdAddress cmdAddress = new SqlCommand(sqlAddress, scAddress); cmdAddress.CommandType = CommandType.StoredProcedure; //adding parameters for command cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); cmdAddress.Parameters.Add("@HomeAddress",SqlDbType.VarChar,100); cmdAddress.Parameters["@HomeAddress"].Value=a.getHomeAddress(); cmdAddress.Parameters.Add("@City",SqlDbType.VarChar,25); cmdAddress.Parameters["@City"].Value=a.getCity(); cmdAddress.Parameters.Add("@State",SqlDbType.VarChar,25); cmdAddress.Parameters["@State"].Value=a.getState(); cmdAddress.Parameters.Add("@Zip",SqlDbType.VarChar,25); cmdAddress.Parameters["@Zip"].Value=a.getZip(); cmdAddress.Parameters.Add("@Country",SqlDbType.VarChar,70); cmdAddress.Parameters["@Country"].Value=a.getCountry(); cmdAddress.ExecuteNonQuery(); //clean up code cmdAddress.Dispose(); scAddress.Close(); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ----------------------------------------- Code executes with out any trouble (no breaks/errors or exceptions). Only problem is when i try to view this data in SQL Server's Enterprise Manager, only first letter of the inserted data appears. e.g : If inserted AddressCode = A03C0001 then in the EnterPrise Managers it appears as : "A". Another strange thing, if try to query database with AddressCode="A", it generates error (that data don't exists). Although If you try to query database with AddressCode="A03C0001", then it extracts complete information as inserted. any suggestions!!!!!
If the database returns the data you asked it to insert, then it is there, the problem can only be in how you are viewing the data within SQL server. If you do a select of everything in the table, what does that look like ? Christian I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
-
Hi, I am using a C# code to insert a data in SQL Server. Stored Procedure which inserts data is created as : ----------------------------------------- CREATE PROCEDURE spAddNewAddress @AddressID char, @AddressType char, @HomeAddress varchar, @City varchar, @State varchar, @Zip varchar, @Country varchar AS INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress, @City, @State, @Zip, @Country) GO ----------------------------------------- Then C# code which inserts data is : public void insertNewData(Address a) { try { //create sql string sqlAddress = "spAddNewAddress"; //Open the connection scAddress.Open(); //create the cmdAddress cmdAddress = new SqlCommand(sqlAddress, scAddress); cmdAddress.CommandType = CommandType.StoredProcedure; //adding parameters for command cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); cmdAddress.Parameters.Add("@HomeAddress",SqlDbType.VarChar,100); cmdAddress.Parameters["@HomeAddress"].Value=a.getHomeAddress(); cmdAddress.Parameters.Add("@City",SqlDbType.VarChar,25); cmdAddress.Parameters["@City"].Value=a.getCity(); cmdAddress.Parameters.Add("@State",SqlDbType.VarChar,25); cmdAddress.Parameters["@State"].Value=a.getState(); cmdAddress.Parameters.Add("@Zip",SqlDbType.VarChar,25); cmdAddress.Parameters["@Zip"].Value=a.getZip(); cmdAddress.Parameters.Add("@Country",SqlDbType.VarChar,70); cmdAddress.Parameters["@Country"].Value=a.getCountry(); cmdAddress.ExecuteNonQuery(); //clean up code cmdAddress.Dispose(); scAddress.Close(); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ----------------------------------------- Code executes with out any trouble (no breaks/errors or exceptions). Only problem is when i try to view this data in SQL Server's Enterprise Manager, only first letter of the inserted data appears. e.g : If inserted AddressCode = A03C0001 then in the EnterPrise Managers it appears as : "A". Another strange thing, if try to query database with AddressCode="A", it generates error (that data don't exists). Although If you try to query database with AddressCode="A03C0001", then it extracts complete information as inserted. any suggestions!!!!!
-
Hi, I am using a C# code to insert a data in SQL Server. Stored Procedure which inserts data is created as : ----------------------------------------- CREATE PROCEDURE spAddNewAddress @AddressID char, @AddressType char, @HomeAddress varchar, @City varchar, @State varchar, @Zip varchar, @Country varchar AS INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress, @City, @State, @Zip, @Country) GO ----------------------------------------- Then C# code which inserts data is : public void insertNewData(Address a) { try { //create sql string sqlAddress = "spAddNewAddress"; //Open the connection scAddress.Open(); //create the cmdAddress cmdAddress = new SqlCommand(sqlAddress, scAddress); cmdAddress.CommandType = CommandType.StoredProcedure; //adding parameters for command cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); cmdAddress.Parameters.Add("@HomeAddress",SqlDbType.VarChar,100); cmdAddress.Parameters["@HomeAddress"].Value=a.getHomeAddress(); cmdAddress.Parameters.Add("@City",SqlDbType.VarChar,25); cmdAddress.Parameters["@City"].Value=a.getCity(); cmdAddress.Parameters.Add("@State",SqlDbType.VarChar,25); cmdAddress.Parameters["@State"].Value=a.getState(); cmdAddress.Parameters.Add("@Zip",SqlDbType.VarChar,25); cmdAddress.Parameters["@Zip"].Value=a.getZip(); cmdAddress.Parameters.Add("@Country",SqlDbType.VarChar,70); cmdAddress.Parameters["@Country"].Value=a.getCountry(); cmdAddress.ExecuteNonQuery(); //clean up code cmdAddress.Dispose(); scAddress.Close(); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ----------------------------------------- Code executes with out any trouble (no breaks/errors or exceptions). Only problem is when i try to view this data in SQL Server's Enterprise Manager, only first letter of the inserted data appears. e.g : If inserted AddressCode = A03C0001 then in the EnterPrise Managers it appears as : "A". Another strange thing, if try to query database with AddressCode="A", it generates error (that data don't exists). Although If you try to query database with AddressCode="A03C0001", then it extracts complete information as inserted. any suggestions!!!!!
The problem isn't your code but your procedure. When you declared "varName varchar" SQL see it like one char variable. If you want to use more than one char you must to declare "varName varchar(10)" for a 10 chars variable. Cya Wender Oliveira .NET Programmer
-
The problem isn't your code but your procedure. When you declared "varName varchar" SQL see it like one char variable. If you want to use more than one char you must to declare "varName varchar(10)" for a 10 chars variable. Cya Wender Oliveira .NET Programmer
-
Hi, I am using a C# code to insert a data in SQL Server. Stored Procedure which inserts data is created as : ----------------------------------------- CREATE PROCEDURE spAddNewAddress @AddressID char, @AddressType char, @HomeAddress varchar, @City varchar, @State varchar, @Zip varchar, @Country varchar AS INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress, @City, @State, @Zip, @Country) GO ----------------------------------------- Then C# code which inserts data is : public void insertNewData(Address a) { try { //create sql string sqlAddress = "spAddNewAddress"; //Open the connection scAddress.Open(); //create the cmdAddress cmdAddress = new SqlCommand(sqlAddress, scAddress); cmdAddress.CommandType = CommandType.StoredProcedure; //adding parameters for command cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); cmdAddress.Parameters.Add("@HomeAddress",SqlDbType.VarChar,100); cmdAddress.Parameters["@HomeAddress"].Value=a.getHomeAddress(); cmdAddress.Parameters.Add("@City",SqlDbType.VarChar,25); cmdAddress.Parameters["@City"].Value=a.getCity(); cmdAddress.Parameters.Add("@State",SqlDbType.VarChar,25); cmdAddress.Parameters["@State"].Value=a.getState(); cmdAddress.Parameters.Add("@Zip",SqlDbType.VarChar,25); cmdAddress.Parameters["@Zip"].Value=a.getZip(); cmdAddress.Parameters.Add("@Country",SqlDbType.VarChar,70); cmdAddress.Parameters["@Country"].Value=a.getCountry(); cmdAddress.ExecuteNonQuery(); //clean up code cmdAddress.Dispose(); scAddress.Close(); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ----------------------------------------- Code executes with out any trouble (no breaks/errors or exceptions). Only problem is when i try to view this data in SQL Server's Enterprise Manager, only first letter of the inserted data appears. e.g : If inserted AddressCode = A03C0001 then in the EnterPrise Managers it appears as : "A". Another strange thing, if try to query database with AddressCode="A", it generates error (that data don't exists). Although If you try to query database with AddressCode="A03C0001", then it extracts complete information as inserted. any suggestions!!!!!
The last two replies are correct, in that you're not declaring your field as a string, but are instead declaring it as a char. I wanted to add that the above code can be made much more efficient by not using the indexer to access your param. The
SqlParameterCollection.Addoverload you're using returns aSqlParameter, so you can either type.Value = _value_;immediately after your closing parenthesis, or store the return value fromAddto aSqlParametervariable and assign the value from there. The latter way works best when executing a batch of statements (add the parameters to the command and loop to add each iteration's values without re-adding the params again; execute the command with each iteration). This will result in better performance, since a string lookup on a collection can be very slow (mO(n) processing time, where n is the number of items in a collection and m is the number of iterations; compare that to O(m)).Microsoft MVP, Visual C# My Articles
-
Hi, I am using a C# code to insert a data in SQL Server. Stored Procedure which inserts data is created as : ----------------------------------------- CREATE PROCEDURE spAddNewAddress @AddressID char, @AddressType char, @HomeAddress varchar, @City varchar, @State varchar, @Zip varchar, @Country varchar AS INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress, @City, @State, @Zip, @Country) GO ----------------------------------------- Then C# code which inserts data is : public void insertNewData(Address a) { try { //create sql string sqlAddress = "spAddNewAddress"; //Open the connection scAddress.Open(); //create the cmdAddress cmdAddress = new SqlCommand(sqlAddress, scAddress); cmdAddress.CommandType = CommandType.StoredProcedure; //adding parameters for command cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10); cmdAddress.Parameters["@AddressID"].Value = a.getAddressID(); cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1); cmdAddress.Parameters["@AddressType"].Value=a.getAddressType(); cmdAddress.Parameters.Add("@HomeAddress",SqlDbType.VarChar,100); cmdAddress.Parameters["@HomeAddress"].Value=a.getHomeAddress(); cmdAddress.Parameters.Add("@City",SqlDbType.VarChar,25); cmdAddress.Parameters["@City"].Value=a.getCity(); cmdAddress.Parameters.Add("@State",SqlDbType.VarChar,25); cmdAddress.Parameters["@State"].Value=a.getState(); cmdAddress.Parameters.Add("@Zip",SqlDbType.VarChar,25); cmdAddress.Parameters["@Zip"].Value=a.getZip(); cmdAddress.Parameters.Add("@Country",SqlDbType.VarChar,70); cmdAddress.Parameters["@Country"].Value=a.getCountry(); cmdAddress.ExecuteNonQuery(); //clean up code cmdAddress.Dispose(); scAddress.Close(); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ----------------------------------------- Code executes with out any trouble (no breaks/errors or exceptions). Only problem is when i try to view this data in SQL Server's Enterprise Manager, only first letter of the inserted data appears. e.g : If inserted AddressCode = A03C0001 then in the EnterPrise Managers it appears as : "A". Another strange thing, if try to query database with AddressCode="A", it generates error (that data don't exists). Although If you try to query database with AddressCode="A03C0001", then it extracts complete information as inserted. any suggestions!!!!!
CREATE PROCEDURE spAddNewAddress @AddressID char, @AddressType char, @HomeAddress varchar, @City varchar, @State varchar, @Zip varchar, @Country varchar AS INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress, @City, @State, @Zip, @Country) GO In the above parameters, a size needs to be added: @AddressID char(nn), @AddressType char(nn), @HomeAddress varchar(nn), @City varchar(nn), @State varchar(2), @Zip varchar(9), @Country varchar(nn) where the "nn" is the size of the field.