Inserting data into MSSQL from Matlab

Question

I am trying to insert data into MSSQL through matlab and i am unable to pass the string values to the table.Here is my code:

username = get(handles.edit1,'String'); %edit1 being Tag of ur edit box
password= get(handles.edit2,'String'); %edit1 being Tag of ur edit box
confirm_pass = get(handles.edit3,'String'); %edit1 being Tag of ur edit box
email = get(handles.edit4,'String'); %edit1 being Tag of ur edit box
import System.Data.SqlClient.*
NET.addAssembly('System.Data');
conn = SqlConnection('Data Source=.;Initial Catalog=Ear;Integrated Security=true;');
conn.Open();
cmd = SqlCommand('insert into Register(username,password,confirm_pass,email) values(@username,2,2,4)', conn);
cmd.Parameters.AddWithValue('@username', username.Value);
r = cmd.ExecuteReader();
r.Close()
conn.Close()

I am able to execute the code if i pass the values directly as follows:

cmd = SqlCommand('insert into Register(username,password,confirm_pass,email) values(1,2,2,4)', conn);

Any help would be appreciated.Thank you!

Edit:

username = get(handles.edit1,'String'); %edit1 being Tag of ur edit box
password= get(handles.edit2,'String'); %edit1 being Tag of ur edit box
confirm_pass = get(handles.edit3,'String'); %edit1 being Tag of ur edit box
email = get(handles.edit4,'String'); %edit1 being Tag of ur edit box
import System.Data.SqlClient.*
NET.addAssembly('System.Data');
conn = SqlConnection('Data Source=.;Initial Catalog=Ear;Integrated Security=true;');
conn.Open();
cmd = SqlCommand('insert into Register(username,password,confirm_pass,email) values(username,password,confirm_pass,email)', conn);
r = cmd.ExecuteReader();
r.Close()
conn.Close()

This is my Error:

Error using Register>pushbutton2_Callback (line 214) Message: Invalid column name 'username'.

Invalid column name 'password'.

Invalid column name 'confirm_pass'.

Invalid column name 'email'. Source: .Net SqlClient Data Provider HelpLink:

Error in gui_mainfcn (line 96) feval(varargin{:});

Error in Register (line 42) gui_mainfcn(gui_State, varargin{:});

Error in @(hObject,eventdata)Register('pushbutton2_Callback',hObject,eventdata,guidata(hObject))

Error while evaluating uicontrol Callback

Table: id -int username -varchar(50) password -varchar(50) confirm_passs-varchar(50) email -varchar(50)

Answer 1

In .NET, you can use two different approaches to define the parameter values of an SqlCommand:

1. Through Parameters.Add, as follows:

cmd.CommandText = "DELETE FROM Products WHERE ID = @ID";
cmd.Parameters.Add("@ID", SqlDbType.Int);
cmd.Parameters["@ID"].Value = 10;

2. Through Parameters.AddWithValue, as follows:

cmd.CommandText = "DELETE FROM Products WHERE Name = @Name";
cmd.Parameters.AddWithValue("@Name", "MyProduct");

The difference between the two approach is subtle, but very important.

Add makes everything explicit, but presents two major drawbacks:

• it requires more code and more efforts;
• one of its overloads (this one, to be exact), even if deprecated, can be very ambiguous.

AddWithValue, on the opposite, infers the type of the parameter from the paramater value and doesn't perform any kind of validation. The latter approach can potentially lead to incorrect conversions, especially if used within an environment that handles variable types in a different way (like Matlab).

Your code is not hard to understand, but you forgot to describe your database table structure and to mention any potential error or exception produced by the code execution. This forces me to guess what's wrong with your implementation:

1. The most probable reason has to be identified in what I explained above: AddWithValue (with its implicit types) used within a non .NET programming environment looks like a very dangerous combo to me.
2. The table fields you are referencing (username, password, confirm_pass and email) look like VARCHAR fields. Despite this fact, your insertion is based on numeric values. I know this should not represent a problem, since a numeric string can be assigned to a VARCHAR field, but I think that, in the first case (VALUES (@username,2,2,4)), something weird could be happening.
3. Although get(handles.edit1,'String') looks absolutely correct, I don't understand why you are passing username.Value to the AddWithValue call. Once get(handles.edit1,'String') is fired, your username variable should already contain the required value.

Answer 2

Actually the values obtained from GUI through edittext is in cell format.So i converted it to the string and then the query is executed.

Here is my answer:

username = get(handles.edit1,'String'); %edit1 being Tag of ur edit box
password= get(handles.edit2,'String'); %edit1 being Tag of ur edit box
confirm_pass = get(handles.edit3,'String'); %edit1 being Tag of ur edit box
email = get(handles.edit4,'String'); %edit1 being Tag of ur edit box
username=username{1};
password=password{1};
confirm_pass=confirm_pass{1};
email=email{1};
import System.Data.SqlClient.*
NET.addAssembly('System.Data');
conn = SqlConnection('Data Source=.;Initial Catalog=Ear;Integrated Security=true;');
conn.Open();
testpath='insert into Register(username,password,confirm_pass,email) values';
open='(''';
close=''')';
string1 = strcat(testpath,open,username);
string2 = strcat(string1,''',''',password);
string3 = strcat(string2,''',''',confirm_pass);
string4 = strcat(string3,''',''',email);
string5 = strcat(string4,close);
disp(string5);
cmd = SqlCommand(string5, conn);
cmd.ExecuteNonQuery();
conn.Close()