MySQL Parameters / AddWithValue - How To Avoid To Check For Nulls
Sep 15, 2010
Let's say I have a MySql stored procedure that inserts a record with some nullable CHAR fields. In VB.NET if I don't check for Nothing (or Null in other languages), I get an exception from the db driver, so I write:
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("_name", if(name Is Nothing, "", name)).Direction = ParameterDirection.Input;
And this is the first thing I don't like; I'd like to pass Nothing and the driver knows it has to put NULL in the Db. But then, in the stored procedure, I have to check back the field if it is empty:
INSERT INTO mytable
(name, -- other 200 char fields)
VALUES
(NULLIF(_name, ''),
-- other 200 char fields
)
I have to check for nothingness/emptiness twice. Is there a better, more direct, way? Even worse, for non reference types (i.e: Integers) the check for nothingness makes no sense, since a primitive type can't be nothing (yes, I could set an Integer to a non meaningful value, say -1 for a positive id, but...).
Basically Commands has Parameters and parameters has functions like Add, AddWithValue, and etc. In all tutorials i've seen, i usually noticed that they are using Add instead of AddWithValue.
[Code]...
since it saves my coding time. So which is better to use? Which is safe to use? Does it improves performance?
I use codes below to inserts values to database by executing stored procedure.
Dim connstring As String = "dsn=test" Dim oConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(connstring) Dim com As New Odbc.OdbcCommand("{? = call sp_test1(?,?,?,?)}", oConn)
I am getting null values in Environment, ApplicationName, ApplicationPath, ExceptionData when inserting rows in a SQL Server 2005 DB. My log4net configuration is as follows :
I have the following VB code.Dim cmd As New MySqlCommand("SELECT code FROM decoder WHERE ann_id = @aid", conn) cmd.Parameters.AddWithValue("@aid", 1)Dim reader As MySqlDataReader = cmd.ExecuteReader()
The reader.Read() then gives me nothing, however, if I replace the code by.Dim cmd As New MySqlCommand("SELECT code FROM decoder WHERE ann_id = 1", conn) Dim reader As MySqlDataReader = cmd.ExecuteReader() reader.Read() gives the correct result. Tried using Add instead, doesn't help. What am I missing here?
Is it possible to use "IN" operator together with OleDbCommand.Parameters.AddWithValue ?
I am using Visual Studio 2008 and Microsoft Access 2000.
I have search online but find no solution on it. Below is my code:
Dim conn As New OleDbConnection Dim cmd As New OleDbCommand Dim Adapter As New OleDb.OleDbDataAdapter Dim strSQL As String Dim dsDoc As New DataSet
[Code]...
Where there is 2 Document No inside parameter @Doc , i can't get any of them. When there is only 1 Document No, then it is ok.Is it possible to use "IN" operator together with OleDbCommand.Parameters.AddWithValue ?
In the example below, what would you name the parameter given that it is used to initialize the property FromDate?For class constructor methods, I like to have the name of the constructor parameter variable match the name of the property which is being initialized. For example, the parameter "fromDate" is used to initialize the module level variable "_FromDate" with the statement _FromDate = fromDate. Likewise, I could have alternatively written Me.FromDate = fromDate.
Proponents of C#'s case sensitivity would probably say that using a leading lower cased letter for the param variable name, which I believe is MS convention, is an acceptable approach to distinguish it from the Property of the same name but different casing.
However, VB is not case sensitive, which I generally appreciate. In the following example, I am using a param name that matches the property name, 'fromDate," and VB refers to the local instance when there is ambiguity. However, many would probably argue that this "ambiguity" introduces the opportunity for the developer to get confused and not realize which variable is being used. For example, my intent below was to have TWO params passed in, "fromDate" and "toDate" but I accidentily ommited one and as a result, the VB.NET did not warn me of the mistake because it assumed that the statement _ToDate = ToDate was equivalent to _ToDate = Me.ToDate instead of informing me that the variable on the right side of the assignment statement was undeclared.
Public Class Period Property FromDate As Date Property ToDate As Date
[code]....
In my judgement, we should have a convention for prefixing all parameter variable with a prefix, but hasn't the use of prefixes been discouraged by Microsoft? For example:
Public Sub New(ByVal paramFromDate As Date, paramToDate As Date)
..or maybe it could be shortened to pFromDate, pToDate...
For the last couple of days I've been having problems inserting data into my MySQL database. I'm using parameters to insert the data and have done so in the past without any problems, but for some reason I haven't been able to get it to work this time.I have a form that the user inputs data to be inserted into the database. This data is then pushed into an array and sent to my class (CastProduction.VB) via a Public Sub. The data than pushed into a datarow and than pushed into a datatable. Once this is done I send the dataset and sql insert statement to a private sub that inserts the data into the database.
DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `Check1`( IN P_Name VARCHAR(50) ,OUT P_Id INT
[code]....
as various columns I need use VB .Net to call this procedure, insert value into the database and get the ID (primary key - company ID).Whenever I insert a row, I need to retrieve the ID. Right now, I am able only to insert the row, but don't know how to call a SP with both input and output parameters from VB.
Just have a quick question really, I am just making a simple application that allows me to check if a user exists in SQL here's my code
Public Function Check(ByVal textbox1, ByVal textbox2) As String Dim comm As New SqlCommand Dim conn As New SqlConnection Dim Adap As New SqlDataAdapter [Code] .....
And when it comes to the Comm line I get this error Operator '&' is not defined for string "Select * from Login where Userna" and type 'TextBox'. I've tried removing the "&" and well i cant figure it out.
I was thinking I should just change the value to "" when a null value has been read from the datasoure. But then I thought maybe there is a property that allows this. is there?
Private Sub QhBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles QhBindingNavigatorSaveItem.Click Me.Validate()
I need to search a buffer for a string of characters.The buffer is essentially a binary file (it's an image) and contains lots of chr(0) characters.The string I need to locate also contains chr(0) characters. In fact it is the following string:
chr(100) & chr(0) & chr(0) & chr(163)
This is a marker in the image file for items I need to process.So I though I would do the following:
Dim sFileBuffer as String = system.io.file.readalltext("myimagefile") Dim sLookFor as String = chr(100) & chr(0) & chr(0) & chr(163) Dim iLocation as Integer = sFileBuffer.IndexOf(sLookFor)
The problem is that, although the characters in sLookFor definitely exist inside of sFileBuffer - IndexOf will always return -1 (not found).I tried the following:
Dim Marker() as Byte = {100, 0, 0 , 163} Dim sLookFor as String = System.Text.Encoding.UTF8.GetString(Marker) Dim sFileBuffer as String = system.io.file.readalltext("myimagefile", Encoding.UTF8) Dim iLocation as Integer = sFileBuffer.IndexOf(sLookFor)
But this appears to only find occurrences of Chr(100) & Chr(0) - not the full 4 character sequence I desire. It appears to see the second Chr(0) as a termination of the search string.I think the sticking point here is the null. Yes - I can string.replace(chr(0), Chr(255)) in both the sLookFor and the sFileBuffer and adjust my search accordingly - but that seems like a mess to me.Is there not a way to search a buffer of binary data? I've tried working with byte arrays - but there appears to be no way to easily search for a sequence of byte values - just individual elements in the array.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near and I don't know how does this happen because whenever I check the SQL that I have made including the inserted data, it shows no error.
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
I use Visual studio 2008 I use MySQL 5.1 I have created een database (test) table (table1 ) with field (Images) Data Type mediumblob size The maximum length of mediumblob is 16777215 (2^24 - 1) characters
I want to Check file size before save in MySQL table1.I want to avoid getting an error message when the image is too large for the mediumblobb field I now use the following code Dim conn As New MySqlConnection Dim cmd As New MySqlCommand
i m using admin levels for a program it uses a Mysql database i now if u use a local db that u can use a dataset but how to do it when u have to Connect to a Mysql and check if the colum admin = 1 ?[code]......
I've got a vb app that writes data to a remote mysql database.
When I run the app on a client that does not have the mysql connector it crashes (obviously)
How do I programatically check if the connector is installed on the client and throw up a nice error advising to install it - instead of the unhandled exception error - could not load mysql.data etc error?
how to properly connect to a MySQL database check if a login is valid.Basically i want someone to write me an example of how i connect to a MySQL database, check if username is correct, check if password is correct. If username and password is correct- it should return True as of boolean dim.
how to properly connect to a MySQL database check if a login is valid.Basically i want someone to write me an example of how i connect to a MySQL database, check if username is correct, check if password is correct. If username and password is correct- it should return True as of boolean dim.Otherwise it should return false.
I have a chat server / client system running, but I want users to authenticate with my vBulletin forum database.I don't want the client to do it, because it is possible to fake the 'successful login' and connect with a fake username. I want to send the username and password details to the server, and make the server check it with the local vBulletin MySQL database for the username and password.Is there a way to do this? To my knowledge vBulletin has a username and a password entries in the database which is salted, and the salt varies from user to user.
I'm writing a query to select all records that has any part of parameter. I have one table called Employees. Some people have name like this: John David Clark If the parameter is
[Code]....
I should be able to get result back as long as there's a match in the parameters. If I use Function Contains (q.FirstName & " " & q.LastName).Contains(employeeName), I will not get any result back if employeeName is "John Clark" Function Contains looks only for next words from left to right. It doesn't match a single word at a time. So that's why I used this in the Linq to SQL: