Sql Server - Handling Null Strings When Concatenating Select Statement?
Nov 9, 2011
I have this simple-minded query:
select a.str_number+' '+a.str_name+' '+a.str_suffix+' '+a.str_apt AS addr from mytable
This query works as long as none of the concatenated values are not NULL. If any is null, the address does not show.
I tried this:
select IsNULL(a.str_number+' '+a.str_name+' '+a.str_suffix+' '+a.str_apt AS addr,'') from table
What I would like to accomplish is to replace NULL with empty space if the value is null but I still get no values.
View 3 Replies
ADVERTISEMENT
Apr 3, 2012
My code seems to behave a way I don't understand. Sometimes it returns null as expected and sometimes it returns a value when its supposed to be null. How can someone explain this? Here is the code that selects from access and displays the result in a messagebox:
cmd5.Connection = cnn
cmd5.CommandText = "SELECT receipt_num " & _
"FROM brought_coffee, farmer where crop_year= " & yr & " and " & _
"brought_date=#" & dtt & "# and farmer_centre='" & ctr & _
[code]....
View 1 Replies
Aug 14, 2009
In my vb.net application if I have a known set of Error Strings i.e.
Failed because I don't know about it
Failed because I know about it but I can't find it
Failed for another reason
[code].....
View 6 Replies
Dec 21, 2011
I develop in VB and often I'm writing long TSQL statements in SSMS but getting them into VB is a bit of a pain. I like SSMS during development because I can change the query / structure and test it when there are problems. Some of them become stored procedures and some of them I just keep in the code.I like keeping them in the code because when I have a problem with something, I can GREP my projects directory and find the code that does something (that I've forgotten) or someone asks something about how something works, I can look it up. I've yet to find a GREP for stored procedures. Is there such a thing?I've read in a "best practices" white paper how we're not supposed to concatenate super long strings with & _ but a hundred line proceure is a pain to nest in
sb.AppendLine(" WHERE [q3] IS NULL "
sb.AppendLine(" AND [q4] > 2
"
[code]....
C# handles these things much better than VB. I'm wondering how I can keep my code together during development and searchable later. I had my hopes up when VS 2010 offered implicit line continuation, that this would help my multi line SQL procedures. Again, I was mistaken. Perhaps I could write it all on one line. I'd be interested in knowing if there's a feature that I am not aware of. Well there are certainly many features which I'm not aware of, but I was referring to "long strings".
View 1 Replies
Apr 12, 2010
I once again feel so angry about VB .NET . Here's what ruined my day :Lets say we have have a string variable :
Dim Var1 As String = "blablabla"
If we want to replace something within it , we can use the Replace function :
Dim Var2 As String = Replace(Var1,"a","o")
This works great . My problem appears when I want to replace the " character (double quote) . Thus , when using :Dim Var2 As String = Replace(Var1,"""","o")it will return an error .Of course you may wonder why I care to replace the " character . You see this string variable receives its value from a text box . Thus , if the user uses the " character , I want to replace it with two ' characters (single quote) . Is this replacement possible in .NET
View 4 Replies
Jun 25, 2011
I have a bunch of tables who share the same kind of information, but each table has a different name for each. (There was no standardization on the naming scheme). Some tables have important data that others do not contain. Some tables that have shared important data, but they are null values. I'm writing a program to combine all the tables into one master table.
The problem is when I read in a field, the reader.getvalue(i) is null, and I cannot use that value to insert into my new master table, obviously. The null values are so random, and I cannot write IF THEN statements for every single field for every single table in the database. I'd have thousands of lines of code constantly checking for nulls, and then trying to figure out what to do with them. What can I do to convert these reader values from NULL into something VB/SQL can handle? ere is the code so you can get a picture of how I'm approaching this problem:
[Code]....
View 3 Replies
Dec 22, 2009
I have a sql query which is returning null value below is the section the iID is null.Once debugger reaches this it goes to exception as its result is NULL. How to control debugger from going to the exception.
[Code]...
View 2 Replies
Nov 12, 2011
From r In ReceiptLines
Where
r.RECEIPT.RECEIPTDATE >= _reportStartDate
And r.RECEIPT.RECEIPTDATE <= _reportEndDate
[Code].....
I am fetching all departments and their sales, average, count from the ReceiptLine, Receipt, ReceiptDiscount tables. The problem i am facing is, if i remove where discount > 0, I am getting null exception. But if I include that, then I only get sales that has discount. How would I write query that bring all sales less discount (if it has one).
View 2 Replies
Jun 24, 2011
I have the following SQL:
[Code]...
I want to put it (the select count statement) in this LINQ statement so I can get the sales count in my linq statement: Dim TheLeads = (From L In DB.Leads Where L.IsDeleted = False Select L).ToList() Is this possible to do in LINQ?
View 1 Replies
Jul 9, 2010
I develop in VB and often I'm writing long TSQL statements in SSMS but getting them into VB is a bit of a pain. I like SSMS during development because I can change the query / structure and test it when there are problems. Some of them become stored proceduresand some of them I just keep in the code.
I like keeping them in the code because when I have a problem with something, I can GREP my projects directory and find the code that does something (that I've forgotten) or someone asks something about how something works, I can look it up. I've yet to find a GREP for stored procedures. Is there such a thing?I've read in a "best practices" white paper how we're not supposed to concatenate super long strings with & _
[code]...
C# handles these things much better than VB. I'm wondering how I can keep my code together during development and searchable later. I had my hopes up when VS 2010 offered implicit line continuation, that this would help my multi line SQL procedures.Again, I was mistaken. Perhaps I could write it all on one line. I'd be interested in knowing if there's a feature that I am not aware of. Well there are certainly many features which I'm not aware of, but I was referring to "long strings".
View 2 Replies
Sep 30, 2011
I am looping through the rows of a DataRowCollection and assigning the fields of each row to a variable. I always get "Input string was not in a correct format" no matter how I cast this. I just need gapCd to contain 0 if the field is null or the value otherwise. It seems the IsDbNull is not returning true properly. I've also tried DbNull.Value comparisons with no l
View 1 Replies
Jun 23, 2010
A co-worker wants to convert the following IfThenElseIf statement into a Select Case statement.
Explain why this is not possible.
If temperature = 100 Then
X = 0
ElseIf population > 1000 Then
X = 1
ElseIf rate < .1 Then
X = -1
End If
View 7 Replies
Mar 14, 2011
This is related to my last question.Even though Access considers Nulls and zero-length strings (ZLS) separate values, often developers wish to treat them as equivalent for the sake of an If...Then statement or some other stretch of code. One way to accomplish this is to write code like this:
' Not so efficient
If IsNull(varAge) or varAge = "" Then
' do something
[code].....
View 4 Replies
Sep 12, 2010
When trying to use Try/Catch in my code the INSERT statement does not work, but when I comment out the Try/Catch it works fine with no error. The section of my code is below:
[Code]...
View 2 Replies
Jan 12, 2010
How can I change the following code to write to the database null instead of empty strings?
Public Sub SetChangeRequest(ByVal referenceLeaseID As Integer, _
ByVal referenceCustomerID As Integer, _
Optional ByVal custUnitNum As Object = Nothing, _
[CODE]...
View 6 Replies
Mar 29, 2012
I am trying to call an external C++ function using VB.NET (answers in C# are fine, I can convert back) which returns a list of device names. This comes in the form of a pointer to a null-terminated array of null-terminated char arrays (correct me if I've misunderstood what I'm dealing with) with a function signature:
long GetNames(char*** names)
Most of the answers to similar questions I've found have involved something more like:
long GetNames(char** names, int length)
It appears from what I've read that I need to give it an IntPtr but I'm uncertain as to how this should be initialized, and in my case I don't know the length of the array. At the moment, in the VB module, I have:
<DllImport("MyExternal.dll", CallingConvention:=CallingConvention.Cdecl)> _
Function GetNames(ByRef names as IntPtr) As UInteger
End Function
I've tried calling the function by passing an IntPtr initialized as IntPtr.Zero or using Marshall.AllocHGlobal, but I always get an AccessViolationException.
Any ideas on how I should be calling this function and how I get the return value into a managed string array would be greatly appreciated.
EDIT: I've found a sample function call in native C as follows:
char **tmplist;
GetNames(&tmplist)
View 2 Replies
May 28, 2009
Now that I've switched over to parameterized queries, everything is great except along with handling all the special characters that would otherwise disrupt an all text SQL stament, it's handling the word NULL. My code basically does this for every variable used in the entire SQL statement:
If strCallName = "" Then
strCallName = "NULL"
End If
[Code]...
and that worked great for my old all text ones because leaving the word NULL out of single quotes would tell it yeah, that's actually null and pass it a true null value. Now with parameterized ones, it's literally passing it the word NULL I tried just leaving the string empty but then when I try to execute the command, it says that the parameter cannot be a zero length string. So how do you give a null value to a a parameterized query and make it actually send that to the database in the statement? Would strCallName = vbNull work or would it still say it's a zero length string?
View 10 Replies
Feb 4, 2011
I am still a newbie with VB. If I return a number the if statement works fine, with opening the new form and updating it as needed. Id the if statement returns a Null from the database it crashes.
[code]...
View 2 Replies
Mar 23, 2012
have a table with following fields
Emp_id varchar(50)
Emp_Name varchar(50)
Emp_surname varchar(50)
[code].....
View 1 Replies
Apr 2, 2009
I need to use a SELECT statement to select all data under a column name (like all employee names) and then to add the values to a listbox.
I usderstand that the select statement must be:
"SELECT * FROM EmployeeTable WHERE EmployeeNames LIKE *" (at least I think)
but then what? combobox1.fill ("EmployeesTable") ?
View 9 Replies
Apr 8, 2012
I have a table which contains a list of products for a company. They input data about how much stock they have and also the level at which they want to be reminded that they need to order new stock.
[Code]...
I want to list all the true results in a form which the user is then able to navigate through. What would be the best way to go about doing this?
View 2 Replies
Feb 1, 2010
Is there an easier way to do this?
The .ColorReportedDate, .ColorTypeKey, .IsColorKeyNull, etc are from my strongly typed dataset row.
If .IsColorKeyNull Then
.ColorReportedDate = Now()
ElseIf cboColorType.SelectedValue <> .ColorTypeKey Then
.ColorReportedDate = Now()
End If
View 5 Replies
Jun 8, 2009
I have the following query that I use to create a new record in a database. If I leave any field on the form blank, for example, it throws the error "Memo.Lastname" cannot be a zero length string. Other than that the query executes appropriately.
Private Sub cmdSaveMemo_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cmdSaveMemo.Click
If Len(Me.txtMemoID.Text) <> 0 Then
Memo.SaveMemo
[Code] .....
View 2 Replies
Oct 14, 2009
In every date field I have, I cannot update it if it is null. For instance, I have the following query and it will throw an error if the date fields are Null or zero lengths. Any ideas? I try and parse and get the following error: String was not recognized as a valid DateTime. if i don't, it throws a null error.[code]
View 7 Replies
Jul 1, 2011
How can i return null values in if statement above. This statement is not working if condtion is false its retruning me 0 value even if condition is false
dim d as nullable(0f Decimal)
d=(if(_dr("value") isnot dbnull.value,Convert.toDecimal((_dr("value")),Nothing)
View 6 Replies
Feb 9, 2009
I have a "if This > That then" statement, that doesn't seem to work, as it triggers regardless that "This" is smaller than "that". Does it matter that these variables are marked as strings vs integers?
View 8 Replies
Nov 4, 2011
how I can select the text in a textbox/richtextbox that is between two designated strings? Like, for instance, lets say "<b>" is the first string, and "</b>" is the second. The text entered in the textbox is "<b>hello world!</b>". How would I get it to programmatically select the "hello world"?
Also, I would like to know of a way to select the text between the two strings and the strings themselves, so that, inputting "Hello <b>world! I'm</b> awesome!", "<b>world! I'm</b>" would be selected.
View 1 Replies
Jan 28, 2012
I want to select all records in a table and only 1 field in 2 related tables so my query is:
strSQL = "SELECT CL.Name, CL.FirstName, T1.Description, T2.Description " & _
" FROM Clients CL, Table1 T1, Table2 T2 " & _
" WHERE CL.Tab1Cod = T1.Code " & _
" AND CL.Tab2Cod = T2.Code"
All works and all records are selected only if fields CL.Tab1Cod and CL.Tab2Cod contain data but if one or both contain NULL value then NO record are selected. how can I test in a query statement if a filed contains a null value and, in this case do not expose the related field ?For example if CL.Tab2Cod is null the field T2.Description will be empty but all other fields are exposed.
View 4 Replies
May 25, 2011
I'm setting up a basic TCP client/server system and I'm wondering how the back-and-forth communication should ideally work. In my specific situation, the client will have to submit login credentials, and make specific requests from the server (the server interacts with a remote database).Here is what I would THINK to do.. and I'm just looking from input from other devs.
SERVER: Wait for new connections
CLIENT: Connects to server
CLIENT: Once connected, send login credentials with an instruction code.
[code]....
And another scenario.. let's say the client wants to request a log of what's happened on the server recently (the text of a console application window).
CLIENT: Send request message to server for recent log
i.e. $REQ$,105
SERVER: If instruction = $REQ$ and request id = 105, get window text and send to client
i.e. $DATA$,105,<data here>
CLIENT: If instruction = $DATA$ and data id = 105, parse data and display to user
The data being requested could be plain text, or a .net datatable translated into XML, etc. It could be a variety of things.
View 1 Replies
Feb 16, 2011
Having trouble importing a value from a .csv file to a .csv file. This this done because data needs to be massaged and the data file renamed. This is a user interface. Do not want to have the user touch the .csv in any way except upload to the application so the applicaiton can do manipulation. Either way here is the issue.
There is a column in the original .csv file that contains a zip code. The first row of the .csv has a value of 17003 in the zip code column. In the second row of the .csv the zip code column has a value of 11746-9984. The value in the zip code column from the second row always comes back as a Null value. My guess is that the OleDbDataAdapter is defining some kind of Schema. Again, the first row containing the zip code column probably insists the value must be numeric. When the data table field is established it probably gets defined as numeric. If that is true and the second row of the .csv zip code column is not numeric that must cause the zip code value to be Null.[code]...
View 2 Replies
