Sorry..but I got 1 question here... How can I close the database connection? Inside this video.. You did open the connection inside a constructor... But how can I close the connection sir? Any suggestion on how can I close the connection by the method inside the video?
Really great videos. Thank you so much for putting these up. I did get an error when connecting to MySql that "The host localhost does not support SSL connections.”". Just needed to add "SslMode=none" to the connection string.
Hi,can you help me? When I run the program, there is an exception about " System.NullReferenceException " Can you tell me what can I do about this? I have been searched for many ways to solve the problems,but there is no useful to it.
Hello Jon, thanks fo a really nice series of tutorial on REST. I tried to access the web server from another device using the IP address instead of localhost. But, it returns error 400 invalid hostname. I wonder if you could share a workaround for this issue? Thanks
Your videos are awesome, thanks for sharing! I do have 1 question, I've been using MS SQL Server as my DB engine and now moving towards MySQL - So my question is, should the connection to the database be closed after the POST has been executed? When using MS SQL, I used to close the connection, or I wrapped the code inside a using statement which automatically closes the connection when done. Thanks for the help! :)
Hi Oerman, Yes you should close the connection. If you use connection pooling you will cache connections so that the performance hit on opening and closing connections all the time is less of an impact. Thanks for watching! Jon
please i am getting an unhadeled exception at the command.executenonquery and it says that connection should be vaid and open, but i have opened it according to the steps you gave.
Hi Lois, You will need to test your connection. Unless you have your database set up exactly like mine - especially with user accounts it will be different. If you are using MySQL use MySQL Workbench to verify you can connect to the database. I hope that helps - thanks for watching! Jon
Hey, I followed the video but when i start debugging it sais: "Column count doesn´t match value count at row 1" any idea why? It happens when i try to execute cmd.ExcecuteNonQuery();
Alright I fixed it by changing the sqlString to the same but then using instead of the VALUE('" + personToSave.LastName... etc., I used VALUE(@FN, @LN... etc., then after cmd = new MyS... etc. Add: cmd.Paramaters.AddWithValue("@FN", personToSave.FirstName);... etc.
Hi, When I went to post, I was following step by step in Visual Studio, but after the line "cmd.ExecuteNonQuery();". it appeared an invalid operation dialog with this text "Connection must be valid and open."
Hi Eduardo, Thanks for watching! It sounds like your database connection did not connect properly. You may have an issue with your database connection string or database. I assume you are using a local database? Jon
Yes, I'm using a local database. What datatype should I use for ID in the database? Because I tested the database connection to Visual Studio and it works well.
Hi Eduardo, I am using an int set to auto increment with a primary key. Here is the SQL: tblpersonnelIDCREATE TABLE `tblpersonnel` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `FirstName` varchar(50) DEFAULT NULL, `LastName` varchar(45) DEFAULT NULL, `PayRate` decimal(10,2) DEFAULT NULL, `StartDate` datetime DEFAULT NULL, `EndDate` datetime DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `ID_UNIQUE` (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; Hope that helps! Jon
Hi Jon, That was the problem, I didn't set auto increment, it works now. I just started to learn REST services. To be honest, it is confusing when it's your first time, but you made it easier. Thanks for sharing.
Hi, can you help me? when a do response.Headers.Location = new Uri(Request.RequestUri, string.Format("Admin/{0}", id)); i get localhost:55129/api/Admin/Admin/1 when a do response.Headers.Location = new Uri(Request.RequestUri, string.Format("/{0}", id)); i get localhost:55129/api/1 it should be localhost:55129/api/Admin/1 any idea?
Hi Jon, great videos to learn from, really good. i am using Microsoft sql server, what can i use in place of "LastInsertedId;" as SQL Server does not have that, i am trying cmd.ExecuteScalar(); but i am getting a null reference exception, but my data is still going in my database. also can you please do a video on paging when you retrieve all records from a database, thank you Jon.
Hi, I switch over to using SQL Server in this video: ua-cam.com/video/PJHyjBrGI-g/v-deo.html. I'll look at doing one on paging. Thanks for watching! Jon
Problems with Comma and Dot. I assume it is because my language is set to German. But if you write a double like 32.12 in the querryString it becomes 32,12 (notice the dot becomes a comma). This led to some confusion since the database treats these now as two sepperate values and therefore generates an error "column count doesn't match value count at row n". Maybe this helps someone. Cheers Nice video series btw.
Could you make this POST request with xml formated object? I'm getting null in the value parameter of My Controller Thank you in advance and good series about REST :) Best regards
thank you for your answer once again, anyways I managed to solve the problem :D All I had to do was add some bindings to the IIS configuration file, allow some ports on the firewall and it works as a perfect machine. Thank you for your time. Keep the good videos. Very informational and easy to understand.
Hi Jon 5:51 i translated your example to vb.net : "POST: api/openWhere Public Function PostValue( ByVal value As myDevice) As HttpResponseMessage Dim x As String = value.data Dim response As New HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created) response.Headers.Location = New Uri(Request.RequestUri, String.Format("resultSet/{0}", x)) Return response End Function really working fine and give this ...in the result set i get exact the data i expect to get: localhost:50418/api/resultSet/5C:CF:7F:B3:03:6E;-46;11;9c:b6:d0:17:e3:a9;WLAN-4F8C20%0A5C:CF:7F:B3:03:6E;-46;11;9c:b6:d0:17:e3:a9;WLAN-4F8C20%0A5C:CF:7F:B3:03:6E;-45;11;9c:b6:d0:17:e3:a9;WLAN-4F8C20%0A5C:CF:7F:B3:03:6E;-45;11;9c:b6:d0:17:e3:a9;%0A5C:CF:7F:B3:03:6E;-45;11;9c:b6:d0:17:e3:88;" i was looking arround for some hrs now ..and THIS example seem to be my solution.. For what i was looking for? the client application should fire some simple SQL ( via this Post ) to this VS 2017 Rest Application. i retrieve this data from the DB..And send the result back via a response-Message. Exactly like done in your example!! Now i am wondering if this is ok from a Rest-API-design-perspective?. I get the ResultMessage in "location"!? here i am swimming a bit if from a design perspective i am on the right trail regards kristina
Hi Kristina, If you mean sending SQL from the client I would not recommend it as it could leave you vulnerable to a SQL Injection attack. I'm working on a more complex example. The purpose of setting the value in the in the Header is to give pack the auto-generated ID. I hope this helps. Thanks for the question and thanks for watching! Jon
Hi Jon - sure u´r right...injection and stuff...ive done it the other way..i created a new Rest controller in VB.net for the URL and task...i guess this is the proper way ( hope so ;- ) )...the application is very robust the moment ...this is fine the moment
Sorry..but I got 1 question here... How can I close the database connection? Inside this video.. You did open the connection inside a constructor... But how can I close the connection sir? Any suggestion on how can I close the connection by the method inside the video?
Really great videos. Thank you so much for putting these up. I did get an error when connecting to MySql that "The host localhost does not support SSL connections.”". Just needed to add "SslMode=none" to the connection string.
Hi Suren,
Thanks for the tip and for watching!
Jon
Hi,can you help me?
When I run the program, there is an exception about " System.NullReferenceException "
Can you tell me what can I do about this? I have been searched for many ways to solve the problems,but there is no useful to it.
Hi,
On what line of code is it throwing the exception?
Jon
The line of code is to insert data to MySQL
Hi,
Make sure all the values you are sending to the database are populated.
Jon
Hi i have the same problem, did u resolve it ??
Hello Jon,
thanks fo a really nice series of tutorial on REST.
I tried to access the web server from another device using the IP address instead of localhost. But, it returns error 400 invalid hostname.
I wonder if you could share a workaround for this issue?
Thanks
Your videos are awesome, thanks for sharing! I do have 1 question, I've been using MS SQL Server as my DB engine and now moving towards MySQL - So my question is, should the connection to the database be closed after the POST has been executed? When using MS SQL, I used to close the connection, or I wrapped the code inside a using statement which automatically closes the connection when done. Thanks for the help! :)
Hi Oerman,
Yes you should close the connection. If you use connection pooling you will cache connections so that the performance hit on opening and closing connections all the time is less of an impact.
Thanks for watching!
Jon
Jon Jensen perfect thanks very much for clearing up for me :D happy coding
please i am getting an unhadeled exception at the command.executenonquery and it says that connection should be vaid and open, but i have opened it according to the steps you gave.
Hi Lois,
You will need to test your connection. Unless you have your database set up exactly like mine - especially with user accounts it will be different. If you are using MySQL use MySQL Workbench to verify you can connect to the database.
I hope that helps - thanks for watching!
Jon
Hey, I followed the video but when i start debugging it sais: "Column count doesn´t match value count at row 1" any idea why? It happens when i try to execute cmd.ExcecuteNonQuery();
Alright I fixed it by changing the sqlString to the same but then using instead of the VALUE('" + personToSave.LastName... etc., I used VALUE(@FN, @LN... etc., then after cmd = new MyS... etc. Add: cmd.Paramaters.AddWithValue("@FN", personToSave.FirstName);... etc.
Hi Thomas,
Thanks for clarifying and for watching!
Jon
great tutorial
Hi Khalid,
Thanks for watching!
Jon
Jon Jensen
its really great taturial .. hope give example for uploading file using api .. and taturial for mvc .. thanks a lote
Hi,
When I went to post, I was following step by step in Visual Studio, but after the line "cmd.ExecuteNonQuery();". it appeared an invalid operation dialog with this text "Connection must be valid and open."
Hi Eduardo, Thanks for watching! It sounds like your database connection did not connect properly. You may have an issue with your database connection string or database. I assume you are using a local database?
Jon
Yes, I'm using a local database. What datatype should I use for ID in the database? Because I tested the database connection to Visual Studio and it works well.
Hi Eduardo, I am using an int set to auto increment with a primary key. Here is the SQL:
tblpersonnelIDCREATE TABLE `tblpersonnel` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(50) DEFAULT NULL,
`LastName` varchar(45) DEFAULT NULL,
`PayRate` decimal(10,2) DEFAULT NULL,
`StartDate` datetime DEFAULT NULL,
`EndDate` datetime DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID_UNIQUE` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Hope that helps!
Jon
Hi Jon,
That was the problem, I didn't set auto increment, it works now.
I just started to learn REST services. To be honest, it is confusing when it's your first time, but you made it easier.
Thanks for sharing.
Hi Eduardo, I'm glad you got it working! Keep learning!
Jon
Hi, can you help me?
when a do
response.Headers.Location = new Uri(Request.RequestUri, string.Format("Admin/{0}", id));
i get
localhost:55129/api/Admin/Admin/1
when a do
response.Headers.Location = new Uri(Request.RequestUri, string.Format("/{0}", id));
i get
localhost:55129/api/1
it should be
localhost:55129/api/Admin/1
any idea?
Hi Jorge, Is this with the examples in the videos or a general question? Thanks for watching!
Jon
Jon Jensen, hi, in this example, you have the same issue, but I did not see how do you fix it.
thanks for the answer
it happens in this video
This is taking the value of id and inserting it in place of {0}.
but I get admin/admin/id instead of admin/id
Hi Jon,
great videos to learn from, really good.
i am using Microsoft sql server, what can i use in place of "LastInsertedId;" as SQL Server does not have that, i am trying cmd.ExecuteScalar(); but i am getting a null reference exception, but my data is still going in my database.
also can you please do a video on paging when you retrieve all records from a database, thank you Jon.
It's ok, i got it... great videos Jon.
Hi,
I switch over to using SQL Server in this video: ua-cam.com/video/PJHyjBrGI-g/v-deo.html.
I'll look at doing one on paging.
Thanks for watching!
Jon
Problems with Comma and Dot. I assume it is because my language is set to German. But if you write a double like 32.12 in the querryString it becomes 32,12 (notice the dot becomes a comma). This led to some confusion since the database treats these now as two sepperate values and therefore generates an error "column count doesn't match value count at row n".
Maybe this helps someone.
Cheers
Nice video series btw.
Hi Jek,
Thanks for posting that and for watching!
Jon
Could you make this POST request with xml formated object? I'm getting null in the value parameter of My Controller
Thank you in advance and good series about REST :)
Best regards
Hi Bruno,
Yes you can. Where are you getting the null? When you examine the data?
Thanks for watching.
Jon
I decided to change my android app client to consume using JSON anyway, the only problem I got is that I'm unable to call the service by IP.
Hi Bruno,
I did a Android Client using Spring REST that might help: ua-cam.com/video/cSsNI01Py3Q/v-deo.html
Thanks
Jon
thank you for your answer once again, anyways I managed to solve the problem :D
All I had to do was add some bindings to the IIS configuration file, allow some ports on the firewall and it works as a perfect machine.
Thank you for your time.
Keep the good videos.
Very informational and easy to understand.
Hi Bruno,
Glad you got it working!
Thanks again for watching.
Jon
Hi Jon 5:51 i translated your example to vb.net :
"POST: api/openWhere
Public Function PostValue( ByVal value As myDevice) As HttpResponseMessage
Dim x As String = value.data
Dim response As New HttpResponseMessage
response = Request.CreateResponse(HttpStatusCode.Created)
response.Headers.Location = New Uri(Request.RequestUri, String.Format("resultSet/{0}", x))
Return response
End Function
really working fine and give this ...in the result set i get exact the data i expect to get: localhost:50418/api/resultSet/5C:CF:7F:B3:03:6E;-46;11;9c:b6:d0:17:e3:a9;WLAN-4F8C20%0A5C:CF:7F:B3:03:6E;-46;11;9c:b6:d0:17:e3:a9;WLAN-4F8C20%0A5C:CF:7F:B3:03:6E;-45;11;9c:b6:d0:17:e3:a9;WLAN-4F8C20%0A5C:CF:7F:B3:03:6E;-45;11;9c:b6:d0:17:e3:a9;%0A5C:CF:7F:B3:03:6E;-45;11;9c:b6:d0:17:e3:88;"
i was looking arround for some hrs now ..and THIS example seem to be my solution..
For what i was looking for?
the client application should fire some simple SQL ( via this Post ) to this VS 2017 Rest Application. i retrieve this data from the DB..And send the result back via a response-Message.
Exactly like done in your example!!
Now i am wondering if this is ok from a Rest-API-design-perspective?. I get the ResultMessage in "location"!?
here i am swimming a bit if from a design perspective i am on the right trail
regards
kristina
Hi Kristina,
If you mean sending SQL from the client I would not recommend it as it could leave you vulnerable to a SQL Injection attack. I'm working on a more complex example.
The purpose of setting the value in the in the Header is to give pack the auto-generated ID.
I hope this helps. Thanks for the question and thanks for watching!
Jon
Hi Jon - sure u´r right...injection and stuff...ive done it the other way..i created a new Rest controller in VB.net for the URL and task...i guess this is the proper way ( hope so ;- ) )...the application is very robust the moment ...this is fine the moment
Hi,
I'm glad it is working for you. Thanks!
Jon