-1

I try to make a CLR in C# to use in SQLServer Management Studio 2014. Everything's working fine right now, unless I start to insert parameters.

How do you insert a parameter in the CLR and use it in a PROC? I tried to put the parameters in the function parentheses:

public static void InsertInto(SqlString street, SqlInt32 number)
    {
        using(SqlConnection conn = new SqlConnection("context connection = true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "INSERT INTO TABLE_CLRTEST (COLRW_STREET, COLRW_NUMBER) VALUES("+street+","+number+")";

I cannot seem to concatenate the SqlString, and it won't let me use a String either.

And in C# and I tried like this:

comm.CommandText = "INSERT INTO TABLE_CLRTEST (COLRW_STREET, COLRW_NUMBER) VALUES(@street,@number)";

            comm.Parameters.Add("@street", SqlDbType.VarChar,50);
            comm.Parameters.Add("@number", SqlDbType.Int);

When I use this is get the following error: CREATE PROCEDURE failed because parameter counts do not match. So still nothing.

Do you guys/girls know what is going on? If you need clarification, just ask.

Thanks in advance

EDIT: The code snippet provided below is working. It seems that Strings just won't work.

public static void updateTest(SqlInt32 lo, SqlInt32 la)
    {
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
conn.Open();
                SqlCommand update = new SqlCommand();
                update.CommandText = "UPDATE TABLE_CLRTEST SET COLRW_LONGITUDE = " + lo + ", COLRW_LATITUDE = " + la + "";
                update.Connection = conn;
                update.ExecuteNonQuery();
                conn.Close();
}
}
user3208216
  • 84
  • 1
  • 8
  • _I cannot seem to concatenate_ You can. You just need to use `'` for character values (in your case it is `street`). But parameterized queries are always prefered. BTW, what is `CREATE PROCEDURE` anyway? – Soner Gönül Sep 24 '14 at 12:30
  • Plese, show us your script for creating 'wrapper' of clr procedure as well – Andy Korneyev Sep 24 '14 at 12:32
  • Try `AddWithValue` e.g. `comm.Parameters.AddWithValue("@street", street)` – Dmitry Bychenko Sep 24 '14 at 12:32
  • Is this your complete code? Your `INSERT` command using parameters (which is highly recommended rather than your first one) looks like fine to me. – Edper Sep 24 '14 at 12:33
  • @SonerGönül I don't get it. Should I do ...`VALUES("+'street'+","+number+"`...? Because that doesn't seem to work, either. CREATE PROCEDURE is something in SQLServer which can be used to run a certain piece of TSQL code. – user3208216 Sep 24 '14 at 12:36
  • @Edper This is kind of my complete code, since the other part is the setup (connection and such, which works. I tested that by using zero parameters). I really don't know why it is not working with the `Parameter.Add`, though – user3208216 Sep 24 '14 at 12:40
  • @DmitryBychenko Like this? `comm.CommandText = "INSERT INTO TABLE_CLRTEST (COLRW_STREET, COLRW_NUMBER) VALUES(@street,@number)"; comm.Parameters.AddWithValue("@street", street);` This doesn't seem to work as well. At the beginning of the function I declared both street and number, but code in the comments section looks kinds crap. – user3208216 Sep 24 '14 at 12:41
  • See my answer you have to single quote string or do it with a parameter. – paparazzo Sep 25 '14 at 12:45

2 Answers2

0
comm.CommandText = "INSERT INTO TABLE_CLRTEST (COLRW_STREET    , COLRW_NUMBER)  
                                        VALUES('" + street + "'," + number +" )";

In your parameters you are not assigning a value.

comm.Parameters.Add(
   "@street", SqlDbType.VarChar,50).Value = street;
paparazzo
  • 42,665
  • 20
  • 93
  • 158
  • Is the value that what is assigned to the variable, or is the value the name of the variable here? It seems weird to give it a value, when you're changing it afterwards. – user3208216 Sep 25 '14 at 07:19
  • That is two options and yes toasters would be the variable. And it that code is not changing it later. – paparazzo Sep 25 '14 at 12:44
-1

I forgot the Add statement. Final code:

SqlCommand command = new SqlCommand();
SqlParameter pstreet = new SqlParameter("@street", SqlDbType.VarChar, 150);
command.CommandText = "INSERT INTO CHIEF_TEST.dbo.TABLE_CLRTEST(COLRW_STREET) VALUES(@street)";
command.Parameters.Add(pstreet);
user3208216
  • 84
  • 1
  • 8