|
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
|
|
|
|
|
HI,
i tried the way u suggested... it was never the order of fields
but no success so far...
it is beyond my understanding... probably problem with SQL Server Instance
|
|
|
|
|
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
|
|
|
|
|
Since You Have Given DataType As Char For AddressCode and AddressType, It will store Only one char. Better user Varchar(size) in the creation of Store proc, It will Execute fine and u can see what u have inserted.
|
|
|
|
|
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 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.Add overload you're using returns a SqlParameter, so you can either type .Value = value; immediately after your closing parenthesis, or store the return value from Add to a SqlParameter variable 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
|
|
|
|
|
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.
|
|
|
|
|
I have a struct explicitly defined in C# as
[
Guid("FE9814A3-D198-4bd0-8475-D055DAA7C659"),
StructLayout(LayoutKind.Sequential)
]
public struct _alloc
{
public _alloc(short a,short b)
{
sx= a;sy=b;
}
public short sx;
public short sy;
}
The following struct contains a pointer member to _alloc in COM IDL
typedef
[
uuid(649B2720-373B-46a4-BC1F-6B53CC38755D),
version(1.0),
helpstring("A Demo struct typedef for Marshal")
]
struct _groupalloc {
_alloc* lx;
short ly;
} _GroupAlloc;
I tried to define it as follows in C#
[
Guid("649B2720-373B-46a4-BC1F-6B53CC38755D"),
StructLayout(LayoutKind.Sequential)
]
public struct _groupalloc
{
public _alloc[] sx;
public short sy;
}
This is to be used in a method definition as follows,to marshal it to COM
int GetComplexStruct(ref _groupalloc _pc);
I get the runtime error - 'cannot marshal field sx of _groupalloc - it cannot be marshalled as member of struct'.
What is the principle behind this?Why cant an array be marshalled as member of a struct - if someone could give pointers to related articles too,apart from the answer to the above;that would be appreciated.
thanks and regards.
|
|
|
|
|
You could declare the member as an IntPtr, then use GCHandle and the Marshal class (specifically it's PtrToStructure method).
If you want to marshal an array, you must also use the MarshalAsAttribute to provide additional information, like the size or size param. When marshaling from unmanaged to managed code, the size of an array is known so this information is needed for the marshaler to construct the managed array.
For more information on marshaling, read Interop Marshaling[^] in the .NET Framework SDK.
If you want more information, read about the classes and methods I mentioned above and continue this thread if you have additional questions.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
Thanks for the response.
I thought that when i am marshalling an array allocated from managed code - i need not give the siz parameter ,as the CLR knows the bounds anyway.
Also in the example i had given ,if I just marshal a _alloc* to COM as in
int GetStructandModify(ref Location _ps);
It just works fine .
However,when it is a member of a struct - it is not marshalled.
Is it something to do with value and reference type?-it would be very helpful if you could clarify this part.
Also ,I tried to marshal it as IntPtr using GCHandle.Alloc and Marshal.AllocCoTaskMem - it works successfully in either case.Also I think GCHandle uses managed memory while AllocCoTaskMem would use the native heap - but I am not sure how to decide which one to be used.
Is there some advice available on parameters to decide whether to use managed or unmanaged memory.
thanks.
|
|
|
|
|
From managed to unmanaged code, the size param is not needed; but, as I said, when going from unmanaged to managed it is because an array is nothing more than an allocated block of memory with no type-safety.
Use GCHandle (pinning the object when it's a reference object) instead of Marshal.AllocCoTaskMem. Use that mainly when COM request the use of CoTaskMemAlloc. A GCHandle pins the object in memory so the GC doesn't move it around, which is what you want here. All you have to do is free the GCHandle, instead of calling Marshal.FreeCoTaskMem. This unpins the object and allows the GC to move it around again.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
Thanks for your advice.
So when a structure pointer is a member of another structure,can i also marshal the pointer member using marshalas too ,instead of using instptr.If so - can you give an example ,as all my attempts have failed.
Also I am yet not clear when I should prefer allocating an object into unmanaged heap(using marshal AllocCoTaskMem) ,rather than pinning it into managed heap and passing the address to unmanged code.
Sorry for the trouble.
|
|
|
|
|
It's already allocated - why allocate it again? For a reference object, all you need to do is pin it in memory (a value type doesn't need (and can't be) pinned since it will not be moved by the GC, although it could be reclaimed so still use a GCHandle or HandleRef to reference it).
You could try:
[StructLayout(LayoutKind.Sequential)]
internal struct S1
{
public int x;
public int y;
}
[StructLayout(LayoutKind.Sequential)]
internal struct S2
{
[MarshalAs(UnmanagedType.LPArray, SizeParamIndex=1)]public S1[] arr;
public int size;
}This will help the marshaler create the array when marshaling from unmanaged code.
Otherwise, you can use the GC handle to pin the S1[] array in memory till after the P/Invoke method returns:
S1[] arr = new S1[];
GCHandle handle = GCHandle.Alloc(arr, GCHandleType.Pinned);
S2 s2 = new S2();
s2.arr = (IntPtr)handle;
s2.size = arr.Length;
CallMyPInvokeMethod(ref s2);
handle.Free(); It's crude, but should hopefully give you a good idea.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
If i try to marshal the pointer element using
[MarshalAs(UnmanagedType.LPArray, SizeParamIndex=1)]public S1[] arr;
as you suggested - I am back to the
"This type cannot be marshalled as a structure field" error,which formed my original question.
|
|
|
|
|
Which is why I gave you the alternative to try below that.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
I have a C# program the asks the user for a UserID, Password, and Domain and does a WinNT Active Directory query to return a list of groups the specified user belongs to. I start the program and enter user1/password 1 on domain 1 and it works just fine. But when I (in the same invocation) try to enter user2/password2 on domain 1 I get the following COM exception:
"Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed....."
I've tried closing and disposing the DirectoryEntry object after each use but to no avail. What do I have to do (close) to allow my program to do 2+ separate Active Directory queries for 2+ userid/password combos in the same invocation??
TIA,
Matt
|
|
|
|
|
The credential cache has to be cleared, and I'm not sure you have any control over this. The default expiration time is around 15 minutes. This is a function of Windows and not .NET.
The best solution is to not emulate users but to - if possible - log in as a user with administrative rights (or enough privileges to enumerate group membership) and query the groups for a user. This would require only 1 login and would allow you to query as many users as you want. This is the way that most programs (including the management tools in Windows) work.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
Now, I don't now anything about encoding... Now lets take this text for example. It's "regular" encoding, correct? Now take this @ for example. Is the @ sign something special?
/\ |_ E X E GG
|
|
|
|
|
yeah, it's big and bold. Perhaps you need to reword your question ?
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
I don't understand... 
/\ |_ E X E GG
|
|
|
|
|
The @ is 'special' in that it's been made larger and bold. Your question was somewhat enigmatic. That's why I suggested you reword it.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
ok, thank you.
/\ |_ E X E GG
|
|
|
|
|
Encoding refers to the way that bits are translated to characters. What you refer to as "regular encoding" is ASCII, which is a 7 bit encoding allowing a total of only 128 characters. Unicode encodings (UTF7, UTF8, UTF16) are more flexible, and can represent far more characters. The numbers in the UTF codes refer to the minimum number of bits that are used to represent a single character.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
