Input to SQL Stored Procedure

Question

I am trying to grab a querystring from the URL and send it to my stored procedure in MSSQL. The querystring is of type varbinary and when i try to send it my application is throwing an exception. I also wanted to return the select statement at the bottom of my storedprocedure that simply says Select 'Processed'

+ ServerVersion 'con.ServerVersion' threw an exception of type 'System.InvalidOperationException' string {System.InvalidOperationException} — Tim, Sep 10 '12 at 19:22

score 2 · Accepted Answer · answered Sep 17 '12 at 14:42

had to actually create a function to parse hex code and then send it to the database

static byte[] ParseHexString(string value)
        {
            if (string.IsNullOrEmpty(value)) return null;
            if (1 == (1 & value.Length)) throw new ArgumentException("Invalid length for a hex string.", "value");

            int startIndex = 0;
            int length = value.Length;
            char[] input = value.ToCharArray();
            if ('0' == input[0] && 'x' == input[1])
            {
                if (2 == length) return null;
                startIndex = 2;
                length -= 2;
            }

            Func<char, byte> charToWord = c =>
            {
                if ('0' <= c && c <= '9') return (byte)(c - '0');
                if ('A' <= c && c <= 'F') return (byte)(10 + c - 'A');
                if ('a' <= c && c <= 'f') return (byte)(10 + c - 'a');
                throw new ArgumentException("Invalid character for a hex string.", "value");
            };

            byte[] result = new byte[length >> 1];
            for (int index = 0, i = startIndex; index < result.Length; index++, i += 2)
            {
                byte w1 = charToWord(input[i]);
                byte w2 = charToWord(input[i + 1]);
                result[index] = (byte)((w1 << 4) + w2);
            }

            return result;
        }

score 1 · Answer 2 · answered Sep 10 '12 at 18:21

1

If you wish Byte[] type, You can try with this code, you don't pass string

cmd.Parameters.Add("@dec", SqlDbType.VarBinary).Value = ;//Relpace with your new Byte[]

Or if you want string type, you can try with string type

cmd.Parameters.Add("@dec", SqlDbType.VarChar).Value = QS;//Your string

Link : http://msdn.microsoft.com/fr-fr/library/system.data.sqldbtype%28v=vs.80%29.aspx

answered Sep 10 '12 at 18:21

Aghilas Yakoub

28,516
5
46
51

not sure that understand what you are trying to tell me to do – Tim Sep 10 '12 at 19:04
if you want pass string value , try with : cmd.Parameters.Add("@dec", SqlDbType.VarChar).Value = QS. If you want use VarBinary pass Byte[] parameter – Aghilas Yakoub Sep 10 '12 at 19:05

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

Shouldn't you send a byte array (byte[]) for a binary? I don't think it will accept pure strings. Try converting it to byte array with System.Text.Encoding.UTF8.GetBytes method.

UPDATE: This question's answer tells to use a special type for binary data: What SqlDbType maps to varBinary(max)?

edited May 23 '17 at 12:22

Community

1
1

answered Sep 10 '12 at 18:20

Can Poyrazoğlu

33,241
48
191
389

could you provide an example sorry new to this and I was hoping that I could just pass the value as a string. Not to sure how I would code that for byte[] Thanks – Tim Sep 10 '12 at 18:55
i don't have access to visual studio right now, so it may not work well, but you may try: `byte[] buf = System.Text.Encoding.UTF8Encoding.GetBytes(yourString); cmd.Parameters.Add("@dec", SqlDbType.VarBinary).Value = buf;` – Can Poyrazoğlu Sep 10 '12 at 19:47
Thanks for the response does UTF8Encoding only work with .net 4.5 though? Seems like it does not exist for me. – Tim Sep 10 '12 at 20:18
sorry yep just got it... appreciate all your help – Tim Sep 10 '12 at 20:36

score 0 · Answer 4 · answered Sep 11 '12 at 20:46

0

Aghilas has specified how to assign value as byte array, and in the second line and the regular way of passing as values

answered Sep 11 '12 at 20:46

Praveen

100
8

Input to SQL Stored Procedure

4 Answers4