current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

So I am fairly new (read just starting) to PostgreSQL and VB.net. I have done research and am using the Npgsql.dll and Mono.Security.Protocol.Tls.dll as references to connect to the database.

|barcode|item|type|received|removed|on_hand|

|ien0001|thing1|type1| 1 | 2 | 3 |

|ien0002|thing2|type2| 4 | 5 | 6 |

|ien0003|thing1|type1| 7 | 8 | 9 |

Example of my table

Now the issue that I am having is that when I use myReader.GetString(0) the value being returned is always 0. Now if I hardcode this line to this below I will get the actual value that is in the database. I'm not sure if I am doing something stupid or if I am in over my head. Any advice would be great. I hope the question is clear. If not please let me know and I will try to rephrase. Also this is my first time posting so let me know if my formatting is awful.

Thanks

Also if I don't hardcode the barcode then (no pun intended) then the if statement is never executed.

Also I forgot to mention what I am trying to accomplish. I am trying to query the database using a VB variable called code. Then based on the results of the query (there will only be at most one result) extract a value from a column and save that to another variable.

If there is another way of doing this please let me know. Thanks

mySQLString = "SELECT On_Hand FROM total_inventory WHERE Barcode = 'ien0002' ;"

 code = "'ien0001'"   'code is a string
    myConnection.ConnectionString = "Server=###.###.###.###;Port=####;Database=inventory;User Id=inventory_user;Password=$$$$$$;" 
    myConnection.Open()
    myCommand = New NpgsqlCommand(mySQLString, myConnection)
    myCommand.ExecuteNonQuery()
    myReader = myCommand.ExecuteReader
    If myReader.Read() Then
    CurrentQty = CInt(myReader.GetString(0))
    MsgBox(":" & CurrentQty)
    End If
    myReader.Close()
    MsgBox(CurrentQty)
    myConnection.Close()
share|improve this question
    
Could you show the sql string when you don't hardcode the value? How do you build the command? –  Steve Jun 13 '13 at 15:16
    
Nevermind I am a moron, changed the if to a while and that fixed it. thanks, for the quick reply @Steve. When I don't hardcode it the string is [code]mySQLString = "SELECT On_Hand FROM total_inventory WHERE Barcode = " & code & ";" –  user2482595 Jun 13 '13 at 15:22

1 Answer 1

up vote 0 down vote accepted

Remove the single quote around the 

code = "ien0001"

and use a parameterized query. The Framework code is smart enough to recognize that you are passing a string and will put the correct quotes around your string without need to do it manually (and make errors)

So if my assumption is correct then you write

Dim code = "ien0001" 
mySQLString = "SELECT On_Hand FROM total_inventory WHERE Barcode = :code;"
myConnection.ConnectionString = "Server=###.###.###.###;Port=####;Database=inventory;User Id=inventory_user;Password=$$$$$$;" 
myConnection.Open()
myCommand = New NpgsqlCommand(mySQLString, myConnection)
myCommand.Parameters.AddWithValue(":code", code)
myCommand.ExecuteNonQuery()
myReader = myCommand.ExecuteReader
If myReader.Read() Then
    CurrentQty = CInt(myReader.GetString(0))
    MsgBox(":" & CurrentQty)
End If
myReader.Close()
MsgBox(CurrentQty)
myConnection.Close()
share|improve this answer
    
Okay, thanks. Do you know if there is a good place that explains parameterized queries? I read about them while trying to figure out how to keep sql injections from being an issue but everyplace that mentioned parameterized queries never really explained other than that it is helpful against injections and was better than using strict filters. Once again thanks for the help. Completely new to this site and SQL/postgresql/vb.net. –  user2482595 Jun 13 '13 at 16:56
    
Keeping it really basic. A parameterized query is a set of statements where you could find a string containing the sql statemente to execute and in which the values to pass to the database are expressed by placeholders (like :code above). These placeholders will be replaced by the actual value that you pass at the framework code via a collection of parameter objects. The parameter object contains information on type and direction (to the database, from the database) and values. At execution time the framework substitutes the placeholders with the corresponding values and format them correctly –  Steve Jun 13 '13 at 17:04
    
Wow, that's powerful. Thanks for all the help. I will have to look into that when I get off of work so that I can figure out how to implement it. One final question if you don't mind. If I am doing multiple queries should I open and close the connection for each query or leave the connection open until all queries are done? Also on that note is there a faster way to query than the way I have done? The device that I am using to update the database is timing out while the query runs even though the query runs in like 16ms. Once again thanks so much. You have saved me quite a bit of time. –  user2482595 Jun 13 '13 at 17:36
    
Never mind I found a solution to my problem. It was throwing an error in the database but the error was never shown to the screen. All is well : ) –  user2482595 Jun 13 '13 at 18:53
1  
For more examples about Npgsql check out: manual.npgsql.org I hope it helps. –  Francisco Junior Jun 16 '13 at 0:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.