Search the Community

As an intern I was given a project to go through to try and understand the developer's code, and I've run into a problem: I have two combo boxes that is populated from sql tables. The only problem is that if nothing is selected (which is possible), I get NullReferenceError: Object variable or With block variable not set. I want to add an extra item to the combo box which says something like: "Please select", this will be displayed when no item is selected. Here is some of the developers code: suburb.DataSource = DB.ReturnDataTable("Select ID,Suburb + ' - ' + City as Info1, Suburb as Info from tblG2O_PostalInfo order by Suburb") suburb.DisplayMember = "Info" suburb.ValueMember = "ID" That is where the suburb combo box is populated. ^^ (DB.ReturnTable is a custom method in a vb page, here is the code of that method: Public Function ReturnDataTable(ByVal sSQL As String) As DataTable Try Dim oConn As New OleDbConnection(DB_DSN) Dim retData As New DataTable Dim oRS As OleDbDataReader Dim oCmd As New OleDbCommand(sSQL, oConn) oConn.Open() oRS = oCmd.ExecuteReader() If oRS.HasRows = True Then retData.Load(oRS) End If oRS.Close() oRS = Nothing oConn.Close() Return retData Catch ex As Exception Trace("ReturnDataTable Error: " & sSQL & "<br>" & ex.Message) Return New DataTable End Try End Function ) Dim strsuburb As String = Trim(suburbc.SelectedItem("Info").ToString()) And that is where the selected suburb is assigned to a string. This string is used to update an existing table. This is the line where I'm having trouble. I have tried: suburbc.Items.Insert(0, "*Please select*") After the databind, but then I get a completely different error and if I do it before the databind, it's just overridden. I've also tried using statements to check if the value returned is Null, but I studied in C# and Java, and am quite new to VB and the ways I've tried to do this haven't worked. My current employer prefers us all to work in VB. Any solutions, tips or ideas will help. Thank you. =)

Hi, I'd like to send SMS messages from a database to my customers. I found this tip at http://www.ozekisms.com/index.php?owpn=400 and I'd like to know if it's relevant to my project. One of the options you can choose to add SMS functionality to your ASP project is to use an SQL SMS gateway architecture. In this architecture, the ASP enabled website uses a database server (Microsoft SQL Server, Oracle or MySQL) for data storage. To add SMS functionality in this case all you have to do is create two database tables, one for sending SMS messages and one for receiving SMS, in the database server and use these tables in an SQL SMS Gateway configuration. In this case the SQL SMS Gateway configuration of Ozeki NG SMS Gateway will work the following way: There are two database tables. One will be used for receiving SMS messages (ozekimessagein) and another will be used to send SMS messages (ozekimessageout). If an SMS message is received by the system Ozeki NG will insert a record into ozekimessagein. Your ASP application can read the ozekimessagein database table and check for incoming messages. If you want to send a message, you can use the ozekimessageout table. You can insert a record into ozekimessageout. Ozeki NG SMS Gateway will periodically check your outgoing database table using an SQL SELECT and if it finds a message to be sent it will send it. I�d appreciate any response. Thank you. Max

I am trying to implement a Login validation using C# 2005 in ASP.net 2.0 web application. The SQL Server database contains a table named "UserList" with columns LoginId, Password and Role. The Login webform should authenticate the LoginId and password and depending upon the Role assigned to that user/visitor should redirect to a specific webform with a pre-defined menu options. The role might be Admin, DEO, Accounts or Member. How should I implement it? I have tried the following: protected void Login1_Authenticate(object sender, AuthenticateEventArgs e) { try { string uname = Login1.UserName.Trim(); string password = Login1.Password.Trim(); int flag = AuthenticateUser(uname, password); if (flag == 1) { e.Authenticated = true; Login1.DestinationPageUrl = "~/MenuAdmin.aspx"; } else if (flag == 2) { e.Authenticated = true; Login1.DestinationPageUrl = "~/MenuDEO.aspx"; } else if (flag == 3) { e.Authenticated = true; Login1.DestinationPageUrl = "~/MenuAccts.aspx"; } else if (flag == 4) { e.Authenticated = true; Login1.DestinationPageUrl = "~/MenuMember.aspx"; } else { e.Authenticated = false; } } catch (Exception) { e.Authenticated = false; } } private int AuthenticateUser(string uname, string password) { int bflag = 0; string connString = ConfigurationManager.ConnectionStrings["LoginDemoConnString"].ConnectionString; string strSQL = "Select * FROM UserList where ULoginId ='" + uname + "' AND UPassword ='" + password + "'"; DataTable dt = new DataTable(); SqlConnection m_conn; SqlDataAdapter m_dataAdapter; try { m_conn = new SqlConnection(connString); m_conn.Open(); m_dataAdapter = new SqlDataAdapter(strSQL, m_conn); m_dataAdapter.Fill(dt); m_conn.Close(); } catch (Exception ex) { dt = null; } finally { //m_conn.Close(); } if (dt.Rows.Count > 0) { if (dt.Rows[0][3].ToString() == "Administrator") bflag = 1; else if (dt.Rows[0][3].ToString() == "DEO") bflag = 2; else if (dt.Rows[0][3].ToString() == "Accts") bflag = 3; else bflag = 4; } return bflag; }

Quick Guide to Parametrising SQL Jump to the bit on using parameters Often when interacting databases we need to provide information to the database that can only be obtained at runtime (this could be from user input, other programmatic means or similar). Two potential approaches to this problem involve either parametrising the database code or relying on string concatenation. In this post I hope to show why concatenation is bad and parameters are good. To keep things simple I am using the simplest code I can and deliberately ignoring non-essential error handling, for similar reasons I am also choosing not to use stored procedures. I am choosing Northwind as the database as this is available to anyone with SQL or MS access but the ideas will convert to other database platforms. Another point to bear in mind is these problems will occur with any form of string concatenation be they String.Format, StringBuilder or some other means. Why not parametrising is bad A typical example of how to perform a query based on user input could look like the following (the example has a simple form with a button, textbox and datagrid - should work in either a web or windows environment) For sql server Dim conn As New SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=true") Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like '" & TextBox1.Text & "%'" Dim cmd As New SqlCommand(sql, conn) Dim ds As New DataSet Dim da As New SqlDataAdapter(cmd) debug.WriteLine(sql) 'Lets us see the sql just before it is executed! da.Fill(ds) DataGridView1.DataSource = ds.Tables(0) 'DataBind 'Uncomment this line if a web application or for access Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Nwind.mdb;Persist Security Info=True") Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like '" & TextBox1.Text & "%'" Dim cmd As New OleDbCommand(sql, conn) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) Debug.WriteLine(sql) da.Fill(ds) DataGridView1.DataSource = ds.Tables(0) 'DataBind 'Uncomment this line if a web application To see the application work try entering a value of chef into the textbox and see the resulting values. Notice this works and is simple, the initial reaction is therefore to use this method. notice the line Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like '" & TextBox1.Text & "%'" is the one that builds the query by concatenating the contents of a textbox with a sql string, it is this concatenation that causes our potential problems. Problem 1 Maybe not a big problem but consider how complex the string building will get if the query is a multiple table join, with multiple textboxes that get concatenated into the sql query. How about if my code needs to use the " character? All of these situations can result in a piece of code spanning multiple lines, maintenance is not it's strong point ;) If this runs but the results are wrong how do we pinpoint the problem - is the SQL correct but the code we are using to build it wrong; is the SQL wrong but at least we are building it correctly; both bits of code are wrong and we really have our work cut out. Problem 2 To get a little more adventurous try searching for the string Chef Anton's and suddenly our system isn't running as smoothly... If you look at the resultant SQL (as displayed in the output window by the Debug.WriteLine(sql)) it looks like SELECT ProductID, ProductName FROM Products WHERE ProductName Like 'Chef Anton's%' Notice the end of the statement 'Chef Anton's%' contains invalid sql due to the single ' character contained within our textbox. If we need to search / insert etc. data that may contain this character we now have a major problem to contend with. Problem 3 change the sql assignment to 'sql server users use Dim sql As String = "SELECT FirstName,LastName FROM Employees WHERE HireDate ='" & TextBox1.Text & "'" 'oledb people use Dim sql As String = "SELECT FirstName,LastName FROM Employees WHERE HireDate =#" & TextBox1.Text & "#" and now try querying the value 17/10/1993 and see the results, also try 10/17/1993 and compare. I would like to tell you the results but there are no guarantees here. On my pc either value worked on the oledb version, while 17/10/1993 throws an exception and 10/17/1993 works against sql server. This is a result of us passing strings to the database that the database then needs to interpret correctly. Differences in client / server locale settings and user input formats can all cause problems here. Problem 4 Firstly if you are using Access then inherent limitations of it's SQL engine prevent this problem occurring, for most other databases however this is a valid and very dangerous problem. Let's revert back to the original sql string Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like '" & TextBox1.Text & "%'" and we will try something a little more fun. We already know that putting a single ' into the text box causes an error because the code then adds an additional ' to the end. Therefore if we search for a very simple - but invalid string like x' we will see the same error as before with the resultant sql being SELECT ProductID, ProductName FROM Products WHERE ProductName Like 'x'%' Notice if we change the textbox contents to x' -- then the resultant sql looks like SELECT ProductID, ProductName FROM Products WHERE ProductName Like 'x' --%' although this doesn't return any data neither is it crashing! By commenting out the extra ' character we now have well formed sql code again. Now enter the following into the text box and see what happens x' INSERT Employees (FirstName,LastName) VALUES('get here?','How did this') -- this gives us a resulting sql string of SELECT ProductID, ProductName FROM Products WHERE ProductName Like 'x' INSERT Employees (FirstName,LastName) VALUES('get here?','How did this') --%' What would you expect to happen if you ran this against your server? If you have been following along you have just ran this against your server - go have a look in the Employees table and check the last entry ;) If the above points haven't convinced you that string concatenation is bad then I'm probably fighting a losing battle and you might as well ignore the rest of this post. If you have encountered any of the above you may have also come across means of preventing them (detecting invalid characters, regular expressions, encoding and decoding characters etc.) these may work but can often involve more effort than simply doing things correctly in the first place.